How to Migrate MySQL Users from one Server to Another

Ever been in the middle of a big project and realized you need to move your MySQL database to a new server? It’s like thinking you’re almost done with a puzzle, only to discover a whole new section you didn’t see before. Moving the database is one thing, but making sure all your users can still access it is another.

I’ve been there, scratching my head and wondering where to start. But don’t worry, this guide will walk you through every step to ensure all your MySQL users, along with their privileges, make it safely to the new server. Let’s make this migration process as smooth as possible.

Feeling overwhelmed?
If the thought of migrating databases and users is giving you a headache, our team at Wisdm Labs is here to help. Reach out to us, and we’ll take care of the heavy lifting so you can focus on what you do best. 

Migrating MySQL Users

Before you begin, make sure you have SSH accesses for both servers. Also, the steps I’ve given below are applicable only for Ubuntu OS. Hence, I believe here, that you want to migrate MySQL users on one Ubuntu server to another.

Get a List of MySQL Users

Fire up a terminal on the source server, and execute the command:  pwd (It will print the current directory, where you are present. Generally, it will be the home directory of user.) Take a note of the directory path shown.

Now, to get the list of all MySQL users with their host, use the below command. We will store the user and host combination in a text file.

mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > mysql_all_users.txt

Upon executing the above command, you will be prompted for the password of ‘root’ mysql user. Enter that password and it will create a file named ‘mysql_all_users.txt’ in your current directory. It will create string for every user.

You may verify this by executing the ls command. If you open the file ‘mysql_all_users.txt’ (or check the content using less mysql_all_users.txt command), you will find the concatenated user name and host string. For example, you may find something like ‘abc’@’xyz’. Which would imply user ‘abc’ and host ‘xyz’, concatenated using the ‘@’ symbol.

Note that we have not selected the user root and debian-sys-maint. If you want to select ‘root’ user as well, then remember, that after importing all users, the password of root user will be changed on the new server. The reason for not importing debian-sys-maint is that, this user along with the credentials in /etc/mysql/debian.cnf, is used by the init scripts to stop the server. Hence if we were to migrate this user onto the new server, it won’t be able to start or stop the MySQL service on the new server.

Migrating Only Specific Users

If you want to migrate only a few specific users to the new server, replace

user != 'debian-sys-maint' AND user != 'root' AND user != ''" with user IN ('your_username1', 'your_username2', 'your_username3')

Till now, we have obtained only the list of users with their hosts. Next, we will try to get privileges assigned to those users.

Obtain a List of User Privileges

Lets fire the next command in the terminal to get the privileges:

while read line; do mysql -B -N -uroot -p<put_password_here> -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

In above command make sure you replace <put_password_here> with your password for root user of MySQL.

Basically, the above command reads every individual line in mysql_all_users.txt and tries to get privileges for each user. Hence if you do not enter a password, it will prompt for the password, every time it tries to obtain privileges for a user. If you execute the query “SHOW GRANTS FOR ‘username’@’hostname’” for any user in mysql, it will print the privileges of that particular user.

The user privileges command will also create a new file ‘mysql_all_users_sql.sql’ in your current directory. This file will contain the SQL queries to obtain users along with their privileges.

Now, if we insert a ‘;’, at the end of every SQL command present in that file, we will end up with executable queries. Instead of having to this manually, you can execute below command:

sed -i 's/$/;/' mysql_all_users_sql.sql

This will place a ‘;’ at the end of every line in the file mysql_all_users_sql.sql. Now our file is ready to import.

Importing the Users

Transfer this file onto the new server manually or using ‘scp’ command. You will need the earlier noted directory path, to upload this sql file manually. Once that SQL file is uploaded on the new server, fire up a terminal on the new server. Navigate to the directory where you have stored the file and fire below command.

mysql -u root -p < mysql_all_users_sql.sql

It will ask for password of root user of mysql. And there you have it. Enter the password for the user and it will import mysql users of old server with their privileges. I’m sure this article will help save a lot of your time, if you follow the steps as mentioned.

If you have any questions or need further assistance, feel free to contact us to schedule a consultation and ensure your migration process goes smoothly.

Let’s talk