MySQL Import Export data

We all know that MyISAM reading data is faster than InnoDB, but importing a large number of tables is MyISAM record especially slow, here is my summary of MySQL in the import and export of big data to speed up the method

I. Import data using into outfile export, load data
1. Export the Big Data table (into outfile the exported data is text) mysql> select COUNT (*) from app_china;+----------+| count (*) |+----------+| 17931 |+-- --------+1 Row in Set (0.00 sec) mysql> Select * from App_china to outfile './app.txt ';   #导出数据的文件放在数据目录Query OK, 17931 rows Affected (0.02 sec)

 2. Importing a big data table exports text data, so you need to first create a table structure 
 mysql> CREATE table ' App_china ' (-id ' int (4) unsigned not null auto_increment,-> ' name ' varchar ($) NOT null DEFAULT ",-> ' Level ' tinyint (3) unsigned defaul T null,-> ' upid ' int (4) unsigned default null,-> ' area_id ' tinyint (3) unsigned not NULL DEFAULT ' 0 ' COMMENT ' big zone ID ',- > ' path ' varchar (+) not null,-> ' Firstletter ' char (1) DEFAULT null,-> PRIMARY KEY (' id ')--Engine=myisam A uto_increment=45055 DEFAULT Charset=utf8; Query OK, 0 rows affected (0.08 sec) mysql> load Data infile './app.txt ' into table App_china;  Query OK, 17931 rows affected (0.07 sec) #17931条数据用时0.07 seconds records:17931 deleted:0 skipped:0 warnings:0  

two. Export Import data using mysqldump 1. Export data [[email protected] ~]# time mysqldump-uroot-p 27_interna App_china > App_ China.sqlenter password:real    0m2.307suser    0m0.008ssys    <  app_china. sql Enter password:real    0m2.545suser    0m0.013ssys    0m0.000s3. Import with source not shown here, this method is the slowest

InnoDB Data import skills for a table of type InnoDB, adjusting the following two parameters when importing large data will greatly increase the import speed by 1. Turn off Autocommit set autocommit=0;2. Modify data write to disk operation set global Innodb_flush _log_at_trx_commit=0#默认都为1, please change the import after completion

Summarize:

When importing MyISAM large amounts of data can be imported using MySQL direct or using into outfile export as text and then load data import, using source will be time consuming

MySQL Import Export data