04- MySQL Administration

MySQL provides various administration features/tools to configure the MySQL server. These includes server maintenance, database backup, MySQL security, user management, start up/shutdown, replication management, Configuration of parameters (resource management), check performance/ status etc.

Creating and grant permissions to a User

Users information of MySQL exists in a database named mysql and the tables named user.Privileges of user exists in different tables according to privilege level on different database although the main privileges are in user table itself.

There are two ways for creating a basic user for accessing mysql server:

  1. Create user command:

To create a new userthat connects from with the password user_password , you can use the statement as follows:

     CREATE USER username@localhostIDENTIFIED BY ‘user_password’ ;

To allow user to connect from any host you use the% wildcard, which means any host.

     CREATE USER username@%IDENTIFIED BY ‘user_password’ ; 

  1. Insert command:

To create a new userthat connects from with the password user_password , you can use the statement as follows:

     INSERT INTO user (host,user,password)VALUES('localhost',’username’,PASSWORD(‘user_password')); 

To allow user to connect from every host you can use the % wildcard, which means all hosts.

      INSERT INTO user (host,user,password)VALUES('localhost',’username’,PASSWORD(‘user_password'));

The above two methods create the basic user for connecting to MySQL server. Although MySQL provides the user management according to the privileges given to the specified user for the security measures.

For example: If your sales teamneeds to analyze the data then you should create a read user for that team and not giving all other privileges to them. For read only you have to give only select permissions to them with the following command:

CREATE USER user_sales@%IDENTIFIED BY ‘sales password’ ;
Grant select on *.* to user_sales@% ;
Flush privileges;

This grant statement gives only select permission on all the database for analyzing purpose to user_sales user.

MySQL Administration provides various kinds of privileges to separate out the user and hence improve the security through proper user management. Some important privileges are as follows:

Select_priv:  For only reading/selecting the data.

Insert_priv: For only insertion of the data on the databases.

Delete_priv: Allow only deleting the data.

Create_priv: For creating a database or a table on MySQL server.

Drop_priv: For dropping any specified table/database.

Grant_priv: Allow to give permission to any other user or able to create a new user.

File_priv: Allow to create a file from the output command and write the data out from the MySQL sever.

Alter_priv: Allow to change the schema of the existing tables.

Process_priv: To check the running process on the server, basic command is show processlist.

Shutdown_priv: Allow user to stop/start the MySQL Process.

Index_priv: Allow user to create indexes on the tables.

Reload_priv:To apply the changes in user management at that time.

All the above privileges can have a value of yes or no by the flags= ‘Y’ or ‘N’. You can give the privileges using the grant statement.

For Example if we need to create a user having select, insert, update privileges on the test database only for localhost.

       Grant select, insert, update on test.* to user123@% ;

If a user with name user123 is not exists in the database then a new user with blank password is created automatically. So the grant command also works to directly create the user as:

       Grant select, insert, update on test.* to user123@% identified by password(‘password123’);

Handling Server Status 

MySQL Administration allows to start, stop, restart and checkthe status of the MySQL server. It provides a tool called mysqladmin to do so with the following commands:

To shut down the running MySQL server:
root@localhost#./mysqladmin -u root -p shutdown
Enter password:********

To start the MySQL server:

And if you already add the mysql.server(mysqld daemon) file to default location /etc/init.d as specified in installation steps then you can start, stop, restart and check the status as:

For start

      root@localhost# /etc/init.d/mysqld  start

For stop

      root@localhost# /etc/init.d/mysqld  stop

For restart

      root@localhost# /etc/init.d/mysqld  restart

For checking the status

     root@localhost# /etc/init.d/mysqld  status

NOTE:  Use the above methods to stop the MySQL server and not kill the process ID directly as it causes the MySQL tables to crash which is very difficult to repair.

Administrative Commands

There are certain administrative commands run by database administrator on routine basis or on requirement basis of the business or application. For example some business/Application requires there tables to be checked at every night for data inconsistency, so we need to run check table and optimize them for eliminating any holes at the physical level for best performance. But we can’t run all commands in every scenario because in some cases the database is of 24*7 hour nature and there is no downtime so we can’t run optimize and check at the runtime to avoid transaction locking.

There are some commands related to security like updating passwords, updating bind-address, changing permission/grants and there are some commands related to monitoring the database system performance like show variables, show processlist, show full processlist, bechmarking etc.

Update user password:You can use the update command to change the password of the user in mysql as:

       Update mysql.user set password = ‘NEW_PASSWORD’ where user = ‘username’ and host=’hostname’

NOTE: Always give username and hostname both to avoid updation of more than one row.Because the username and hostname define uniqueness on combination.

Set variables: SET is a admin command to set variables in the MySQL server. There are two types of variables session and global variables that can be set with this simple set command. For example you can skip slave error in mysql as:


Analyze table: This command is used for statistics of the table. It checks for the key distribution of the table and then stores the relevant values into the information_schema (contains meta information of the tables). This command is generally used after alter queries to change engine or some other column types. Command:

       Analyze table table_name;  

Repair table: This command is use to repair the table as the name indicates. Repair in MySQL means to correct any faulty key/index in the table or any row corruption due to which whole table act abnormally. NOTE: Repair table may changes your row count and deleted some corrupted data. The repair command:

      Repair table table_name;   

Optimize table: This command is used to optimize table for better performance, it eliminates any present holes in the data or index so that provide fast access to the data, rebuilt indexes in the database. Optimize can be run as:

      Optimize table table_name;       

Kill query: You can kill a query during its execution. For this you can pass the kill query with query id as the parameter in the command. The query id is shown in the processlist output. For example:

mysql> show processlist;
| Id | User | Host            | db        | Command | Time | State       | Info                             |
| 19 | root | localhost:55107 | town_list | Query   |    0 | System lock | alter table toen engine='innodb' |
2 rows in set (0.00 sec)
Here the query id is 19 for the command “ alter table toen engine=’innodb’ “.
So you can kill the query in middle as:
mysql> kill 19 ;

Show grants: Grants means the privileges to a mysql user, you can see the grant of any user by the help of show grants command, for this you need to pass the user and its host as it uniquely defines the user. The show grants command can be used as:

mysql> show grants for greg@xxx.xxx.xxx.xxx ;
GRANT SELECT, INSERT, UPDATE, DELETE, DROP on `test`.* TO ‘greg'@'xxx.xxx.xxx.xxx'
Here greg is the username and xxx.xxx.xxx.xxx is the host name for greg.

Show variables: This command is used to check the system variables at the current time. These are default variables or we can set them in the configuration file of MySQL server. The command to show the session or global variables are:

For global:
mysql> show global variables like 'join_buffer_size' ;

Variable_name     Value    
join_buffer_size   536870912
1 row in set (0.00 sec)

For session:
mysql> show session variables like 'join_buffer_size' ;
Variable_name     Value    
join_buffer_size   12480970
1 row in set (0.00 sec)


Like us on Facebook