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.

Advertisements

One thought on “MySQL Migrate Users and Grants to Another MySQL Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s