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":{"0":"post-1836","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-backup","9":"tag-backup-privileges","10":"tag-backup-user-grants","11":"tag-mysql","12":"tag-mysql-database","13":"tag-pt-show-grants","14":"tag-user-privileges"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1836"}],"version-history":[{"count":16,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1836\/revisions"}],"predecessor-version":[{"id":2882,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1836\/revisions\/2882"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1836"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}$] 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