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

Question

I'm setting up up a new MySQL server and I'd like to give it the same set of usernames, allowed hosts, and passwords as an existing server (which is going away).

Would it work to just do a dump of the users table and then load it on the new server?

Is there a better way than that?

Answer 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

Answer 2

I would take a look at the Maatkit MySQL toolkit (http://www.maatkit.org/tools.html). The description of the mk-show-grants tool says it all...

mk-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....

Answer 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.

Answer 4

mysqldump will work, but since the mysql database uses ISAM, you can just copy the directory to the new server. You'll find this in different places, but the most common location for the mysql database will be /var/lib/mysql/mysql. Be sure to stop mysql on the new server, move the old directory out of the way, copy the directory and restart. An example using standard locations as root:

# /etc/init.d/mysqld stop
# cd /var/lib/mysql
# mv mysql ../mysql.backup
# rsync -a root@oldserver:/var/lib/mysql/mysql .
# /etc/init.d/mysql start

Answer 5

it is so simple to run grant commands. It is like

GRANT ALL on database.table to "user"@"host" identified by "password"

so

GRANT ALL on database.* to "jack"@"localhost" identified by "somepass"

Answer 6

This has already been solved here http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server/13050#13050