0

I cannot get mysqldump to generate a dump that will properly restore. The dump is being generated to setup a new slave, but whenever the slave starts up, it gets foreign key constraint failures.

I'm generating the dump with slave positions and as a single transaction so that the dump is (allegedly) atomic, but when I essentially execute the following:

mysqldump \
    -h master \
    --flush-logs --single-transaction --master-data \
    --apply-slave-statements \
    --databases my-db \
    | mysql -h slave

I get the following error when I "SHOW SLAVE STATUS":

Last_Errno: 1452
Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails ...

All of the tables use the InnoDB engine, and the foreign key is an integer.

Using MariaDB 10.3.

1

The --apply-slave-statements parameter doesn't make sense here, as it is used when you dump from a slave, not from a master.

And this one most likely causes your problems: --databases my-db

My guess is, that you expect, that MySQL/MariaDB only replicates the database that you've dumped and restored on the slave. But without filtering MySQL/MariaDB replicates everything from the master to the slave. To filter you can use the following manual page: https://mariadb.com/kb/en/library/replication-filters/

If that is not your problem, then the first thing I would check is, if someone has disabled foreign key checking on your master with select @@global.foreign_key_checks; If this returns 0 it's bad. Then you can use my stored procedure from this answer to check for data inconsistencies.

| improve this answer | |

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.