Percona MySQL 5.6 主主复制环境报错"Got fatal error 1236 from master.."
发表于:2024-11-26 作者:千家信息网编辑
千家信息网最后更新 2024年11月26日,Percona MySQL 5.6 主主复制环境报错:mysql> show slave status\G*************************** 1. row ************
千家信息网最后更新 2024年11月26日Percona MySQL 5.6 主主复制环境报错"Got fatal error 1236 from master.."Percona MySQL 5.6 主主复制环境报错:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000153
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000153
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 601
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160809 11:42:02
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
报错原因:
Master端二进制日志被删除,导致Slave端的IO线程无法在Master端读取到指定二进制日志
mysql> show variables like '%log%bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog/mysqld-bin |
| log_bin_index | /var/lib/mysql/binlog/mysqld-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
[root@dmcdb2 ~]# cd /var/lib/mysql/binlog
[root@dmcdb2 binlog]# ls -trl
total 1613364
-rw-r--r-- 1 root root 1651957529 Feb 17 14:29 3962.sql
-rw-rw---- 1 mysql mysql 40 Aug 9 11:16 mysqld-bin.index
-rw-rw---- 1 mysql mysql 174 Aug 9 14:17 mysqld-relay-bin.000001
-rw-rw---- 1 mysql mysql 92 Aug 9 14:17 mysqld-relay-bin.index
-rw-rw---- 1 mysql mysql 461 Aug 9 14:21 mysqld-bin.000155
-rw-rw---- 1 mysql mysql 104500 Aug 9 14:39 mysqld-relay-bin.000002
修复方法:
由于丢失了Master端的二进制日志,只能从现有的二进制日志重新配置复制环境
--Master端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000155 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--Slave端
mysql> change master to master_host='vsql02', master_port=3306, master_user='mysql_slave', master_password='mysql_slave', master_log_file='mysqld-bin.000155', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000155
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000155
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 458
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
--Master端
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000153
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000153
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 601
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160809 11:42:02
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
报错原因:
Master端二进制日志被删除,导致Slave端的IO线程无法在Master端读取到指定二进制日志
mysql> show variables like '%log%bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog/mysqld-bin |
| log_bin_index | /var/lib/mysql/binlog/mysqld-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
[root@dmcdb2 ~]# cd /var/lib/mysql/binlog
[root@dmcdb2 binlog]# ls -trl
total 1613364
-rw-r--r-- 1 root root 1651957529 Feb 17 14:29 3962.sql
-rw-rw---- 1 mysql mysql 40 Aug 9 11:16 mysqld-bin.index
-rw-rw---- 1 mysql mysql 174 Aug 9 14:17 mysqld-relay-bin.000001
-rw-rw---- 1 mysql mysql 92 Aug 9 14:17 mysqld-relay-bin.index
-rw-rw---- 1 mysql mysql 461 Aug 9 14:21 mysqld-bin.000155
-rw-rw---- 1 mysql mysql 104500 Aug 9 14:39 mysqld-relay-bin.000002
修复方法:
由于丢失了Master端的二进制日志,只能从现有的二进制日志重新配置复制环境
--Master端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000155 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--Slave端
mysql> change master to master_host='vsql02', master_port=3306, master_user='mysql_slave', master_password='mysql_slave', master_log_file='mysqld-bin.000155', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000155
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000155
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 458
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
--Master端
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
二进制
日志
环境
端的
原因
方法
线程
配置
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
bms软件开发代码
静安区营销软件开发协议
服务器管理文件共享权限
乡政府网络安全责任书
网络技术层面的生产力三要素
小米提示无法连接服务器
itunes本地数据库
造艺网络技术
手机网络安全的本质和定义
山东省网络安全技能大赛
性价比高的网络安全零信任
我的世界.9服务器
新软科技软件开发
日照花城互联网科技有限公司
找不到认证服务器
软件开发情况
如何加强网络安全培训心得
网络安全手抄报设计黑白
导航软件开发用
怎么在服务器管理器中添加功能
魔兽免费公益服务器
吉利汽车软件开发部
湖北分布式服务器虚拟主机
java软件开发就业
服务器修改管理地址
网络安全法什么时候颁布的
服务器内存价格走势2022年
dell服务器黄灯亮
绣绣网络技术北京有限公司
网络安全坏处20字