Percona MySQL 5.6 主主复制环境报错"Got fatal error 1236 from master.."

妖狐艹你老母 2024-04-18 00:17 62阅读 0赞

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2123211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2123211/