There are several backup methods depending on the database status during data backup.

There are 3 backup methods: 1. Cold backup, also called offline backup, refers to a complete backup of the database when the database is closed and the database cannot be updated, and the data can be restored in a specified manner. 2. Hot backup, also called online backup, is a method of backing up the database in archivelog mode while the database is running. 3. Logical backup refers to using software technology to export data from the database and write it into an output file. The format of the file is generally different from that of the original database, and is just an image of the data content in the original database.

The operating environment of this tutorial: Windows 7 system, Dell G3 computer.

There are three backup methods depending on the database status during data backup: cold backup (offline backup), hot backup (online backup), and logical backup.

Cold backup of database

Cold backup (cold backup), also known as offline backup, refers to shutting down the database and the database cannot A complete backup of the database is carried out under updated conditions, and the data can be restored in a specified manner.

The advantage of this is that it can ensure the integrity of the database, the backup process is simple and the recovery speed is relatively fast, but closing the database will affect the current business. While the server is down, users can no longer access the website. For example, if some e-commerce websites have to shut down their databases for backup during store celebrations, the losses will be immeasurable. Therefore, cold standby is generally used for less important and non-core businesses.

Cold backup is the fastest and safest method.The advantages of cold backup are:

1. It is a very fast backup method (just copy the file)

2. Easily archive (just copy)

3. Easily restore to a certain point in time (just copy the files back)

4. Can be combined with archiving methods to restore the "best state" of the database.

5. Low maintenance, high security.

But cold backup also has the following shortcomings:

1. When used alone, it can only provide recovery to "a certain point in time".

2. During the backup process, the database must be backed up and cannot do other work. In other words, during the cold backup process, the database must be closed.

3. If the disk space is limited, you can only copy to other external storage devices such as tapes, which will be very slow.

4. It cannot be restored by table or user.

If possible (mainly depends on efficiency), the information should be backed up to disk, then start the database (so that users can work) and copy the backed up information to tape (while copying, the database can also work ).

The files that must be copied in cold backup include:

1, all data files

2, all control files

3 , All online REDO LOG files

４, Init.ora file (optional)

It is worth noting that cold backup must be performed when the database is closed. When the database is open, Performing a database file system backup is ineffective.

Hot backup of database

Hot backup is a method of backing up the database in archivelog mode while the database is running. That is, a hot backup is a backup when the system is in normal operation. So, if you have a cold backup and a hot backup file, you can use these data to restore more information when a problem occurs. Hot backup requires the database to operate in Archivelog() mode and requires a large amount of file space. Once the database is running in the archivelog state, a backup can be made.

The hot backup command file consists of three parts:

1. Data file, table space and table space backup.

(1) Set the table space to the backup state

(2) Back up the data files of the table space

(3) Restore the table space to the normal state

2. Back up archive log files

(1) Temporarily stop the archive process

(2) Log files in the archive rede log target directory

(3) Restart archive Process

(4) Back up archived redo log file

3. The advantages of using the alter database bachup controlfile command to back up the control file hot backup are:

• It can be backed up at the table space or database file level, and the backup time is short.

• The database can still be used during backup.

• Can achieve second-level recovery (recovery to a certain point in time).

• Can restore almost all database entities

• Recovery is fast and in most cases the database is still working.

The shortcomings of hot backup are:

1. Don't make mistakes, otherwise the consequences will be serious

2. If the hot backup is unsuccessful, the results obtained cannot be used for point-in-time recovery

3. Because it is difficult to maintain, you must be very careful and do not allow it to "end in failure."

Logical backup of database

Logical backup refers to using software technology to export data from the database and write it into an output file. The format of the file is generally different from that of the original database, and is just an image of the data content in the original database. Therefore, logical backup files can only be used for logical recovery of the database, that is, data import, but cannot be used for physical recovery based on the original storage characteristics of the database. Logical backup is generally used for incremental backup, that is, to back up data that has changed since the last backup.

Logical backup:The backup is the SQL statements (DDL DML DCL) executed by operations such as table creation, database creation, insertion, etc. It is suitable for small and medium-sized databases, and the efficiency is relatively low.

**Essence: **The exported SQL statement file

**Advantages:**No matter what storage engine, you can use mysqldump to prepare SQL statements

**Disadvantages:** The speed is slow, format incompatibility may occur during import, and incremental backup and cumulative incremental backup cannot be performed.

Provides three levels of backup, table level, library level and full library level

Note:

• The data is consistent , Service available: How to ensure data consistency, locking the table during backup will automatically lock the table. Back up after locking.

本身为客户端工具: 远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql

Common backup options

• -A, --all-databases

Back up all libraries

Example:mysqldump -uroot -p'QianFeng@123' -A > /opt/a.sql

Analysis: - There is no need to specify anything after A, just back up the entire database

• -B, –databases bbs test mysql

Back up multiple databases

Example:mysqldump -uroot -p'QianFeng@123' -B db1 db2 db3 > /opt/a.sql

Analysis: -B can be followed by multiple databases , separated by spaces

Export the specified table

Example:mysqldump -uroot -p'QianFeng@123' db1 table1 table2 > /opt/a.sql

Analysis: If -B is not added, the default is the library name, table name, and table name. The subsequent tables must be in the previous library. The library name is unique, but the table name is not.

• –no-data, -d

Do not export any data, only export the database table structure.

Example:mysqldump -uroot -p'QianFeng@123' db1 -d > /opt/a.sql

Analysis: -d needs to be specified in front of us What data should be backed up? -d means to only export the table structure.

For more related knowledge, please visit theFAQcolumn!

The above is the detailed content of There are several backup methods depending on the database status during data backup.. For more information, please follow other related articles on the PHP Chinese website!