MySQL Global Transaction Identifiers and MySQL Backups

Submitted by utweb-systems on Sun, 06/08/2014 - 22:11

When site backups run for GT ID enabled MySQL database servers the following happens in the backup process:

Dumping MySQL database <db name> ..
    .. dump failed! 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-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

Which triggers a backup failed message.

The fix is to pass the --set-gtid-purgerd=OFF which allows the backup to complete successfully and allows the restore to be replicated across a GT ID enabled MySQL cluster by assigning new GT IDs.

Thanks,

-Alex

Status: 
Closed (fixed)

Comments

Submitted by JamieCameron on Mon, 06/09/2014 - 00:27

Thanks! However, is there any way to detect if MySQL has GT ID enabled? Because it doesn't look like that --set-gtid-purgerd=OFF flag is supported on all versions.

Submitted by utweb-systems on Mon, 06/09/2014 - 13:01

Jamie,

I think the easiest way is to detect the MySQL variable for gtid_mode = ON. This is the setting required to enable GT IDs on MySQL 5.6.

Thanks,

-Alex

Submitted by Issues on Mon, 06/23/2014 - 16:51

Automatically closed -- issue fixed for 2 weeks with no activity.

Submitted by utweb-systems on Wed, 09/10/2014 - 22:10

Jamie,

Thanks for this update. However we have a report from Alex that there is a typo in the code:

       $gtidsql = "--set-gtid-purgerd=OFF";

i.e. the string "purgerd" should instead be "purged".

--mic--

Submitted by JamieCameron on Wed, 09/10/2014 - 23:12

Oops - this will be fixed in the next Webmin release.

Submitted by Issues on Wed, 09/24/2014 - 23:21

Automatically closed -- issue fixed for 2 weeks with no activity.