0

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)
  • How did you setup the slave exactly? – mikky Nov 4 '17 at 23:01
0

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/

  • The replication error is a warning to you that you did not initially set up replication correctly. This solution is only masking that fact at this point. Your data sets may or may not be consistent across the two machines at this point, since you appear to have played back some transactions that you should not have, and you have no meaningful way of knowing what their side effects were. – Michael - sqlbot Nov 5 '17 at 3:18
  • There was only one additional transaction committed to the database during that time and I setup 3 additional test slaves all with the same error. Counts from all the DB's all are accurate and everything is in sync. I'll keep digging to see if I can find any other reason for the discrepency – RomeNYRR Nov 5 '17 at 15:16
  • Be sure you are using a version of mysqldump that was bundle with the same or newer version of MySQL Server. (mysqldump -V should give you the server version that your version of mysqldump was bundled with). The --master-data=1 option should cause it to emit the correct GTID statements near the top of the dump file, iirc. – Michael - sqlbot Nov 5 '17 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.