Month: November 2012

1. User Management

# Create a database (replace '<DB NAME>' to your Database name)
CREATE DATABASE <DB NAME> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# Create a user and grant the database's permission to it
grant all privileges on <DB NAME>.* to <USER_NAME>@localhost identified by '<PASSWORD>';
# In MySQL 8.0 and later, the use of the IDENTIFIED BY clause with GRANT is deprecated. 
# Instead, you should use CREATE USER and ALTER USER statements to create and modify users,
CREATE USER '<USER_NAME>'@'localhost' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON <DB NAME>.* TO '<USER_NAME>'@'localhost';
flush privileges;

# Create a user and grant parts of the database's permission
grant select,update on <DB NAME>.* to <USER_NAME>@localhost identified by '<PASSWORD>';
CREATE USER '<USER_NAME>'@'localhost' IDENTIFIED BY '<PASSWORD>';
GRANT select,update ON <DB NAME>.* TO '<USER_NAME>'@'localhost';
flush privileges;

# See the User's Permission
show grants for <USER_NAME>@localhost;

# Delete a database
drop database <DB NAME>;

# Delete a user
DELETE FROM mysql.user WHERE User="<USER_NAME>" and Host="localhost";
flush privileges;

# Change Password for a User
update mysql.user set password=password('<NEW_PASSWORD>') where User="<USER_NAME>" and Host="localhost";
flush privileges;

2. DATABASE and Table Management

# Check a DB's Charset and Collection
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '<DB NAME>';


# Alter a DB's charset and Collection
ALTER DATABASE <DB NAME> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


# Check the DB size
SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables GROUP BY table_schema; 


# Check the Table size (replace myDB with your DB name)
SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "<DB NAME>" ORDER BY (data_length + index_length) DESC;


# Optimize Table
OPTIMIZE TABLE <table name>;
OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

# Optimize Tables Using the Terminal
mysqlcheck -u root -p --auto-repair --optimize --myDB  # For a Single DB
mysqlcheck -u root -p --auto-repair --optimize --all-databases

3. Optimize Table

use <DB NAME>;

# Find tables need to be optimized
select table_name, data_length, data_free from information_schema.tables
where table_schema='<DB NAME>' order by data_free desc;
+-----------------------------------+-------------+-----------+
| table_name                        | data_length | data_free |
+-----------------------------------+-------------+-----------+
| wordpress_commentmeta             |       44028 |     19784 |
| wordpress_comments                |     2005588 |     13024 |
| wordpress_postmeta                |      569124 |       112 |
| wordpress_links                   |        1220 |         0 |
| wordpress_aioseo_posts            |       16384 |         0 |
| wordpress_terms                   |       38876 |         0 |
| wordpress_tla_rss_map             |           0 |         0 |
| wordpress_actionscheduler_groups  |       16384 |         0 |

the output shows some general information about the table. The following two numbers are important:

• Data_length represents the amount of space the database takes up in total.
• Data_free shows the allocated unused bytes within the database table. This information helps identify which tables need optimization and how much space will be released afterward.

optimize table wordpress_commentmeta, wordpress_comments;

# Check tables again
select table_name, data_length, data_free from information_schema.tables
where table_schema='<DB NAME>' order by data_free desc;
+-----------------------------------+-------------+-----------+
| table_name                        | data_length | data_free |
+-----------------------------------+-------------+-----------+
| wordpress_commentmeta             |       44028 |         0 |
| wordpress_comments                |     2005588 |         0 |
| wordpress_postmeta                |      569124 |       112 |
| wordpress_links                   |        1220 |         0 |
| wordpress_aioseo_posts            |       16384 |         0 |
| wordpress_terms                   |       38876 |         0 |
| wordpress_tla_rss_map             |           0 |         0 |
| wordpress_actionscheduler_groups  |       16384 |         0 |

The results of the optimization change the values of data_length and data_free of the optimized table. Both values are lowered, indicating:

1. The optimization freed the unused allocated memory.
2. The overall memory of the database is lower because of the released space.

3 Comments