How to Migrate MySQL Users to Another Server

I wanted to Migrate MySQL from one server to another. Usually, I could just copy over the MySQL data directory (/var/lib/mysql) to the new server, this will copy over MySQL users and data. On this server, the MySQL data file (ibdata1) was too large, 200 GB approx. If I do a mysqldump, the SQL file was only 10 GB, so I decided not to copy over the data directory to the new server.

I did a MySQL dump of all databases with the command

mysqldump --events --routines --triggers --all-databases > all-dbs.sql

Copy the file all-dbs.sql to the new server and tried to restore with the command

mysql < all-dbs.sql

It failed because MySQL on old and new servers are different, recent MySQL versions have users table changed to view.

So I backed up individual MySQL databases using MySQL backup all databases, copied over to the new server, and restore the databases. When restoring databases, I get an error saying the database does not exist, I created the databases and restored the SQL files.

Now we have all databases and data on new server. We need to create all users with correct permissions. To do this, on source server, run

pt-show-grants

pt-show-grants is part of Percona Toolkit. You can install Percona Toolkit on Ubuntu/Debian with the command

apt install percona-toolkit

If you have password for root user, you can use

pt-show-grants --host localhost --user root --ask-pass

The command will show the SQL commands used to create the users with the correct permission. Copy it and run it on new server MySQL command prompt. This will create all users on new server with proper permissions.

Copy a specific user to new server

To view commands to create a specific user, you can run the command

show grants for 'USER_HERE'@'HOST_HERE';

Example

show grants for 'root'@'localhost';

Copy all MySQL users to the new server

If you don't have Percona Toolkit installed, you can use the following command

mysql -u root --silent --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u root --skip-column-names -A

It will generate SQL commands to create users, copy and run it on new server to create MySQL users.

Here is a bash script to do the same

mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort | \
while read u
 do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'
done

Back to MySQL