If the database is already installed on RAID01 or RAID10, then does our database need to be backed up: required because our raid is only meant to ensure hardware corruption without disrupting the business. But the software fails or artificially deletes the database. RAID is not able to restore the data. Because you deleted it, if it was on a raid, it would delete two copies of the data. So database backup and RAID are two different levels of concept. How does MySQL backup work and what data should be backed up? Backup type: Our database is very busy, with read and write operations every second. If you execute a copy command, then you can only copy out the data at that time period, and then write the data there is no way to back up, so the data copied out of the incomplete corrupted. So we can only stop the database service, then flush the in-memory data to the hard disk and copy the database. However, our database services generally have no way to stop. Therefore, the backup is divided into, can disable the database server, the backup type is divided into: 1. Hot backup: Online backup, the database does not need to stop 2. Warm backup: You can only read from the database 3. Cold backup: Completely stop the database service and then back up the database directly back up, or Back up, the backup of this situation can be divided into: physical backup: Direct Copy database file logical backup: Export data to a text file based on whether we backed up the entire content of the data, or part of the content, Backup is divided into: Full backup: Back up all data differential backups: Just back up the data that changed since the last full backup (consumes more hard disk space than an incremental backup)Incremental backup: Backs up data only after the last full incremental backupso a database backup strategy is generally a full backup + incremental backup, or a full backup + differential backupBackup data is for restore, how to know if the data we backed up can be restored. A: You need to test and perform a restore test.data restore requires frequent rehearsal to ensure full, timely recovery when a data restore is required, good backup and restore strategies, and scenarios for restoring data in an emergency situationWhat backup should back up: Backup Data + profile + log (binary log, transaction log) If we make an incremental backup Monday Tuesday has been backed up and Wednesday has not been able to back up. Database hangs, we need to use binary log for Instant point restore, restore from binary log Wednesday the data before the database hangs should be a hot or warm backup, or a cold backup is typically a hot backup. However, the hot backup is an online backup, the database is still read and write when the backupHot backup is more complex, to ensure that the backup data is available, the Mysam storage engine can not be hot-backed backup only with a snapshot, otherwise in only warm backup, InnoDB can be hot backup. Hot Backup tool: Xtrabackupmysql provides a logical backup tool mysqldump can also perform a hot backup. Offline Backup is a reliable, online service database if you need to stop for backup, you can use some technical means, such as to provide our database from the server, need to backup time to stop from the server, and then back up, then boot, After starting, the server automatically synchronizes the data from the primary server until the service is stopped. So it needs to be implemented with MySQL master-slave architecture. Should I make a physical or logical backup? Physical backup directly copy the data file, if this file is not related to our operating system's file system, our data files are very cross-platform, portability, but some storage engine data files may be related to the operating system's file system, which will lead to the portability is not strong, but the advantage is that the backup speed quickly A logical backup relies on the MySQL process to read our data from the table. and saved as a text file, so this logical backup process requires the MySQL server process to participate in, backup slow, logical backup because it is directly exported to the file is stored in all possible loss of data floating point precision. Advantage, you can use a text editor to process data, portability is also strong, you can also backup policy across MySQL server version: Full + Delta full + difference how long to make a backup: If the amount of data change is not very large, daily change is not particularly frequent, we can do it once a week completely, How long does it take to make a daily increment or difference? Depending on the amount of data that you have in your database and how long you can tolerate the restore, using physical or logical backups depends on your needs
MySQL Backup type
