Migrate mySQL user to new server

To mirgrate databases to a new server we need two things:

1. User information (password, grants)
2. the database

In order to get the userinformation we need to execute the following query which returns a list of commands to show the users grants and encrypted passwords.

SELECT DISTINCT CONCAT( 'show grants for ', user, '@', host, ';' ) AS query 
FROM mysql.user 
ORDER BY user
INTO OUTFILE '/tmp/mysql_user_grant_queries.sql';

After creating a file containing all the users we have to manually check and remove certain users like root, froxlor etc. which might be already configured on the new server by editing the file vim /tmp/mysql_users_grant_queries.sql . The we use the file as input to get the real information.

mysql -uroot -N -p -s -r < /tmp/mysql_user_grant_queries.sql > /tmp/mysql_user_grants.sql
sed -i 's/abc\.abc\.abc\.abc/def\.def\.def\.def/g' /tmp/mysql_user_grants.sql

The first command obtains the grants and the second changes the IP from abc.abc.abc.abc to def.def.def.def so change this!

Now lets a list of the databases we want to dump

mysql> SELECT GROUP_CONCAT(databasename ORDER BY databasename SEPARATOR ' ') FROM `panel_databases` WHERE 1 INTO OUTFILE '/tmp/customer_databases.txt';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

Great now lets dump the databases. Use the second command to add further databases to be dumped, like test.

DATABASES=`cat customer_databases.txt`
DATABASES="${DATABASES} test"
for db in ${DATABASES}; do \
    mysqldump -uroot -p<PASSWORD> --add-drop-database ${db} > /tmp/dump_${db}.sql \
done

I know that putting the password in the command line is very insecure, but since we migrate the data and will give up the old server it does not really matter. Then transfer the files to the new server and import them with mysql -uroot -p < <filename>.sql .