Import MySQL data failed with error 1839

I have master slave setup of MySQL with GTID configured. I took back data backup of master and importing it to individual Test server. It is failing to import as

ERROR 1839 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON

I tried with --set-gtid-purged=OFF and AUTO, but no luck.

Solution 1:

If you run a

SHOW MASTER STATUS\G

you'll see something like this:

mysql> show master status\G
*************************** 1. row ***************************
         File: mysql-bin.000299
         Position: 780437462
         Binlog_Do_DB:
         Binlog_Ignore_DB:
         Executed_Gtid_Set: 075d81d6-8d7f-11e3-9d88-b4b52f517ce4:1-616637650,
         e907792a-8417-11e3-a037-b4b52f51dbf8:1-25385296642
         1 row in set (0.00 sec)

Becuase when GTID is enabled all the servers got their own uuid, and there are transactions. I suppose you created the dump with mysqldump, and if you look at the beginning of that file, you'll find something similiar as this:

--
-- GTID state at the beginning of the backup 
--

 SET @@GLOBAL.GTID_PURGED='075d81d6-8d7f-11e3-9d88-b4b52f517ce4:1-616648986,
 e907792a-8417-11e3-a037-b4b52f51dbf8:1-25385296642';

This is the command which cannot be executed.

You have the following options:

• Remove this command from the mysql dump file. Simply delete it. All the inserts will appear on slave as it's local transactions

• If you want to prevent this happening, you can also reset master on slave

  mysql> RESET MASTER;

  This command will clean up the 'Executed_Gtid_Set' variable on slave, so you can import the dumpfile directly, and the previously mentioned set_global_gtid_purged variable takes action

• When you create the mysqldump, you can skip the GTID setup part as adding the --set-gtid-purged=OFF parameter for mysqldump.

NOTE:

if the GTID subset differs on master between master and slave (if you want to use this in a replication setup) then the replication will not work, I'd recommend a binary dump and restore, as setting the slave's GTID exactly to the master's.

With GTID there are a lot of new problems emerge, but your replica setup will be more consistent. It is worth working with that.

Solution 2:

If you are like me and you don't want to re-run your dump because it was a very long operation you can just remove those lines after the fact.

find . -name '*.sql' -type f -exec perl -0 -i.bak -pe 's/SET \@\@GLOBAL\.GTID_PURGED=\x27.*?\x27;//gs' {} +

Run this in the folder with your .sql files. It will save the old veresion as .bak.

This worked for me.

Solution 3:

I have a huge database, so, like @Goddard, I have a backup/dump spread across several files.  I have low disk space, so I export my dump in compressed format (i.e., .sql.gz).  @Goddard's solution appeals to me, but, as I am running on low disk space, I can't afford to extract those files to .sql and then apply changes.  Instead of that I'll execute the following adaptation of @Goddard's answer to import .sql.gz files, removing the GTID query as I go.

find "$DIR" -name "*.sql.gz" | while read table
do
    echo "$table"
    zcat "$table" | perl -pe 's/SET \@\@GLOBAL\.GTID_PURGED=\x27.*?\x27;//gs' | \
                    mysql -h "$HOST" -u "$USER" -p"$PASS" -P "$PORT" "$DB"
done