This is the cluster setup:

------       ------
| m1 |  ---  | m2 |
------       ------
  ||           ||
------       ------
| s1 |       | s2 |
------       ------

m1 and m2 run mariadb-server-10.1 version 10.1.18+maria-1~jessie.

m1 and s2 run mariadb-server-10.1 version 10.1.18+maria-1~wheezy.

Nodes m1 and m2 are in a Galera Cluster, and replication between these nodes works fine. There is also an arbitrator node running on a separate server.

s1 is a slave node to m1, and s2 is a slave node to m2. The replication to slave nodes is using classic MySQL replication.

Now, the issue here is that the slave nodes needed to be started from fresh state. This was the process to start the replication:

  1. service mysql stop on slave node.
  2. Empty /var/lib/mysql on slave node.
  3. Run innobackupex -u user -p password on master node.
  4. Run innobackupex -u user -p password --apply-log /path/to/backup on the master node.
  5. Copy database backup files to slave server.
  6. Run innobackupex --copy-back /path/to/backup on slave server.
  7. Run chown -r mysql.mysql /var/lib/mysql/* on slave server to fix permissions.
  8. service mysql start on slave server.

After this, I started mysql -u root -p password, and issued this command:


Values for NNNNNN and nnn were retrieved from xtrabackup_binlog_info file from the backup files.

Now, this process worked perfectly between m1 and s1. However, with m2 and s2, it generates an error message (I split the error message to multiple lines for better readability):

Could not execute Write_rows_v1 event on table db.table;
Cannot add or update a child row: a foreign key constraint fails
(`db`.`table`, CONSTRAINT `FK3EBA76781F6AACF1` FOREIGN KEY (`job`)
REFERENCES `table2` (`id`)), Error_code: 1452; handler error
HA_ERR_NO_REFERENCED_ROW; the event's master log mariadb-bin.000710, end_log_pos 1523526

I have tried to repeat the process again a couple of times without any success.

I have also issued RESET MASTER on m2 to reset the replication log file in case it had some corruption.

I am running out of ideas what is happening here and how I can restart replication correctly. Any ideas?

  • use set global foreign_key_checks=0; and then try restoring and after restoration revert it to set global foreign_key_checks=1; – Nawaz Sohail Nov 1 '16 at 14:39
  • This was tried too, and still the same error message appears. – Tero Kilkanen Nov 26 '16 at 14:15

I see that you have used a multi-master configuration for m1 and m2 and a master-slave config for m1-s1 and m2-s2. Currently, the multi-master configuration does not support write actions on the tables with foreign keys. Thus, when there is a write action on either m1 or m2, the error occurs.

test to debug: set up a master-slave config for m1-s1 and m2-s2 without connecting m1 and m2. If both of them work as expected, then you can confirm that the foreign keys issue is due to the multi-master m1-m2 config.

| improve this answer | |
  • Thanks for the explanation. I cannot test it right now since I am no longer involved with this stuff. The explanation sounds solid though. – Tero Kilkanen Feb 24 '19 at 1:03

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.