Mysql – Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

configurationmariadbMySQLmysql-5.5replication

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.

Setup

This is the content of our my.cnf files:

Replication Setup A

  • Where: (master) Mysql 5.5.40 > (slave) MariaDB 10.0.19

Master:

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

Slave:

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

Master:

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

Slave:

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.

Questions

  1. Is this a bug or an undocumented feature?
  2. Did i do something wrong on my setup?
  3. What could be other factors that might effect to this behaviour?
  4. 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?

Update (2015-06-04)

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 TABLE command just yesterday and we only found out about it now.
  • After the incident, I tried testing executing ALTER TABLE commands to all other DBs of the same server and it replicated just fine. In this test, I am unable to remove replicate-do-db from my.cnf as restarting the server is currently not an option.

Best Answer

USE db_99;
do something with db_1 or 2 or 3;

will not replicate because of your binlog-do-db and replicate-do-db. (The latter is redundant in your case.)

This is because replication (except for "wild" versions) is based on the USE, not on any explicit db spelled out in the statement.

Is it a "bug"? Well, a lot of people get burned by it.

Related Question