2

I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information.

I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). I'll name these SRV1 and SRV2.

Out of 5 databases on SRV1, I want to replicate only 3 of them on SRV2.

I setup the replication filter with the following query, made to ignore the two remaining databases, so that in the future the replication process will pickup any new database created.

CHANGE REPLICATION FILTER 
     REPLICATE_IGNORE_DB = (ignore1,ignored2);

The replication process worked as expected and I saw the three databases created and fully replicated on SRV2. So far so good.

The result of SHOW SLAVE STATUS \G; was correct, showing

[...]
    Replicate_Do_DB:
Replicate_Ignore_DB: ignore1,ignore2
[...]

But a wild Windows Update appeared. And made SRV2 restart during the night. I detected today that the replication process stopped because the replication user tried to do a query on ignore1.

One more SHOW SLAVE STATUS \G; confirmed it, my replication filters are not here anymore.

[...]
    Replicate_Do_DB:
Replicate_Ignore_DB:
[...]

Wanting to troubleshooting it, I put again my replication filters, restarted the slave process, waited that Seconds_Behind_Master went to 0. I then asked Windows to stop the MySQL service, and start it again, as it would do on a normal OS restart.

Once again the replication filters got lost, and the slave process stopped because it's trying to replicate a database that doesn't exist on SRV2

Before going on 8.0.15, I went on 5.7 during 3 years with the exact same setup, but never encountered a single issue like that during server restarts. Is it something intended (that I would have missed in the 8.0 release notes?) or a bug in my configuration?

I know that I can fix it by adding the --replicate-ignore-db argument to the MySQL service binary path, but I still want to know if there is another way.

3

The short answer is: this is a feature.

The CREATE REPLICATION FILTER syntax was created as a way to dynamically modify replication filters on the slave without a restart. (bug report)

You are right, that the documentation is a bit lean on this, but I found this snippet from FAQs in the MySQL Release announcement of the feature in 5.7:

11) Are these changes made through the new command persistent upon restarting the server?
A) No, the changes are not persistent. You have to change the values again after restarting the server. If you want these changes to be persistent, you can put them in my.cnf file.

[src]

What is interesting is that you claim this is the same setup on 5.7. It would be good if you could get that behavior reproducible (5.7 setup without options in my.cnf survive restarts).

I'd also recommend opening a bug report for 8.0/5.7 to at the very least get this behavior documented on the official documentation.

Immediate fix is of course to set the config options.

  • Oh thanks so much for having digged so deep in the bug database and the blog, I would have never found it myself! I guess that answers my question, at least. I should still have my 5.7 service and configuration installed, I'll check back on Monday if I can reproduce it or if it was more like a bad memory of what I'd done 3 years ago. – Benjamin C. May 18 at 0:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.