Bug #54405 Master Slave Replication will not start
Submitted: 10 Jun 2010 16:48 Modified: 10 Jul 2010 18:02
Reporter: Kai-jens Meyer Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:Ver 14.14 Distrib 5.1.31 OS:Linux (Ubuntu 8.04LTS)
Assigned to: CPU Architecture:Any
Tags: replication reading event log

[10 Jun 2010 16:48] Kai-jens Meyer
Description:
We have a really big database with >27 Mio records. 
This database will be replicated to 3 slaves (all running under Linux)
Now a new system was installed and on this system we like to install slave #4.
After database dump was included we have started the slave with the correct logfile, logfile position, replication user and password.
Replication start and fills logfiles but the second thread _WILL_NOT_INSERT_ any statement.

+----+-------------+-----------+----+---------+--------+----------------------------------+------------------+
| Id | User        | Host      | db | Command | Time   | State                            | Info             |
+----+-------------+-----------+----+---------+--------+----------------------------------+------------------+
| 56 | system user |           |    | Connect | 2693   | Waiting for master to send event |                  |
| 57 | system user |           |    | Connect | 120986 | Reading event from the relay log |                  |

The database is now 120986 seconds behind master and growing. 

With the SHOW SLAVE command we can see that the slave reads from master, because the start logfile was 'mysql-bin.000520' and currenly we are in 'mysql-bin.000566'.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: magdi.***.**
                  Master_User: atlas
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000566
          Read_Master_Log_Pos: 33047957
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 13990571
        Relay_Master_Log_File: mysql-bin.000501
             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: 20965499
              Relay_Log_Space: 6783262781
              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: 122481

I have a lot of mysqld-relay-bin.log but no success so far.

How to repeat:
dumping in database from mysqldump export
setting replication info with set master...
start slave
[10 Jun 2010 17:00] Kai-jens Meyer
The master server has the same version. 
Ver 14.14 Distrib 5.1.31

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000566
        Position: 101836173
    Binlog_Do_DB: ho------
Binlog_Ignore_DB: mysql,information_schema
[10 Jun 2010 18:02] Sveta Smirnova
Thank you for the report.

But version 5.1.31 is old and many bugs were fixed since. Please try current version 5.1.47 and if problem still exists send us full error log file.
[10 Jul 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".