MySQL Database Migration Quick Export Import Large Amount of Data

Database migration is a problem that we often encounter. For a small amount of data, migration is basically no problem. In a production environment, there are the following situations that need to be migrated:

There is not enough disk space. For example, for some old projects, the selected models are not necessarily suitable for the database. As time goes by, there is a good chance that there will be a shortage of hard disks; There is a bottleneck in business. For example, in the project, a single machine is used to undertake all the reading and writing business, which makes the business pressure increase and overwhelmed. If the IO pressure is in an acceptable range, the read-write separation scheme will be adopted; There is a bottleneck in the machine. The bottleneck of the machine is mainly in the disk IO capability, memory and CPU. At this time, in addition to doing some optimization for the bottleneck, choosing migration is a good scheme; Project transformation. The database of some projects has the situation of crossing computer rooms, which may add nodes in different computer rooms or migrate machines from one computer room to another. For another example, different businesses share the same server, which will also be migrated in order to relieve the pressure on the server and facilitate maintenance.

MySQL migration usually uses three methods:

1. Export the database directly, copy the file to the new server and import it on the new server.

2. Use the third-party migration tool.

3. The data files and library table structure files are copied directly to the new server and mounted under the MySQL service with the same configuration.

Advantages of the first scheme: it will rebuild data files, reduce the space occupied by data files, have the best compatibility, have few problems in exporting and importing, and have flexible requirements. Disadvantages: Using traditional export takes a long time to import.

Advantages of the second scheme: After the setting is completed, the transmission is unattended and completed automatically. Disadvantages: It is not flexible enough, complicated to set up, long transmission time, and it is difficult to continue transmission from the abnormal position after exception.

Advantages of the third scheme: short time occupation, file breakpoint transmission and few operation steps. Disadvantages: The MySQL version and configuration must be the same in the old and new servers, which may cause unknown problems.

If the database migration is due to business bottlenecks or project transformation needs to change the data table structure (such as partition sub-table), we can only use the first method.

Use SELECT INTO OUTFILE, LOAD DATA INFILE of MySQL to quickly export import data

LOAD DATA INFILE Statement reads from a text file into a table at a high speed. MySQL official documents also show that this method is 20 times faster than inserting one piece of data at a time.

When user 1 uses it before and after SELECT ... INTO OUTFILE And LOAD DATA INFILE When you write data from 1 database to 1 file and then read it from the file to the database, the field and row processing options of the two commands must match. Otherwise, LOAD DATA INFILE will not interpret the file contents correctly.

The following is an example of a project where MySQL is migrated from windows platform to Linux platform, and the total data volume is 12G

Windows platform export data:

tables. txt is a file that saves the name of the data table. By reading the name of the data table from the file, all tables are exported cyclically. If the sub-table is taken in the process, the exported sql statement and batch code can be modified according to the sub-table rules, which is very flexible.

@echo off & setlocal enabledelayedexpansion
for /f %%i in (tables.txt) do ( set table=%%i
 echo "dump table -- !table! --"
 mysql -uroot -p12345678 codetc_old -e "SELECT * INTO OUTFILE 'F:/MySQL/Uploads/!table!.txt' FIELDS TERMINATED BY ',' FROM !table!"
)
pause

Linux platform import data:

#!/bin/bash
while read line
do
    mysql -uroot -p12345678 codetc_new -e "LOAD DATA INFILE '/var/lib/mysql-files/$line.txt' INTO TABLE $line FIELDS TERMINATED BY ','"
done < tables.txt

Before data import, you need to create a table structure on the new machine. The data export of 12G takes about 3 minutes, and the import takes about 4 minutes (the execution time will be different according to the configuration of the machine, so it has no reference value)

Summarize