MySQL Migrate Users and Grants to Another MySQL Database


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;

showgr.sql looks like :

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

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.

MySQL Size Queries

Here is some useful queries for finding MySQL database , schema or table size information.

  • Size of Database group by schema Name :

select table_schema "Schema Name",
sum(data_length + index_length)/1024/1024 "Data Base Size in MB"
from information_schema.tables group by table_schema;

  • Size of Schema group by table names :

select table_name, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
from information_schema.tables where table_schema ='<Schema Name>';

  • All Database Size :

select sum( data_length + index_length) / 1024 / 1024 "Data Base Size in MB"
from information_schema.tables;