0

I have a sequence of sql files created with the command mysqlbinlog binlog.000093 > log93.sql, from 93 to 109. These logs go back to the creation of the database, so I suppose I can fully restore the database. I need to restore the database to the state it was at a specific datetime. I tried to execute the command cat log93.sql | mysql -u root -p but I get the error @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

I tried two things:

  • I added:
[mysqlbinlog]
skip-gtids

in the my.cnf file, but nothing, I get the same error.

  • I added (instead of the previous configuration):
[mysqld]
enforce_gtid_consistency = 'ON'
gtid-mode = 'ON'

but I get the error @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

I am running mysql Ver 8.0.33-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu)). How can I solve the issue and effectively restore the database?

Damiano Scevola
  • 86
  • 7

1 Answers1

-1

The mistake thou art encountering is concerning Global Transaction Identifiers (GTIDs) in MySQL. GTIDs are utilized to uniquely identify transactions across multiple servers and aid in maintaining data consistency in a replication setup.

When restoring a database using SQL files generated from binlogs, thou need to be cautious with GTID-related configurations to ensure a successful restoration. Since thou art attempting to restore to a specific datetime, thou should disable GTID-related checks during the restoration process.

To resolve the GTID error and effectively restore the database, follow these steps:

  1. Edit the my.cnf file: Open the MySQL configuration file (my.cnf) and append the following lines under the [mysqld] section to deactivate GTID-related checks:
[mysqld]
skip-gtids

  1. Restart MySQL Server: Save the changes to the my.cnf file and restart the MySQL server to apply the new configuration.

  2. Restore the database: After disabling GTIDs, thou can now restore the database using the SQL files. Execute the following command:

cat log93.sql | mysql -u root -p

The skip-gtids option in the my.cnf file will prevent the GTID checks from being applied during the restoration, allowing thou to restore the database from the SQL files without encountering GTID-related errors.

Once the restoration is complete, thou can revert the my.cnf file to its original state if thou desire to re-enable GTIDs for future operations:

  1. Remove the skip-gtids line from my.cnf.
  2. Restart MySQL Server again to apply the changes.
flournoy bristal
  • 1
  • 2
  • If I add the skip-gtids under [mysqld] I get an error in /var/log/mysql/error.log saying "[ERROR] [MY-000068] [Server] unknown option '--skip-gtids'." And as I mentioned I already tried the skip-gtids under [mysqlbinlog]. – Damiano Scevola Jul 24 '23 at 14:19