Navicat、into outfile、mysql命令、mysqldump、mysqlpump、mydumper导出导入数据

--总数据量大约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
4、使用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

mysqldump

--- 触发器
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;
 
------------- mysqldump
--- MySQL替换掉“DEFINER=`root`@`xxx`”，避免RDS中错误：[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysqldump --databases lhrdb --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u root -plhr@zr123 -h 121.36.12.84 -P 3306 |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/' > d:\C.sql
cat a.sql  |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/' > b.sql
------------- mysqldump
-- 直接导出表结构和数据
mysqldump -uroot -plhr -h192.168.1.35 -P3306 --single-transaction --hex-blob --routines --events --triggers --set-gtid-purged=OFF --databases jl_hotel > jl_hotel.sql
--导出表结构
mysqldump -uroot -plhr -h192.168.1.35 -P3306 --databases jl_hotel --single-transaction --hex-blob --no-data --routines --events --triggers --set-gtid-purged=OFF  | 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_ddl.sql
--导出数据
mysqldump -uroot -plhr -h192.168.1.35 -P3306 --databases jl_hotel --single-transaction --hex-blob --no-create-info --skip-triggers --set-gtid-purged=OFF > jl_hotel_data.sql
--导入表结构和数据
mysql -f -h192.168.1.35 -P 3306 -u root -plhr  < jl_hotel_ddl.sql
mysql -f -h192.168.1.35 -P 3306 -u root -plhr -D ehr_dev < jl_hotel_data.sql
---导出时设置
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;
1、如果单独导出表，若表上有触发器，则会自动导出触发器。

About Me