I'm new to mysql. So, sorry if you find it an easy/silly question.
I have a quick question since I encountered a strange scenario. I want to make sure does it actually happens or did I do the right thing.
I was taking backup of a table from a prod replica and what I found that after some time it locked production master database table. We started getting 'Too many connections' error on live. I checked mysql documentation for this and found: That you should stop replication before starting the dump from replica to ensure that the dump contains the consistent set of data.
But, One thing I'm unable to get that yeah..from documentation I can conclude it's good practice to stop. But, nowhere it's being mentioned in the way that 'you have to stop the replication otherwise – it can make your master unreachable. Did anyone face the same issue?
Also, one more thing – I couldn't get- dump operation always make schema level lock -not to lock the whole table. So, how could this happen.
Thanks for any advice/suggestion/reason in advance!
Best Answer
Official documentation will rarely list all the things that could happen if advice or practices are not followed as the list would very quickly become far too long, unwieldy, and outdated. Instead it is much easier for the group — or individual — to state the recommended practice and move on, leaving all the different possible situations, conditions, and failures to sites like this one. By sharing the issue you encountered, other people can learn from your experience and hopefully resolve their issue.
With regards to your second question:
I am not quite sure what you’re asking but, when a backup of a table is being made, the table must be locked to ensure consistency. If records are modified or deleted during a backup process, then the backup may be wrong. If it is wrong, then it is unreliable. If it is unreliable then it cannot be trusted. If one backup cannot be trusted then …
You get the picture.
Generally what I will do with MySQL backups is pause replication, wait for any in-progress transactions to complete, lock the entire database, make a snapshot, unlock the database, then resume replication. This ensures the entire backup is accurate to a specific point in time.