MySQL – Migrate Users from Server to Server

Sometimes we need to migrate our databases to a new MySQL server.  It is easy to move the databases, but without the users and their permissions, our new databases would be worthless.  Below is a step-by-step on migrating MySQL users to a new MySQL server

Step 1 – Create a Query List That We Can Use to Get Grants for All Users

I use these options so that I wouldn’t get any formatting characters that I would have to manually delete later.

  • -N skip column names in the output
  • -p password – Asks me to type the password so nobody can get it from the command line history
  • -s  silent mode – less formatting output that we don’t want like “|” and “-“

So, let’s get a list of the users in a query that we can use to get the grants.  Our query will be output into the “myfile” file

$ mysql -uroot -N -p -s > myfile
Enter password:
select Distinct CONCAT(‘show grants for `’, user, ‘`@`’, host, ‘`;’) as query from mysql.user;

If we want to see what our query file look like, we can take a quick peek:

[root@classes-dev-mysql ~]# cat myfile
show grants for `user1`@`%`;
show grants for `user2`@`%`;
show grants for `user3`@`10.%`;
show grants for `user4`@`10.%`;
show grants for `jeff`@`10.%`;

Step 2 – Create the MySQL 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”

[root@classes-dev-mysql ~]# mysql -uroot -N -p -s -r < myfile > grantfile
Enter password:

We can take a peek at what our grantfile contains:

$ cat grantfile
GRANT USAGE ON *.* TO ‘user1’@’%’ IDENTIFIED BY PASSWORD ‘5ea9af6g6t27032f’
GRANT ALL PRIVILEGES ON `database1`.* TO ‘user1’@’%’
GRANT USAGE ON *.* TO ‘user2’@’10.%’ IDENTIFIED BY PASSWORD ‘2a123b405cbfe27d’
GRANT SELECT ON `database1`.`table1` TO ‘user2’@’10.%’GRANT ALL PRIVILEGES ON *.* TO ‘user3’@’10.%’ IDENTIFIED BY PASSWORD ‘753af2za1be637ea’

Step 3 – Create Users and Grant MySQL Permissions on the New MySQL Machine

Now we are done working on the source machine.  We need to copy our file named “grantfile” over to the new machine.

$ scp grantfile

Next, we login to the destination or the new MySQL machine that we are building and run the “grantfile” in MySQL to create our users on the new MySQL machine.

$ mysql -uroot -p < ./grantfile

That’s it.  As long as our databases are named the same in the new MySQL, our users should be ready to use the copy of the databases in the new MySQL machine.

Migrate Users from MySQL to MySQL

Migrate Users from MySQL to MySQL


The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

4 comments for “MySQL – Migrate Users from Server to Server

  1. Teshome
    August 31, 2014 at 11:07 am

    Thanks for the good post. I had to add semicolon at the end of each line in the grantfile to make this work.

  2. Markku
    September 19, 2014 at 4:55 am

    Thanks from me too. I thought it might be something like this but your article saved quite a few hours 🙂

  3. December 10, 2014 at 5:45 am

    Thanks! Helped me, but you should modify script, there are no semicolons at the end of lines, so mysql import is not working. Send it to mysql wiki.

  4. December 10, 2014 at 5:48 am

    Just use sed to add semicolons at the end of lines before executing:
    # sed -i ‘/[^;] *$/s/$/;/’ ./grantfile

Leave a Reply

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