Detailed explanation of how MySQ implements data backup

This article will introduce to you what data backup is in MySQ and let you understand the method of data backup in MySQ. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

MySQL Backup Overview

Question: What is the difference between backup and redundancy?

• Backup: It can prevent data loss due to mechanical failure and human operations, such as saving database files in other places.

• Redundancy: There are multiple redundant copies of data, but it is not equivalent to backup. It can only prevent data loss caused by mechanical failure, such as master-standby mode, database cluster.

What is backup?

databases Binlog my.cnf /data/xxx (data directory)

Back up the database, log files, and configuration files. Try to save all the files in the data directory Backup.

Factors that must be considered during the backup process:

• 1. A detailed backup plan (backup frequency, time point, cycle) must be developed (according to Based on the current business situation, you need to consider the backup time and the size of the backup data. If the amount of data is too large, use disaster redundancy).

• 2. Backup data should be stored locally in a non-database location, and multiple copies are recommended

• 3. Data recovery drills must be done (Every once in a while, the backed-up data is simulated and restored in the test environment to ensure that the data can be restored in time when a data disaster occurs.) (To ensure the availability of data) After the backup is completed, conduct a power outage simulation drill. Test whether the database can be started normally and whether the data can be restored normally.

• 4. Choose the correct backup tool according to the occasion and characteristics of data application.

• 5. Data consistency.

• 6. Data availability.

Backup type

##Logical backup

Logical backup is to perform backup without stopping business.

What is backed up is the SQL statements (DDL DML DCL) executed by operations such as table creation, database creation, and insertion. It is suitable for small and medium-sized databases and has relatively low efficiency.

(Generally performed under the premise that the database provides normal services); such as: mysqldump, mydumper, into outfile (table export and import), etc.

Physical backup

Directly copy database files, suitable for large database environments, not restricted by storage engines, but cannot be restored to different MySQL versions.

(Generally, backup is performed when the database is completely shut down or cannot complete normal service provision); such as: tar, cp, xtrabackup, lvm snapshot, etc.

Online hot backup

Online hot backup: AB replication (online real-time) (referring to data redundancy)

M-S

M-Sn

M-S1-S2

M-M-Sn

Backup Tool

##Community Edition Installation Package The backup tool in

mysqldump

## is included in both Enterprise and Community editions

• Essentially use SQL statements to describe the database and data and export

• Lock the table in the MYISAM engine and lock the rows in the Innodb engine

• Not recommended when the amount of data is large
mysqlhotcopy

Both the Enterprise Edition and Community Edition include

• A script written in perl, which essentially uses a lock table statement to copy data

• Only supports the MYISAM data engine

Backup tool in the enterprise version installation package

mysqlbackup

Online Backup

• Incremental backup

• Partial backup

• Consistency at a specific time Status backup

Third-party backup tools

##XtraBackup and innobackupex

Xtrabackup is a tool for data backup of InnoDB. It supports online hot backup (backup does not affect data reading and writing). It is a good substitute for the commercial backup tool InnoDB Hotbackup. Xtrabackup has two The main tools: xtrabackup, innobackupex

xtrabackup can only back up InnoDB and XtraDB data tables. It cannot back up myisam type tables, nor can it back up the data table structure.

• innobackupex is a perl script that encapsulates Xtrabackup, so it can back up the storage engines of innodb and myisam at the same time, but you need to add a read lock when processing myisam

• mydumper

mydumper multi-threaded backup toolhttps://launchpad.net/mydumper/mydumper-0.9.1.tar.gz

Backup method

完全备份

增量备份

 

差异备份

差异增量备份

 

 • Sunday
An incremental level 0 backup backs up all blocks that have ever been in use in this database.

• Monday through Saturday
On each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. The Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

累计增量备份

• Sunday
An incremental level 0 backup backs up all blocks that have ever been in use in this database.

• Monday - Saturday
A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.

需要掌握的备份方法：

逻辑数据的导入导出（into outfile）、mysqldump、mysqlhotcopy、xtrabackup和innobackupex
lvm-snapshot、mysqlbackup

总结：以上就是本篇文的全部内容，希望能对大家的学习有所帮助。更多相关教程请访问mysql数据库图文教程 ，MySQL视频教程，bootstrap视频教程！

The above is the detailed content of Detailed explanation of how MySQ implements data backup. For more information, please follow other related articles on the PHP Chinese website!