Automatic timing backup strategy and recovery tutorial for MySQL database

Note: master- slave synchronization cannot replace database backups

For example, if we mistakenly delete a table on the main database, then the corresponding operation from the database, if there is no backup, this will result in deleted data can not be retrieved. (even rolling back binary logs may not be recovered, because binary logs are saved with time limits, and rolling back binary logs are complex).

Therefore, a full backup of the database, incremental backup is critical.






Backup strategy

1. Daily database backup.

mysqldump--master-data--single-transaction-r--databases [DB1] [DB2] [DB3] | Gzip-9-| PV >all-db-with-master-data-$ (Date +%y%m%d). sql.gz

2. Synchronize the daily backup to the server so that it can be built from the database at any time.

Recovery of the database


1. Restore the primary database.
Comment out the database information from the Master-data production, and then restore it.

2. Recover from database.
Use the backed-up data file, recover from the database, and then start the slave.

Start slave;

Cloud Backup

Regular backup of the database to the Baidu cloud or other cloud disk, to ensure that the data storage, in case the server crashes.

PS: Baidu Cloud Synchronization script can be found on the internet, or upload the data to Mount Baidu network disk Windows Server, so that Baidu network disk can be automatically synchronized.