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.
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.