Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to quickly export and import a large amount of data in the MySQL database. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

There are three methods commonly used 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. The data files and database table structure files are directly copied to the new server and mounted to the MySQL service with the same configuration.

The advantages of the first scheme: it will rebuild the data file, reduce the occupied space of the data file, the compatibility is the best, the export and import has few problems, and the demand is flexible. Cons: using traditional export to import takes a long time.

The advantage of the second scheme: after the setting is completed, the transmission is unattended and completed automatically. Disadvantages: inflexible, tedious setup, long transmission time, it is difficult to continue transmission from the abnormal location after the exception.

The advantages of the third scheme are as follows: short time occupation, file breakpoint transfer and few operation steps. Cons: 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 changes that need to change the structure of the data table (such as partitioned tables), we can only use the first method.

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

LOAD DATA INFILE statements are read into a table at a high speed from a text file. The official MySQL documentation also shows that this method is 20 times faster than inserting a piece of data at once.

When users use SELECT back and forth. When INTO OUTFILE and LOAD DATA INFILE write data from a database to a file and then read it into the database from the file, the fields and line processing options of the two commands must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file correctly.

Here is an example of a project where MySQL is migrated from windows platform to Linux platform with a total data volume of 12 gigabytes

Windows platform exports 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 in a loop: if the sub-table is involved in the process, the exported sql statements and batch code can be modified according to the sub-table rules, which is very flexible.

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

Linux platform imports data:

#! / bin/bashwhile read linedo mysql- uroot-p12345678 codetc_new-e "LOAD DATA INFILE'/ var/lib/mysql-files/$line.txt' INTO TABLE $line FIELDS TERMINATED BY','" done < tables.txt on how to quickly export and import a large amount of data in the MySQL database is shared here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.