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
--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.