0

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.

https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-solutions-backups-mysqldump.html

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!

3
  • Could you take the dump from the Replica instead of the Primary? And stop replication while you do it?
    – Rick James
    Jun 9, 2021 at 23:23
  • I took dump from replica only. Only the thing was that I didn't stop replication
    – Shiwangini
    Jun 13, 2021 at 13:07
  • There is no way (to my knowledge) that anything on the Replica can impact the Primary. I suggest that something else caused "too many connections" on the Primary.
    – Rick James
    Jun 13, 2021 at 17:15

1 Answer 1

0

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 couldn't get dump operation always make schema level lock -not to lock the whole table. So, how could this happen?

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.

1
  • ok. what I wanted to know if anybody else encountered the same problem ..only because of the table dump..but thanks for clarification
    – Shiwangini
    May 2, 2021 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.