Recently I had to move my services from one server into another. The problem was that I had lots of web pages relaying on many databases with specific users. What’s more I didn’t want to change the databases users and passwords.

If you have similar problem this is the way you can do it.

First let’s migrate users

On the source DB execute:

$ mysql -uroot -N -p -s > myusersgrantsquery

Enter password:

SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

quit

This will create the myusersgrantsquery file with number of queries looking similar to this:

SHOW GRANTS FOR 'user1'@'localhost';
SHOW GRANTS FOR 'user2'@'127.0.0.1';
SHOW GRANTS FOR 'user3'@'::1';

Creating the grant file

We don’t have quite what we want and need yet.  We are looking for a query that will create all of our users on the new MySQL server.  We need to run the query that we just created and it will give us the query that we will use later to create the users.  It will create our grant permission statements in a file named “grantfile”

$ mysql -uroot -N -p -s -r < myusersgrantsquery > mygrantsfile

Enter password:

This will create the mygrantsfile file with number of queries looking similar to this:

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*036F72A3D67924C06C5FD5C1E8D22EBAC8E14623' WITH GRANT OPTION

GRANT USAGE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY PASSWORD '*8024AC712AD165BC6A1120E5D1312ECBF3121F17'

GRANT USAGE ON *.* TO 'user3'@'127.0.0.1' IDENTIFIED BY PASSWORD '*0024A5312C4165B13D812CC5D1312E6AF3101F16'

We are almost there but what is missing is semicolons at the end of each line. Let’s correct this.

$ sed -i '/[^;] *$/s/$/;/'  mygrantsfile

Now we are ready to move to the destination server.

Creating users and granting their privileges

Copy the mygrantsfile file to the destination server, log in to it and execute:

$ mysql -uroot -p < mygrantsfile

That’s all. You should have all your users in the new Mysql server.

Dumping the database

Get back to the source server.What we want is all databases but not the system ones.

Create a shell script named alldbdump.sh:

#! /bin/bash

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#

SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} -c | gzip -9 > myalldatabases.sql.gz

If you want to omit other databases add them to this part of script:

SQL="${SQL} ('mysql','information_schema','performance_schema', 'db1_to_omit', 'db2_to_omit')"

So in this example two extra databases db1_to_omit and db2_to_omit will be added to list of databases excluded from the dump.

Now execute the alldbdump.sh script:

$ sh alldbdump.sh

In the current directory you should now have gzip compressed database file myalldatabases.sql.gz.

Copy it to the destination server.

Restore databases on the destination server.

On the destination server unzip the copied file and then import it into the MySQL server as follows:

$ gzip -d myalldatabases.sql.gz $ mysql -uroot -p < myalldatabases.sql

All done.

Comments are welcome.