Backing up permissions for individual databases
Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:
shell> mysqldump -u root -p --databases db1 db2 ... > backup.sql
The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.
-- Grab the users with global permissions, -- with permissions to the databases you want, -- and tables/stored procedures in it. mysql> SELECT u.* INTO OUTFILE '/tmp/user.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.USER u WHERE u.Select_priv='Y' UNION SELECT u.* FROM mysql.USER u, mysql.db d WHERE d.Db IN('db1', 'db2', ...) AND d.USER = u.USER UNION SELECT u.* FROM mysql.USER u, mysql.tables_priv t WHERE t.Db IN('db1', 'db2', ...) AND t.USER = u.USER UNION SELECT u.* FROM mysql.USER u, mysql.procs_priv p WHERE p.Db IN('db1', 'db2', ...) AND p.USER = u.USER; -- Now, grab the database permissions, and those of objects in the database. mysql> SELECT * INTO OUTFILE '/tmp/db.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.db WHERE Db IN('db1', 'db2', ...); mysql> SELECT * INTO OUTFILE '/tmp/tables_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.tables_priv WHERE Db IN('db1', 'db2', ...); mysql> SELECT * INTO OUTFILE '/tmp/procs_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.procs_priv WHERE Db IN('db1', 'db2', ...);
Then, re-loading the permissions onto the new server is simple:
mysql> LOAD DATA INFILE '/tmp/user.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.USER; mysql> LOAD DATA INFILE '/tmp/db.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.db; mysql> LOAD DATA INFILE '/tmp/tables_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.tables_priv; mysql> LOAD DATA INFILE '/tmp/procs_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.procs_priv;
All up, a few queries to account for everything, but pretty easy to include in your backup/restore process. For further development, you could put the database list in a variable, so that you only need to change it on one line, rather than 6.
Comments
3 Responses to “Backing up permissions for individual databases”
Leave a Reply



Yeah. Thanks that’s handy.
Maatkit’s mk-show-grants utility (http://www.maatkit.org/doc/mk-show-grants.html) can dump permissions in the form of grant or revoke statements.
Should anyone else run across this, when manipulating mysql.user directly, it is necessary to:
FLUSH PRIVILEGES;
afterward, or the changes won’t take effect until the service is restarted.