Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants

Everybody knows the criticality of backup. You might have had your database dumped, data directory copied, flat files exported and even config and binary log files backed-up. But did you backup your MySQL user privileges?

One can easily forget to backup the user privileges but it is much easier to do and you should backup your MySQL server user privileges / grants regularly.

1. Backup MySQL user privileges using mysqldump:

$] mysqldump -uUSER -pPASS --databases mysql > mysql.sql

2. Copy MySQL data directory:

$] cd /var/lib/mysql/ [ assuming default data directory ]

mysql> flush tables with read lock;

$] cp -R mysql /path/to/backup/mysql_grants_dir

mysql> unlock tables

3. Using Percona tool pt-show-grants:

pt-show-grants shows grants (user privileges) from a MySQL server. It can also write REVOKE and DROP user statements as required.

If you donot have Percona Tools already installed you may download individual tool as follows:

$] wget percona.com/get/pt-show-grants
$] chmod +x pt-show-grants
$] pt-show-grants -uUSER -pPASSWORD > user_grants.sql

You may also include following options:
–separate: Write grants with individual privileges
–revoke: Write revoke grants for all grants
–drop: Add drop user command before grant syntax

For more info:

$] perldoc pt-show-grants

Using pt-show-grants:
Getting MySQL user grants for specified user:

root@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --drop --only kedar
 -- Grants dumped by pt-show-grants
 -- Dumped from server Localhost via UNIX socket, MySQL 5.5.27-0ubuntu2-log at 2012-12-17 04:54:34
 -- Revoke statements for 'kedar'@'localhost'
 REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost';
 DROP USER 'kedar'@'localhost';
 DELETE FROM `mysql`.`user` WHERE `User`='kedar' AND `Host`='localhost';
 -- Grants for 'kedar'@'localhost'
 GRANT LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO 'kedar'@'localhost';
You can also specify comma separated list of users.

Advantages / use:
– Easily replicate users from one server to another.

// Creating user USER_X on server B like that on server A

pt-show-grants -uUSER -pPASS -hSERVER_A --only USER_X | mysql -uUSER -pPASS -hSERVER_B

– Unified sorted canonicalized grant syntax to put in version control
– As pt-show-grants canonicalize the grant syntax, it’d be good to do “diff” the grants of two different MySQL instances.
– Easily generate revoke syntax for particular user/ users:

root@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --only kedar | grep REVOKE
 REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost';

It is important to ensure that your backup is complete and we’re not missing the database users.