How do i copy a mysql database to another server?

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.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.

Using MySQL Workbench [Migration Wizard]

To directly copy a database from one server to another (even a local one) without creating intermediary export/dump files, you can do so within MySQL Workbench using its Migration Wizard. Go to Database --> Migration Wizard.

From there you can select the source and destination connections if you've previously saved them to your Connections list, or you can create a new connection on the fly (be sure to check the box add it to your Connections list).
You'll also choose the database type, or select their generic version (don't remember what it's called) and let it figure out what kind of database servers you have. Generally you can select MySQL for either MySQL or MariaDB databases, as MariaDB is generally designed to be a "binary drop-in replacement" to MySQL.

When it asks you to select which databases, it's my understanding that INFORMATION_SCHEMA is read only, and should not be selected. Otherwise, unless you have specific needs, probably select all the other tables, if you are, say, cloning the server (and perhaps its related website).

From there you'll let MySQL Workbench do its thing. Keep clicking next while it goes through the process of reverse engineering your schema from the old to the new servers, until it finally copies the data over.

There are many points where you can make changes before proceeding. Also you have the option of viewing potential issues and warnings it notes during the process.

When it's done, you'll still need to add users (manually?) for the cloned database. There is probably an automated way to clone the users table over as well, but I don't know about it. You can do this from the command line if nothing else. Since I use this for my local dev, I only have myself to add anyway.

I'm not sure it's any faster using this tool than exporting and importing the databases in the usual manner. But it's sure easier.
This tool is actually designed for more complex migrations between differing kinds of databases (eg PostgressSQL and MySQL) rather than as a general tool for, say, retrieving a copy of your live database down to your local dev server or vice versa.

Under the same menu, there is Database -> Schema Transfer Wizard, which I understood to be used when you need to update to a new server version. I'm not clear specifically the differences between the two tools, or their actual use cases.

Instructions on how to do standard Import / Export operations, in MySQL workbench, the docs are SQL Data Export and Import Wizard, and Table Data Export and Import Wizard.

It would be cool if it were possible to directly import / export between dev and live servers, simply by choosing Connections, rather than saving then reading in dump files.
I think it can be done via the command line though.

How do I copy a database from one server to another?

How do I move a MySQL database from one server to another using Workbench?

How do I move a SQL database to another server?

How do I export an entire MySQL database?