Database data backup and recovery concepts

This article mainly introduces the related concepts of data backup and recovery

backup : A copy of the stored data;

Raw data: continuous change;

Restore : Apply the Copy to the online system;

Only the data status can be restored to the time of backup operation;

Point-in- time recovery : Using binary logs to achieve time-based recovery;

Why backup ?

Disaster recovery: Hardware failure (redundancy, only when hardware is unavailable, service is available), software failure (bug), natural disaster, hacking, mis-operation (human error operation causes the greatest chance of occurrence) 、...

Test, copy the production data into the test environment, and test it to achieve the same effect as the production environment.

Backup can cause I/O pressure on the system, deciding how to back up the tradeoff

Things to keep in mind when backing up:

Can tolerate the maximum amount of data lost;

How long the recovery data needs to be completed;

What data to restore and what data to back up

To do a recovery walkthrough:

Testing the availability of backups;

Enhance the efficiency of recovery operations;

Categorized by Backup type:

Scope of the backed up datasets:

Full backup and partial backup

Full backup: The entire data set;

Partial backup: Part of a data set, such as a partial table;

Full backup, incremental backup, differential backup:

Full backup: Back up all the data

Incremental backup: Backs up only the data from the variable since the last full or incremental backup;

Differential backup: Backs up only the data from the variable since the last full backup; the variance is another manifestation of the increment. This can lead to more space, but the benefit is that the recovery time will be faster than the incremental backup recovery

Which backup form can be implemented according to requirements

If the database is abnormal, first with all recovery, then incremental recovery, and then the binary file to repeat the corresponding time period of recovery, complete recovery.

Physical backup, logical backup:

Physical Backup: Copy data files for backup, do not need to start the MySQL service, directly copy the relevant files, more efficient.

Logical backup: Exporting data from a database exists in one or more files and is often used when the data volume is small. Backing up with MySQL statements

When the amount of data is large, a physical backup is recommended.

Depending on whether the data service is online:

Hot-Standby: The backup that can be done in the state of read and write operation, the most feasible solution. may result in inconsistent timestamps of data, such as the backup process, where some data is changed at the same time, resulting in a different timestamp of the data. So the hot-standby technology is very complex, but this must be used. So there's a need for technology to do that. MyISAM does not support hot-standby, InnoDB engine supports hot-standby.

Note that InnoDB can do hot spares for two reasons

1.LSN: Log sequence number, each time the log increases, this value will be added 1, will always accumulate. Doing a full-scale backup will cause the LSN to increase to the maximum value.

InnoDB uses a tablespace to store data, and the table space has a black box inside it, which can be understood as dividing the table space into chunks of data, each moment being a separate, self-made unit, logged by the log sequence number (LSN), and if the data block's data is modified, the LSN is incremented, as the first block is modified. Then add 1, such as LSN is 1, at this time the second block of data is modified, plus 1, at this time the LSN is 2, followed by the LSN sequentially. Backups are recoverable based on the serial number of the LSN.

2. Key factors, InnoDB supports the MVCC mechanism to enable snapshots for each transaction. So the data read is the data at the moment the transaction is enabled. Equivalent to the internal snapshot feature, the snapshot provides access to the same data at point-in-time, so InnoDB can support hot standby.

Win Bei: A readable but non-writable backup; it is not possible to write, because many functions cannot be implemented.

Cold: A backup made in a state where read and write operations are not available, the most secure, but not essential. If there is a database master-slave replication, you can stop the master, using the slave, so that the cold standby can be achieved.

Backup needs considerations:

How long is the resource locked?

How long is the backup process?

Server load at the time of backup?

How long is the recovery process?

Backup policy:

Whole amount + difference + binlogs

Total + increment + binlogs

Backup means: physical, logical

It is recommended to use physical hot spare, because the physical backup performance is good, high efficiency, hot standby uninterrupted business.

Implementation method:

Mysqldump+binlog

Lvm2+cp/tar+binlog

Xtrabackup (InnoDB) +binlog

Backup what?

Data

Binary log, innodb transaction log;

Code (stored procedures, stored functions, triggers, event schedulers)

Configuration file for the server

Backup tool:

Mysqldump:mysql Backup tool with the service, logical Backup tool;

Support full backup and partial backup;

InnoDB engine in mysqldump support hot-standby;

MyISAM engine in mysqldump support Win Bei;

Cp/tar: Archive after copy

With the lvm2 snapshot (Request a global lock) function for backup, then immediately release the lock, to achieve almost hot-standby effect (snapshot execution speed, can not be accessed when executing, so called almost hot standby, short interrupt time, but the data recovery difficulties after LVM failure, but the LVM extension is convenient) ; belongs to physical backup;

Note: You cannot back up only data files; You must back up the transaction log at the same time: The data file and transaction log are required to be in the same logical volume;

Xtrabackup: Implementation of Innobackup (Innobackup is charged, but this tool is very useful) the function of another tool, provided by Percona, open source tools, support for InnoDB do hot spare, physical backup tools;

Full backup, partial backup;

Full backup, incremental backup;

Full backup, differential backup;

Mysqlhotcopy: Achieve almost cold, MySQL comes with

Select: Generally used for some rows in a table, with this tool, other cases do not

Backup: SELECT cluase into OUTFILE ' FILENAME ';

Restore: CREATE TABLE

Import: LOAD DATA

Backup recommendations are implemented using either the mysqldump or Xtrabackup tools. Examples of how these two tools can be backed up are described in the example

Xtrabackup: Support Physical backup

Total + difference +binlog, such as every month to do the full amount of daily differences, and then use Binlog to do point-in-time backup

Full Volume + incremental +binlog

Mysqldump: Only support full volume, small quantity, such as dozens of g of data, you can use this tool

Full Volume +binlog

Database data backup and recovery concepts