Search

How to Migrate MySQL Users from one Server to Another

    Sumit Pore
Listen to this article
Migrate-MySQL-Users
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.

Sumit Pore

Sumit Pore

14 Responses

  1. Hello,
    I actually want to know that “how to assign products to customers in mysql?”. Why i am asking that ? Because i exported all users a opencart based website. I joined which product bought by whom but i just import only users in my wordpress website as customer. So i could not figure out how to migrate these user’s products which bought opencart website.
    Could you help me please ?

    1. Hi Ahmet,
      In OpenCart’s database there will be table which should be maintaining user orders with columns for user ID and order items. You will first have to create products in WordPress. A tool then needs to be written which will generate a relation between product ids in OpenCart and WordPress. And then another tool will read the OpenCart order items and map them with WordPress’s product ids.

  2. Hello ,

    Taking mysql table backup and then importing that on remote server will help me ? please suggest

  3. Thank you. Why arent you #1 on search results. Thx for sharing

    This tip should be the FIRST one for Newbie developer turning sysop 🙂 Have a great 2016

  4. in the SQL script for the receiving end server you might need to include flush privileges.

    I liked the grant table query a lot. wish someone had show that to me earlier.

  5. Works great on Ubuntu 14.04.

    Getting this:
    mysql: [ERROR] unknown variable ‘sql_mode=ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

    On Ubuntu 16.0x

    … researching…

  6. It doesn’t work with mysql 5.6.34 and above because password comes as in the show grants command. Is there any other way around?

  7. Nice guide.
    Sadly i’m getting the following error:

    ERROR 1827 (HY000) at line 11: The password hash doesn’t have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

    Do you have at fix for that ?

  8. Never mind.

    It was a old user. so i removed the user and ran it again. And this time it worked great

    thanks again !

  9. In a small addition to this wonderful guide – I would like to add that if you make a file called .my.cnf in /home/root or ~/ (if you are root), it will use the user and password specified so that any mysql or mysqldump commands that you use or your script uses – there is NO need to specify -u or -p. This has helped me saved LOTS of hours – interactively or using excellent bash scripts like this one!

Leave a Reply

Your email address will not be published. Required fields are marked *

Get The Latest Updates

Subscribe to our Newsletter

A key to unlock the world of open-source. We promise not to spam your inbox.

Suggested Reads

Join our 55,000+ Subscribers

    The Wisdm Digest delivers all the latest news, and resources from the world of open-source businesses to your inbox.

    Suggested Reads