Multi-source replication in MySQL8.0 & reuse of auto-incremented column values

Recently there is a project to use the multi-source copy function. Some problems were found during use. Research and testing in this area were carried out

Multi-source replication
Multi-source replication is to copy the data of each primary database to a unified secondary database for summary.

Instructions

As follows: The 5.7 and 8.0 versions are basically the same.

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

Changing the replication filter to set one or more replication filter rules on the slave node is the same as starting the slave node mysqld using the replication filter option (such as-replicate-do-db or-replicate-wild-ignore-table). Unlike the case of the server option, this statement does not need to restart the server to take effect, just use STOP slave SQL_THREAD to stop the slave SQL thread (then use START slave SQL_THREAD to restart). Super privileges are required to change the replication filter.

Parameter Description:

  • REPLICATE_DO_DB: Includes updates based on the database name.
  • REPLICATE_IGNORE_DB: Exclude updates based on the database name.
  • REPLICATE_DO_TABLE: Including updates based on the table name.
  • REPLICATE_IGNORE_TABLE: Exclude updates based on the table name.
  • REPLICATE_WILD_DO_TABLE: Includes updates based on wildcard pattern matching table names.
  • REPLICATE_WILD_IGNORE_TABLE: Exclude updates based on wildcard pattern matching table names.
  • REPLICATE_REWRITE_DB: After replacing the new name of the slave with the specified database on the master, perform the update on the slave.

test

Let's take a look at the specific implementation

##指定数据库db1,db2CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1, db2) ##指定表db1.t7 ,忽略表db2.t7CHANGE REPLICATION FILTERREPLICATE_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=();

Note that after online execution, you must write in the configuration file my.cnf to avoid invalidation after restart.

##my.cnf配置[mysqld]replicate-rewrite-db = db1 -> db2

common problem

In a multi-source replication scenario, when two parameters are set, replicate-do-db and replicate-rewrite-db find that Mysql5.7.29 master-slave replication replicate-rewrite-db is invalid. After multiple confirmations and actual simulations, it is found that replicate-do-db = db1 is easy to use if this option is removed.

replicate-*-db The configuration recommended to be removed  is a pit. When copying, it is all copied, and there is no need to configure the specified library.

Let's take a look at the binlo input parsing process:
1. Generate binlog log

##主库执行:mysql> use perconaDatabase changedmysql> 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. Confirm from the library relay-log

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

3. Confirm from the library binlog

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

Summary: The binlog information of the master library will all be written to the replay log on the slave library, and then the transaction will be skipped by using GTID_NEXT.
If the output binlog logs of multiple sources are very large, the slave database may be blocked.

Auto-
increment column value reuse problem When a table deletes the last auto-increment id data added, restart the mysql service, causing the previously inserted auto-increment id value to be lost.

The following comparisons were performed under the environment of 5.7.19 and 8.0.19 respectively.

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)

By comparison, it is found that 5.7.29 has this problem. 8.0.19 will not have this problem.
The solution in MySQL8.0: Persist the
   counter of the self-incrementing primary key to the redo log. Every time the counter changes, it will be written to the redo log. If the database is restarted, InnoDB will initialize its memory value according to the counter information in the redo log. In order to minimize the impact on system performance, the counter is written to the redo log and will not be refreshed immediately.

\storage\innobase\log\log0recv.cc

image.png

If redo is lost or damaged, the problem may still exist. Can this value be recorded in the data dictionary?