How to Transfer All MySQL Databases from One Server to Another

MySQL is a popular database used by millions of developers and organizations. Often you may need to transfer MySQL databases from one server to another during system migration or upgrade. In this article, we will learn how to transfer all MySQL databases from one server to another.

How to Transfer All MySQL Databases from One Server to Another

Here are the steps to transfer all MySQL databases from one server to another. Basically, it involves exporting MySQL data to a .sql file, then transferring this file to destination server, and then importing the data from this file into databases in the destination server.

1. Export MySQL Databases to Dump File

First log into your source server and stop MySQL process in it, using the following command.

# systemctl stop mysql
OR
# systemctl stop mariadb

Please note, you should log out of MySQL if you are already logged into it on your source system. You need to stop the MySQL service so that your databases are not modified while they are being modified.

Then export all your databases into a .sql file with the following command. Replace [user] with the MySQL username.

# mysqldump -u [user] -p --all-databases > all_databases.sql

If you want to export only a specific database use the following command instead. Replace [database name] with the name of database that you want to export.

# mysqldump -u root -p --opt [database name] > database_name.sql

2. Transfer MySQL Database Dump File to Another Server

Use any file transfer command to transfer the .sql file you created above, to the destination server. Replace user and example.com with the username and domain name/IP address of destination server.

# scp all_databases.sql user@example.com:~/       [All Databases]
# scp database_name.sql user@example.com:~/       [Singe Database]

Please note, you need to have remote write access ( a remote user that has access to write files to remote system) ready before you run the above command. You will be asked for login password after you run the above command. Enter the password for remote Linux user (not database user). Only when you enter correct password, scp will start file transfer. Otherwise, the command will terminate with error saying invalid authentication.

3. Import MySQL Database Dump to New Server

After you have transferred the MySQL dump to new file, import it into the database on destination server, using the following command. Here is the command to import all data for all databases. Replace [user] with database username in your destination server, not the source server.

# mysql -u [user] -p --all-databases < all_databases.sql   [All Databases]

If you only want to import data about a single database, use the following command instead. Replace [database_name] with the name of database.

# mysql -u [user] -p [database_name] < database_name.sql      [Singe Database]

Once you have imported data into new database, log into MySQL and run the following commands to view your databases.

# mysql -u user -p
# show databases;

The above steps transfer only the database definitions and actual data from one server to another. If you want to also transfer users & permissions, use the following command to transfer them from one server to another. This information is generally stored in /var/lib/mysql folder. Replace user and example.com with the remote username and domain name/IP address

# rsync -avz /var/lib/mysql/* user@example.com:/var/lib/mysql/

Once the files are transferred, change the ownership of /var/lib/mysql folder to mysql user and mysql group.

# chown mysql:mysql -R /var/lib/mysql/
# ls  -l /var/lib/mysql/

In this article, we have learnt how to transfer all MySQL databases from one server to another.

How to Transfer All MySQL Databases from One Server to Another