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
|
1 |
mysqldump --events --routines --triggers --all-databases > all-dbs.sql |
Copy the file all-dbs.sql to the new server and tried to restore with the command
|
1 |
mysql < all-dbs.sql |
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
|
1 |
pt-show-grants |
pt-show-grants is part of Percona Toolkit. You can install Percona Toolkit on Ubuntu/Debian with the command
|
1 |
apt install percona-toolkit |
If you have password for root user, you can use
|
1 |
pt-show-grants --host localhost --user root --ask-pass |
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
|
1 |
show grants for 'USER_HERE'@'HOST_HERE'; |
Example
|
1 |
show grants for 'root'@'localhost'; |
Copy all MySQL users to the new server
If you don’t have Percona Toolkit installed, you can use the following command
|
1 |
mysql -u root --silent --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u root --skip-column-names -A |
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
|
1 2 3 4 |
mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort | \ while read u do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/' done |
Back to MySQL