《MySQL系列-主从相关》MySQL主从同步问题记录

DATA数据猿

已于 2023-12-24 15:19:41 修改

阅读量1.9k

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库

于 2022-05-14 14:59:32 首次发布

本文链接：https://blog.csdn.net/m0_51197424/article/details/124768404

版权

MySQL 专栏收录该内容

34 篇文章 5 订阅

订阅专栏

文章目录

MySQL主从同步问题记录
一、MySQL主从同步Slave_SQL_Running错误
二、MySQL从库Error:"You cannot 'Alter' a log table..."
三、MySQL从库Slave_IO_Running异常，Last_IO_Error: Got fatal error 1236 from master when reading data from binary log

MySQL主从同步问题记录

在这里插入图片描述

一、MySQL主从同步Slave_SQL_Running错误

1.Slave_SQL_Running错误

在执行SQL语句的时候发生错误

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.130.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1927
               Relay_Log_File: relay-log.000034
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '6' for key 'PRIMARY'' on query. Default database: 'zxy'. Query: 'insert into zxy values (6,'e')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1717
              Relay_Log_Space: 1243
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '6' for key 'PRIMARY'' on query. Default database: 'zxy'. Query: 'insert into zxy values (6,'e')'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 220514 13:03:48
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

1.1 解决方式一：跳过当前错误

跳过当前错误，适用于少数误差的情况

## 先关闭slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
## 通过命令跳过当前错误语句，sql_slave_skip_counter可以选择跳过多少步
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
## 启动slave
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
## 当前已正常
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.130.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1927
               Relay_Log_File: relay-log.000035
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:

1.2 解决方式二：重新同步从库

主从之间出现大量的数据不一致，为了快速的恢复业务，对从库进行重新同步

1.2.1. 从库：重置从库的同步设置，清除该数据库

## 停止slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

## 重置slave
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

## 删除需要同步的数据库
mysql> drop database zxy;
Query OK, 7 rows affected (0.19 sec)

1.2.2. 主库：重置主库同步设置，锁定主库，备份主库

通过show master status查看，如果业务数据一直在变化，那File和Position也会一直变化

这种情况下，需要等待业务数据少的时候，锁库处理

或者主从不一致紧急的情况下，通知业务暂停，进行锁库处理

## 通过show master status查看，如果业务数据一直在变化，那File和Position也会一直变化
## 这种情况下，需要等待业务数据少的时候，锁库处理
## 或者主从不一致紧急的情况下，通知业务暂停，进行锁库处理

## 1.查看主机状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     2563 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

## 2.重置主库同步配置
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

## 再次查看主机状态，发现File和Position都重置了
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

## 3.锁定主库，只能查Read
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

## 锁定主库后，File和Position也就不会再变化
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

## 4.备份指定数据库，指定IP，Port，数据库账号密码,目标数据库名
[root@hadoop mysql]# mysqldump -h 192.168.130.100 -P 3306 -uroot -p111213 zxy > ./master-zxy.sql

## 5.scp将主机的master-zxy.sql备份文件，发送到从机的/var/lib/mysql目录
[root@hadoop mysql]# scp master-zxy.sql root@192.168.130.101:/var/lib/mysql
master-zxy.sql                                                                                                                                          100% 4571     3.9MB/s   00:00
[root@hadoop mysql]#

## 6.解锁主库
## 6.1 解锁前，插入一条数据会报错
mysql> insert into zxy values(10,'c');                                                           ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
## 6.2 解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
## 6.3 解锁后测试
mysql> insert into zxy values(11,'j');
Query OK, 1 row affected (0.00 sec)

1.2.3. 从库：恢复数据

恢复方式一：进入MySQL命令行Source命令

## 创建数据库
mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)

## 进入数据库
mysql> use zxy;
Database changed

## 恢复数据
mysql> source /var/lib/mysql/master-zxy.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

恢复方式二：Linux命令行mysql命令

## 创建数据库
mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)

## 进入数据库
mysql> use zxy;
Database changed

## mysql命令与mysqldump命令相互配合
[root@hadoop mysql]# mysql -h192.168.130.101 -P3306 -uroot -p111213 zxy < master-zxy.sql
Warning: Using a password on the command line interface can be insecure.
[root@hadoop mysql]#

1.2.4. 从库：修改同步配置

MASTER_HOST为主机IP，MASTER_PORT为主机MySQL端口，MASTER_USER是主机为从机同步创建的用户，MASTER_PASSWORD是同步用户对应的而密码，MASTER_LOG_FILE是在主机通过show master status中File字段对应的内容，MASTER_LOG_POS在主机通过show master status中Position字段对应的内容

mysql> CHANGE MASTER TO MASTER_HOST='192.168.130.100',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=330;

操作

## 更改配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.130.100',
    -> MASTER_PORT=3306,
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='slave',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=330;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

## 启动从机
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

## 查看从机状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.130.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 330
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:

2.语句记录功能preformance_schema

2.1. 查看语句记录功能开启状态

## 在preformance_schema中语句时间记录表中针对每一条语句的执行状态都记录了较为详细的信息，其中就包含了执行错误的信息
## events_statements_current，默认只记录每个线程最近的一条SQL信息
## events_statements_history，默认记录每个线程最近的十条SQL信息
## events_statements_history_long，默认记录每个线程最近的10000条SQL信息
mysql> select * from performance_schema.setup_consumers where name like 'events_statements%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
+--------------------------------+---------+
3 rows in set (0.09 sec)

2.2. 开启语句记录功能

mysql> update performance_schema.setup_consumers set enabled = 'YES' where name in ('events_statements_history','events_statements_history_long');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from performance_schema.setup_consumers where name like 'events_statements%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
+--------------------------------+---------+
3 rows in set (0.00 sec)

2.3. 根据错误号查找详情

mysql> select * from performance_schema.events_statements_history_long where mysql_errno=1062\G;
*************************** 1. row ***************************
              THREAD_ID: 28
               EVENT_ID: 2
           END_EVENT_ID: 2
             EVENT_NAME: statement/sql/insert
                 SOURCE: log_event.cc:4896
            TIMER_START: 5512737119549000
              TIMER_END: 5512737245104000
             TIMER_WAIT: 125555000
              LOCK_TIME: 49000000
               SQL_TEXT: insert into zxy values (6,'e')
                 DIGEST: 70f8ef4bc1dc9d8a7c6020e9dbb0d2c7
            DIGEST_TEXT: INSERT INTO `zxy` VALUES (...)
         CURRENT_SCHEMA: zxy
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 1062
      RETURNED_SQLSTATE: 23000
           MESSAGE_TEXT: Duplicate entry '6' for key 'PRIMARY'
                 ERRORS: 1
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
1 row in set (0.00 sec)

二、MySQL从库Error:“You cannot ‘Alter’ a log table…”

Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log

1.导致报错的原因

mysql_upgrade执行的所有语句都被记二进制记录，然后复制到slave，导致报错

2.解决方式一：

停止复制线程，关闭慢查询日志，当前错误可恢复

stop slave;
set global slow_query_log="OFF";
start slave;
set global slow_query_log="ON";

3.解决方式二：

跳过该错误步骤

stop slave;
# 使用命令跳过当前错误语句，sql_slave_skip_counter可以指定跳过多少步
set global sql_slave_skip_counter=1;
start slave;

三、MySQL从库Slave_IO_Running异常，Last_IO_Error: Got fatal error 1236 from master when reading data from binary log

1.从库Slave_IO_Running异常

从库start slave;启动后，通过show slave status\G;查看主从状态，结果Relay_Master_Log_File异常，Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:

                  Master_User: slave
                  Master_Port: 33061
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1140
               Relay_Log_File: relay-log-master_1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000004' at 1140, the last event read from './mysql-bin.000004' at 123, the last byte read fro

2.主库刷新日志

刷新日志后binlog文件的位置会+1

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

3.查看主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 | zxy          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.主库重置salve

# 1.先关闭slave
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

# 2.重置slave
mysql> reset slave;
Query OK, 0 rows affected (0.08 sec)

5.重新配置连接master

重新连接时，只需要修改MASTER_LOG_FILE、MASTER_LOG_POS即可

mysql> CHANGE MASTER TO MASTER_HOST='121.4.106.102' ,MASTER_PORT=33061,MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154 for channel 'master_1';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

6.重启slave

# 1.启动slave
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 2.查看状态，启动成功
mysql> show slave status\G;
Query OK, 0 rows affected (0.01 sec)

				  Master_User: slave
                  Master_Port: 33061
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log-master_1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

DATA数据猿

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
《MySQL系列-主从相关》MySQL主从同步问题记录

1.MySQL主从同步Slave_SQL_Running错误2.MySQL从库Error:"You cannot 'Alter' a log table..."3.MySQL从库Slave_IO_Running异常，Last_IO_Error: Got fatal error 1236 from master when reading data from binary log
复制链接

扫一扫