CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at all.
This is the content of our my.cnf files:
Replication Setup A
- Where: (master) Mysql 5.5.40 > (slave) MariaDB 10.0.19
server-id = 1002 binlog-do-db = db_1 binlog-do-db = db_2 binlog-do-db = db_3 sync_binlog = 1 expire_logs_days = 30 innodb_flush_log_at_trx_commit = 1
server-id = 1005 replicate-do-db = db_1 replicate-do-db = db_2 replicate-do-db = db_3 slave-parallel-threads=4 slave_compressed_protocol = ON slave-skip-errors = all log-slave-updates = ON
Strangely, DB structure altering commands works just fine on a different server pair with an *almost similar replication setup:
Replication Setup B
- Where: (master) MariaDB 10.0.17 > (slave) MariaDB 5.5.41
server-id = 1004 bind-address = "0.0.0.0" binlog-do-db = db_a binlog-do-db = db_b binlog-do-db = db_c binlog-do-db = db_d binlog-do-db = db_e binlog-do-db = db_f binlog-do-db = db_g sync_binlog = 1 expire_logs_days = 30 innodb_flush_log_at_trx_commit = 1
server-id = 1003 bind-address = "0.0.0.0" replicate-do-db = db_a replicate-do-db = db_b replicate-do-db = db_c replicate-do-db = db_d replicate-do-db = db_e replicate-do-db = db_f replicate-do-db = db_g slave_compressed_protocol = ON slave-skip-errors = all
Notes for both replication pairs:
- Names of the databases were obscured.
- Parts of the my.cnf file where i think replication is configured are only posted.
- Is this a bug or an undocumented feature?
- Did i do something wrong on my setup?
- What could be other factors that might effect to this behaviour?
- If there really are (query)commands that aren't supported for
replication, what are these?
- 4.a) Mysql/MariaDB version setups that has the same effect?
I tried looking at the error logs of the slave for any hints and used structure altering query commands as search keywords but to no avail. Maybe I'm not looking in the right direction?
Further testing ensued. As suggested by this SO post, I might have to remove or comment out
replicate-do-db from my.cnf; So I did and I found out that:
- Creating a new table is replicated.
- Altering a table is not replicated.
- CRUD transactions were not replicated.
Also, things just got stranger for Replication Setup B where everything was just supposed to work.
- One DB failed to replicate an
ALTER TABLEcommand just yesterday and we only found out about it now.
- After the incident, I tried testing executing
ALTER TABLEcommands to all other DBs of the same server and it replicated just fine. In this test, I am unable to remove
replicate-do-dbfrom my.cnf as restarting the server is currently not an option.