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 [email protected]:~/ [All Databases] # scp database_name.sql [email protected]:~/ [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/* [email protected]:/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.
There is a mistake in this article. To restore a database from a dump generated with mysqldump the correct syntax is :
The command is this article " mysql -u [user] -p newdatabase < database_name.sql" works only if the database already exists.
Dear,
We have mysql 4 version database and we need to import that database into a new MySql version.
how we can do that? is there any procedure? shall we have to write some scripts?
Please help me…
Importing is:
# mysql -uroot -p < all_databases.sql
If you stop MySQL or MariaDB server you can not dump the DB.
This only works to go from one version to the same one. I tried it to move the tables from my old install to the new one and all it did was mangle it to the point it wouldn’t work because of structure changes, table changes, storage methods, and so on. Doing user tables is fine, but taking all the admin tables over too just isn’t going to work!
@Colin
We are in the process of reviewing this article based on feedback like this. Many thanks.
I need to transfer all databases from one server to another and start replication as well..!
Will this help me out..?
Please help with it
Agreed with Jordan Sorenson on rhel-7.4.
Consider suggesting, in some but not all cases, for large databases, to compress the
*.sql
dump file.On CentOS 6 running MySQL 5.5, the mysqldump command doesn’t run, unless the mysqld daemon is running.
Further, the “-all-databases” doesn’t exist.
@Sorin
Okay, we will cross check this, thanks for the feedback.
You probably just missed the
"--"
, as in “--all-databases
“.@Sorin,
Thanks, corrected in the article..
It is still not corrected in the first export section, also like what was already pointed out mysqldump needs mysqld running to work.