Mysql replication is enabled but stuck at “waiting for binlog to be updated”

MySQLreplication

I have set up replication between an existing database (server-id=23), and a new one (server-id=51).

I have followed the steps in this stackoverflow question, but replication is not happening.

On the master:

State: Has sent all binlog to slave; waiting for binlog to be updated

When I show slave hosts:

+-----------+------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+------+------+-------------------+-----------+
|        51 |      | 3306 |                 0 |        23 |
+-----------+------+------+-------------------+-----------+
1 row in set (0.01 sec)

On the slave:

State: Slave has read all relay log; waiting for the slave I/O thread to update it

show slave status

           Slave_IO_State: Waiting for master to send event
 ...snip...
         Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 98
           Relay_Log_File: mysqld-relay-bin.000002
            Relay_Log_Pos: 244
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

Is there something I need to do to get replication happening?

Best Answer

MySQL Replication is running just fine. How do I know ?

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

This indicates that the two replication threads are alive.

To verify this, run the following on the Slave:

SELECT COUNT(1) ReplicationThreadCount FROM information_schema.processlist
WHERE user = 'system user';

If ReplicationThreadCount > 1, then replication is running just fine.

You can also run SHOW PROCESSLIST; and look for two connections whose user is system user

The two states you mentioned for the Master and Slave are normal for a running replication status

As you add data to the Master, you can know that data is coming to the Slave by looking at

  • Read_Master_Log_Pos
  • Exec_Master_Log_Pos
  • Relay_Log_Space

in the output of SHOW SLAVE STATUS\G. These numbers will be changing, which indicate its working.

Related Question