Adding GTID_PURGED to mysqldump Output

The mysqldump command is used to create logical backups of MySQL databases. When working with replication setups using GTIDs (Global Transaction Identifiers), it’s essential to manage the replication state consistently across servers. The option --set-gtid-purged=name allows specifying how GTID information should be handled in the dump output.

When using --set-gtid-purged=name, replace name with one of the following values:

• OFF: GTID information is not included in the dump output.
• ON: GTID information is included for all transactions in the dump output.
• UUID: Only transactions executed on the source with the same server UUID are included.
• CONSISTENT: All transactions executed on the source server are included, and the server’s GTID_PURGED set is updated to the GTID set of the dump.

Let’s illustrate each option with an example:

1. Using --set-gtid-purged=OFF:

                   mysqldump --set-gtid-purged=OFF -u username -p database_name > dump_without_gtid.sql
               

   This command creates a MySQL dump without including GTID information.

2. Using --set-gtid-purged=ON:

                   mysqldump --set-gtid-purged=ON -u username -p database_name > dump_with_gtid.sql
               

   This command includes GTID information for all transactions in the dump output.

3. Using --set-gtid-purged=UUID:

                   mysqldump --set-gtid-purged=UUID -u username -p database_name > dump_with_uuid_gtid.sql
               

   This command includes transactions executed on the source server with the same server UUID.

4. Using --set-gtid-purged=CONSISTENT:

                   mysqldump --set-gtid-purged=CONSISTENT -u username -p database_name > dump_consistent_gtid.sql
               

   This command includes all transactions executed on the source server, updating the server’s GTID_PURGED set accordingly.

To verify if the GTID settings are applied correctly in the dump file, you can search for GTID-related statements within the generated SQL file. For example, using a text editor or command-line tools to inspect the dump file:

        grep 'SET @@GLOBAL.GTID_PURGED' dump_with_gtid.sql
    

This command would show the line where the GTID_PURGED setting is defined in the dump file, confirming that GTID information is indeed included as specified.

Also check similar articles.

Setting Character Set in mysqldump Output
Dumping Routines (Functions and Procedures) with mysqldump
Directing Output to File in mysqldump
Using REPLACE INTO Instead of INSERT INTO in mysqldump
Quoting Table and Column Names in mysqldump Output