Mysql亿级数据大表单表备份

上一篇Mysql已有亿级数据大表按时间分区，介绍了亿级数据大表如何按时间分区，也留下了一个问题：备份亿级数据大表要耗时多久。本篇将就如何备份亿级数据大表展开讨论。
注意：我这里所说的备份指的是数据从一张表拷贝到另外一张表，也就是说单表备份。

创建原表t_send_message_send的sql：

CREATE TABLE `t_send_message_send` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plan_id` bigint(20) DEFAULT NULL,
  `job_uuid` varchar(36) DEFAULT NULL,
  `send_port` varchar(16) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  `product_code` varchar(16) DEFAULT 'HELP',
  `fake` bit(1) DEFAULT b'0',
  `date_push` datetime DEFAULT NULL,
  `activity_id` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mobile` (`mobile`),
  KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

原表一个自增主键id，两个索引mobile、date_push，数据量如下图：

创建新表的t_send_message_send2的sql：

CREATE TABLE `t_send_message_send2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plan_id` bigint(20) DEFAULT NULL,
  `job_uuid` varchar(36) DEFAULT NULL,
  `send_port` varchar(16) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  `product_code` varchar(16) DEFAULT 'HELP',
  `fake` bit(1) DEFAULT b'0',
  `date_push` datetime NOT NULL,
  `activity_id` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`,`date_push`),
  KEY `mobile` (`mobile`),
  KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(date_push)
(PARTITION p2016 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);

新表一个联合主键(id，date_push)，两个索引mobile、date_push，5个分区，字段和结构跟原表一样，数据量为0。

上一篇提供了两类备份方式：①在线备份；②离线备份。

1.在线备份；
数据一直在数据库中不离线。

insert into t_send_message_send2 (select * from t_send_message_send);

sql很简单，意思很明确，就是将select的查询结果插入到t_send_message_send2。这个过程我跑了一个多小时，没跑完，被我中止了。用navicate查看t_send_message_send2的对象信息，看到有500多万行记录，打开t_send_message_send2表，里面一行记录都没有，空的。应该是请求中止了，数据还没提交。好吧，看下为什么慢，解析下：

EXPLAIN
insert into t_send_message_send2 (select * from t_send_message_send);

执行结果：

"id"	"select_type"	"table"	"partitions"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"filtered"	"Extra"
"1"	"INSERT"	"t_send_message_send2"	"p2016,p2017,p2018,p2019,p2020"	"ALL"	""	""	""	""	""	""	""
"1"	"SIMPLE"	"t_send_message_send"	""	"ALL"	""	""	""	""	"100568970"	"100.00"	""

好家伙，第5列type都是ALL。type表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从前往后，性能从差到好）。ALL，Full Table Scan， MySQL将遍历全表以找到匹配的行。明白了吧，每次插入全表扫描，这能不慢吗？

2. 离线备份
数据先导出到本地，再从本地导回数据库。
1）数据导出（数据备份）
离线备份也分为冷备和热备。
冷备：数据库处于关闭的状态下的备份，优点是：①保证数据库的完整性；②备份过程简单并且恢复速度快。缺点是：①关闭数据库，意味着相关的业务无法正常进行，用户无法访问你的业务，一般冷备用于不是很重要、非核心业务上面。
冷备显然是不满足我的业务需求的，冷备是全库备份，而我只是单表备份。
热备：数据库处于运行状态下的备份，不影响现有业务的进行。热备又分为裸文件备份和逻辑备份。裸文件备份：基于底层数据文件的copy datafile。进入到数据库的数据目录，再进入到你的库目录，你会发现在这个目录下有很多.frm文件和.ibd文件，.frm文件是表的结构文件，.ibd文件是表的数据文件。逻辑备份：备份成SQL语句或者其他文件（如csv），恢复时执行SQL，实现数据库数据的重现。
裸文件备份显然也是全库备份，也是不满足我的业务需求的，下面讨论逻辑备份。
逻辑备份常见的两种方式：
①mysqldump

mysqldump -u root -p marketing t_send_message_send > e:/mysql/marketing_t_send_message_send.sql;

哈哈哈，暴露了在windows上操作的。
mysqldump导出相当快，亿级的记录，50多个G数据量，大概仅用了40分钟左右。没记录到具体时间，是因为执行这个脚本不需要登录到mysql，命令行就可以了，而命令行不会提示执行脚本花了多长时间，如果登录mysql，每次执行都会提示执行脚本好了多长时间。

②select … into outfile …;

mysql> use marketing;
Database changed
mysql> select * from t_send_message_send into outfile 'e:/mysql/t_send_message_send.csv';
Query OK, 110900005 rows affected (34 min 10.22 sec)

mysql>

亿级的记录，50多个G数据量，仅需要34分钟，就问你快不快？

2）数据导入（数据恢复）
①mysqldump方式导出的

mysql> use marketing;
Database changed
mysql> source e:/mysql/marketing_t_send_message_send.sql

或者

mysql -uroot -p marketing < e:/mysql/marketing_t_send_message_send.sql

mysqldump方式不满足我的业务需求的，mysqldump备份了整个t_send_message_send表，包括表结构，而表结构是我不需要的，如果恢复的话，只会是恢复成t_send_message_send，数据不会恢复到t_send_message_send2中。

②select … into outfile …;导出的

mysql> use marketing;
Database changed
mysql> load data infile 'e:/mysql/t_send_message_send.csv' into table t_send_message_send2;

或者
将备份的t_send_message_send.csv重命名为t_send_message_send2.csv，然后命令行里面执行：

mysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csv 

很遗憾，这种方式不可行，我从凌晨1点开始执行，到早上9点多还没执行完。七八个小时，插入了2700多万记录，13个G数据量，1.7个G索引。

之前我一直觉得应该是可行的，开始执行的那一刻我就感觉不对。分析下了原因，大概是因为有索引。我的理解是这样的：索引相当于排序，插入数据前，还得先全表扫描下，才晓得数据应该插入到哪个位置，插入一亿条记录，就得一亿次全表扫描，这能不慢吗？那既然这样，先把索引删了，先不排序，数据直接插到最后面，等数据插完之后再排序，再建索引，这样应该会快一些。开搞，先删除索引：

##先truncate掉t_send_message_send2##
TRUNCATE TABLE t_send_message_send2;
ALTER TABLE t_send_message_send2 DROP INDEX mobile;
ALTER TABLE t_send_message_send2 DROP INDEX date_push;

然后再次导入。

C:\Users\maanjun>mysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csv
Enter password: ******
marketing.t_send_message_send2: Records: 110900005  Deleted: 0  Skipped: 0  Warnings: 0

耗时3个多小时，跟Mysql数据库快速插入亿级数据差不多。最后，再重建索引：

ALTER TABLE `t_send_message_send2` ADD INDEX (mobile);
ALTER TABLE `t_send_message_send2` ADD INDEX (date_push);

重建两个索引，一个varchar类型，一个datetime类型，建一个索引差不多二三十分钟，加上数据导入过程耗时，数据导入、重建索引总共耗时4个小时。
回过头来想，插入数据前删除索引，然后插入数据，最后重建索引，不管是哪种导入方式差不多都是耗时3个多小时，加上重建索引的时间，整个恢复过程差不多4个小时。再加上导出耗费的时间，5个小时内亿级记录表单表备份是可以的。当然这说的离线备份，其实如果顺利的话，在线备份花费的时间会更短，因为在线备份也可以是删除索引–>插入数据–>重建索引这个过程，况且在线备份不需要耗费导出数据这段时间。其次，在线备份也不需要占用本地几十个G的中转空间。但是在线备份一定好吗？未必！在线备份频繁地查询原表，会不会影响线网业务？我是在本机测试的，直接操作数据库，没有业务在跑，当然没有关系，如果是线网那就值得考虑下啦。再者，我在用navicate进行在线备份过程中连接无故中断了。

[SQL]insert into t_send_message_send2 (select * from t_send_message_send);
[Err] 2013 - Lost connection to MySQL server during query

在数据导出导入过程中还踩了一些，这些坑在百度上搜一下，都有解决方法。下一篇，将对整个mysql亿级数据大表分区的过程做个总结。

附：
type
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
更多explain解释，参见MySQL Explain详解

1、https://www.cnblogs.com/xuanzhi201111/p/4175635.html
2、https://blog.csdn.net/weixin_44297303/article/details/99197637
3、https://www.jianshu.com/p/c64b857a9996