Claim your Biolink Click Here
1 like 0 dislike
1.8k views

MySQL replication failed and getting the below error

show slave status

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

in Education & Reference by (1.6k points) | 1.8k views

1 Answer

0 like 0 dislike
mysql [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 2.2.2.2
                  Master_User: msandbox
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000393
          Read_Master_Log_Pos: 442768036
               Relay_Log_File: mysql_sandbox5508-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000393
             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: 442768036
              Relay_Log_Space: 106
              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: 'Client requested master to start replication from impossible position'
               Last_SQL_Errno: 0
               Last_SQL_Error:

so

Master_Log_File: mysql-bin.000393
Read_Master_Log_Pos: 442768036

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000394 | 34023557 | asterisk | |
+------------------+----------+--------------+------------------+

[root@2 ~]# cd /var/lib/mysql/
You have mail in /var/spool/mail/root
[root@2 mysql]# ll
total 6635276
-rw-rw---- 1 mysql mysql  10485760 Feb  7  2019 ibdata1
-rw-rw---- 1 mysql mysql   5242880 Jun 18 15:46 ib_logfile0
-rw-rw---- 1 mysql mysql   5242880 Apr 29  2016 ib_logfile1
drwx------ 2 mysql mysql      4096 Jun 23  2016 mysql
-rw-rw---- 1 mysql mysql 524288272 May 30 03:00 mysql-bin.000381
-rw-rw---- 1 mysql mysql 524288316 May 31 17:44 mysql-bin.000382
-rw-rw---- 1 mysql mysql 524288189 Jun  2 07:19 mysql-bin.000383
-rw-rw---- 1 mysql mysql 524288067 Jun  3 23:05 mysql-bin.000384
-rw-rw---- 1 mysql mysql 524288074 Jun  5 15:01 mysql-bin.000385
-rw-rw---- 1 mysql mysql 524288047 Jun  7 06:58 mysql-bin.000386
-rw-rw---- 1 mysql mysql 524288197 Jun  8 22:56 mysql-bin.000387
-rw-rw---- 1 mysql mysql 524288150 Jun 10 14:56 mysql-bin.000388
-rw-rw---- 1 mysql mysql 524288064 Jun 12 06:55 mysql-bin.000389
-rw-rw---- 1 mysql mysql 524288386 Jun 13 22:51 mysql-bin.000390
-rw-rw---- 1 mysql mysql 524288110 Jun 15 14:43 mysql-bin.000391
-rw-rw---- 1 mysql mysql 524288522 Jun 17 06:20 mysql-bin.000392
-rw-rw---- 1 mysql mysql 442602926 Jun 18 15:45 mysql-bin.000393
-rw-rw---- 1 mysql mysql  39334911 Jun 19 12:46 mysql-bin.000394
-rw-rw---- 1 mysql mysql       266 Jun 18 15:46 mysql-bin.index
srwxrwxrwx 1 mysql mysql         0 Jun 18 15:46 mysql.sock

[root@2 mysql]# mysqlbinlog mysql-bin.000393|tail -n 15 /!/;

at 442602587

#200618 15:45:52 server id 1 end_log_pos 442602762 Query thread_id=387821294 exec_time=0 error_code=0 SET
TIMESTAMP=1592495152/!/; UPDATE vicidial_campaigns SET
campaign_logindate='2020-06-18 15:45:52' where campaign_id
IN('10124789') /!/;

at 442602762

#200618 15:45:52 server id 1 end_log_pos 442602926 Query thread_id=387821266 exec_time=0 error_code=0 SET
TIMESTAMP=1592495152/!/; UPDATE server_updater set
last_update='2020-06-18 15:45:52' where server_ip='2.2.2.2' /!/;
DELIMITER ;

End of log file ROLLBACK / added by mysqlbinlog /; /!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;

And yes, there is no position Read_Master_Log_Pos: “442768036” as the slave requested! The position number starts with

at

for example

at 442602762

As you can see from the bash commands above we got the last 15 lines of our binlog mysql file and the last position was 442602762. Here is what is going on: slave requested to continue from master’s position at 442768036, but master has last position 442602762:

442768036 > 442602762

But this 442602762 postion doesnt exist so to fix it just use the next binlog file and position 1 and your slave will continue normally. Let’s say there is a possibility your master missed to write the last commands to the master’s binlog because of the reset and in this situation your slave could be out of sync and in this case you should recover your slave from a full mysql dump and import in the slave. But in most cases it is fairly safe to continue.

so

mysql [localhost] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} ((none)) > CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000394', MASTER_LOG_POS=1; 
Query OK, 0 rows
affected (0.02 sec)

mysql [localhost] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 2.2.2.2
                  Master_User: msandbox
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000394
          Read_Master_Log_Pos: 39457363
               Relay_Log_File: mysql_sandbox5508-relay-bin.000002
                Relay_Log_Pos: 7524348
        Relay_Master_Log_File: mysql-bin.000394
             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: 7524203
              Relay_Log_Space: 39457675
              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: 71118
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)
by (4.6k points)

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Jul 25, 2013 in Education & Reference by Marc (4.6k points) | 793 views
1 like 0 dislike
1 answer
2 like 0 dislike
1 answer
2 like 0 dislike
1 answer

Where your donation goes
Technology: We will utilize your donation for development, server maintenance and bandwidth management, etc for our site.

Employee and Projects: We have only 15 employees. They are involved in a wide sort of project works. Your valuable donation will definitely boost their work efficiency.

How can I earn points?
Awarded a Best Answer 10 points
Answer questions 10 points
Asking Question -20 points

1,308 questions
1,469 answers
568 comments
4,809 users