Question: Does anyone have advice on what I can do to successfully get this new replica set up and replicating from our production server just like the old replica?


I am trying to set up a new MariaDB replica from an existing replica so that we do not have to take the production server down to create the new replica. Here is the procedure I go through to import the DB to the new host.

  1. On the current replica: MariaDB> STOP SLAVE;
  2. On the current replica: $ /usr/bin/mysql -u $USER -p $PASS -e 'SHOW SLAVE STATUS \G' > slave_status.txt
  3. On the new replica: $ /usr/bin/mysqldump --add-drop-database --all-databases -u $USER -p $PASS -h $CURRENT_REPLICA --flush-privileges | /usr/bin/mysql -u root
  4. On the current replica: Make sure that the MariaDB> SHOW SLAVE STATUS \G output is still the same as it was in slave_status.txt
  5. Grab the Relay_Master_Log_File, Master_Host, Master_User, and Exec_Master_Log_Pos values from slave_status.txt
  6. On the new replica: MariaDB> CHANGE MASTER TO MASTER_HOST='$Master_Host', MASTER_USER='$Master_User', MASTER_PASSWORD='$Master_Password', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Exec_Master_Log_Pos;
  7. On the new replica: MariaDB> SHOW SLAVE STATUS \G and compare to slave_status.txt... All looks good
  8. On the new replica: MariaDB> START SLAVE;

Once the replication has been running for a little bit, usually a few minutes, I get error #1451 below

Error 'Cannot delete or update a parent row: a foreign key constraint fails ('$DATABASE'.'$TABLE', CONSTRAINT '$TABLE_ibfk_3' FOREIGN KEY ('$KEY') REFERENCES '$OTHER_TABLE' ('$OTHER_TABLE_KEY') ON UPDATE CASCADE)' on query. Default database: '$DATABASE'. Query: 'DELETE FROM $OTHER_TABLE WHERE $OTHER_TABLE_KEY in (#########, #########, #########, #########, #########, ...

There are multiple databases hosted in this MariaDB instance and every time I have done this process it is the same database that runs into this foreign key dependency error. The current replica has been running for years without a problem. I have tried the same MariaDB version (10.1.12) on the new host as the old host, as well as a newer version (10.4.17) on the new host. The database which results in this error is using InnoDB.

I have verified that /etc/my.cnf and /etc/my.cnf.d/* are the same except for the server ID for the two replicas. One of the first times I ran through this process I copy-pasted the failed command from the error log and it ran without a problem, I then set the Skip_Counter to 1 and it continued for a while and got the same error. I did this 5 times before giving up on that as a method to proceed.

If there is any other useful information I can provide, please let me know. I tried to include everything I could think of but I wouldn't be surprised if I missed something useful.

1 Answer 1


There seems to have been some issue regarding timezones with the replicated database. I tried using mysql_tzinfo_to_sql to set up the timezone in the SQL database but that didn't work. So I moved on to trying docker and setting up the timezone at the image level and now I have the replication fully functioning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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