40

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?

shgnInc
  • 1,634
  • 3
  • 21
  • 29
Agvorth
  • 2,429
  • 4
  • 28
  • 29

5 Answers5

33
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

Dave Cheney
  • 18,307
  • 7
  • 48
  • 56
  • 18
    The 'mysql' db contains lots more uses than just the grants. Make sure you want to transfer it all, before performing the above steps. – Martijn Heemels Dec 20 '10 at 21:29
  • I'm getting the error `You can't use locks with log tables`. Is there any work around for this? – Despertar Feb 06 '13 at 03:21
  • If only users are needed, I'd do `mysqldump mysql user > mysql.sql` – Minras Mar 18 '13 at 15:04
  • 1
    I got `unknown database flush privileges`. What worked for me: `mysqladmin reload`. I suppose that `service mysql reload` or `restart` would also do the job. –  Feb 18 '15 at 18:41
  • The first two lines will not acquire database-level, table-level, and column-level privileges. – RolandoMySQLDBA May 26 '15 at 14:13
  • 11
    This is not the right answer. You should not migrate the whole mysql table. Use `SHOW GRANTS FOR ...` to extract the current privieges and then GRANT them on the new host. – caponica Mar 27 '16 at 10:40
  • 9
    On behalf of @eerick who doesn't have enough rep yet to comment: DO NOT use mysqldump with any of the mysql schema tables. This can have terrible consequences. I actually just used the accepted answer to try and solve this problem myself and I ran into all sorts of errors. I effectively ended up locking myself out of the database because my admin account wasn't recognizing my password and I couldn't create new users. You may be able to use mysqldump if the versions are the same, but I still wouldn't risk it. – rrauenza Jun 09 '16 at 22:11
22

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

Justin Jenkins
  • 158
  • 1
  • 1
  • 7
Aaron Bush
  • 399
  • 1
  • 4
13

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.
staticsan
  • 1,529
  • 1
  • 11
  • 14
11

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

Community
  • 1
abu
  • 211
  • 2
  • 3
  • I upvoted this but then was surprised to see [official docs](https://dev.mysql.com/doc/refman/5.7/en/copying-databases.html) saying "Do not forget to copy the `mysql` database because that is where the grant tables are stored." – Ryan Nov 04 '19 at 23:00
5

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
Tim
  • 1,879
  • 3
  • 18
  • 16
  • 1
    The 'mysql' db contains lots more uses than just the grants. Make sure you want to transfer it all, before performing the above steps. – Martijn Heemels Dec 20 '10 at 21:29