2. it
  3. MySQL-1236问题处理

MySQL-1236问题处理

mac2022-06-30  99
  2018年9月26日 9:25 现象：192.168.7.206、192.168.7.207互为双主，同步报错  1236   7.206现象如下 root@mysqldb 09:15  [mysql]> show slave status \G; *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.7.207    --7.206的master主机为 7.207                   Master_User: replusr                   Master_Port: 3309                 Connect_Retry: 60               Master_Log_File:  bin.000010           Read_Master_Log_Pos: 234                Relay_Log_File: slave-relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File:  bin.000010              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: 234               Relay_Log_Space: 154               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: 207                   Master_UUID: c4aff4f5-c097-11e8-b9e2-0050568d3207              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp: 180925 18:00:51      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set: 232b5ddf-c098-11e8-af2d-0050568d4206:1-4, 232b5ddf-c098-11e8-af2d-0050568d4e69:1-5, c4aff4f5-c097-11e8-b9e2-0050568d3207:2-4                 Auto_Position: 0          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)   ERROR: No query specified 7.207现象如下 root@mysqldb 09:16  [mysql]> show slave status \G; *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.7.206        --7.207的master主机为 7.206                   Master_User: replusr                   Master_Port: 3309                 Connect_Retry: 60               Master_Log_File:  bin.000011           Read_Master_Log_Pos: 274                Relay_Log_File: slave-relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File:  bin.000011              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: 274               Relay_Log_Space: 154               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: 206                   Master_UUID: 232b5ddf-c098-11e8-af2d-0050568d4206              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp: 180925 18:01:32      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set: c4aff4f5-c097-11e8-b9e2-0050568d3207:1-4, c4aff4f5-c097-11e8-b9e2-0050568d38d8:1-8                 Auto_Position: 0          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec) 分别在7.206、7.207关闭 slave并且刷新日志   7.206做stop slave、刷新日志等操作     root@mysqldb 09:15  [mysql]> stop slave; Query OK, 0 rows affected (0.01 sec) root@mysqldb 09:18  [mysql]> flush logs; Query OK, 0 rows affected (0.01 sec) 再次确认日志刷新后的条目和位置 root@mysqldb 09:18  [mysql]> show master status \G; *************************** 1. row ***************************              File: bin.000012    --当前的日志条目          Position: 274   --当前的日志位置      Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 232b5ddf-c098-11e8-af2d-0050568d4206:1-4, 232b5ddf-c098-11e8-af2d-0050568d4e69:1-5, c4aff4f5-c097-11e8-b9e2-0050568d3207:2-4 1 row in set (0.00 sec)    

 

    7.207做stop slave、刷新日志等操作 root@mysqldb 09:15  [mysql]> stop slave; Query OK, 0 rows affected (0.01 sec)   root@mysqldb 09:18  [mysql]> flush logs; Query OK, 0 rows affected (0.01 sec)   root@mysqldb 09:18  [mysql]> show master status \G; *************************** 1. row ***************************              File: bin.000011   --当前的日志条目          Position: 234    --当前的日志位置      Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: c4aff4f5-c097-11e8-b9e2-0050568d3207:1-4, c4aff4f5-c097-11e8-b9e2-0050568d38d8:1-8 1 row in set (0.00 sec)

 

    7.206做change master 、start slave操作： root@mysqldb 09:18  [mysql]> change master to master_log_file='bin.000011',master_log_pos=234; Query OK, 0 rows affected (0.01 sec)   root@mysqldb 09:20  [mysql]> start slave; Query OK, 0 rows affected (0.01 sec)   root@mysqldb 09:20  [mysql]> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.7.207                   Master_User: replusr                   Master_Port: 3309                 Connect_Retry: 60               Master_Log_File: bin.000011           Read_Master_Log_Pos: 234                Relay_Log_File: slave-relay-bin.000002                 Relay_Log_Pos: 314         Relay_Master_Log_File: bin.000011              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: 234               Relay_Log_Space: 521               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: 207                   Master_UUID: c4aff4f5-c097-11e8-b9e2-0050568d3207              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            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: 232b5ddf-c098-11e8-af2d-0050568d4206:1-4, 232b5ddf-c098-11e8-af2d-0050568d4e69:1-5, c4aff4f5-c097-11e8-b9e2-0050568d3207:2-4                 Auto_Position: 0          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)  

 

7.207做change master 、start slave操作： root@mysqldb 09:19  [mysql]> change master to master_log_file='bin.000012',master_log_pos=274; Query OK, 0 rows affected (0.00 sec)   root@mysqldb 09:20  [mysql]> start slave; Query OK, 0 rows affected (0.01 sec)   root@mysqldb 09:21  [mysql]> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.7.206                   Master_User: replusr                   Master_Port: 3309                 Connect_Retry: 60               Master_Log_File: bin.000012           Read_Master_Log_Pos: 274                Relay_Log_File: slave-relay-bin.000002                 Relay_Log_Pos: 314         Relay_Master_Log_File: bin.000012              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: 274               Relay_Log_Space: 521               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: 206                   Master_UUID: 232b5ddf-c098-11e8-af2d-0050568d4206              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            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: c4aff4f5-c097-11e8-b9e2-0050568d3207:1-4, c4aff4f5-c097-11e8-b9e2-0050568d38d8:1-8                 Auto_Position: 0          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)

 

  ok，完美解决

转载于:https://www.cnblogs.com/iyoume2008/p/9705404.html

最新回复(0)