Anvesanam Infotech

MySQL Administration

In this section you will find a lot of useful MySQL administration tutorials including MySQL server startup and shutdown, MySQL server security, MySQL database maintainance, backup and replication.

Getting Started with MySQL Access Control System

Summary: In this tutorial you are getting to know MySQL Access Control System and various privileges tables in MySQL.
MySQL implemented a sophisticated access control and privilege system to allow you to create comprehensive access rules for handling client operations and prevent unauthorized clients from accessing the database system.
The MySQL access control has two stages when a client connects to the server:

• Connection verification: A client, which wants to connect to the MySQL database server, needs to have a valid username and password. In addition, the host, which the client connects from, has to be match with the host in the MySQL grant table.
• Request verification: Once a connection established successfully, MySQL checks the privileges to verify whether the client has sufficient privileges. It is possible to check privilege at database, table, and even field level.

There is a database called mysql is created automatically by MySQL installer. The mysql database contains five main grant tables:

• user: contains user account and global privileges columns. MySQL uses this table to either accept or reject connection from a host. A privilege granted in user table is effective to all databases on the server.
• db: contains database level privileges. MySQL uses this db table to determine which database user can access from which host. A privilege granted at database level in db table applies to the database and all objects belong to that database such as table, view, stored procedures.
• table_priv and columns_priv: contains table and column level privileges. A privilege granted in table_priv table applies to the table and all its columns while a privilege granted in columns_priv table applies only to a specific column.
• procs_priv: contains function and stored procedure privileges

All of privileges of MySQL are controlled by those grant tables. And from those grant tables you can create flexible access rules to meet your requirements.

MySQL Create User

Summary: In this tutorial, you will learn how to create user in MySQL by using CREATE USER and INSERT statements.
In MySQL you specify not only who can connect to a database server but also from where. Therefore account in MySQL consists of username and hostname, where user connect from, which separated by the character ‘@’. If user ‘root’ connects from host ‘mysqltutorial.org’ to the database server the account name would be ‘root’@’mysqltutorial.org’. This allows you to setup multiple accounts with the same name but connect from different hosts and have different privileges. In addition, you have control to setup account to connect from either specific or broad a set of hosts. The username and host are stored in the grant table called user.

Create new user by using MySQL CREATE USER statement

MySQL provides you CREATE USER statement to allow you to create a new user in a database server. The syntax of the CREATE USER statement is as follows:

1

CREATE USER user IDENTIFIED BY password

User account in the format ‘username’@’hostname’ is followed by the CREATE USER clause. The password is specified after the IDENTIFIED BY clause. The password must be in plaintext. MySQL will encrypt the password for you when it saves the user into the user table. For example, to create a new user dbadmin that connects from localhost with the password ‘CrEate-User’ we use the CREATE USER statement as follows:

1	CREATE USER ‘dbadmin’@‘localhost’ IDENTIFIED BY ‘CrEate-User’;

To allow user to connect from any host you use the wildcard ‘%’ that means any host.

1	CREATE USER ‘superadmin’@’%’ IDENTIFIED ‘Secured’;

The wildcard ‘% ‘works like in the SQL LIKE statement. So in order to allow user to connect from any host in the mysqltutorial.org you use wildcard ‘%’ as follows:

1	CREATE USER ‘mysqladmin’@’%.mysqltutorial.org’ IDENTIFIED ‘SecurePass704’;

Note that you can also use the wildcard ‘_’ in the CREATE USER statement as well. If you omit the hostname part of the account, MySQL still accepts it and allow user to connect from any host. The quote is very important especially when the account contains special characters such as ‘-‘ or ‘%’. If you accidentally quote the account name like ‘username@hosname’, MySQL will create a user with username username@hosname and allow you to connect from any host that is not expected. MySQL CREATE USER statement only creates a new user and does not grant any privileges to that user. If you want to grant privileges to the user you use the MySQL GRANT statement.

Create new user by using INSERT statement

A less common way to create user is using INSERT statement to insert a new record into the grant table user. By doing this way, you need to use PASSWORD function to encrypt the password by yourself before insert it into the table.

1	INSERT INTO user (host,user,password)

2	VALUES('localhost','dbadmin',PASSWORD(‘CrEate-User’));

In this tutorial, you’ve learned various way to create user in MySQL by using CREATE USER and INSERT statement.

MySQL Changing Password for Accounts

Summary: In this tutorial you will learn how to change or reset password for MySQL account in various ways by using the UPDATE, SET PASSWORD and GRANT statements.
MySQL provides several ways to allow you to change or reset password of a MySQL account. Before changing the password, you have to answer the following questions:

• Which user do you want to change the password?
• From host, which user connects to, you want to change the password? 
• Do you notify the related applications which are using the MySQL account that you are planning to change password? Otherwise those applications will lose the connection to the database and stop functioning.

The first way to change the password is by using the UPDATE statement to update the User and Host values for account user table in the database MySQL. And then you need to run the command FLUSH PRIVILEGES to reload privileges from the grant table in the mysql database.
Suppose you want to change the password for user mysqltutorial from host mysqltutorial.org to Secret1970, you need to perform the following queries:

1	USE mysql;

2

3	UPDATE user

4	SET password = PASSWORD('Secret1970')

5	WHERE user = 'mysqltutorial' AND

6	host = 'mysqltutorial.org';

7

8	FLUSH PRIVILEGES;

It is important to note that we've used the PASSWORD() function to encrypt the plain text password to store in the password column of the grant table. Here is an example of using password function to return an encrypted format of plain text password.

1	SELECT PASSWORD('Secret1970') AS encrypt_password

*16A54B5EE15C823362FAE7F64409A8F8C8501DD6

The second way to change the password is by using SET PASSWORD statement. You use the MySQL account format as user@host to update the password. If you need to change password for other account, you need to have UPDATE privilege. You don't need to call FLUSH PRVILILEGES to reload privileges from grant table.
Here is the query for changing password using SET PASSWORD statement.

1	SET PASSWORD FOR 'mysqltutorail'@'mysqltutorial.org' = PASSWORD('Secret1970')

The third way to change the password is by using GRANT USAGE statement with an IDENTIFIED BY clause, in which you specify the plain text password instead of PASSWORD function. Here is the query to change the password by using GRANT USAGE with the IDENTIFIED BY clause:

1	GRANT USAGE ON *.* TO ‘mysqltutorial@mysqltutorial.org IDENTIFIED BY ‘Secret1970

In case you want to reset the root account's password because you forget it, you need to force the server to stop and restart without using grant table validation.

 

How to Use MySQL GRANT to Grant Privileges to Account

Summary: In this tutorial, you will learn how to use MySQL GRANT statement to grant privileges to accounts. In order to follow this tutorial, we recommend that you read the ‘Getting started with MySQL access control system" and "How to create a user in MySQL" tutorials first.

MySQL GRANT Statement Explained

In order to give access privileges to an account, you use the MySQL GRANT statement with the syntax as follows:

1	GRANT privileges (column_list)

2	ON [object_type] privilege_level

3	TO account [IDENTIFIED BY 'password']

4	[REQUIRE encryption]

5	WITH with_options

If the account exists, MySQL modifies its privileges. If account does not exist, GRANT statement creates a new account with the specified privileges. Besides granting privileges to the account, the GRANT statement also sets other account's characteristics such as limit on access to the database server resources and use secure connection. If you want to grant a privilege to an account, you must have that privilege and GRANT OPTION privilege. We will examine the MySQL GRANT statement in detail as follows:

• privileges indicates the privileges to assign to the account. For example the CREATE privilege allows user to create database and tables. You can assign multiple privileges at a time. Each privilege is separated by a comma. You can use the privileges in the table 1.1 below.
• column_list specifies the columns to which a privilege applies. Each column is separated by commas and listed within parentheses. The column_list is optional.
• privilege_level specifies the level at which the privileges apply. You can use global privileges, database-specific privileges, table-specific privileges, column-specific privileges…
• account specifies which account is being granted the privileges.
• password specifies the password to assign to the account. If the account exists, the GRANT statement replaces the old password. Like the CREATE USER statement, you use plaintext password followed by the IDENTIFIED BY. The IDENTIFIED BY clause is optional.
• After the REQUIRE clause, you specifies whether the user has to connect to the database server over secure connection using SSL.
• If you want the account to have the privilege to grant its own privileges to other accounts, you need to use WITH with GRANT OPTION. In addition, WITH clause is used to allocate the database server's resource that enable you to set how many connections or statements an account can use per hour.

The MySQL GRANT statement is normally used with the CREATE USER statement. You create a new user first before granting privileges to that user.

Examples with MySQL GRANT

Let's practice with some examples to have a better understanding of MySQL GRANT statement. If you want to create a superuser account that can do anything including being able to grant privileges to other user you can use the following queries:

1	CREATE USER 'super'@'localhost' IDENTIFIED BY 'SecurePass1';

2

3	GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

The ON *.* clause means all databases and all objects in them. The only limit here is that super can only connect to the database server from localhost, which makes the server more secured. To create a user that has all access in our sample database classicmodels and can connect from any host you use the following queries:

1	CREATE USER 'super2'@'%' IDENTIFIED BY 'SecurePass2';

2

3	GRANT ALL classicmodels.* TO 'super2'@'%' WITH GRANT OPTION;

You can grant multiple privileges at a time; a comma separates each privilege. For example, you can create a user to use SELECT, INSERT and UPDATE statements on our sample database classicmodels, you can use the following queries:

1	CREATE USER 'rfc'@'%' IDENTIFIED BY 'SecurePass3';

2

3	GRANT SELECT, UPDATE, DELETE ON  classicmodels.* TO 'rfc'@'%';

Available privileges to use with MySQL GRANT

Here is the table that lists all the available privileges in MySQL

Privilege	Description
ALL [PRIVILEGES]	Grant all privileges at specified access level except GRANT OPTION
ALTER	Allow to use of ALTER TABLE statement
ALTER ROUTINE	Allow user to alter or drop stored routine
CREATE	Allow user to create database and table
CREATE ROUTINE	Allow user to create stored routine
CREATE TABLESPACE	Allow user to create, alter or drop tablespaces and log file groups
CREATE TEMPORARY TABLES	Allow user to create temporary table by using CREATE TEMPORARY TABLE
CREATE USER	Allow user to use the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.
CREATE VIEW	Allow user to create or modify view
DELETE	Allow user to use DELETE
DROP	Allow user to drop database, table and view
EVENT	Allow user to schedule events in Event Scheduler
EXECUTE	Allow user to execute stored routines
FILE	Allow user to read any file in the database directory.
GRANT OPTION	Allow user to have privileges to grant or revoke privileges from other accounts
INDEX	Allow user to create or remove indexes.
INSERT	Allow user to use INSERT statement
LOCK TABLES	Allow user to use LOCK TABLES on tables for which you have the SELECT privilege
PROCESS	Allow user to see all processes with SHOW PROCESSLIST statement.
PROXY	Enable user proxying
REFERENCES	Not implemented
RELOAD	Allow user to use FLUSH operations
REPLICATION CLIENT	Allow user to query to see where master or slave servers are
REPLICATION SLAVE	Allow user to use replicate slaves to read binary log events from the master.
SELECT	Allow user to use SELECT statement
SHOW DATABASES	Allow user to show all databases
SHOW VIEW	Allow user to use SHOW CREATE VIEW statement
SHUTDOWN	Allow user to use mysqladmin shutdown command
SUPER	Allow user to use other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin command
TRIGGER	Allow user to use TRIGGER operations.
UPDATE	Allow user to use UPDATE statement
USAGE	Equivalent to “no privileges”

Table 1.1

In this tutorial, you've learned how to use MySQL GRANT statement to grant various privileges to users with different options.

 

Revoking Privileges from Users by Using MySQL REVOKE

Summary: In this tutorial, you will learn how to use MySQL REVOKE statement to revoke privileges from MySQL accounts.

MySQL REVOKE Syntax

In order to revoke privileges from an account you use the MySQL REVOKE statement. The syntax of MySQL revoke statement is as follows:

1	REVOKE   privilege_type [(column_list)]      [, priv_type [(column_list)]]...

2	ON [object_type] privilege_level

3	FROM user [, user]...

Let’s examine the MySQL Revoke statement in detail.

• You list a list of privileges you want to revoke from an account after the REVOKE keyword. Each privilege is separated by a comma.
• ON clause specifies the privilege level at that privileges are to be revoked.
• After FROM keyword, you specify the account that you want to revoke the privileges. You can list multiple users in FROM clause. Each user is separated by a comma.

In order to revoke privileges from an account, you must have GRANT OPTION privilege and privileges you are revoking.To revoke all privileges you use the following MySQL REVOKE Syntax:

1	REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…

 Note that to use the above syntax; you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.To revoke proxy user you use the REVOKE PROXY syntax as below:

1	REVOKE PROXY ON user FROM user [, user]...

A quick remind that a proxy user is a valid user in MySQL who can impersonate as another user therefore has all privileges of that user.Before revoking privileges of a user, it is better to check if the user has that privilege by using SHOW GRANT S statement as follows:

1	SHOW GRANTS FOR user;

MySQL REVOKE examples

Suppose account rfc has privileges SELECT, UPDATE and DELETE on our sample database classicmodels. If you want to revoke UPDATE and DELETE privilege from rfc you can do as follows:First we check the privileges of rfc by using SHOW GRANTS statement:

1	SHOW GRANTS FOR 'rfc'@'localhost';

GRANT SELECT, UPDATE, DELETE ON 'classicmodels'.* TO 'rfc'@'localhost'

If you do not followthe previous tutorial on granting privileges to user, you can first grant the privileges SELECT, UPDATE and DELETE for rfc connecting from localhost to the database classicmodels as follows:

1	GRANT SELECT, UPDATE, DELETE ON  classicmodels.* TO 'rfc'@'localhost';

 Second, we can revoke the UPDATE and DELETE privileges from user rfc as follows:

1	REVOKE UPDATE, DELETE ON classicmodels.*  FROM 'rfc'@'localhost';

Finally we can check the privileges of user rfc again by using SHOW GRANTS command.

1	SHOW GRANTS FOR 'rfc'@'localhost';

GRANT SELECT ON 'classicmodels'.* TO 'rfc'@'localhost'

 If you want to revoke all privileges of user rfc you can do it as follows:

1	REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'rfc'@'localhost';

If you check the privileges of rfc again by using SHOW GRANTS statement, you will see the user rfc has no privilege.

1	SHOW GRANTS FOR 'rfc'@'localhost';

GRANT USAGE ON *.* TO 'rfc'@'localhost'

Note that USAGE privilege means no privileges in MySQL.

When MySQL REVOKE takes effect

The effect of MySQL REVOKE statement depends on the privilege level as follows:

• Changes are made to the global privileges only effect next time client connect to the database server. All current connected clients do not affect by the changes.
• Database privilege’s change applies after the next USE statement.
• Table and column privilege’s changes apply to all queries issued after the changes are made.

In this tutorial, you've learned how to use MySQL REVOKE statement to revoke privileges from users.

 

Maintaining Database Tables

Summary: MySQL provides several commands to allow you to maintain database table more efficiently. Those commands enable you to analyze, optimize, check, and repair the database tables. In this tutorial, you will learn those MySQL command to maintain database tables.

Analyze table statement

Basically analyze table statement allows you to update cardinality of an index column. By updating cardinality, you can select data faster by utilizing all index features of database tables.
You will work with the employees and offices tables in our sample database. Let’s follow the examples below to understand more how analyze table statement works.
We can get the indexes information from employees table by executing the show index statement as follows:

1	SHOW INDEX FROM employees

MySQL gives you quite a lot of information about index in the employee table including cardinality; in this case the cardinality is 23.
Now we create a new index in the officeCode column to allow us to retrieve office and employee information faster by using join clause.

1	ALTER TABLE employees

2	ADD INDEX employee_offices (officeCode)

At this time the cardinality of the index is not updated, we can see this by performing the show index statement on employees table again.

1	SHOW INDEX FROM employees

The cardinality now is NULL.
So the cardinality of index is not updated automatically when index created and a new record is inserted to the table. In this case, we can use analyze table statement to trigger an update on cardinality of the index column by executing the following query:

1	ANALYZE TABLE employees

If you perform the show index statement on the table employees again you can see that the cardinality of indexed column employeeNumber and officeCode are updated.

Optimize table statement

While working with the database, you do a lot of changes such as insert, update and delete data in the database tables therefore it causes the physical of database table fragmented. As a result, the performance of database server is  degraded. MySQL provides you optimize table statement to allow you to optimize the database table to avoid this problem by defragmenting the table at physical level. The optimize table statement is as follows:

1	OPTIMIZE TABLE table_name

With this optimize table statement, you should run it often with the tables which updated frequently.
Suppose you want to optimize the employees table to make it defragmented, you can perform the following query:

1	OPTIMIZE TABLE employees

Here is the output

Table                    Op        Msg_type  Msg_text
-----------------------  --------  --------  --------
classicmodels.employees  optimize  status    OK      

Check table statement

Something wrong can happen to the database server such as server turn off unexpectedly, error while writing data to the hard disk and so on… All of these situations could make the database operate incorrectly and in the worst case it can be crashed. MySQL supports you to check database tables by using check table statement. Here is the syntax of check table statement:

1	CHECK TABLE table_name

The check table statement checks both table and correspondent indexes. For example, you can you check table statement to check the table employees as follows:

1	CHECK TABLE employees

And here is the output

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
classicmodels.employees  check   status    OK      

Check table statement only detects problems in a database table but it does not repair them. In order to do so you can you repair table statement.

Repair table statement

Repair table statement allows you to repair some errors occurred in database tables. MySQL does not guarantee that this statement can repair all errors which your database may have. The repair table statement can be written as follows:

1	REPAIR TABLE table_name

Suppose you have some errors in the employees table and need to fix, you can use repair table statement by performing this query:

1	REPAIR TABLE employees

MySQL will return what it has done with the table and the table is repaired or not. Here is the output you always want to see in such cases:

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
classicmodels.employees  repair  status    OK      

 

How to Backup Databases Using mysqldump Tool

Summary: In thistutorial you will learn how to use mysqldump tool back up MySQL databases.
MySQL GUI tools such as phpMyAdmin, SQLyog and etc often provide features for backup MySQL databases with ease. However if your database is big, the backup process could be very slow because the backup file need to be transferred across the network to your client PC. As the result, the backup process increases locking time therefore MySQL unavailability.
MySQL provides a very useful tool for backup or dump MySQL databases locally on server very fast. The backup file is stored in the file system in the server so you just need to download it when needed. The tool to backup MySQL databases is mysqldump. It is located in the root/bin folder of MySQL installation folder. The mysqldump is a program provided by MySQL that can be used to dump databases for backup or transfer database to another database server. The dump file contains a set of SQL statements to create and populate tables. In addition, the mysqldump can be used to generate CSV, delimited or XML files. In this tutorial, we will focus only on how to backup MySQL database by using mysqldump tool.

How to Backup a MySQL Database

To backup a MySQL database, the database first has to exist in the database server and you have access to that server as well. You can use SSH or Telnet to login to the remote server if you do not have remote desktop to it. The command to backup a MySQL database as follows:

1	mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

The parameter of the command above as follows:

• [username]: valid MySQL username.
• [password]: valid password for the user. Note that there is no space between –p and the password.
• [database_name]: database name you want to backup
• [dump_file.sql]: dump file you want to generate.

By executing the above command all database structure and data will be exported into a single [dump_file.sql] dump file. For example, in order to back our sample database classicmodels, we use the following command:

1	mysqldump -u mysqltutorial –psecret  classicmodels > c:\temp\backup001.sql

How to Backup MySQL Database Structure Only

If you only want to backup database structure only you just need to add an option –no-data to tell mysqldump that only database structure need to export as follows:

1	mysqldump -u [username] –p[password] –no-data [database_name] > [dump_file.sql]

For example to backup our sample database with structure only, you use the following command:

1	mysqldump -u mysqltutorial –psecret  -no-data classicmodels > c:\temp\backup002.sql

How to Backup MySQL Database Data Only

There is a case that you want to refresh data in staging and development system so the data in those systems are the same as production system. In this case you just need to export data only from production system and import it to staging and development system. In order to backup data only, you use option –no-create-info of mysqldump as follows:

1	mysqldump -u [username] –p[password] –no-create-info [database_name] > [dump_file.sql]

For example to backup our sample database with data only, you use the following command:

1	mysqldump –u mysqltutorial –psecret –no-create-info classicmodels > c:\temp\backup002.sql

How to Backup Multiple MySQL Databases into a Single File

If you want to backup multiple database just separate database name by command in the [database_name]. If you want to back up all databases in the database server use the option –all-database.

1	mysqldump -u [username] –p[password]  [dbname1,dbname2,…] > [dump_file.sql]

2

3	mysqldump -u [username] –p[password] –all-database > [dump_file.sql]