I have a MySQL instance (5.7) which is just a single instance. It is not setup to have slaves/replicate. I've updated my stack to have slaves. In the process of moving to this newer stack, I needed to export the existing MySQL instance and import the data.

During the export via mysqldump I discovered that all recent data was not written to the dump. I discovered this because prior to doing to the dump I had happened to be on the phone with a user that was changing some data. So I knew what he had changed (he changed his profile name). What I found with the dump was that his user account was in the dump, but his old name was listed.

I had tried a few other mysqldump permutations by removing some flags and came across a set of params that "worked". But I discovered that my original command did actually work. It seems that there was some latency of time before MySQL could dump out all the contents. Once I past either some time threshold or even perhaps some invocation of mysqldump, the data being dumped is now up to date. For reference, I essentially removed permutations of --compress and --hex-blob to see if they would have an effect.

My process to dump was essentially:

  • Disable the ALB directing to the server so I knew there would no longer be traffic.
  • Waited a few minutes
  • Logged into the instance and then immediately ran:

/usr/local/mysql/bin/mysqldump --host HOST_IP --port 3306 -u root -p --databases my_db --add-drop-database --complete-insert --extended-insert --compress --hex-blob --events --single-transaction > dump.sql

Note that I did not stop the MySQL instance or issue any other commands prior to this.

Needless to say, this was a little disheartening finding that a dump could result in some data missing.

Note that the newer system takes dumps from the slave. Prior to doing the dump, the slave is stopped. In addition I also use the --master-data=2 as well as --set-gtid-purged=ON in that particular case.

My question is whether or not there can be a latency between the last update to tables and what mysqldump will dump. I'm trying to understand why this happened so I can prevent it in the future.

Your Answer

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

Browse other questions tagged or ask your own question.