MySQL database migration quickly exports and imports large amounts of data

Database migration is a problem we often encounter. For a small amount of data, migration will basically not be a problem. In the production environment, migration work is required in the following situations:

• Insufficient disk space. For example, for some old projects, the selected models may not necessarily be suitable for the database. As time goes by, there is a high probability that hard drives will become scarce;
• There is a bottleneck in the business. For example, if a single machine is used to handle all read and write operations in a project, the business pressure will increase and become unbearable. If the IO pressure is within an acceptable range, a read-write separation solution will be adopted;
• The machine is bottlenecked. The bottlenecks of the machine are mainly disk IO capacity, memory, and CPU. In addition to optimizing the bottlenecks, migration is a good solution.
• Project transformation. The databases of some projects are located across different computer rooms, so it is possible to add nodes in different computer rooms or migrate machines from one computer room to another. For example, different businesses share the same server, and migration will be performed to relieve server pressure and facilitate maintenance.

There are three common methods for MySQL migration:

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

2. Use third-party migration tools.

3. Copy the data files and library table structure files directly to the new server and mount them to the MySQL service with the same configuration.

The advantages of the first solution are: it will rebuild the data files, reduce the space occupied by the data files, has the best compatibility, rarely has problems with exporting and importing, and is flexible in requirements. Disadvantages: Traditional export and import takes a long time.

Advantages of the second solution: After the settings are completed, the transmission is unattended and completed automatically. Disadvantages: Not flexible enough, complicated to set up, long transmission time, and it is difficult to continue transmission from the abnormal location after an abnormality occurs.

The advantages of the third solution: less time, files can be transferred at breakpoints, and fewer operation steps. Disadvantages: The MySQL version and configuration in the new and old servers must be the same, which may cause unknown problems.

If the database migration is due to business bottlenecks or project transformation, which requires changes to the data table structure (such as partitions and tables), we can only use the first method.

Use MySQL's SELECT INTO OUTFILE and LOAD DATA INFILE to quickly export and import data

The LOAD DATA INFILE statement reads data from a text file into a table at a very high speed. The MySQL official documentation also states that this method is 20 times faster than inserting one row of data at a time.

When you use SELECT ... INTO OUTFILE and LOAD DATA INFILE in tandem to write data from a database to a file and then read it from the file into the database, the field and row handling options of the two commands must match. Otherwise, LOAD DATA INFILE will not be able to interpret the file contents correctly.

The following is an example of a project where MySQL is migrated from Windows to Linux with a total data volume of 12G.

Export data on Windows platform:

Tables.txt is a file that stores the names of data tables. By reading the names of data tables from the file, all tables are exported in a loop. If sub-tables are involved in the process, the exported SQL statements and batch codes 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

Import data on Linux platform:

#!/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 importing data, you need to create a table structure on the new machine. It takes about 3 minutes to export 12G of data and about 4 minutes to import it (the execution time will vary depending on the configuration of the machine and is not of reference value)

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links