Bug #81494 mysqldump can't get all databases backup with Group Replication

• Submitted: 19 May 2016 3:22
• Modified: 16 May 2019 16:45
• Reporter: Yoshiaki Yamasaki
• Status: Closed
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S4 (Feature request)
• Version: 5.7.10, 5.7.17
• OS: Any
• CPU Architecture: Any
• Tags: group replication, mysqldump

[19 May 2016 3:22] Yoshiaki Yamasaki

Description:
mysqldump can't get all databases backup with transaction_write_set_extraction=MURMUR32. It mean mysqldump can't get all databases backup with Group Replication.

* When using Group Replication, we must set transaction_write_set_extraction=MURMUR32.

* I want to add new server to Group Replication group online. I think it is important things to achieve this.

How to repeat:
[root@yyamasaki mysql]# mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.10, for linux-glibc2.5 (x86_64)
[root@yyamasaki mysql]# 
[root@yyamasaki mysql]# mysqldump --user=root --password=root --master-data=2 --flush-logs -h 127.0.0.1 -P3306 --hex-blob --default-character-set=utf8mb4 --all-databases --single-transaction --triggers --routines --events > mysql_bkup_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
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)
[root@yyamasaki mysql]# 

Suggested fix:
Execute "set session transaction_write_set_extraction='OFF';" before execute 'SAVEPOINT sp'.

* "transaction_write_set_extraction" is dynamic variable.
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_wri...

[30 Dec 2016 3:20] MySQL Verification Team

Hello Yoshiaki,

Thank you for the feature request!

Thanks,
Umesh

[2 Feb 2017 14:47] MySQL Verification Team

https://bugs.mysql.com/bug.php?id=84799 marked as duplicate of this one.

[17 Apr 2018 22:27] Kenny Gryp

I believe this problem is fixed in:

* 8.0.1: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
* 5.7.19: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html

I tried it on both latest 8.0 and 5.7 versions and mysqldump --all-databases --single-transaction works!

[18 Apr 2018 1:15] Yoshiaki Yamasaki

> I believe this problem is fixed in:
> 
> * 8.0.1: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
> * 5.7.19: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html
> 
> I tried it on both latest 8.0 and 5.7 versions and mysqldump --all-databases --single-transaction works!

Thank you!
I think so. Because Group Replication now supports SAVEPOINT SQL transactions.

I tried it on 5.7.21 and mysqldump --all-databases --single-transaction works also.

[16 May 2019 16:43] Margaret Fisher

Posted by developer:
 
Closing off as already fixed - handling with Doc Bug #25331755 MISSING DOCUMENTATION FOR BACKUP/RECOVER WHEN USING MYSQL GROUP REPLICATION MySQL Bug:  84371