Export MySQL database with a GTID

This week I was trying to migrate a single database from MySQL 8 server with replication to my local MariaDB installation (no replica) and I got this error:

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.

And when I tried to import the dumped file, I got this error:

Unknown system variable 'GTID_PURGED'

I Google it to understand the error and I found is about GTID which stands for Global Transaction Identifier, this allow to set a unique identifier for all the transactions on database replications.

The thing here is that I wanted to export a single database just to do some local tests and development, and I found is possible to turn the GTID purge option off using a single flag –set-gtid-purged with the mysqldump program like this:

mysqldump mydatabase  --set-gtid-purged=OFF > mydatabase.sql

This option removes the --set-gtid-purged=OFFSET @@SESSION.SQL_LOG_BIN=0; from the output file which is no needed because MariaDB and MySQL8 have different GTID implementations and won’t recognize the GTID_PURGED variable.

Leave a Comment