碎碎念
一晃一年多没在公众号坚持写笔记了，一方面是工作变动后，刚开始面对新的环境，新的项目，需要投入更多的精力去适应。另外一方面大抵是人都有惰性吧，后来虽然有一些空闲时间了，一段时间松懈了，再看书便没了那份心境，周末不想看书，学习，只想出去玩。荒废了这么长时间，总是有点浪费大好青春年华了。毕竟，青春有几年了，再过几年就奔三了，为了不至于三十五岁就毕业，偷懒是行不得的，于是，决定继续开始记录我的学习之旅了。
load data replace问题
案例说明
先来说一下背景，MySQL版本为5.7.32,架构简化为两实例双向复制架构，暂且称实例A，实例B吧，在A实例对某主键为自增列并含有唯一索引的表执行load data replace操作，然后在B实例执行insert操作报错主键冲突。
测试流程:
创建测试表，插入数据，此时，主从数据一致:
CREATE TABLE my_test(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
school VARCHAR(50),
address VARCHAR(50),
UNIQUE KEY idx_uni(name,school)
);
./mysql_random_data_load -h127.0.0.1 -P3310 -uuniverse_op -p'xxx' --max-threads=8 test my_test 50000
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select id from my_test order by id desc limit 5;
+-------+
| id |
+-------+
| 50000 |
| 49999 |
| 49998 |
| 49997 |
| 49996 |
+-------+
在主库执行如下操作,模拟插入唯一键重复记录,并记录执行前binlog日志位置，便于后续解析binlog进行信息查看:
mysql> SELECT name, school, address
-> INTO OUTFILE '/root/test/my_test.csv'
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM my_test limit 2;
Query OK, 2 rows affected (0.00 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2874 |
| mysql-bin.000002 | 12643944 |
| mysql-bin.000003 | 194 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> LOAD DATA INFILE '/root/test/my_test.csv'
-> REPLACE INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> (name, school, address);
Query OK, 4 rows affected (0.01 sec)
Records: 2 Deleted: 2 Skipped: 0 Warnings: 0
查看主从库的表结构和自增列最大值,此时会发现虽然主从正常，但是主从库表结构中的AUTO_INCREMENT值不一致且从库中的AUTO_INCREMENT值小于max(id)值，此时，如果进行了主从切换，在原从库对该表执行插入操作，会导致主键冲突问题:
Master:
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=50004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select id from my_test order by id desc limit 5;
+-------+
| id |
+-------+
| 50002 |
| 50001 |
| 50000 |
| 49999 |
| 49998 |
+-------+
5 rows in set (0.00 sec)
Slave:
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select id from my_test order by id desc limit 5;
+-------+
| id |
+-------+
| 50002 |
| 50001 |
| 50000 |
| 49999 |
| 49998 |
+-------+
解析binlog查看load data replace操作对应记录在binlog中的操作:
# at 331
#231129 17:07:33 server id 144838299 end_log_pos 513 CRC32 0x9bd9367b Rows_query
# LOAD DATA INFILE '/root/test/my_test.csv'
# REPLACE INTO TABLE my_test
# FIELDS TERMINATED BY ','
# ENCLOSED BY '"'
# LINES TERMINATED BY '\n'
# (name, school, address)
# at 513
#231129 17:07:33 server id 144838299 end_log_pos 572 CRC32 0x442312e2 Table_map: `test`.`my_test` mapped to number 265
# at 572
#231129 17:07:33 server id 144838299 end_log_pos 1162 CRC32 0xc1b967f2 Update_rows: table id 265 flags: STMT_END_F
### UPDATE `test`.`my_test`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='saepe amet consequatur occaecati nesciunt sequi!' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='enim unde perferendis aut eligendi et!' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @4='sapiente eveniet officiis ut autem sit ad.' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### SET
### @1=50001 /* INT meta=0 nullable=0 is_null=0 */
### @2='saepe amet consequatur occaecati nesciunt sequi!' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='enim unde perferendis aut eligendi et!' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @4='sapiente eveniet officiis ut autem sit ad.' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### UPDATE `test`.`my_test`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='asperiores ducimus itaque consequatur aut exercita' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='est et aspernatur quo laudantium.' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @4='minus non id exercitationem ea officiis quod culpa' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### SET
### @1=50002 /* INT meta=0 nullable=0 is_null=0 */
### @2='asperiores ducimus itaque consequatur aut exercita' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='est et aspernatur quo laudantium.' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @4='minus non id exercitationem ea officiis quod culpa' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
我测试了五个场景，分别为: 1.load data replace插入唯一值重复场景 2.load data replace插入唯一键不重复场景 3.insert/replace插入唯一键重复场景 4.load data插入唯一键不重复场景 5.innodb_autoinc_lock_mode设置为0场景 上面的测试流程仅为场景一，其他的测试场景测试流程相似，可以自行进行测试。然后，最后说一下结论吧: a.对于load data replace/replace操作，如果唯一索引列重复，会导致从库auto_increment值小于max(id),从而导致后续在从库进行插入时会报错主键冲突，这个对应bug可参考https://bugs.mysql.com/bug.php?id=73563 ,在MySQL8.0版本已修复该bug，但是在innodb_autoinc_lock_mode值不为0的场景下，主从的auto_increment值会不一致，但是对主从数据一致性和主从复制无影响，只是会存在间隙。
b.对于场景一到四，innodb_autoinc_lock_mode值为2，此时如果进行批量操作，例如load data操作，INSERT ... SELECT, REPLACE ... SELECT操作，会导致auto_increment值出现间隙，见官网描述:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes在场景五中，设置innodb_autoinc_lock_mode值为0，则可以避免该种问题，但是在高并发下性能较差。
c.对于从库auto_increment值小于max(id)的bug，目前的处理方法为手动执行alter table xxx auto_increment=max(自增列值)+1。
d.从解析binlog得知，对于load data replace/replace操作，在主库的执行时 1. 先按照insert语句执行，发现uk冲突，但是auto_increment值会更新，然后替换旧的记录 在binlog中记录的是update语句。 但到备库，只会按照binlog中的update语句执行， 所以auto_increment在备库没有递增，最终auto_increment值小于max(自增列值)+1。
load data中NULL值问题
案例说明
在使用load data命令向数据库中导入数据时，我们可能需要关注一些NULL值问题，如果处理不当，最终导入的数据与我们所期望的会产生偏差，结合生产遇到的案例进行分析。
测试流程：
初始文件test2.csv为:
id,name,age
"44","Jane","2"
"47",,"3"
"113","","2"
"124",,"7"
"110","Jdddd","2"
执行数据库操作:
mysql> LOAD DATA local INFILE '/root/test2.csv'
-> INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (id, name, age);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
查询结果:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
上面初始文件中，对于NULL值，直接表示为空，对于空值，用""表示，但是使用load data进行导入后，我们发现并没有达到我们的预期，不管是NULL还是""都被识别为空值。然后接下来,依旧使用test2.csv文件，我们换一种方法重新导入: 测试流程：
使用nullif函数:
mysql> LOAD DATA local INFILE '/root/test2.csv'
-> INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (id, @name, age)
-> SET name = NULLIF(@name, '');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
查询结果:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | NULL | 3 |
| 110 | Jdddd | 2 |
| 113 | NULL | 2 |
| 124 | NULL | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
使用nullif函数，引入变量，如果读取的值为空，则使用NULL值代替，但是仔细查看结果，""表示的空值也被识别为NULL，还是不满足我们的期望,然后对test2文件改造一下,继续测试: 测试流程：
对原文件进行改造:
id,name,age
"44","Jane","2"
"47",\t,"3"
"113","","2"
"124",\t,"7"
"110","Jdddd","2"
使用\t进行转换:
mysql> LOAD DATA local INFILE '/root/test2.csv'
-> INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (id, @name, age)
-> SET name = NULLIF(@name, '\t');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
查询结果:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | NULL | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | NULL | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
清理数据:
mysql> delete from my_test;
Query OK, 5 rows affected (0.00 sec)
不使用nullif函数:
mysql> LOAD DATA local INFILE '/root/test2.csv'
-> INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (id, name, age);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
查询结果:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
改造原数据文件，将表示NULL的值替换为\t,然后使用nullif函数进行替换，并测试不使用nullif函数测试的结果，可以发现，使用nullif函数时，导入数据后结果符合我们的预期，而不使用nullif函数，则结果令人失望，接下来，再对原文件进行一次改造。 测试流程：
文件名称为test3.csv,文件格式:
id,name,age
"44","Jane","2"
"47",\N,"3"
"113","","2"
"124",\N,"7"
"110","Jdddd","2"
实现test2.csv到test3.csv的转变，使用sed神器:sed -i 's/,,/,\\N,/g' test3.csv
导入数据:
mysql> LOAD DATA local INFILE '/root/test3.csv'
-> INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (id, name, age)
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
查询结果:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | NULL | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | NULL | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
改造原数据文件，将表示NULL的值替换为\t,然后使用nullif函数进行替换，并测试不使用nullif函数测试的结果，可以发现，使用nullif函数时，导入数据后结果符合我们的预期，而不使用nullif函数，则结果令人失望，接下来，再对原文件进行一次改造。 测试流程：
文件名称为test3.csv,文件格式:
id,name,age
"44","Jane","2"
"47",\N,"3"
"113","","2"
"124",\N,"7"
"110","Jdddd","2"
实现test2.csv到test3.csv的转变，使用sed神器:sed -i 's/,,/,\\N,/g' test3.csv
导入数据:
mysql> LOAD DATA local INFILE '/root/test3.csv'
-> INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (id, name, age)
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
查询结果:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | NULL | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | NULL | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
导出数据:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | NULL | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | NULL | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
查询结果:
mysql> SELECT id, name, age
-> INTO OUTFILE '/root/test/test4.csv'
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM my_test;
Query OK, 5 rows affected (0.00 sec)
文件内容:
"44","Jane","2"
"47",\N,"3"
"110","Jdddd","2"
"113","","2"
"124",\N,"7"
改造原数据文件，将表示NULL的值替换为\N,然后直接进行导入，可以发现结果也是符合预期的，这里我们可以使用反向思维，不妨试试将数据导入到文件中，看看NULL值和空值在文件中的各自表现形式，可以发现空值使用""表示，而NULL值使用\N表示,然后，我们还可以对比下NULL和空值的区别。 测试流程：
查询所有数据:
mysql> select * from my_test;
+-----+-------+------+
| id | name | age |
+-----+-------+------+
| 44 | Jane | 2 |
| 47 | NULL | 3 |
| 110 | Jdddd | 2 |
| 113 | | 2 |
| 124 | NULL | 7 |
+-----+-------+------+
5 rows in set (0.00 sec)
查看为null的记录:
mysql> select * from my_test where name is null;
+-----+------+------+
| id | name | age |
+-----+------+------+
| 47 | NULL | 3 |
| 124 | NULL | 7 |
+-----+------+------+
2 rows in set (0.00 sec)
查看空值记录:
mysql> select * from my_test where name="";
+-----+------+------+
| id | name | age |
+-----+------+------+
| 113 | | 2 |
+-----+------+------+
1 row in set (0.00 sec)
最后，小结一下，对于load data中含NULL值的场景，可以使用以下两种方式: 1.对于NULL值在数据文件中使用\t等特殊字符表示，然后load语句中使用nullif函数，对特殊字符进行替换，建议当含NULL值的列较少时使用，不然一堆列写起来还是很难受的。 2.对于NULL值使用\N代替，如果原文件不是使用\N表示，可以使用sed命令对源文件进行改造，当然，改造前别忘记了备份源文件。
快速备份恢复的一些小诀窍
案例说明
在误操作某张表或者某个库时，如果是delete/update等操作时，可以通过闪回工具进行恢复，但是如果是drop/truncate操作，则多半需要通过全量备份+增量binlog进行恢复，考虑以下场景: 场景一，全量数据相对需要恢复的库或表来说数据量级大的多，这时候如果对全量数据进行恢复然后再导出数据就会比较耗费时间。 场景二，原来一个实例包含多个库，现在需要将一部分拆分出去，这部分数据量相对来说数据量不算小，如果使用mysqldump等逻辑备份也会消耗较长时间。 结合这些场景，可以开动一下脑筋，是否可以节省时间呢，毕竟，在数据误操作后，时间就是金钱。 针对以上场景: 可以从这些方面发力: 1.备份单库/单表 2.binlog伪装成relay log文件，进行回放 3.MySQL参数回放加速参数调优
对于历史全量数据，可以通过xtrabackup工具的include选项/databases选项/tables-file选项进行单表/单库备份操作，需要注意的时候恢复的时候需要加上--export选项，然后可以通过拷贝数据文件+导入表空间的方式进行恢复，可以参考如下: 备份单库/单表：
include选项正则表达式指定备份单库单表
innobackupex --defaults-file=/etc/my.cnf --host=localhost --port=3310 --user=root --password='xxx' --socket=/opt/mysql/data/3310/mysqld.sock --include="my_test" /data/test/
databases选项指定备份单库单表
innobackupex --defaults-file=/etc/my.cnf --host=localhost --port=3310 --user=root --password='xxx' --socket=/opt/mysql/data/3310/mysqld.sock --databases="my_test.sbtest1 my_test.sbtest2" /data/test/
tables-file选项指定备份单库单表
innobackupex --defaults-file=/etc/my.cnf --host=localhost --port=3310 --user=root --password='xxx' --socket=/opt/mysql/data/3310/mysqld.sock --tables-file=/data/test/tables.txt /data/test/
tables.txt格式如下:
my_test.sbtest1
my_test.sbtest2
备份后单独表进行恢复，需要加--export参数，会额外多两个文件，sbtest1.exp与sbtest1.exp
innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf --apply-log --export /data/test
丢弃表空间
mysql> ALTER TABLE sbtest2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.02 sec)
复制ibd文件和cfg文件
cp /data/test/2024-03-06_15-37-02/my_test/sbtest2.{ibd,cfg} /opt/mysql/data/3310/my_test/
修改权限和属组
chown mysql.mysql sbtest2.cfg
chown mysql.mysql sbtest2.ibd
chmod 640 sbtest2.ibd
chmod 640 sbtest2.cfg
导入表空间
mysql> ALTER TABLE sbtest2 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.02 sec)
如果没有sbtest.cfg文件，会出现类似如下警告，不进行元数据校验
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './my_test/sbtest2.cfg', will attempt to import without schema verification |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
对于增量数据，通过binlog实现定点恢复，可以通过将binlog伪装成relay log文件，然后通过过滤器实现只同步某张表或者某个库的操作，注意，在将binlog伪装为relay log时，需要设置relay_log_recovery值为0，server_id与原备份实例不一样，否则会导致操作失败。
binlog修改为relay log进行回放：
原始文件:
[root@localhost 3312]# ll
-rw------- 1 root root 177 Mar 15 17:35 mysql-bin.000001
-rw------- 1 root root 177 Mar 15 17:47 mysql-bin.000002
-rw------- 1 root root 177 Mar 15 17:47 mysql-bin.000003
-rw------- 1 root root 177 Mar 15 17:41 mysql-bin.000021
-rw------- 1 root root 177 Mar 15 17:41 mysql-bin.000321
-rw------- 1 root root 177 Mar 15 17:41 mysql-bin.004321
重命名以mysql-bin.00000开头的文件:
[root@localhost 3312]# rename mysql-bin mysql-relay mysql-bin.00000?
[root@localhost 3312]# ll
-rw------- 1 root root 177 Mar 15 17:41 mysql-bin.000021
-rw------- 1 root root 177 Mar 15 17:41 mysql-bin.000321
-rw------- 1 root root 177 Mar 15 17:41 mysql-bin.004321
-rw------- 1 root root 177 Mar 15 17:35 mysql-relay.000001
-rw------- 1 root root 177 Mar 15 17:47 mysql-relay.000002
-rw------- 1 root root 177 Mar 15 17:47 mysql-relay.000003
重命名所有以mysql-bin.0开头的文件
[root@localhost 3312]# rename mysql-bin mysql-relay mysql-bin.0*
[root@localhost 3312]# ll
-rw------- 1 root root 177 Mar 15 17:35 mysql-relay.000001
-rw------- 1 root root 177 Mar 15 17:47 mysql-relay.000002
-rw------- 1 root root 177 Mar 15 17:47 mysql-relay.000003
-rw------- 1 root root 177 Mar 15 17:41 mysql-relay.000021
-rw------- 1 root root 177 Mar 15 17:41 mysql-relay.000321
-rw------- 1 root root 177 Mar 15 17:41 mysql-relay.004321
追加进mysql-relay.index文件:
[root@localhost 3312]# ls mysql-relay.0* > mysql-relay.index
[root@localhost 3312]# cat mysql-relay.index
mysql-relay.000001
mysql-relay.000002
mysql-relay.000003
mysql-relay.000021
mysql-relay.000321
mysql-relay.004321
增加过滤:
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('test.sbtest2');
定点恢复,可指定gtid,relay log位点等:
START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 'xxx';
在进行回放时，可以根据服务器的配置对以下参数进行临时调整，以加快进行回放，另外，需要注意的是，部分参数需要重启同步后才能生效，因此操作时需要额外进行关注。
相关参数：
slave_parallel_type
slave_parallel_workers
innodb_flush_log_at_trx_commit
sync_binlog
slave_pending_jobs_size_max
slave_rows_search_algorithms
innodb_io_capacity
innodb_io_capacity_max
innodb_buffer_pool_size
上面从恢复的不同阶段展开，针对性的进行优化，后续如果遇到类似场景，便可以使用上面的一些小技巧加快恢复数据了，想想是不是很美好的一件事情呢。
mysqlbinlog的另类用法
案例说明
日常操作中，对于mysqlbinlog工具，一般我们使用其解析binlog,很少使用他的远程备份模式，其实，可以利用mysqlbinlog的远程拉取功能实现对binlog的远程备份，尤其对于本地binlog写入量比较大本地存放不下但是对binlog保留时间又有要求的场景下，这种时候远程备份就有了用武之地。进行远程备份binlog时，mysqlbinlog服务类似于一个从库，但是没法实现断点续连，不过这种可以通过脚本进行实现，对应的，还可以实现binlog备份文件的定时清理。可以参考如下脚本:
脚本示例：
Binlog实时抓取脚本名称为binlogServer.sh，在备份主机上执行nohup sh binlogServer.sh &即可，脚本内容简单参考:
#!/bin/sh
BACKUP_BIN=/opt/mysql/base/5.7.28/bin/
LOCAL_BACKUP_DIR=/root/test/binlog/
BACKUP_LOG=/root/test/backup.log
#数据库服务器主机ip:
REMOTE_HOST=xxx
#数据库服务端口:
REMOTE_PORT=xxx
#数据库连接用户,需要授予replication slave权限
REMOTE_USER=xxx
#数据库连接用户密码
REMOTE_PASS='xxx'
FIRST_BINLOG=`${BACKUP_BIN}mysql -h${REMOTE_HOST} -u${REMOTE_USER} -p${REMOTE_PASS} -P${REMOTE_PORT} -s -e"show master logs;"|awk 'NR==2 {print}' |awk -F" " '{print $1}'`
#进程探活时间
SLEEP_SECONDS=1
#备份保留时间
STORE_DAYS=7
#如果目录不存在则创建
if [ ! -d "${LOCAL_BACKUP_DIR}" ]; then
mkdir -p "${LOCAL_BACKUP_DIR}"
fi
#进入备份目录
cd ${LOCAL_BACKUP_DIR}
#循环探活
while :
do
#获取目前已拉取到的最新binlog
if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 0 ];then
LAST_FILE=${FIRST_BINLOG}
#echo "${LAST_FILE}"
else
#删除历史备份文件
find ${LOCAL_BACKUP_DIR} -type f -mtime +${STORE_DAYS} -exec rm {} ;
LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} |tail -n 1 |awk '{print $9}'`
#echo "${LAST_FILE}"
fi
#进程存活判断
if ! pgrep -x "mysqlbinlog" > /dev/null; then
${BACKUP_BIN}mysqlbinlog --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} --result-file=${LOCAL_BACKUP_DIR} ${LAST_FILE} >> ${BACKUP_LOG} 2>&1
echo "`date +"%Y/%m/%d %H:%M:%S"` 发现mysqlbinlog停止，尝试自动重启，重启返回代码：$?" | tee -a ${BACKUP_LOG}
fi
sleep ${SLEEP_SECONDS}
done
注意，脚本中涉及的路径，数据库连接信息等需要根据实际进行调整，也可以根据自己的需求进行进一步优化，这里只是简单的抛砖引玉，更多的可以自行优化。
今日份总结
以上4个小Case就是本篇的全部分享了，从load data的小bug到NULL值问题，从物理恢复到逻辑备份，每个小Case可以当作一个独立的的案例进行学习，也可以将其视作一个整体，都是与数据操作紧密关联，好了，希望小伙伴们阅读完能有所收获，也就不枉费我辛辛苦苦写笔记了,更多好的案例和知识点等待我的后续，哈哈。
温馨提示
原文作者：DBA修行之路；如有侵权，请联系管理员删除。
