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?<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
1. Backup MySQL user privileges using mysqldump:<\/strong><\/p>\n\n\n\n 2. Copy MySQL data directory:<\/strong><\/p>\n\n\n\n 3. Using Percona tool pt-show-grants:<\/strong><\/p>\n\n\n\n pt-show-grants shows grants (user privileges) from a MySQL server. It can also write REVOKE and DROP user statements as required.<\/p>\n\n\n\n If you donot have Percona Tools already installed you may download individual tool as follows:<\/p>\n\n\n\n You may also include following options: For more info:<\/p>\n\n\n\n Using pt-show-grants: Advantages \/ use:<\/strong> – Unified sorted canonicalized grant syntax to put in version control It is important to ensure that your backup is complete and we’re not missing the database users.<\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[15,351,350,427,318,352,349],"class_list":["post-1836","post","type-post","status-publish","format-standard","category-mysql","category-mysql-articles","tag-backup","tag-backup-privileges","tag-backup-user-grants","tag-mysql","tag-mysql-database","tag-pt-show-grants","tag-user-privileges"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t$] mysqldump -uUSER -pPASS --databases mysql > mysql.sql<\/code><\/code><\/pre>\n\n\n\n\n\n\n\n$] cd \/var\/lib\/mysql\/ [ assuming default data directory ]
\nmysql> flush tables with read lock;
\n$] cp -R mysql \/path\/to\/backup\/mysql_grants_dir
\nmysql> unlock tables<\/code><\/code><\/pre>\n\n\n\n$] wget percona.com\/get\/pt-show-grants
$] chmod +x pt-show-grants
$] pt-show-grants -uUSER -pPASSWORD > user_grants.sql<\/code><\/code><\/pre>\n\n\n\n
–separate: Write grants with individual privileges
–revoke: Write revoke grants for all grants
–drop: Add drop user command before grant syntax<\/p>\n\n\n\n$] perldoc pt-show-grants<\/code><\/code><\/pre>\n\n\n\n
Getting MySQL user grants for specified user:<\/p>\n\n\n\nroot@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';<\/code>
You can also specify comma separated list of users.<\/code><\/pre>\n\n\n\n
– Easily replicate users from one server to another.<\/p>\n\n\n\n\/\/ Creating user USER_X on server B like that on server A
\npt-show-grants -uUSER -pPASS -hSERVER_A --only USER_X | mysql -uUSER -pPASS -hSERVER_B<\/code><\/code><\/pre>\n\n\n\n
– 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:<\/p>\n\n\n\n\nroot@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --only kedar | grep REVOKE\n REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost';<\/code><\/code><\/pre>\n\n\n\n