MySQL之Navicat、into outfile、mysql命令、mysqldump、mysqlpump、mydumper等导出导入文本数据（csv、txt、sql等）
Tags： csv文件into outfileLoad DatamydumperMySQLmysqldumpmysqlpumptxt文件数据迁移文本导入文本导出文本导出导入逻辑导出导入
👉 本文共约1182个字，系统预计阅读时间或需5分钟。
总结
Navicat、into outfile、mysql命令、mysqldump、mysqlpump、mydumper导出导入数据
| 工具 | 导出命令 | 导入命令 | 导出文件模式 | 导出导入文件位置 | 导入效率 |
|---|---|---|---|---|---|
| Navicat | 界面操作 | 1、界面操作、LOAD DATA INFILE 2、mysqlimport命令 | csv、txt或SQL | 客户端 | |
| into outfile | select * from sbtest.sbtest6 into outfile '/mysqldata/sbtest6.sql' FIELDS TERMINATED BY ',' ; | 1、LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ','; 2、mysqlimport命令 | csv、txt | 服务器 | LOAD DATA需要花费5小时 |
| mysql命令 | mysql -h127.0.0.1 -uroot -plhr -q -D lhrdb --execute="select * from sensor;" > sensor_mysql.txt | 1、LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ','; 2、mysqlimport命令 | csv、txt | 客户端 | |
| mysqldump | mysqldump -S/tmp/mysql.sock --databases business_db_jingbain --set-gtid-purged=OFF --tables sensor > sensor_mysqldump.sql | 1、source a.sql 2、mysql -f -h192.168.1.35 -P3340 -u root -plhr < sbtest_data.sql | SQL | 客户端 | 开4个线程，导入花费需要6小时 |
| mysqlpump | mysqlpump -uroot -plhr -h192.168.66.35 -P13341 sbtest sbtest1 --single-transaction --set-gtid-purged=OFF --default-parallelism=4 > /bk/sbtest_sbtest1.sql | 1、source a.sql 2、mysql -f -h192.168.1.35 -P3340 -u root -plhr < sbtest_data.sql | SQL | 客户端 | 开4个线程，导入花费需要6小时 |
| mydumper | mydumper -h 192.168.66.35 -u root -p lhr -P 13341 -B sbtest -T sbtest1 -l 14400 -r 100000 -t 8 -k -o /data/ | 1、myloader -h localhost -u root -p lhr -B sbtest -o sbtest1 -t 4 -d /data/ -v 3 2、source a.sql 3、mysql -f -h192.168.1.35 -P3340 -u root -plhr < sbtest_data.sql | SQL | 客户端 | 开4个线程，导入花费需要385分钟，大概6.5小时 |
| MySQL Shell | util.dumpSchemas(['sbtest'],'/data/backup/schema') | util.loadDump("/data/backup/full",{loadUsers: true}) | txt | 客户端 |
实例SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 | -- 总数据量大约70G，行数为1632840301 1、Navicat 导出26G后报错 效率：505308501行/41421s=12200行/s 或 1.7M/s -- 2、使用mysql导出，花费约3小时30分钟，约12600秒，效率为1632840301行/12600秒=129590行/s 或 5.7M/s [root@OCPLHR data]# date Fri Dec 13 09:03:47 CST 2019 [root@OCPLHR data]# mysql -h192.168.1.35 -uroot -pLHR -D business_db_jingbain --execute="select * from sensor;" -q > sensor_mysql.txt [root@OCPLHR data]# date Fri Dec 13 12:32:32 CST 2019 [root@OCPLHR data]# ll total 71000516 -rw-r--r-- 1 root root 72633519676 Dec 13 12:32 sensor.txt [root@OCPLHR data]# ll -h total 68G -rw-r--r-- 1 root root 68G Dec 13 12:32 sensor.txt 注意：mysql -h192.168.1.1 -uroot -p123 -D dbxx --execute="select * from XXT;" > sensor111.txt 其中XXT表一共70G，如果用这种方式导出数据，那么一定需要加上-q参数，否则这个命令会把内存撑爆。先耗内存，然后耗swap空间，直到OS夯住。 -q, --quick Don't cache result, print it row by row. This may slow down the server if the output is suspended. Doesn't use history file. -- 3、使用mysqldump导出，花费约200分钟，基本和mysql导出性能差不多 /var/lib/mysql57/mysql5719/bin/mysqldump -h192.168.1.35 -uroot -pLHR --databases business_db_jingbain --set-gtid-purged=OFF --tables sensor > sensor_mysqldump.sql [root@LHRDB data]# time /var/lib/mysql57/mysql5719/bin/mysqldump -h192.168.1.35 -uroot -pLHR --databases business_db_jingbain --set-gtid-purged=OFF --tables sensor > sensor_mysqldump.dmp mysqldump: [Warning] Using a password on the command line interface can be insecure. real 200m8.739s user 38m21.922s sys 12m21.432s [root@LHRDB data]# ll total 77311652 -rw-r--r-- 1 root root 79167122033 Dec 13 18:00 sensor_mysqldump.dmp [root@LHRDB data]# ll -h total 74G -rw-r--r-- 1 root root 74G Dec 13 18:00 sensor_mysqldump.dmp -- 4、使用mysqlpump导出 mysqlpump -h192.168.1.35 -uroot -pLHR database business_db_jingbain sensor --set-gtid-purged=OFF > sensor_mysqlpump.sql /var/lib/mysql57/mysql5719/bin/mysqldump -S/var/lib/mysql57/mysql5719/data57193310/mysql57193310.sock business_db_jingbain sensor --set-gtid-purged=OFF > sensor_mysqlpump.sql -- 5、使用mydumper导出，开10个线程，花费约2小时，效率为1632840301行/3600秒=453566行/s 或 21M/s mydumper -h 192.168.1.35 -u root -p LHR -B business_db_jingbain -T sensor -l 14400 -r 150000000 -t 12 -k -o /data/ myloader -h localhost -u root -p lhr -B business_db_jingbain -o sensor -t 12 -d /data/ -v 3 mydumper -S /var/lib/mysql57/mysql5719/data57193310/mysql57193310.sock -B business_db_jingbain -T sensor -l 14400 -r 60000000 -t 12 -k -o /data/datatmp/ [root@LHRDB data]# mydumper -h 192.168.1.35 -u root -p LHR -B business_db_jingbain -T sensor -l 14400 -r 150000000 -t 12 -k -o /data/ ** (mydumper:28001): WARNING **: Executing in no-locks mode, snapshot will notbe consistent [root@LHRDB data]# ll -h total 74G -rw-r--r-- 1 root root 82 Dec 16 12:39 business_db_jingbain-schema-create.sql -rw-r--r-- 1 root root 25M Dec 16 12:40 business_db_jingbain.sensor.00000.sql -rw-r--r-- 1 root root 24M Dec 16 12:40 business_db_jingbain.sensor.00001.sql -rw-r--r-- 1 root root 21M Dec 16 12:40 business_db_jingbain.sensor.00002.sql -rw-r--r-- 1 root root 23M Dec 16 12:40 business_db_jingbain.sensor.00003.sql -rw-r--r-- 1 root root 24M Dec 16 12:40 business_db_jingbain.sensor.00004.sql -rw-r--r-- 1 root root 20M Dec 16 12:40 business_db_jingbain.sensor.00005.sql -rw-r--r-- 1 root root 21M Dec 16 12:40 business_db_jingbain.sensor.00006.sql -rw-r--r-- 1 root root 22M Dec 16 12:40 business_db_jingbain.sensor.00007.sql -rw-r--r-- 1 root root 21M Dec 16 12:40 business_db_jingbain.sensor.00008.sql -rw-r--r-- 1 root root 21M Dec 16 12:40 business_db_jingbain.sensor.00009.sql -rw-r--r-- 1 root root 467 Dec 16 12:39 business_db_jingbain.sensor-schema.sql -rw-r--r-- 1 root root 150 Dec 16 12:39 metadata.partial [root@LHRDB data]# ll -h total 134G -rw-r--r-- 1 root root 82 Dec 16 12:39 business_db_jingbain-schema-create.sql -rw-r--r-- 1 root root 5.9G Dec 16 14:06 business_db_jingbain.sensor.00000.sql -rw-r--r-- 1 root root 5.6G Dec 16 14:06 business_db_jingbain.sensor.00001.sql -rw-r--r-- 1 root root 5.9G Dec 16 14:06 business_db_jingbain.sensor.00002.sql -rw-r--r-- 1 root root 6.1G Dec 16 14:06 business_db_jingbain.sensor.00003.sql -rw-r--r-- 1 root root 6.2G Dec 16 14:06 business_db_jingbain.sensor.00004.sql -rw-r--r-- 1 root root 6.0G Dec 16 14:06 business_db_jingbain.sensor.00005.sql -rw-r--r-- 1 root root 5.8G Dec 16 14:06 business_db_jingbain.sensor.00006.sql -rw-r--r-- 1 root root 6.6G Dec 16 14:06 business_db_jingbain.sensor.00007.sql -rw-r--r-- 1 root root 6.0G Dec 16 14:06 business_db_jingbain.sensor.00008.sql -rw-r--r-- 1 root root 5.8G Dec 16 14:06 business_db_jingbain.sensor.00009.sql -rw-r--r-- 1 root root 467 Dec 16 12:39 business_db_jingbain.sensor-schema.sql -rw-r--r-- 1 root root 150 Dec 16 12:39 metadata [root@LHRDB data]# ll -h total 149G -rw-r--r-- 1 root root 82 Dec 16 12:39 business_db_jingbain-schema-create.sql -rw-r--r-- 1 root root 7.5G Dec 16 14:30 business_db_jingbain.sensor.00000.sql -rw-r--r-- 1 root root 7.5G Dec 16 14:32 business_db_jingbain.sensor.00001.sql -rw-r--r-- 1 root root 7.5G Dec 16 14:30 business_db_jingbain.sensor.00002.sql -rw-r--r-- 1 root root 7.5G Dec 16 14:28 business_db_jingbain.sensor.00003.sql -rw-r--r-- 1 root root 7.5G Dec 16 14:27 business_db_jingbain.sensor.00004.sql -rw-r--r-- 1 root root 7.5G Dec 16 14:30 business_db_jingbain.sensor.00005.sql -rw-r--r-- 1 root root 7.6G Dec 16 14:32 business_db_jingbain.sensor.00006.sql -rw-r--r-- 1 root root 7.7G Dec 16 14:23 business_db_jingbain.sensor.00007.sql -rw-r--r-- 1 root root 7.7G Dec 16 14:30 business_db_jingbain.sensor.00008.sql -rw-r--r-- 1 root root 7.7G Dec 16 14:32 business_db_jingbain.sensor.00009.sql -rw-r--r-- 1 root root 467 Dec 16 12:39 business_db_jingbain.sensor-schema.sql -rw-r--r-- 1 root root 188 Dec 16 14:32 metadata ------------ sqlldr导入 CREATE TABLE xxt.sensor ( id int , record_date date , value varchar2(300), sid int , gid int ) tablespace xxt nologging; options(SKIP=1,errors=1000) --options(SKIP=1,ROWS=1000,errors=1000) UNRECOVERABLE load data LENGTH CHARACTER infile '/data/sensor_mysql.txt' APPEND into table xxt.sensor fields terminated by x'09' trailing nullcols ( id, record_date, decode(value,'NULL','') , sid , gid ) sqlldr xxt/lhr control= xxt.ctl parallel=y log='log.txt' bad='bad.bad' direct=true readsize=510430400 streamsize=510430400 multithreading=y ----------- 结果 Bind array size not used in direct path. Column array rows : 5000 Stream buffer bytes:16777216 Read buffer bytes:510430400 Total logical records skipped: 1 Total logical records read: 1632840300 Total logical records rejected: 0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 326640 Total stream buffers loaded by SQL*Loader load thread: 0 Run began on Mon Dec 16 11:32:18 2019 Run ended on Mon Dec 16 13:05:23 2019 Elapsed time was: 01:33:04.33 CPU time was: 01:19:30.76 ------------------- select count(*) from sensor; --653136040行，44G csv文件，花费37181s，csv文件大约28g ------------- mysqldump mysqldump -uroot -plhr -h192.168.1.35 --single-transaction --hex-blob --set-gtid-purged=OFF --databases jl_hotel > jl_hotel.sql -- http://blog.itpub.net/26736162/viewspace-2686075/ mysqldump --databases jl_hotel --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -uroot -plhr -h192.168.1.35 -P3306 |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' -e 's/DEFINER[ ]*=.*SQL SECURITY DEFINER/SQL SECURITY DEFINER/' > jl_hotel.sql mysqldump --databases jl_hotel --single-transaction --hex-blob --set-gtid-purged=OFF -uroot -plhr -h192.168.1.35 -P3306 > jl_hotel_data.sql mysqldump --databases jl_hotel -uroot -plhr -h192.168.1.35 -P3306 > jl_hotel_data.sql mysql -f -h192.168.1.35 -P 3306 -u root -plhr < jl_hotel.sql [root@lhrcentos76 mysql]# mysqldump --databases jl_hotel --single-transaction --hex-blob --set-gtid-purged=OFF --no-create-info --skip-triggers -uroot -plhr -h192.168.1.35 -P3306 -r jl_hotel_data.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `sys_api_log` at row: 112614 mysqldump导出比较慢，报错：mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `sys_api_log` at row: 112614 ---导出时设置 set global wait_timeout=28800000; set global net_read_timeout=28800; set global net_write_timeout=28800; set global max_allowed_packet=2147483648; ---导入 set sql_log_bin=0; set global innodb_flush_log_at_trx_commit = 2; set global sync_binlog = 20000; set global max_allowed_packet=100000000; set global net_buffer_length=100000; set global interactive_timeout=28800000; set global wait_timeout=28800000; mysqldump --single-transaction --hex-blob --set-gtid-purged=OFF -uroot -plhr -h192.168.1.35 -P3350 --databases sbtest > sbtest_data.sql mysql -f -h192.168.1.35 -P3340 -u root -plhr < sbtest_data.sql mysql -f -uroot -plhr -h192.168.1.35 -P3309 -D ehr_dev < C:\Users\lhrxxt\Desktop\ehr_dev_datafull.sql --- https://support.huaweicloud.com/bestpractice-rds/rds_02_0010.html --- 触发器 set GLOBAL log_bin_trust_function_creators=on; DROP FUNCTION if exists rand_string; delimiter // CREATE DEFINER=`root`@`%` FUNCTION lhrdb.`rand_string`(n INT) RETURNS varchar(255) CHARSET utf8 BEGIN DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE return_str varchar(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); SET i = i +1; END WHILE; RETURN return_str; END;// delimiter ; select lhrdb.rand_string(5); -- 存储过程 delimiter // DROP PROCEDURE IF EXISTS mock_isam// CREATE PROCEDURE mock_isam (IN rowCount int) BEGIN DECLARE insertCount int; SET insertCount = 0; DROP TABLE IF EXISTS `isam_table`; /*删掉之前创建的表，因为下面要建这个名字的表*/ /*自己按需求修改以下建表语句构造需要的表*/ CREATE TABLE `isam_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` tinyint(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; START TRANSACTION; loopHandler : LOOP /*插入数据*/ INSERT INTO `isam_table`(`name`,`age`) VALUES( rand_string(20), CEIL(RAND() * 110) ); SET insertCount = insertCount + 1; IF (insertCount >= rowCount) THEN LEAVE loopHandler; END IF; END LOOP loopHandler; COMMIT; END // delimiter ; delimiter // DROP PROCEDURE IF EXISTS mock_innodb// CREATE PROCEDURE mock_innodb (IN rowCount int) BEGIN DECLARE insertCount int; SET insertCount = 0; DROP TABLE IF EXISTS `innodb_table`; /*删掉之前创建的表，因为下面要建这个名字的表*/ /*自己按需求修改以下建表语句构造需要的表*/ CREATE TABLE `innodb_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` tinyint(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=innodb DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; START TRANSACTION; loopHandler : LOOP /*插入数据*/ INSERT INTO `innodb_table`(`name`,`age`) VALUES( rand_string(20), CEIL(RAND() * 110) ); SET insertCount = insertCount + 1; IF (insertCount >= rowCount) THEN LEAVE loopHandler; END IF; END LOOP loopHandler; COMMIT; END // delimiter ; call mock_innodb(20000); -- 66s call mock_isam(20000); -- 108s select * from innodb_table; select * from isam_table; select * from mysql.proc where db = 'lhrdb' and `type` = 'PROCEDURE' ; //存储过程 select * from mysql.proc where db = 'lhrdb' and `type` = 'FUNCTION' ; //函数 -- 触发器 create table lhrdb.time (time varchar(100)); CREATE TRIGGER lhrdb.trig1 AFTER INSERT ON isam_table FOR EACH ROW INSERT INTO time VALUES(NOW()); SHOW TRIGGERS from lhrdb; select * from information_schema.`TRIGGERS` where trigger_schema='lhrdb' ; -- event事件 drop table if exists lhrdb.events_list; create table lhrdb.events_list(id int PRIMARY key auto_increment, event_name varchar(20) not null, event_started timestamp not null); drop event lhrdb.event_minute; create event lhrdb.event_minute on schedule every 10 minute do insert into lhrdb.events_list(event_name,event_started) values('event_now', now()); set global event_scheduler =1; show processlist; show events; select * from information_schema.`EVENTS` where event_schema='lhrdb' ; select * from events_list; -- 视图 create or replace view vw_pro_tri_fun_event_lhr as select 'EVENTS' type,event_name NAME,DEFINER from information_schema.`EVENTS` where event_schema='lhrdb' union all select 'TRIGGER',trigger_name,DEFINER from information_schema.`TRIGGERS` where trigger_schema='lhrdb' union all select 'PROCEDURE',NAME,DEFINER from mysql.proc where db = 'lhrdb' and `type` = 'PROCEDURE' union all select 'FUNCTION',NAME,DEFINER from mysql.proc where db = 'lhrdb' and `type` = 'FUNCTION' union all select 'VIEW',TABLE_name,DEFINER from information_schema.VIEWS where TABLE_SCHEMA = 'lhrdb' ; select * from vw_pro_tri_fun_event_lhr; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
