MySQL dump created via charm action cannot be restored on a different cluster
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL InnoDB Cluster Charm |
Fix Committed
|
Undecided
|
Unassigned |
Bug Description
We had a scenario where we needed to migrate an entire keystone service from one model, to a different model. This was needed because the existing cloud was to be expanded to multiple regions, where one keystone would be responsible for them all.
So the need arose to dump the keystone database and restore it in a different model in the MySQL innodb cluster present there. The problem is that once we restored the database dump, all secondary machines exited the cluster.
If we restored the dump without first creating the database, each of the secondary nodes would fail with:
32023-10-
If the database was created prior to restoring the dump, it would fail with:
2023-10-
2023-10-
This turned out to be because of the way the MySQL database dump was created. Currently, the action sets the --set-gtid-purged to COMMENTED, which according to the documentation[1], will set the `sql_log_bin` variable. This in turn breaks replication. We had to remove both secondary units and add new ones to recover.
Funny enough, this warning is displayed when creating a dump:
gabriel@
Running operation 33 with 1 task
- task 34 on unit-mysql-
Waiting for task 34...
mysqldump-file: /var/backups/
outcome: Success
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-
For this use case, the proper value for that option is "OFF". This will allow mysql group replication to properly handle the GTID and just insert the needed information into the database.
[1]https:/
Changed in charm-mysql-innodb-cluster: | |
status: | New → In Progress |
Reviewed: https:/ /review. opendev. org/c/openstack /charm- mysql-innodb- cluster/ +/899062 /opendev. org/openstack/ charm-mysql- innodb- cluster/ commit/ d931f05933278c2 4aa86b86db187f9 df85d032fb
Committed: https:/
Submitter: "Zuul (22348)"
Branch: master
commit d931f05933278c2 4aa86b86db187f9 df85d032fb
Author: Gabriel Adrian Samfira <email address hidden>
Date: Mon Oct 23 15:03:22 2023 +0300
Add gtid-purged-mode option
When creating a backup of a database, the --set-gtid-purged settin must
be set to OFF if the dump is to be restored on a different MySQL
cluster. This change creates a knob in the mysql dump action of the
charm that allows setting this option to one of the supported values.
Closes-Bug: #2040159
Change-Id: Ide1b222c36edbe 65de123a57630fe 3dc24c4d6b6
Signed-off-by: Gabriel Adrian Samfira <email address hidden>