5

I have master-master replication set up on 2 Debian servers, and they replicate everything, including the mysql database itself (so that new users and such also replicate). This generally works very well, except that most, if not all, apt upgrades to mysql involve some changes to the mysql database schema, which cause replication errors that halt replication. Ultimately, I always need to manually fix by skipping the errant statements on each side. This is always time-consuming, and I worry I could make mistakes doing it manually (skipping too many statements, mistyping CHANGE MASTER details, etc.).

Is there something I can do to make sure that apt-get updates to MySQL in the future will get processed smoothly without causing replication problems? Surely there's a well-established best practice for this?

dlo
  • 451
  • 1
  • 4
  • 14
  • 1
    You might want to try using `replicate_ignore_table` to not replicate all tables in the `mysql` database. I personally don't replicate that database, and apply user etc. updates on each node separately. – wurtel Apr 29 '15 at 12:33
  • On an ongoing operating basis I wouldn't want to turn it off, as it's very helpful to be syncing users, permissions, etc. without having to apply to each node. But if there was a script I could run before and after updating to toggle this on/off, I suppose that would be fine. My understanding is that newer versions of mysql allow you to toggle replication filters dynamically using sql statements, but my version (5.5) apparently doesn't (?), so such a script would need to restart mysql with varying parameters... if the downtime is quick, it would be acceptable. Suggestions for how to do it? – dlo Apr 29 '15 at 13:39
  • 1
    Does this help ? https://dev.mysql.com/doc/refman/5.6/en/replication-upgrade.html – koustuv Apr 30 '15 at 19:28

3 Answers3

2

it would be nice to know what commands broke your replication, but I suppose, the mysql_upgrade script would be that rogue. If yes, you can rebuild the mysql package, adding to the post install script a --skip-write-binlog (this is not needed after 5.6.7)

But normally I never would just apt-get upgrade a server which is in production, stop the slave, upgrade it and reconnect them. This is the zen way.

banyek
  • 379
  • 1
  • 10
  • Reasonable advice, but do you have a tested, step-by step method or script? (whether it uses apt-get or not) One thing I was thinking was have a temporary file I could put in conf.d that had skip-write-binlog along with other settings that would stop my applications from connecting to mysql but allow upgrades to happen (is this possible using cnf settings?). That way I could restart knowing that no apps could change data, perform my upgrade (apt-get or otherwise), then remove the temp cnf file and restart clean. – dlo May 07 '15 at 18:46
  • I looked at the source for the mysql-server deb package, and the postinst/fix_privs routine has a number of `ALTER TABLE user` commands... that's probably what's broken replication in the past. So maybe the easiest path is temporarily `binlog-ignore-db=mysql`, upgrade as usual, and then remove the ignore-db? Requires one additional restart, but seems simple enough. – dlo May 07 '15 at 21:32
2

I don't know if it will work for every possible upgrade scenario, but I just tested this, and the upgrade worked without any replication problems:

# /etc/mysql/conf.d/binlog_ignoredb_mysql.cnf.disabled
# Rename this to end in .cnf prior to performing `apt-get upgrade`.
# Otherwise, its attempts to `ALTER TABLE users` will cause replication errors.
# After upgrade is complete, rename back to .disabled and then /etc/init.d/mysql restart

[mysqld]
binlog-ignore-db=mysql

Note that my test was on a minor upgrade (5.5.41 to 5.5.43).

dlo
  • 451
  • 1
  • 4
  • 14
1

While investigating whether mysql_upgrade causing issues in replicated setups was still a thing (because it has bitten me and my team before, but at the same time binlog-ignore-db=mysql also has issues) I ran into this page and I was excited to read the answer by banyek which states that this is a solved problem!

However I wanted a more explicit reference to confirm that the problem is solved (and also I was curious how it was solved) so I looked through the MySQL 5.6.7 change logs and here it is:

Replication: mysql_upgrade failed when the server was running with gtid_mode=ON and --disable-gtid-unsafe-statements because the MySQL system tables are stored using MyISAM. This problem is fixed by changing the default logging behavior for mysql_upgrade; logging is now disabled by default. (Actions taken by mysql_upgrade depend on the server version, and thus should not be replicated to slaves.) To enable logging, you can execute mysql_upgrade using the --write-binlog option.

Update:

In response to the comment by @dlo, the help text of the mysql_upgrade command can be inspected to confirm whether a given server has received the update mentioned above:

$ mysql_upgrade --help | grep -A2 write-binlog
  --write-binlog      All commands including mysqlcheck are binlogged. Disabled
                      by default; use when commands should be sent to
                      replication slaves.

If the Disabled by default text is there you should be good .

xolox
  • 161
  • 1
  • 4
  • I'm not able to confirm this right away in a working setup, but it's a very logical behavior change, so I hope it's true. – dlo Mar 27 '20 at 15:36