Mysql Replicated database UPDATE not working

Question

I have been trying to start replication in a MySQL database. I followed all the steps from the MySQL manual to setup and configure the replication.

http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

I could start the replication without any trouble. Newly inserted data was replicated properly. But after a while I observed that though INSERTs are working, the UPDATEs are not replicating. So, my replicated database contains data just as it was inserted first time.

My master database is on an UNIX server with MySQL-5.1.56. The slave is used in Windows using WAMP package with MySQL 5.5.8. I have also tried a slave with lower version MySQL with WAMP5, with same effect.

Please share your ideas and experience on this. Thanks.

in the default configuration mysql replication stops on errors, so you would expect to see the replicated slave become further out of sync over time, (**rather than seeing just some queries replicated**). If you do not see any errors from the out put of `show slave status` on the slave machine, you should post onto your question your my.cnf configuration file so we can see what options might have effected the replication process. and also the slave my.cnf if that is also bespoke. — Tom, Feb 28 '12 at 22:01

Tom · Answer 1 · 2012-02-26T06:07:03.743

On the slave, you should run the command SHOW SLAVE STATUS

it will provide information on the reason for the failure;

mysql> show slave status\G


*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master.db.sever
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000154
        Read_Master_Log_Pos: 209998
             Relay_Log_File: mysqld-relay-bin.000480
              Relay_Log_Pos: 105395
      Relay_Master_Log_File: mysql-bin.000154
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: mysql
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0         <-----here this value
                 Last_Error:              <-----here this value
               Skip_Counter: 0
        Exec_Master_Log_Pos: 209998
            Relay_Log_Space: 105395
            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
1 row in set (0.00 sec)

You are interested in the Last Errno, to see that it is indeed 0, the the last error message content.

Presumably you could also check that your replication user has "INSERT" and "UPDATE" permissions on the table that it is supposed to replicate to. This information is in the mysql database and can be checked with the SHOW GRANTS statement;

mysql> show grants for root@'someserver.com';
+---------------------------------------------------------------------------------------+
| Grants for root@someserver                                       |
+---------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'oomeserver  ' WITH GRANT OPTION | 
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

score 1 · Accepted Answer · answered Feb 24 '14 at 12:29

I sorted it out a few months ago. The problem was, I tried to replicate only one single database, and set binlog_do_db and binlog-ignore-db options in master to select only that database. But that left out any statement which was executed without selecting database by USE database. Using replicate-ignore-db and replicate-do-db options in slave would do the same. So I finally fixed it by using the replicate-wild-do-table option in the slave, referenced here. Added the following in the configuration of slave server.

replicate-wild-do-table=mydb.%

Mysql Replicated database UPDATE not working

2 Answers2