database backup usnig mysqldump what are the steps?

MySQL & MariaDB Other MySQL® Questions
1

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!

2

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

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.