MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db

MySQLreplication

I have a standard simple master slave replication that usually runs fine.
But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table.

I have explicitly only specified one database in replicate_do_db and in binlog_do_db. So it should only log and process that database. Why am I getting errors on another table?

This is the output of master status and slave status:

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.001473
        Position: 84364707
    Binlog_Do_DB: my-database
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: my-master-host
                  Master_User: pm-rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001473
          Read_Master_Log_Pos: 82124707
               Relay_Log_File: mysqld-relay-bin.000486
                Relay_Log_Pos: 38975372
        Relay_Master_Log_File: mysql-bin.001473
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: my-database
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 38975226
              Relay_Log_Space: 82125053
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5309510
1 row in set (0.00 sec)

Best Answer

Replication filtering isn't bulletproof. Due to how the filtering is implemented the events responsible for your errors are being generated because the default database at query runtime is the my-database schema as expected and the query being executed is fully qualified INSERT INTO phpmyadmin.pma_column_info...

Peter Zaitsev explains the scenario well in this post:

Filtered MySQL Replication

Related Solutions

Mysql – Create a thesql slave database from master database

Going by your question, I will like to review what I believe you did thus far:

You stopped mysql on the Master
You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
I surmise the binlogs on the Master were copied as well

Look at the Slave's last binlog. From the question, it should be

mydbm1-bin.008524
Filesize 1330529

Believe it or not, you have to do a few things:

1) On the Master, create a replication user like this:

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';

2) Make /var/lib/mysql on the Slave owned by mysql user

chown -R mysql:mysql /var/lib/mysql

3) Make sure Master's server_id is explicitly set in my.cnf

[mysqld]
server_id = 1

4) Make sure Slave's server_id is explicitly set in my.cnf

[mysqld]
server_id = 2

5) Startup mysql on the Slave

service mysql start

6) Setup replication by running this on the Slave

CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

You will see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           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: 858190247
            Relay_Log_Space: 873772
            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)

If Slave_IO_Running and Slave_SQL_Running are both Yes, CONGRATULATIONS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql

Mysql – Get problem with Binlog_Do_DB in Master configuration

In Windows, you would need to modify the my.ini file and not the my.cnf file

Locate you my.ini file

If MySQL is being run as a service

Enter "services.msc" on the Start menu search box.
Find MySQL service under Name column, for example, MySQL56.
Right click on MySQL service, and select Properties menu.
Look for "Path To Executable" under General tab, and there is your .ini file, for instance, "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56

Otherwise the my.ini file is probably in a sub-directory of D:\xampp\mysql\

Once you've added in the binlog_do_db parameter, be sure to restart the MySQL service.

Edit by RolandoMySQLDBA

You can restart mysql from services.msc window by right clicking and choosing or you can login from DOS CommandLine as Administrator as run

C:\> net stop mysql
C:\> net start mysql

Also, make sure your settings are under the [mysqld] group header in my.ini

Best Answer

Related Solutions

Mysql – Create a thesql slave database from master database

Mysql – Get problem with Binlog_Do_DB in Master configuration

Related Question