How to Migrate MySQL Users from one Server to Another

Migrate MySQL Users

At some point of time, just like me, you might come across the need to migrate your MySQL database, from one server to another. After migrating to a new database, there will be some configuration files, which will need to access the database. But you probably already know, for it to work, you will need all (or at least the needed) users set up.

In case, you wanted to start by migrating the databases from one server to another, this article, to Backup and Restore MySQL Database, can really help you. Once you have successfully migrated the databases you want, you can proceed with migration of MySQL users.

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. In case you had any doubts, you can surely leave your comments, in the comment section below, and I’ll surely try and help you out.

• WordPress Tips & Tricks
• With tags: database, Migration, MySQL

Share:

• Sumit P

Please note, some of the links in this blog post might be affiliate links. This means if you go on to purchase a product using such a link, we receive a small commission (at no additional cost to you). This helps us support the blog and produce free content. We only recommend products we work with or love. Thank you for your support!