Analysis of three parameters of MySQL replication problem

Analysis of three parameters of MySQL replication problem

Today is Tuesday. I woke up late in the morning and was late for work. It was really... . . Without further ado, in yesterday's article, we mentioned three parameters, namely:

  • slave_exec_mode parameter;
  • sql_slave_skip_counter=N parameter;
  • slave-skip-errors=N parameter.

These three parameters can solve some specified errors in parallel replication, such as duplicate key 1062 error. Today we will briefly test the differences between these three parameters:

01 sql_slave_skip_counter parameter

This parameter is mainly used to skip certain erroneous "events". Note that the word used here is event rather than transaction, because its essence is to skip events one by one. It should be noted that this parameter needs to be used in offset replication mode. If gtid replication mode is used, this parameter cannot be used. Let's look at an example. First, we build a replication relationship:

master 10.30.124.68

slave 10.30.124.128

These two instances are master and slave to each other. We create a test table test.yeyz and insert some data, where id is the primary key and is unique, as follows:

On master

mysql:(none) 22:25:56>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.00 sec)

On slave

mysql:(none) 22:25:38>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
5 rows in set (0.00 sec)

We can find that the slave node has one more record than the master node, with an additional record of id=5. Then we insert data on the master node:

mysql:(none) 22:26:06>>insert into test.yeyz values ​​(5,5),(6,6);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Observe the slave node at this time:

mysql:(none) 22:26:34>>show slave status\G
                  Master_Host: 10.30.124.68
                  Master_User: dba_repl
                  Master_Port: 4306
                Connect_Retry: 60
              Master_Log_File:mysqlbin.000002
          Read_Master_Log_Pos: 523
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: mysqlbin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1062
                   Last_Error: Coordinator stopped because there were error(s)
 in the worker(s). The most recent failure being:
 Worker 0 failed executing transaction 'ANONYMOUS' at
 master log mysqlbin.000002, end_log_pos 492.
 See error log and/or performance_schema.replication_applier_status_by_worker
 table for more details about this failure or others, if any.
                 Skip_Counter: 0

It can be found that the SQL thread of the slave node has been disconnected. At this time, query the binlog at position 492 of this error on the master node and you can see:

mysql:(none) 22:30:28>>show binlog events in 'mysqlbin.000002' from 194;
  +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
| mysqlbin.000002 | 194 | Anonymous_Gtid | 192 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysqlbin.000002 | 259 | Query | 192 | 327 | BEGIN |
| mysqlbin.000002 | 327 | Rows_query | 192 | 391 | # insert into test.yeyz values ​​(5,5),(6,6) |
| mysqlbin.000002 | 391 | Table_map | 192 | 439 | table_id: 108 (test.yeyz) |
| mysqlbin.000002 | 439 | Write_rows | 192 | 492 | table_id: 108 flags: STMT_END_F |
| mysqlbin.000002 | 492 | Xid | 192 | 523 | COMMIT /* xid=38 */ |
+-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
6 rows in set (0.00 sec)

From the binlog above, we can see that one of our insert operations actually generated five enents, with corresponding pos from 259 to 492. We will talk about events later.

Because the record with id=5 was inserted on the master node, it conflicts with the record on the slave node. Checking the error log, we can find:

Duplicate entry '5' for key 'PRIMARY',
 Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
 the event's master log FIRST,
 end_log_pos 492 | 2019-07-16 22:26:25

We solve this problem by setting the sql_slave_skip_counter parameter. The steps are as follows:

mysql:(none) 22:29:32>>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:(none) 22:32:45>>set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql:(none) 22:33:06>>start slave;

In yesterday's article, we said that the value following sql_slave_skip_counter is the number of events, so here we are equivalent to skipping an event. MySQL stipulates that if you skip an event and are still in a transaction, then the transaction will continue to be skipped.

After using this parameter to skip an event, we look at the data and replication status in the database table and see:

Slave table:

mysql:(none) 22:33:10>>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.30.124.68
                  Master_User: dba_repl
                  Master_Port: 4306
                Connect_Retry: 60
              Master_Log_File:mysqlbin.000002
          Read_Master_Log_Pos: 523
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 319
        Relay_Master_Log_File: mysqlbin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


mysql:(none) 22:33:16>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
5 rows in set (0.00 sec)

Take a look at the master table:

mysql:(none) 22:33:36>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----+------+
6 rows in set (0.00 sec)

It can be found that the data insertion in the master is successful, but the data insertion in the slave fails, that is to say:

When this parameter is skipped incorrectly, the master and slave data will be inconsistent.

02 slave_skip_errors parameter

This parameter is to skip the specified error, that is, we need to set the corresponding error_code. From the content of the log below, we can see that the value of error_code is 1062

Duplicate entry '5' for key 'PRIMARY',
 Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
 the event's master log FIRST,
 end_log_pos 492 | 2019-07-16 22:26:25

We need to manually change the value of this parameter to 1062. It should be noted that the change of this parameter requires restarting the MySQL service because this parameter is a read-only parameter.

The modified situation is as follows:

[email protected]:(none) 22:38:55>>show variables like '%errors%';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_connect_errors | 1000000 |
| slave_skip_errors | 1062 |
+--------------------+---------+
2 rows in set (0.01 sec)

At this point we update the data in the master table and the slave table, and the updated situation is as follows:

master:

mysql:(none) 22:39:15>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 || 2 | 2 |
| 3 | 3 || 4 | 4 |
| 5 | 5 || 6 | 6 |
+----+------+
6 rows in set (0.00 sec)

On the slave:

mysql:(none) 22:40:15>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+------+
7 rows in set (0.00 sec)

We found that the slave table has one more data than the master table, that is, the record with id=7. At this time, we execute on the master:

mysql:(none) 22:34:15>>insert into test.yeyz values ​​(7,7),(8,8);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Check the replication and data status on the slave as follows:

mysql:(none) 22:39:05>>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.30.124.68
                  Master_User: dba_repl
                  Master_Port: 4306
                Connect_Retry: 60
              Master_Log_File:mysqlbin.000002
          Read_Master_Log_Pos: 852
               Relay_Log_File: slave-relay-bin.000005
                Relay_Log_Pos: 648
        Relay_Master_Log_File: mysqlbin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:


 mysql:(none) 22:40:15>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+------+
7 rows in set (0.00 sec)

As you can see, there is no error in the replication, even though there is already a record with id=7 on the slave. It was also found that the data in the slave database remained consistent with the previous one, that is, the record with id=8 inserted in the master database was not synchronized.

To summarize: when this parameter skips replication errors, the MySQL service needs to be restarted, which may cause inconsistency between master and slave data.

03 slave-skip-errors=N parameter

Let's look at the last parameter. This parameter indicates the slave replication mode during parallel replication. The default value is strict mode. As above, let's first look at the data of the master and slave libraries:

Master data:

mysql:(none) 22:39:20>>select * from test.yeyz;
                 +----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
+----+------+
8 rows in set (0.00 sec)

Slave data:

mysql:(none) 22:42:46>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+------+
9 rows in set (0.00 sec)

At this point we modify the parameters on the slave library as follows:

mysql:(none) 22:42:59>>show variables like '%exec%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| gtid_executed_compression_period | 1000 |
| max_execution_time | 0 |
| rbr_exec_mode | STRICT |
| slave_exec_mode | STRICT |
+----------------------------------+--------+
4 rows in set (0.00 sec)

mysql:(none) 22:44:05>>set global slave_exec_mode='IDEMPOTENT';
Query OK, 0 rows affected (0.00 sec)

mysql:(none) 22:44:10>>show variables like '%exec%';
           +----------------------------------+------------+
| Variable_name | Value |
+----------------------------------+------------+
| gtid_executed_compression_period | 1000 |
| max_execution_time | 0 |
| rbr_exec_mode | STRICT |
| slave_exec_mode | IDEMPOTENT |
+----------------------------------+------------+
4 rows in set (0.00 sec)

After modifying the parameters, we perform the insert operation on the main database:

insert into test.yeyz values ​​(9,9),(10,10);

Check the replication status and data status of the slave library as follows:

mysql:(none) 22:44:14>>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.30.124.68
                  Master_User: dba_repl
                  Master_Port: 4306
                Connect_Retry: 60
              Master_Log_File:mysqlbin.000002
          Read_Master_Log_Pos: 1183
               Relay_Log_File: slave-relay-bin.000007
                Relay_Log_Pos: 650
        Relay_Master_Log_File: mysqlbin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

1 row in set (0.00 sec)

mysql:(none) 22:44:38>>select * from test.yeyz;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
+----+------+
10 rows in set (0.00 sec)

It can be found that there is no replication error and the data inserted on the primary database is also synchronized.

To summarize:

  • slave_exec_mode parameter;
  • sql_slave_skip_counter=N parameter;
  • slave-skip-errors=N parameter.

These three parameters can solve the inconsistency during the replication process. The differences are as follows:

The slave_exec_mode parameter can ensure that the master-slave data is consistent, but the other two cannot.

The slave-skip-errors parameter can skip specified errors, but it requires restarting the instance and cannot guarantee data consistency.

The sql_slave_skip_counter parameter needs to be used in the offset replication mode and cannot guarantee data consistency.

The above is the detailed content of the three parameter analysis of MySQL replication problem. For more information about MySQL replication problem, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL5.7 parallel replication principle and implementation
  • Detailed explanation of MySQL master-slave replication and read-write separation
  • Common repair methods for MySQL master-slave replication disconnection
  • Comprehensive analysis of MySql master-slave replication mechanism
  • MySQL Series 13 MySQL Replication

<<:  New ideas for time formatting in JavaScript toLocaleString()

>>:  Using CSS to implement loading animation of Android system

Recommend

LinkedIn revamps to simplify website browsing

Business social networking site LinkedIn recently...

How to create a simple column chart using Flex layout in css

The following is a bar chart using Flex layout: H...

Summary of seven MySQL JOIN types

Before we begin, we create two tables to demonstr...

Analysis of Linux configuration to achieve key-free login process

1.ssh command In Linux, you can log in to another...

Complete steps for deploying a front-end and back-end separated nginx configuration

Preface It's a cliché. Here I will talk about...

Detailed explanation of how to use Nginx + consul + upsync to achieve dynamic load balancing

Table of contents Prerequisites DNS domain name r...

How to quickly copy large files under Linux

Copy data When copying data remotely, we usually ...

Media query combined with rem layout in CSS3 to adapt to mobile screens

CSS3 syntax: (1rem = 100px for a 750px design) @m...

Enable sshd operation in docker

First, install openssh-server in docker. After th...

JavaScript canvas Tetris game

Tetris is a very classic little game, and I also ...

How to reset the password if the Ubuntu 18.04 server password is forgotten or tampered with

Recently, two accounts on the server were hacked ...

Mysql8.0 uses window functions to solve sorting problems

Introduction to MySQL Window Functions MySQL has ...

Analysis of the operating principle and implementation process of Docker Hub

Similar to the code hosting service provided by G...

Disable input text box input implementation properties

Today I want to summarize several very useful HTML...

How to remotely connect to MySQL database with Navicat Premium

The party that creates a new connection is equiva...