MySQL Forums
Forum List  »  Backup

Understanding mysqldump in a bit more detail
Posted by: Brian Kinney
Date: January 30, 2020 04:45PM

I need to restore a slave server, as it got horrendously out of sync with its master. We're talking over a month out of sync, and just restarting the slave won't help, and now the task fell on me, the MySQL newbie. While I've seen a few documents on Google as to how to recover, things haven't gone right, so I need a better understanding on what steps the restore process goes through. I know it could be literally read through as SQL statements, but this dump is over 4GB, so not a tiny file to go reading....

My backup request is:
mysqldump -u dba -p --all-databases --master-data > fulldump.sql

My restore request is:
mysql -u dba -p < fulldump.sql

And yes, I have "skip-start-slave" set (and STOP SLAVE) so that we don't go processing while we're still trying to restore the rest of the databases. Not worrying about syntax quite yet, as I am needing clarity on the existing state of the slave.

My basic question is regarding when a "restore" like this is done, are the existing DBs on the slave "deleted" and the incoming ones "created", or is it doing some other sleight of hand?

I am asking as I've run into multiple failures when restoring - like losing connection to the slave server, and other people stepping in to try to help, undoubtedly leaving debris in its wake. I don't want to lose the my.cnf settings and start over, as this is *ancient* v5.1.x software, so highly knowledgeable help is uncommon to rare now.

If the restore wipes the slate clean each time, when using a dump made in this fashion, fantastic! If not, I need to know how to safely clean the DB slate and restore.

Many thanks to those who respond!

Options: ReplyQuote

Written By
Understanding mysqldump in a bit more detail
January 30, 2020 04:45PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.