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

ChinaUnix.net

  平台 论坛 博客 认证专区 大话IT 视频 徽章 文库 沙龙 自测 下载 频道自动化运维 虚拟化 储存备份 C/C++ PHP MySQL 嵌入式 Linux系统
最近访问板块 发新帖
查看: 976 | 回复: 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;,然后再按上面的步骤做一次试试。
您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票6.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月2日前

活动链接>>
  

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

清除 Cookies - ChinaUnix - Archiver - WAP - TOP