MySQL 5.7 Replication Error “Can’t create database”

MySQLmysql-replication

I've dumped a test database backup to a newly created test slave server, but I'm having trouble getting the replication moving. Since the dump, I've only added one record to the master database to attempt to kick off the replication. The error I'm getting on the slave is:
Last_SQL_Error: Error 'Can't create database 'products'; database exists' on query. Default database: 'products'. Query: 'create database products' I've tried setting the GTID to the next transaction but no luck. Any pointers would be greatly appreciated.

my.cnf

[mysqld]
server-id=4
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin
expire_logs_days=1
read_only=ON

show slave status: 

Last_SQL_Error: Error 'Can't create database 'products'; database exists' on query. Default database: 'products'. Query: 'create database products'
Retrieved_Gtid_Set: dd652f47-c162-11e7-ad42-42010a8005a4:1-29824
Executed_Gtid_Set: dd652f47-c162-11e7-ad42-42010a8005a4:1-5928

** show master status **

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1426140
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: dd652f47-c162-11e7-ad42-42010a8005a4:1-5928
1 row in set (0.00 sec)

Best Answer

So in case anyone bumps into this, here's how I solved my issue:

  1. stop slave;
  2. reset master;
  3. set global GTID_PURGED="SET_THIS_TO_MY_MASTER_LAST_KNOWN_GTID"; you can find this out by running get master status
  4. show slave status\G

For more information you can check out this helpful article: https://avdeo.com/2015/03/04/restoring-slave-when-gtid-is-enabled-on-master/

Related Topic