忘记密码   免费注册 查看新帖 | 论坛精华区

ChinaUnix.net

  平台 论坛 博客 认证专区 大话IT 视频 徽章 文库 沙龙 自测 下载 频道自动化运维 虚拟化 储存备份 C/C++ PHP MySQL 嵌入式 Linux系统
最近访问板块 发新帖
查看: 846 | 回复: 1

MySQL5.6基于GTID复制的一次故障 [复制链接]

论坛徽章:
8
数据库技术版块每日发帖之星
日期:2015-12-22 06:20:00数据库技术版块每日发帖之星
日期:2015-12-23 06:20:00数据库技术版块每周发帖之星
日期:2016-02-03 16:55:09数据库技术版块每日发帖之星
日期:2016-07-15 06:20:00IT运维版块每日发帖之星
日期:2016-08-13 06:20:00数据库技术版块每日发帖之星
日期:2016-08-15 06:20:00IT运维版块每日发帖之星
日期:2016-08-16 06:20:00IT运维版块每日发帖之星
日期:2016-08-17 06:20:00
发表于 2016-09-16 22:53 |显示全部楼层
本人在虚拟机上做实验,主从IP如下:Master:192.168.1.107
Slave1:192.168.1.111
Slave2:192.168.1.112

每台虚拟机分配的磁盘空间仅仅有20G,由于在Master上执行sysbench进行基准测试,自动产生了大量数据和binlog,磁盘满了,结果导致了slave上的I/O线程断开了。
在从库上执行show slave status\G显示:

  1. mysql> show slave status\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State:
  4.                   Master_Host: 192.168.1.107
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File:
  9.           Read_Master_Log_Pos: 4
  10.                Relay_Log_File: mysqld-relay-bin.000001
  11.                 Relay_Log_Pos: 4
  12.         Relay_Master_Log_File:
  13.              Slave_IO_Running: No
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB:
  16.           Replicate_Ignore_DB: mysql,information_schema,performance_schema
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 0
  25.               Relay_Log_Space: 151
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 1236
  38.                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica'
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids:
  42.              Master_Server_Id: 107
  43.                   Master_UUID: 6a2a620f-736c-11e6-8384-000c299dda36
  44.              Master_Info_File: mysql.slave_master_info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind:
  50.       Last_IO_Error_Timestamp: 160916 22:50:59
  51.      Last_SQL_Error_Timestamp:
  52.                Master_SSL_Crl:
  53.            Master_SSL_Crlpath:
  54.            Retrieved_Gtid_Set:
  55.             Executed_Gtid_Set: 6a2a620f-736c-11e6-8384-000c299dda36:1-500046,
  56. 90bced3c-736e-11e6-8392-000c291db4ba:1-12
  57.                 Auto_Position: 1
  58. 1 row in set (0.00 sec)
复制代码
在从库(192.168.1.111)查看错误日志:

tail -n200 var/log/mysqld.log

  1. 2016-09-16 22:35:11 2283 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
  2. 2016-09-16 22:35:11 2283 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
  3. 2016-09-16 22:35:24 2283 [Note] Error reading relay log event: slave SQL thread was killed
  4. 2016-09-16 22:36:01 2283 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.107', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
  5. 2016-09-16 22:36:05 2283 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
  6. 2016-09-16 22:36:05 2283 [Note] Slave I/O thread: connected to master 'repl@192.168.1.107:3306',replication started in log 'FIRST' at position 4
  7. 2016-09-16 22:36:05 2283 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
  8. 2016-09-16 22:36:05 2283 [ERROR] Error reading packet from server: Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been (server_errno=1236)
  9. 2016-09-16 22:36:05 2283 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
  10. 2016-09-16 22:36:05 2283 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
  11. 2016-09-16 22:38:20 2283 [Note] Error reading relay log event: slave SQL thread was killed
  12. 2016-09-16 22:50:54 2283 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.107', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
  13. 2016-09-16 22:50:59 2283 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
  14. 2016-09-16 22:50:59 2283 [Note] Slave I/O thread: connected to master 'repl@192.168.1.107:3306',replication started in log 'FIRST' at position 4
  15. 2016-09-16 22:50:59 2283 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
  16. 2016-09-16 22:50:59 2283 [ERROR] Error reading packet from server: Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been (server_errno=1236)
  17. 2016-09-16 22:50:59 2283 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
  18. 2016-09-16 22:50:59 2283 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
复制代码


针对以上种种错误提示,百度了一下 没有找到合适解决办法。在slave上重新stop slave并且change master也不行。

哪位高人遇到过这种问题,并且知道如何解决?谢谢!

论坛徽章:
2
IT运维版块每日发帖之星
日期:2016-04-15 06:20:0015-16赛季CBA联赛之福建
日期:2016-08-08 13:37:03
发表于 2016-09-21 08:44 |显示全部楼层
到主库上show master status查一下主库当前的gtid(Executed_Gtid_Set),
然后到从库执行下面命令试试。
RESET MASTER;
set global gtid_purged = 'xxxx';                 -- 这里xxxx是主库的Executed_Gtid_Set
start slave;

意思是从库同步时,丢弃现有主库执行过的gtid。

主库磁盘满了以后是直接rm的binlog吗?可以在主库执行一下flush logs;,然后再按上面的步骤做一次试试。
您需要登录后才可以回帖 登录 | 注册

本版积分规则

久等啦!10张门票开启你的DTCC2017之旅

2017中国数据库技术大会将于2017年5月11-13日如约而至,本届大会以“数据驱动•价值发现”为主题,共设定2大主场和21个技术专场,云集海内外120+位技术大牛,共同探讨Oracle、MySQL、NoSQL、云端数据库、区块链、深度学习等领域的前瞻性热点话题。
即日起,填写DTCC2017会前调查问卷,即有机会赢取价值2600元的大会门票1张!仅限10张!
----------------------------------------
活动截止时间:2017年5月5日统一公布

问卷入口>>
  

北京皓辰网域网络信息技术有限公司. 版权所有 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员  联系我们:
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP