MySQL database backup policy and recovery

Database Backup is crucial. previous articles also explained how to back up MySQL and how to set up the master-slave synchronization policy, synchronization and backup of MySQL, we often think that master-slave synchronization can also be used as a MySQL backup policy. Therefore, we also emphasize that master-slave synchronization cannot replace database backup.

Database Backup is crucial. previous articles also explained how to back up MySQL and how to set up the master-slave synchronization policy, synchronization and backup of MySQL, we often think that master-slave synchronization can also be used as a MySQL backup policy. Therefore, we also emphasize that master-slave synchronization cannot replace database backup.

Database Backup is crucial. previous articles also explained how to back up MySQL and how to set up the master-slave synchronization policy, synchronization and backup of MySQL, we often think that master-slave synchronization can also be used as a MySQL backup policy. Therefore, we also emphasize one point here:
Master-slave synchronization cannot replace Database Backup

For example, if a table is accidentally deleted from the master database, the slave database also performs corresponding operations. If no backup is available, the deleted data cannot be retrieved. (Even if binary logs are rolled back, they cannot be retrieved because the storage of binary logs is time-limited and it is very complicated to roll back binary logs ).

Therefore, full backup and Incremental backup of databases are crucial.

Backup Policy
1. Back up the database every day.

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 slave server so that you can build the slave database at any time.

Database Restoration
1. Restore the primary database.
Comment out the slave database information produced by master-data and restore it.
2. Restore the slave database.
Use the backup data file, restore the data from the database, and then start slave.

start slave;

Cloud backup
Regularly transmits the backed up database to Baidu cloud or other cloud disks to ensure data storage, in case the server crashes.
PS: Baidu cloud's synchronization script can be found online or uploaded to the windows Server mounted to Baidu's network disk, so that Baidu's Network Disk can be automatically synchronized.

Related links:
Drupal database backup and MySQL backup policy
MySQL InnoDB database backup and Restoration
How to add a new database to the MySQL master-slave copy list
Apply MySQL Master/Slave at the database layer of Drupal

Original article address: MySQL database backup policy and recovery. Thanks to the original author for sharing.