mysql 自增字段复制_MySQL8.0中的多源复制 & 自增列值重复利用

忘是亡心i 2023-01-12 10:28 152阅读 0赞

最近有个项目要用到多源复制功能。使用中发现一些问题.进行了这方面的调研和测试

多源复制

多源复制就是把各个主数据库的数据复制到统一的从数据库上，进行汇总。

使用方法

如下:5.7 和 8.0版本基本一样。

CHANGE REPLICATION FILTER filter\[, filter\]\[, ...\]

filter:

REPLICATE\_DO\_DB = (db\_list)

| REPLICATE\_IGNORE\_DB = (db\_list)

| REPLICATE\_DO\_TABLE = (tbl\_list)

| REPLICATE\_IGNORE\_TABLE = (tbl\_list)

| REPLICATE\_WILD\_DO\_TABLE = (wild\_tbl\_list)

| REPLICATE\_WILD\_IGNORE\_TABLE = (wild\_tbl\_list)

| REPLICATE\_REWRITE\_DB = (db\_pair\_list)

db\_list:

db\_name\[, db\_name\]\[, ...\]

tbl\_list:

db\_name.table\_name\[, db\_table\_name\]\[, ...\]

wild\_tbl\_list:

'db\_pattern.table\_pattern'\[, 'db\_pattern.table\_pattern'\]\[, ...\]

db\_pair\_list:

(db\_pair)\[, (db\_pair)\]\[, ...\]

db\_pair:

from\_db, to\_db

更改复制筛选器在从属节点上设置一个或多个复制筛选规则，方法与使用复制筛选选项(如——replicate-do-db或——replicate-wild-ignore-table)启动从属节点mysqld相同。与服务器选项的情况不同，此语句不需要重新启动服务器才能生效，只需先使用STOP slave SQL\_THREAD停止从SQL线程(然后使用START slave SQL\_THREAD重新启动)。更改复制筛选器需要超级特权。

参数说明：

REPLICATE\_DO\_DB:包括基于数据库名称的更新。

REPLICATE\_IGNORE\_DB:排除基于数据库名称的更新。

REPLICATE\_DO\_TABLE:包括基于表名的更新。

REPLICATE\_IGNORE\_TABLE:排除基于表名的更新。

REPLICATE\_WILD\_DO\_TABLE:包括基于通配符模式匹配表名的更新。

REPLICATE\_WILD\_IGNORE\_TABLE:排除基于通配符模式匹配表名的更新。

REPLICATE\_REWRITE\_DB:将从上的新名称替换为主上的指定数据库后，在从上执行更新。

测试

下面看看具体实现

\#\#指定数据库db1，db2

CHANGE REPLICATION FILTER REPLICATE\_DO\_DB=(db1, db2)

\#\#指定表db1.t7 ,忽略表db2.t7

CHANGE REPLICATION FILTER

REPLICATE\_WILD\_DO\_TABLE = ('db1.t7'),

REPLICATE\_WILD\_IGNORE\_TABLE = ('db2.t7');

\#\#忽略表t1,t2开头的表

CHANGE REPLICATION FILTER

REPLICATE\_WILD\_DO\_TABLE = ('db2.t1%','db2.t2%');

\#\#下面的语句重写发生在主数据库db1上的语句到从数据库db2上的语句:

CHANGE REPLICATION FILTER REPLICATE\_REWRITE\_DB = ((db1, db2));

CHANGE REPLICATION FILTER

REPLICATE\_REWRITE\_DB = ((dbA, dbB), (dbC, dbD));

\#\#\#清空

CHANGE REPLICATION FILTER REPLICATE\_DO\_DB=();

CHANGE REPLICATION FILTER REPLICATE\_DO\_TABLE=();

CHANGE REPLICATION FILTER REPLICATE\_WILD\_DO\_TABLE=();

CHANGE REPLICATION FILTER Replicate\_Ignore\_DB=();

CHANGE REPLICATION FILTER Replicate\_Wild\_Ignore\_Table=();

注意一点是在线执行完后，一定要在配置文件my.cnf里写，以免重启后失效。

\#\#my.cnf配置

\[mysqld\]

replicate-rewrite-db = db1 -> db2

常见问题

在多源复制场景下，设定两个参数的时候 replicate-do-db，replicate-rewrite-db发现Mysql5.7.29 主从复制 replicate-rewrite-db 无效。经过多方确认和实际模拟发现 replicate-do-db = db1 这个选项去掉就好用。

建议去掉 replicate-\*-db 的配置，是个坑。复制的时候全复制，不需要做指定库的配置。

下面看看binlo输解析过程：

1.生成binlog日志

\#\#主库执行：

mysql> use percona

Database changed

mysql> create table k1(id int,primary key(id));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into k1 select 9999;

Query OK, 1 row affected (0.00 sec)

2.从库relay-log确认

mysqlbinlog --no-defaults --base64-output=decode-rows -vv relay-log.000005

![25d822405cc3dd3a0c9c94d82c9f8bc8.png][]

3.从库binlog确认

mysqlbinlog --no-defaults --base64-output=decode-rows -vv /opt/data8.1/binlog/mysql-bin.000001

![58f82bce3f0b829160f62359bf81b22c.png][]

总结： 主库的binlog信息 会全部写到从库上的replay log里，之后通过使用GTID\_NEXT方式跳过事务。

要是多个源端的输出binlog日志非常大，那从库可能会存在堵塞。

自增列值重复利用问题

当一张表删除最后添加的自增id数据后，重新启动mysql服务，导致之前插入的自增id值 丢失问题。

以下5.7.19和8.0.19环境下分别对比进行了测试。

mysql> DROP TABLE IF EXISTS autoTable;

CREATE TABLE autoTable(

id bigint auto\_increment,

name varchar(20) ,

primary key(id)

);

mysql> INSERT INTO autoTable(name) values('table1'),('table2'),('table3'),('table4'),('table5'),('table6');

mysql> DELETE FROM autoTable WHERE id=6 OR id=5;

mysql>shutdown;

mysql> select version();

\+------------+

| version() |

\+------------+

| 5.7.29-log |

\+------------+

1 row in set (0.00 sec)

mysql> INSERT INTO autoTable(name) values('table7');

Query OK, 1 row affected (0.00 sec)

mysql> select \* from autotable;

\+----+--------+

| id | name |

\+----+--------+

| 1 | table1 |

| 2 | table2 |

| 3 | table3 |

| 4 | table4 |

| 5 | table7 |

\+----+--------+

mysql> select version();

\+-----------+

| version() |

\+-----------+

| 8.0.19 |

\+-----------+

1 row in set (0.00 sec)

mysql> INSERT INTO autoTable(name) values('table7');

Query OK, 1 row affected (0.01 sec)

mysql> select \* from autotable;

\+----+--------+

| id | name |

\+----+--------+

| 1 | table1 |

| 2 | table2 |

| 3 | table3 |

| 4 | table4 |

| 7 | table7 |

\+----+--------+

5 rows in set (0.00 sec)

对比下来发现5.7.29 存在这个问题。 8.0.19 就没这个问题了。

在MySQL8.0的解决思路：

将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新。

\\storage\\innobase\\log\\log0recv.cc

![7a767579f3addd2e8572aa0009dcb5f1.png][]

如果出现redo丢失 或损坏，问题可能还会存在，那能不能把这个值 记录在数据字典中呢。

[25d822405cc3dd3a0c9c94d82c9f8bc8.png]: /images/20221119/8becca896f05485f9afcaa081725e4d4.png
[58f82bce3f0b829160f62359bf81b22c.png]: /images/20221119/2e8ab40a4f914e19a08a2585935be733.png
[7a767579f3addd2e8572aa0009dcb5f1.png]: /images/20221119/461fee30aae94bd8ab8db88ff05355be.png