List:Backup« Previous Message
From:Christian Heinze Date:September 11 2017 11:53am
Subject:mysqldump and missing table content
View as plain text  
hm, nothing in past 2 years... i hope the ml is not dead?
(the dump from 2015 looks ok to me, it's just simple, no locking, so when the table has
been modified during the dump, that might lead to results looking wrong)

i have a similar problem, just maybe a bit more sophisticated:


i have several rather large mysqld instances running (each around maybe 50-250gb, lots of
databases, lots of tables).
to set up (and/or resync) a slave from scratch, i use (on the slave, with $srchost being
the master):

echo "update mysql.user set alter_priv = 'N'; flush privileges;" | mysql -h $srchost -p
( echo "SET @@session.foreign_key_checks = 0;"; mysqldump -h $srchost -p
--single-transaction --master-data --routines --databases `echo "show databases;" | mysql
-h $srchost -p | grep "^dbsiwant.*$"` ) | mysql -p

there is just one user with super_priv, and it is guaranteed not to do any ALTER while the
dump is in progress.
foreign_key_checks is also used as our semaphore for disabling triggers (so they don't
fire on the slave when the dump is restored to it).
when the dump is started, the CHANGE MASTER data on the slave is already set up (slave is
just stopped), so that the master-data from the dump is applied and not thrown away.

this first seems to work nicely, when it's finished all that's left is to START SLAVE;

first the slave looks fine, it starts reading and applying the bin log from the master,
but soon runs into some inconsistency error (missing row) and the slave stops replication.
turns out, the dump seems to always have all the INSERTs of at least one table missing
(completely). the drop is there, the create is there. no data.
i've found an interesting article by a percona guy
(https://www.percona.com/blog/2012/03/23/best-kept-mysqldump-secret/), explaining that an
ALTER on the master might lead to a (wrongly) empty table in the dump, that's exactly what
i'm experiencing, but making sure there is no ALTER during the dump didn't help.

with very 'small' dbs (maybe around 30g) i actually experienced single runs where all
seemed to have worked fine. also, which table is missing its data from the dump seems to
be random. i guess this is a problem that only happens on quite big dbs (or has a
probability which increases with db size).

i have seen spurious reports of this problem, some of them quite old, but no solution or
explanation (except the ALTER article, but that's obviously at least not the only thing
leading to this problem).

to me all this looks quite severe. apart from this being the straightforward safe way to
add a slave from scratch to a master (online, in production), it's at the same time the
backup procedure.

is this known?
is there a fix for this?
in case there isn't, does this problem exist in mariadb, too?
Thread
mysqldump and missing table contentChristian Heinze11 Sep