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 @@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.