Move large mysql database to new server

We all know the importance of a database in every system, in this article we ask you to back up, move database to another server.

This will be easily handled if your db has a small number of records. And if the number of records is too large (a few million records or more) then exporting on the current machine and importing to another server will take a lot of time, not feasible.

You can use one of two ways:

Method 1:

mysql -u root -p your_db_name < your_path/../database.sql
Example: C:/Users/admin/dumps/backup-2020.sql

Method 2: login with root:

mysql -u root -p –binary-mode=1
use your_db_name
source your_path/../database.sql (C:/Users/admin/dumps/backup-2020.sql)

However, in many cases there will be a dump error, because of the data type, it is not possible to import all the records into the new database.

I assume you are using mysql with Xampp server service, the data when running is saved with the folder name as the database name located C:\xampp\mysql\data\your_db_name, at the new server address, also installed corresponding xampp.

You just need to copy the folder your_db_name, move to the new server, the backup will be successful, together with some log files in mysql, the specific steps are as follows:

Your path database name

Step 1: Stop Mysql, Apache if you have started the application at the new server.

Step 2: Backup 3 files: ib_logfile0, ib_logfile1, ibdata1 in the new server.

Step 3: In the root server, at address C:\xampp\mysql\data

– Copy the folder your_db_name
– Copy 3 files: ib_logfile0, ib_logfile1, ibdata1
-> Move 1 folder and 3 files above to C:\xampp\mysql\data in the new server.

Step 4: Start MySQL, Apache in the new server.

Step 5: Check the connection to the new DB.

Note: If you want to return to the new server state before backup, you can return 3 files ib_logfile0, ib_logfile1, ibdata1 server to  C:\xampp\mysql\data, you will be able to reconnect with DB (if available before).