How to Transfer All MySQL Databases From Old to New Server
Aaron KiliMarch 12, 2019 Categories MariaDB, MySQL 14 Comments
freestar

Transferring or Migrating a MySQL/MariaDB database between servers usually takes
only few easy steps, but data transfer can take some time depending on the 
volume of data you would like to transfer.

In this article, you will learn how to transfer or migrate all your
MySQL/MariaDB databases from old Linux server to a new server, import it
successfully and confirm that the data is there.


Important Notes


Make sure to have the same version of MySQL installed on both server
with same distribution.
Make sure to have enough free space on both server to hold the database
dump file and the imported database.
Don’t ever consider moving the data directory of database to another
server. Never mess with internal structure of the database, if you do, you
will face problems in future.


Export a MySQL Databases to Dump File


First start by login into your old server and stop the mysql/mariadb service
using the systemctl command as shown.

# systemctl stop mariadb
OR
# systemctl stop mysql

Then dump all your MySQL databases to a single file using the mysqldump command.

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

Once the dump is completed, you are ready to transfer the databases.

If you want to dump a single database, you can use:

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


Transfer MySQL Databases Dump File to New Server


Now use scp command to transfer your databases dump file to the new server
under home directory as shown.

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

Once you connect, the database will be transferred to the new server.


Import MySQL Databases Dump File to New Server


Once the MySQL dump file has been traferred to the new server, you can use
the following command to import all your databases into MySQL.

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

Once the import is completed, you can verify the databases on both servers
using the following command on the mysql shell.

# mysql -u user -p
# show databases;


Transfer MySQL Databases and Users to New Server


If you want to move all your MySQL databases, users, permissions and data
structure old server to new, you can use rsync command to copy the all the
content from the mysql/mariadb data directory to new server as shown.

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

Once the transfer completes, you can set the ownership of the mysql/mariadb
data directory to user and group mysql, then do a directory listing to check
that all files have been transferred.

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

That’s all! In this article, you learned how to easily migrate all
MySQL/MariaDB databases from one server to another. How do you find this
method compared to other methods? We would like to hear from you via the
comment form below to reach us.