MySQL Migrate Users and Grants to Another MySQL Database

Hi,

Sometimes we need to change our database. In this case we have to migrate users and their grants to the new database server.

Here is a procedure for this issue

1- Prepare grant sqls

[root@olddb ~]# mysql -uroot -pmypass -s -N << EOF > showgr.sql
select distinct CONCAT('show grants for "', user, '"@"', host, '";') as query from mysql.user;
EOF

showgr.sql looks like :

[root@olddb ~]# head showgr.sql
show grants for "USER1"@"xxx.xxx.xxx.xxx";
show grants for "USER2"@"xxx.xxx.xxx.xxx";
show grants for "USER3"@"xxx.xxx.xxx.xxx";

Let’s create grant sqls running showgr.sql against to the old database.

[root@olddb ~]# mysql -uroot -N -ppmypass -s -r < showgr.sql > grant.sql

Unfortunately MySQL creates grant sql without “;” at the end. To add “;” at the end of every line we use sed.

[root@olddb ~]# more grant.sql |sed 's/$/;/g' > grants.sql

2- SCP file and import it!

Now we have a list of grants for importing too the new one.

[root@olddb ~]# scp grants.sql user@newdb:/tmp/

Now  we can import grant file on the new host

[root@newdb ~]# mysql -uroot -pmypass  < grants.sql

Good Luck.