MySQL Backup Strategy.

Back up Strategies

If you are admin taking the backup is really an important task in light of MySQL you should do the following

1. Take the Backup regularly(weekly,regularly as you wish).Store the Back-up in some safe place.

2. Keep the binary logging enabled so that changes can be recorded in it.

3. While taking the backup flush the logs so that it generates the new binary log which record the changes after the backup is made.The Syntax is as

mysqldump -uuser -psecret --flush-logs --lock-all-tables --all-databases > dump.sql

use the other options with mysqldump if you need

--flush-logs : will generate new binary log before which the all dump will be generated.

--lock-all-tables : will lock all tables upto the time till the dump is being generated.

4. Keep binary logs in some safe place so in case of some failure you can use the most recent backup's and the binary logs generated after that to reach to the actual level of the database.