Home / server / Questions / 686025
Accepted
dlo
Asked: 2015-04-28 17:37:20 +0800 CST

MySQL package upgrades (Debian apt-get) always break master-master replication due to changes in mysql schema

  • 772

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?

mysql

3 Answers

  1. 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.

    • 2
  2. Best Answer

    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).

    • 2

  3. 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 ?.

    • 1