Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » replication problem after master crash
replication problem after master crash [message #1181] Wed, 02 May 2007 05:28 Go to next message
dyker is currently offline dyker
Messages: 3
Registered: May 2007
Junior Member
Hi

I have a problem in replication after master server crash. I have one master mysql process and one slave mysql process running on one server. When this server rebooted (after several months uptime) I see the following situation:

On slave in error log:
070501 20:03:35 [Note] Slave SQL thread initialized, starting replication in log 'database3-bin.000071' at position 668379575, relay log './database3-relay-bin.000001' position: 665822307
070501 20:03:35 [Note] Slave I/O thread: connected to master 'slave@d3:3416',  replication started in log 'database3-bin.000071' at position 668379575
070501 20:03:36 [ERROR] Error reading packet from server: Client requested master to start replication from impossible
position (server_errno=1236)
070501 20:03:36 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
070501 20:03:36 [ERROR] Slave I/O thread exiting, read up to log 'database3-bin.000071', position 668379575


On slave "SHOW SLAVE STATUS\G":
             Slave_IO_State:
                Master_Host: d3
                Master_User: slave
                Master_Port: 3416
              Connect_Retry: 1
            Master_Log_File: database3-bin.000071
        Read_Master_Log_Pos: 668379575
             Relay_Log_File: database3-relay-bin.000002
              Relay_Log_Pos: 4
      Relay_Master_Log_File: database3-bin.000071
           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: 668379575
            Relay_Log_Space: 4
            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


On master "SHOW MASTER LOGS;":
........
| database3-bin.000062 |    1617447 |
| database3-bin.000063 |   43913303 |
| database3-bin.000064 |  284939867 |
| database3-bin.000065 | 1073742101 |
| database3-bin.000066 |  110044733 |
| database3-bin.000067 | 1073742071 |
| database3-bin.000068 |  290598099 |
| database3-bin.000069 |        126 |
| database3-bin.000070 |          4 |
| database3-bin.000071 |  668358853 |
| database3-bin.000072 |         79 |
| database3-bin.000073 |   85353363 |


Have anyone this problem? It looks like master doesn't flushed logs to disk or smth like this...

PS
sorry for my english I'm not a native speaker Wink
Report message to a moderator
Re: replication problem after master crash [message #1185 is a reply to message #1181 ] Wed, 02 May 2007 14:11 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
I think you are correct.

For some reason your binary log file is shorter than what the slave is expecting.

You didn't say what happened when the master crashed.
Was it just MySQL or was it the entire OS?

Either way you have ended up with that the slave is requesting a position in the binary log on the master that is of the end of the file.

So you must perform a re-syncronization between the master and slave to sort this out.
Report message to a moderator
Re: replication problem after master crash [message #1189 is a reply to message #1185 ] Thu, 03 May 2007 06:11 Go to previous messageGo to next message
dyker is currently offline dyker
Messages: 3
Registered: May 2007
Junior Member
sterin wrote on Thu, 03 May 2007 01:11

I think you are correct.

For some reason your binary log file is shorter than what the slave is expecting.

You didn't say what happened when the master crashed.
Was it just MySQL or was it the entire OS?



It was a server reboot (power problems or smth like this)

sterin wrote on Thu, 03 May 2007 01:11


Either way you have ended up with that the slave is requesting a position in the binary log on the master that is of the end of the file.

So you must perform a re-syncronization between the master and slave to sort this out.



JFYI:
I found this in mysql documentation may be it helps somebody else:
Quote:


A crash on the master side can result in the master's binary log having a final position less than the most recent position read by the slave, due to the master's binary log file not being flushed. This can cause the slave not to be able to replicate when the master comes back up. Setting sync_binlog=1 in the master my.cnf file helps to minimize this problem because it causes the master to flush its binary log more frequently.


I'll try this.

[Updated on: Thu, 03 May 2007 06:34]

Report message to a moderator
Re: replication problem after master crash [message #1501 is a reply to message #1189 ] Fri, 29 June 2007 11:36 Go to previous messageGo to next message
dyker is currently offline dyker
Messages: 3
Registered: May 2007
Junior Member
setting sync_binlog=1 and innodb-safe-binlog=1 doesn't solve this problem.. Any other suggestions?
Report message to a moderator
Re: replication problem after master crash [message #1503 is a reply to message #1501 ] Fri, 29 June 2007 12:15 Go to previous message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
dyker wrote on Fri, 29 June 2007 17:36

setting sync_binlog=1 and innodb-safe-binlog=1 doesn't solve this problem.. Any other suggestions?

That does not solve your problem that the master and slave is out of sync.
It just minimizes the chance of it occuring again.

As I said you need to resyncronize the master and slave.
Read about it here: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.htm l
Step 3 - 10.

[Updated on: Fri, 29 June 2007 12:16]

Report message to a moderator
Previous Topic:InnoDB's compact row format
Next Topic:Optimized Configuration
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Sun Jul 5 18:06:19 EDT 2009

Total time taken to generate the page: 0.05212 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.