MySQL master-slave replication not updating

This is my first time setting up master-slave replication. I am using this as a guide.

I followed the steps and all works till the last part when i need to verify the replication. When i create a new table and INSERT some data to the table in the master database, it does not being replicated in the slave database.

Below is the master's my.cnf

server-id = 1
binlog-do-db = databasename
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

And the slave's my.cnf

server-id = 2
master-host=10.77.88.111
master-connect-retry=60
master-user=slave_user
master-password=slave_password
replicate-do-db=databasename
replicate-ignore-table=table1
replicate-ignore-table=table2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Below is the SHOW SLAVE STATUS

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.77.88.111
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 389591
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: databasename
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: databasename.table1,databasename.table2
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 389591
              Relay_Log_Space: 120695
              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:
1 row in set (0.00 sec)

I have tried:

• access master database from slave machine using slave_user ==> success; so issue does not lie here
• restart mysql service in slave machine ==> no change
• there is no error shown in /var/lib/mysql/mysql.err
• stop firewall using # service iptables stop ==> no change

Please help! Really appreciate it!

Solution 1:

There is nothing wrong.

Here is how you can tell

INDICATION #1 : Slave Threads

When you ran SHOW SLAVE STATUS\G, it shows

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

This shows that both the IO Thread (Intercepts Binlog Events From the Master and Stored Them in the Relay Logs FIFO) and the SQL Thread (Reads Binlog Events From the Relay Logs FIFO and executes the SQL) and connected and running

INDICATION #2 : Relay Logs

When you ran SHOW SLAVE STATUS\G, it shows

Relay_Log_Space: 120695

This shows that the IO Thread has read 120K from the Master. This value should be constantly changing while the IO thread is reading new events.

INDICATION #3 : Executed Statements

When you ran SHOW SLAVE STATUS\G, it shows

Read_Master_Log_Pos: 389591
Exec_Master_Log_Pos: 389591

If these values are changing, IO threads is up to whatever position is in Read_Master_Log_Pos and the SQL thread has executed up to whatever position is in Exec_Master_Log_Pos.

CAVEAT

I see you have binary logging enabled on the Slave. You will not see the binary logs grow on the Slave. Why ? You forgot to add log-slave-updates to the Slave's my.cnf.

You would have to add log-slave-updates to the Slave's my.cnf and restart MySQL.

Solution 2:

There is nothing obviously wrong here.

The first thing I would do is ensure that when issuing the CREATE TABLE and INSERT commands you are actually USEing the database databasename.

You could also try removing the restrictions imposed by binlog-do-db,replicate-do-db etc and see if that helps.