Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email C[email protected] for any comments or concerns.

database backup usnig mysqldump what are the steps?

iberkneriberkner ContributorInactive User Role Beginner
I'd like to backup all of our databases in one large mysqldump file. Our databases are approaching 10GB in size so this is probably a few hours of processing. I'd like to run this against a slave which is a read only.

What would be the best steps to do this? For example:

1. Stop slave

2. Restart database in read only mode

3. Run mysqldump command, what are the exact / best parameters to backup everything? all databases, users, permissions, etc?

4. After the backup is done, I'll restart the database in normal mode and restart the slave and let it catch up.

After I have the backup file, I'd like to test a restore against an empty database, what would be the mysqldump command?

Is there a faster / better way?

Thanks!

Comments

  • sterin71sterin71 Advisor Inactive User Role Beginner
    You don't have to set the slave in read only mode or anything like that.

    The only thing you need to do is run the mysqldump command something like this:

    mysqldump --all-databases > all_databases.sql</pre>

    It will lock all databases during backup and will give you a backup of them in the file all_databases.sql.

    When the backup is complete the lock is automatically lifted and the slave will start to catch up again.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.