2

I'm running a test cluster with three nodes - db-[1-3]. All three were happy members of a single primary group replication cluster. They were all configured with group_replication_start_on_boot=on. After an unattended upgrade (.. which has now been disabled) they all got stuck in a failure mode, as dpkg was unable to properly upgrade the package; it got stuck on a futex lock waiting forever. My test cluster was then dead as all three nodes had got stuck in that upgrade lock over a few days.

I were able to rectify the problem by switching group_replication_start_on_boot to off, which meant that dpkg and apt were able to upgrade the system and restart the MySQL daemon. However, this broke my group replication cluster, since the upgrade process ran a an SQL statement locally on each node as part of the upgrade process, which got inserted into the binlog - now each node thinks it's ahead of all the other ones as they have local statements in the binlog that hasn't been synced to other nodes.

After digging through the binlogs I were able to see that the locally issued statements were identical on each server, so I could safely ignore them:

mysql> show binlog events in 'binlog.000010';
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
| binlog.000010 |   4 | Format_desc    |         2 |         126 | Server ver: 8.0.33-0ubuntu0.22.04.1, Binlog ver: 4                                                  |
| binlog.000010 | 126 | Previous_gtids |         2 |         253 | 3ae6293d-dfc6-4896-af5d-92963a090e0e:15-195:1000035-1000037,
3f11c191-ca88-11ed-89cf-fa163ea3e6a7:1 |
| binlog.000010 | 253 | Gtid           |         2 |         330 | SET @@SESSION.GTID_NEXT= '3f11c191-ca88-11ed-89cf-fa163ea3e6a7:2'                                   |
| binlog.000010 | 330 | Query          |         2 |         477 | use `mysql`; ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket' /* xid=3 */                |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

I assume the 3f11c191-ca88-11ed-89cf-fa163ea3e6a7 GTID is the what broke the state information between the primary and this node.

However, this left a bit of a bad taste, as I would like to be able to upgrade the MySQL server versions in the future, and I ended up with a broken cluster. This might have been a special case since the cluster actually died when all three nodes became unavailable.

I'd like to avoid this in the future, so the question is two-fold:

If I manually disable group_replication_start_on_boot before running apt upgrade in the future, will the transaction be properly synced when group replication is started next time? (I'm guessing it won't since it will then have transactions locally that the master doesn't have)

.. and if not, what is the recommended way of handling distribution based upgrades when running a group replication based cluster of nodes? How would you upgrade a cluster in a way that doesn't cause downtime if you have to manually zero out the used gtids on the primary after upgrading the nodes?

1 Answer 1

0

If I manually disable group_replication_start_on_boot before running apt upgrade in the future, will the transaction be properly synced when group replication is started next time?

No, not by itself, I've tried this, but the scripts in the deb package always make some mods to mysql.user

.. and if not, what is the recommended way of handling distribution based upgrades when running a group replication based cluster of nodes?

The best solution I've found is to disable replication on boot loose-group_replication_start_on_boot = OFF before upgrade and to use the solution here: on serverfault that disables the binlog for the mysql database during the upgrade process. To quote the solution:

# /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

You could also choose to not replicate the mysql db all together.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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