MySQL Group Replication and logical backup


Taking a logical backup of a member of a Group Replication Cluster is not something very easy.

Currently (5.7.17, 5.7.18 or 8.0.0) if you want to use mysqldump to take a logical backup of your dataset, you need to lock all the tables on the member you are taking the dump. Indeed, a single transaction can’t be used as savepoints are not compatible with Group Replication.

[root@mysql3 ~]# mysqldump -p  --single-transaction --all-databases --triggers \
                      --routines --events >dump.sql
Enter password:
mysqldump: Couldn't execute 'SAVEPOINT sp': The MySQL server is running with the 
--transaction-write-set-extraction!=OFF option so it cannot execute this 
statement (1290)

So we need to use:

[root@mysql3 ~]# mysqldump -p  --lock-all-tables --all-databases --triggers \
                      --routines --events >dump.sql
Enter password:

This can have a negative effect on the full Group’s performance as the member having all the tables locked might start sending statistics reaching the flow control threshold. Currently we don’t have any way to decide to ignore those statistics for a given node.

The replication development team was of course aware of this problem (reported in bug 81494) and decided to support savepoints with group replication too.

Anibal blogged yesterday about this new improvement.

So with MySQL 8.0.1, we can now take a logical backup using a single transaction:

[root@mysql1 ~]# mysql -p -e "select @@version";
Enter password:
| @@version     |
| 8.0.1-dmr-log |
[root@mysql1 ~]# mysqldump -p  --single-transaction --all-databases --triggers \
                 --routines --events >dump.sql
Enter password:

Wooohoo, it works ! Good Job replication team ! Savepoints can now be considered as NOT being a LIMITATION anymore for MySQL Group Replication !

One thought on “MySQL Group Replication and logical backup

Leave a Reply

Your email address will not be published. Required fields are marked *


Last Tweets Last Tweets

    Locations of visitors to this page