Import MySQL data failed with error 1839

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.

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.