While dumping the database using mysqldump, sometimes we encounter the following warning message.
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.
Even though it’s only a warning, it will add the following lines to the dump file-
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=”;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
And when the database is restored to a MySQL server, it will result in the following exceptions. SUPER user privilege is required to pass these statements, but it doesn’t exist in RDS.
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 14573: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
As stated in the earlier post, you can still restore your database because the force command will disregard these errors and continue with the subsequent stages.
Alternately, you can take a backup or dump while setting the following flag.
–set-gtid-purged=OFF
Example:
mysqldump -h myrds-db.axybxybasqkr.eu-east-2.rds.amazonaws.com -P 3306 -u myuser -p –set-gtid-purged=OFF emirateshr-1366 > emirateshr1366_stabletest.sql
Documentation:
–set-gtid-purged=value
This option is for servers that use GTID-based replication (gtid_mode=ON). It controls the inclusion of a SET @@GLOBAL.gtid_purged statement in the dump output, which updates the value of gtid_purged on a server where the dump file is reloaded, to add the GTID set from the source server’s gtid_executed system variable.
gtid_purged holds the GTIDs of all transactions that have been applied on the server, but do not exist on any binary log file on the server. mysqldump therefore adds the GTIDs for the transactions that were executed on the source server, so that the target server records these transactions as applied, although it does not have them in its binary logs.
–set-gtid-purged also controls the inclusion of a SET @@SESSION.sql_log_bin=0 statement, which disables binary logging while the dump file is being reloaded. This statement prevents new GTIDs from being generated and assigned to the transactions in the dump file as they are executed, so that the original GTIDs for the transactions are used.
Hope you find this article helpful.
Big Data and SQL 