patternsqlMinor
MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db
Viewed 0 times
areslavereplicate_do_dbreplicatesmysqlthatchangesnorbinlog_do_dbneither
Problem
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
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
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
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
Solution
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
Peter Zaitsev explains the scenario well in this post:
Filtered MySQL Replication
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
Context
StackExchange Database Administrators Q#78362, answer score: 2
Revisions (0)
No revisions yet.