【MySQL 07】Database recovery technology

1. Four types of failures

Transaction failure, system failure, media failure, computer virus

1. Transaction failure: Transaction internal failures can be divided into expected and unexpected, among which Most failures are unexpected. Expected internal transaction failures refer to internal transaction failures that can be discovered through the transaction program itself; unexpected internal transaction failures cannot be handled by the transaction program, such as operation overflow failures, concurrent transaction deadlock failures, and violations of certain integrity restrictions. resulting in malfunctions, etc.

2. System failure: System failure, also called soft failure, refers to the system stopping due to hardware failure, database software and operating system vulnerabilities, or sudden power outages during the operation of the database. A type of failure in which a running transaction terminates abnormally and requires a system restart. This type of transaction does not destroy the database, but affects all running transactions.

3. Media failure: Media failure is also called a hard failure. It mainly refers to the fact that during the operation of the database, due to head collision, disk damage, strong magnetic interference, natural disasters, man-made disasters, etc., the data in the database may be partially damaged or damaged. A type of failure in which all is lost.

4. Computer virus failure: Computer virus failure is a malicious computer program that can reproduce and spread like a virus. While causing damage to the computer system, it may also cause damage to the database system (destruction method Mainly database files).

2. Solution

1. Expected internal transaction failure: roll back the transaction and undo modifications to the database.
2. Unexpected internal transaction failure: Force rollback of the transaction, and use the log file to undo its modifications to the database while ensuring that the transaction has no impact on other transactions.
3. System failure: After the computer is restarted, unfinished transactions may be written to the database, and the results written by all unfinished transactions will be rolled back; part or all of the completed transactions may be left in the buffer. As a result, all committed transactions need to be redone (that is, all uncommitted transactions are revoked and all committed transactions are redone).
4. Software fault tolerance for media failure: Use database backup and transaction log files, and use recovery technology to restore the database to the state at the end of the backup.
5. Hardware fault tolerance for media failure: Use dual physical storage devices so that the two hard disks store the same content. When one of the hard disks fails, the other backup hard disk is used in a timely manner.
6. Computer virus failure: Use firewall software to prevent virus intrusion. For virus-infected database files, use anti-virus software to scan and kill them. If the anti-virus software fails to kill the virus, you can only use the database backup file at this time to ensure software fault tolerance. Method to restore database files.

3. Data recovery implementation technology

The basic principle of database recovery is redundancy, that is, using redundant data stored elsewhere in the system to reconstruct the damaged or incorrect database in the database that part of the data.

There are two main methods for creating redundant data: data dump (Backup) and registration log (Logging).

1. Data dump

The DBA regularly copies the entire database or part of the data in the database to other disks and saves them. The file is called a fallback copy or fallback copy.

Static dump: A dump operation performed when there are no transactions running in the system. No access or modification activities to the database are allowed during the dump period.

Dynamic dump: Access or modification of the database is allowed during the dump, that is, the dump operation is executed concurrently with the user transaction.

Mass dump: Dump all databases each time.

Incremental dump: Only the data updated after the last dump is dumped each time.

2. Registration log

Registration log is the system automatically recording transaction update operations on the database. The log is usually recorded in stable storage.

Log files in record units:
Transaction identification (indicate which transaction it is), operation type (insert, delete or modify), operation object (record internal identification), old value of data before update , the new value of the updated data

Log file in units of data blocks:
Transaction identification, updated data block