Transfer MySQL Privilege Data

From Wiki
Jump to: navigation, search

First export the account privilege info to a file:

SELECT * FROM mysql.user where User LIKE "username_%" INTO OUTFILE '/tmp/users.txt';
SELECT * FROM mysql.db where User LIKE "username_%" INTO OUTFILE '/tmp/db.txt';

Copy these files to the new server and import them into the "mysql" database.

mysql mysql
LOAD DATA INFILE '/tmp/users.txt' REPLACE INTO TABLE user ;
LOAD DATA INFILE '/tmp/db.txt' REPLACE INTO TABLE db ;
FLUSH PRIVILEGES ;