Mysql – How to not replicate index creation on ignored tables in MySQL/MariaDB

indexmariadbMySQLreplication

I have a setup with two MariaDB masters, each being the slave of the other.
Some tables are ignored, with the configuration option :

replicate-ignore-table = moodle.mdl_temp_%

My problem is when a statistic batch is run against one of the server :
a CREATE TABLE followed by a CREATE INDEX is issued, and the last statement is replicated (looking at the binlogs the CREATE TABLE is effectively not replicated), which broke the replication on the other server.

Who to avoid the CREATE INDEX being replicated ?

[EDIT]
Here is the error on the second server when the statistic batch is ran on the first server.

Error 'Table 'moodle.mdl_temp_stats_daily' doesn't exist' on query. Default database: 'moodle'. Query: 'CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)

And when I take a look at the binlogs, there's no "CREATE TABLE" but there is still the CREATE INDEX :

# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000101 | grep -i create
CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)
CREATE INDEX mdl_tempstatdail_tim_ix ON mdl_temp_stats_daily (timeend)
CREATE INDEX mdl_tempstatdail_rol_ix ON mdl_temp_stats_daily (roleid)
[...]

Best Answer

Plan A: Include the index(es) in the CREATE TABLE statement.

Plan B: Use this to add an index:

ALTER TABLE moodle.mdl_temp_stats_daily ADD INDEX(courseid);

(I have not tested either.)

Related Question