Backing up permissions for individual databases

March 12, 2009 · Posted in MySQL · 2 Comments 

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.

Tags: <, , , , >

  • Gary Pendergast, noun: Gamer, developer, MySQL, WordPress, web and music geek. Currently taking a leave of absence from life as a MySQL Support Engineer for Oracle to tour around Europe. Can be observed in his natural habit at pento.net, or usually in Bologna, Italy.

  • My Twitter