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

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

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 is part of Percona Toolkit. You can install Percona Toolkit on Ubuntu/Debian with the command

If you have password for root user, you can use

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


Copy all MySQL users to the new server

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

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

Back to MySQL

Need help with Linux Server or WordPress? We can help!

Leave a Reply

Your email address will not be published. Required fields are marked *