How can I export / import a database correctly?

Hello,

What you could try is to open the .sql dump file and remove the GLOBAL statement from line 24. Then try the import again.

Hope that this helps! Regards, Bobby

The error you’re encountering:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

occurs because you’re working with a GTID (Global Transaction ID)-enabled MySQL cluster. When exporting a database from a cluster with GTIDs enabled, the mysqldump command captures the GTID state in the dump file, including the @@GLOBAL.GTID_PURGED variable. This variable holds information about the transactions that have been purged and is relevant in a GTID environment.

However, when importing this dump file into a different MySQL instance (like your local environment with Homestead), if GTIDs are not enabled or not used in the same way, this causes the conflict you’re seeing. The error indicates that @@GLOBAL.GTID_PURGED can only be set when the @@GLOBAL.GTID_EXECUTED is empty (i.e., when no transactions have been executed on the target MySQL instance).

Steps to Correct the Issue

1. Skip GTID-Related Information During the Dump

• When exporting your database from the cluster, you can skip the GTID information by using the --set-gtid-purged=OFF option in the mysqldump command.

Example:

mysqldump --single-transaction --quick --set-gtid-purged=OFF -u[user] -p[password] -h[host] -P[port number] [database] > [path]

This option ensures that the GTID information is not included in the dump file, making it compatible with databases that don’t use GTIDs (such as your local Homestead environment).

2. Alternatively, Remove GTID Entries Manually

• If you have already created the dump file, you can manually remove the GTID-related lines (such as SET @@GLOBAL.GTID_PURGED=) from the SQL file before importing it.

Steps:

1. Open the dump file in a text editor.
2. Search for lines that include SET @@GLOBAL.GTID_PURGED and remove them.
3. Save the file and then import it again with your mysql command.

3. Use the Correct Import Options

• When importing the database, you might also want to ensure you’re not applying any specific GTID settings. The standard import command should work as long as GTID information is removed or ignored:

mysql -uhomestead -psecret -D [database] < [file.sql]

Conclusion

The error is due to the GTID information that is part of the database cluster. To avoid this error, you can either:

• Use --set-gtid-purged=OFF when exporting the database, or
• Manually remove the GTID-related lines from the dump file before importing.

This will make the export/import process compatible with your local setup where GTIDs may not be in use.