How can I copy MySQL users table from one server to another?

Solution 1:

oldserver$ mysqldump mysql > mysql.sql
newserver$ mysql mysql < mysql.sql
newserver$ mysql 'flush privileges;'

Should do it, remember to add -u $USER and -p$PASSWORD as required

Solution 2:

I would take a look at the Percona Toolkit. The description of the pt-show-grants tool says it all...

pt-show-grants extracts, orders, and then prints grants for MySQL user accounts.

Why would you want this? There are several reasons.

The first is to easily replicate users from one server to another; you can simply extract the grants from the first server and pipe the output directly into another server.

The second use is to place your grants into version control....

---

Solution 3:

Whilst a dump of the mysql database would probably work, in my experience, properly documenting all the access and setting it up again with GRANT statements is much better. There are two benefits to doing it this way:

1. You will understand your database security and how it is implemented.
2. You will get to remove access that is no longer required.

---

Solution 4:

The accepted answer (using mysqldump to backup mysql.user table) is a very dangerous approach if you are migrating to a new server version.

I did that in the past (migrating users from Mysql 4 to Mysql 5.1), and later I had problems trying to grant or modify privileges to my database users.

This happens because mysql.users table structure differs between mysql server versions, as explained here:

https://dba.stackexchange.com/a/16441/18472

So, take care if you are upgrading your server version. Some more hints here:

https://stackoverflow.com/a/13218162/710788

---

Solution 5:

This has already been solved here How can I export the privileges from MySQL and then import to a new server?