Exporting and importing large databases in MySQL

To import a large database to mysql, if you have service management permissions, download and package the data you want to copy in the mysqldata directory, and put it in the data directory to be imported, however, if you do not have this permission, you can only perform the following operations.

In mysql, if you want to import a large database, if you have service management permissions, download the data you want to copy directly in the mysql data DIRECTORY, package the data you want to copy, and put it in the data directory to be imported, however, if you do not have this permission, you can only perform the following operations.

At this time, the native tool of MySQL can solve these problems well.

Example

Total number of records: 1016126, the average size of each line is 46822

Suppose we want to export and import a database named blog

Export:

Mysqldump-u database username-p password blog> path/export name. SQL

The Code is as follows:
Method: mysqldump-t-n -- default-character-set = latin1 test yejr>/backup/yejr. SQL

Time consumption: 2124 sec

The specific operation is as follows: open a command prompt (Windows is used as an example here) and enter the bin folder in the directory where mysql is installed (because my mysql is not installed as a local service, so you need to perform this step), and finally enter and run the above command.

Import:

Mysql-u database username-p password blog <path/database name. SQL

The Code is as follows:
Method: mysql test </backup/yejr. SQL

The above method can still be optimized in terms of performance, and we will try again later to use outfile

Export to text

The Code is as follows:
Method: SELECT * into outfile '/backup/yejr.txt' FROM yejr;

Duration: 3252.15 seconds

The operation is as above. Enter the bin folder at the command prompt and enter the command to run.

Conclusion:

1. using load data is a fast method

2. in the case of large data volumes, it is best to create a table and related indexes. although it is faster to import data without indexes, it takes a lot of time to create an index after the data is imported.