Shulou(Shulou.com)06/01 Report--

This article mainly introduces what types of data backup in the database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to know about it.

Data backup can be divided into hot backup, cold backup and warm backup. Hot backup can be backed up directly while the database is running, without any impact on the running database operation. The cold backup must be backed up when the database is stopped, and the read and write operations of the database cannot be performed. Warm backup is carried out while the database is running, and only read operations, not write operations, are supported during backup.

Backup is a necessary means just in case, in the event of hardware damage or non-human factors leading to data loss, you can use backup to restore data to minimize the loss, so backup is necessary. Backups can be divided into the following types.

According to the method of backup (whether the database is offline or not), backups can be divided into:

Hot standby (Hot Backup)

Cold standby (Cold Backup)

Warm standby (Warm Backup)

Hot backup can be directly backed up in the operation of the database, without any impact on the running database operations, and the read and write operations of the database can be performed normally. This method is called Online Backup (online backup) in the official MySQL manual.

The cold backup must be backed up when the database is stopped, and the read and write operations of the database cannot be performed. This kind of backup is the simplest, generally only need to copy the relevant database physical files. This method is called Offline Backup (offline backup) in the official MySQL manual.

Warm backup is also carried out in the operation of the database, but it will have an impact on the operation of the current database. Only read operations, not write operations, are supported during backup.

According to the contents of the file after backup, hot backup can be divided into:

Logical backup

Bare file backup

In MySQL databases, logical backup means that the contents of the backed up files are readable, usually text contents. The content is usually made up of SQL statements or the actual data in the table. Such as mysqldump and SELECT * INTO OUTFILE. The advantage of this kind of method is that you can observe the contents of the exported file, which is generally suitable for database upgrade, migration and so on. But its disadvantage is that it takes a long time to recover.

Bare file backup refers to copying the physical files of the database, not only when the database is running (such as ibbackup, xtrabackup), but also directly when the database stops running. The recovery time for this type of backup is often much shorter than that for logical backups.

According to the contents of the backup database, the backup can be divided into:

Full backup

Partial backup

Full backup refers to a complete backup of the database, that is, backing up the entire database, which will take up more time and space if there is more data.

Partial backup refers to backing up part of the database (for example, backing up only one table).

Some backups are divided into:

Incremental backup

Differential backup

Incremental backup requires the use of professional backup tools. Refers to the backup of changed data on the basis of the last full backup. In other words, each backup only backs up the data generated from the last backup to the backup time. Therefore, each backup saves more space than differential backup, but it is troublesome to restore data.

Differential backups refer to data that has changed since the last full backup. Compared with incremental backup, it is a waste of space, but it is easier to recover data than incremental backup.

Different backup methods in MySQL also need to consider whether the storage engine supports it. For example, MyISAM does not support hot backup, warm backup and cold backup. InnoDB supports hot backup, warm backup and cold backup.

In general, the data we need to back up can be divided into the following categories:

Table data

Binary log, InnoDB transaction log

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

Server profile

Thank you for reading this article carefully. I hope the article "what are the types of data backup in the database" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.