"How can I move MySQL from one physical server to another? Such as this exact scenario: I have a MySQL server that uses InnoDB table and is about 20GB in e. I want to move it to a new server, what's the most efficient way to do this?"
MySQL is a relational database management system based on the Structured Query Language. We utilize it for managing information stored in the computer, including personal data or the vast amount of info in a company. Thanks to this free-to-use database management system, the companies have the easy to power their business. So how to migrate the database between two servers for saving these data?
Follow our step-by-step guide to move MySQL database to another server by third-party database transfer tool and the backup program of MySQL. These two methods apply to migrate database in Windows.
There is no need to learn the command line of other articles mentioned; a few clicks can move the MySQL databases. That's why Qiling Disk Master stands out from them. All you need is to download this database migration tool and do the 3 easy steps. Trust Qiling! We are professional to move database from one server to another.
Click the download button for a trial:
Your smart Windows Server file transfer software.
Qiling Disk Master supports the migration of MySQL installed on the system disk by default. Also, it helps to transfer the custom installation of MySQL.
Step 1. Run Qiling Disk Master on both PCs
On the source PC, select "File sync" on the main screen of Qiling Disk Master and move on.
Step 2. Connect two computers
1. Select your target PC to connect via its IP address or device name and click "Connect".
2. Enter the login password of your target PC and click "OK" to confirm.
If you didn't see the target PC in the listed devices, you can also click "Add PC" to manually connect the target PC by inputting its IP address or device name.
Step 3. Select apps, programs, and software and transfer to the target computer
1. You can select and choose what you want to transfer from the current computer to the target PC.
2. After selecting desired applications and programs, click "Proceed" to start the transferring process.
If you want to do the remote transfer, you can try the "Backup and Restore" mode of Qiling Disk Master. In the next part, we will introduce database backup program to back it up as a single file using the mysqldump.
Migrating the database between two servers requires the Mysqldump, a command-line utility power to create the MySQL database backup, to clone MySQL database.
First, you need to export databases to Dump file. Then transfer it to the destination server. In the end, import MySQL databases to a new server.
Log in to your old server and input the systemctl command to stop the MySQL service:
systemctl stop mysql
Export your database as a dump file using mysqldump command:
Dump all MySQL databases: mysqldump -u [user] -p --all-databases > all_databases.sql
Dump a single database: mysqldump -u root -p --opt [database name] > database_name.sql
SCP is a file transfer program installed on Linux. If you want to use it, you can put the SCP command line on the Command Prompt. If it does not work, you can download the SCP first. Or go back to use Qiling Disk Master.
All databases: scp all_databases.sql support@idiskhome.com:~/
Single database: scp database_name.sql support@idiskhome.com:~/
Once it is done, you can see the database in the new server.
Run the following command line to import MySQL database's dump file into the new server.
All databases: mysql -u [user] -p --all-databases < all_databases.sql
Single database: mysql -u [user] -p newdatabase < database_name.sql
If you want to move any specific tables of MySQL database, input the following command:
mysqldump -u [username] -p [database] [table1] [table2] > dump.sql