mysql 自增字段 复制_MySQL8.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

3.从库binlog确认

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

总结： 主库的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

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