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 mysqlThen dump all your MySQL databases to a single file using the mysqldump command.
# mysqldump -u [user] -p --all-databases > all_databases.sqlOnce 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.sqlTransfer 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.
To create a copy of an existing MySQL database, you need to make use of MySQL internal tools called mysqldump.
The mysqldump tool is usually installed when you install the MySQL server. It can be accessed from your computer’s terminal or command line using the mysqldump command.
You can use the following command to check if mysqldump exists on your computer:
$ which mysqldump /usr/local/mysql/bin/mysqldump
The result above shows that mysqldump is installed on the bin/ folder of the MySQL installation folder.
Just like its name, mysqldump is used to “dump” one or more MySQL databases as a .sql file. The dump result can then be imported into MySQL server to create a copy or a backup of the original database.
For example, suppose you have a source database named school_db and you want to create a copy named school_db_copy as a backup.
First, you need to create an SQL dump file of the existing database using the mysqldump command as follows:
mysqldump --user=[username] --password=[password] school_db > dump.sql
Replace [username] and [password] above with your actual MySQL username and password.
Now that the dump.sql file has been created, you need to create the target database where the dump.sql file content will be imported.
You can log in to your MySQL server and create a new database using CREATE DATABASE statement as follows:
CREATE DATABASE school_db_copy;
Alternatively, you can use the mysqladmin command line tool which is very similar to mysqldump tool.
While mysqldump tool allows you to create a dump file of MySQL databases, the mysqladmin tool allows you to perform administrative tasks like creating and dropping databases from the command line.
Use the create command to create your database as shown below:
mysqladmin --user=[username] --password=[password] create school_db_copy
Now that you have a new database, all you need to do is import the dump.sql file with the mysql command as follows:
mysql --user=[username] --password=[password] school_db_copy < dump.sql
Now the school_db_copy database should have the exact same tables and data as school_db.
Copy MySQL database into another server
The MySQL dump file is essentially a bunch of statements to create tables and insert values derived from an existing database.
You can also use the file to copy a database from one MySQL server into another server by adding the --host option to the commands.
First, dump the database from your machine with mysqldump command:
mysqldump --user=[username] --password=[password] school_db > dump.sql
Then, use mysqladmin command with the --host option to create a database on the destination server.
For example, suppose you want to create a database on host 192.168.2.2, this is how you issue the command:
mysqladmin --host=192.168.2.2 \ --user=[username] --password=[password] create school_db_copy
Now that you have the school_db_copy database on the destination server, you can use mysql command to run the dump.sql file on that database by adding the --host option as well:
mysql --host=192.168.2.2 \ --user=[username] --password=[password] school_db_copy < dump.sql
And that’s how you copy a MySQL database into another server.
Copy MySQL database using client applications
If you’re using a MySQL client application to connect to a MySQL database server, then your client application probably has the feature to duplicate database(s) for your convenience.
For example, MySQL Workbench has Data Export and Data Import/Restore options from the Administration tab to help you create a MySQL dump file that you can use to copy your database over:
In the Data Export window, you can select which databases and the tables you wish to export from the server as a dump file. MySQL Workbench allows you to export each database table as its own .sql file so that you can selectively restore them later.
You can select whether to export the entire database tables or just specific tables.
Once you set the options, click the Start Export button to start the export process. Next, you can use the Data Import/Restore option from the left panel to restore your databases.
Other MySQL client application like Sequel Ace has the Duplicate Database command inside the Database tab.
You should be able to find similar feature in other MySQL client applications like PHPMyAdmin and SQLyog as well.