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

This article will explain in detail the method of data backup based on MySQ. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Overview of MySQL backup

Question: what's the difference between backup and redundancy?

Backup: can prevent data loss due to mechanical failure and human operation, such as saving database files elsewhere.

Redundancy: data has multiple redundancy, but it is not equal to backup. It can only prevent data loss caused by mechanical failure, such as active / standby mode and database cluster.

What is a backup?

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

Back up the database, log files, and configuration files to back up all the files in the data directory as much as possible.

Factors must be considered during the backup process:

1. You must make a detailed backup plan (backup frequency, time point, cycle) (according to the current business situation, you need to consider the backup time and the size of the backup data. Use redundancy if the amount of data is too large.

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

3. The exercise of data recovery must be done well (every once in a while, the backup data is simulated in the test environment to ensure that the data can be recovered in time in the event of a data disaster. (to ensure the availability of data) A power outage simulation exercise is carried out after the backup is completed. Test whether the database can be started normally and whether the data can be recovered normally.

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

5. Consistency of data.

6. Availability of data.

Backup Typ

Logical backup

Logical backup is the backup in the case of non-stop business.

The backup is the SQL statement (DDL DML DCL) executed by table building, database building, insertion and other operations, which is suitable for small and medium-sized databases, and the efficiency is relatively low.

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

Physical backup

Directly copy database files, suitable for large database environments, not limited by the storage engine, but can not be restored to different MySQL versions.

(generally, backups are carried out on the premise that the database is completely shut down or cannot complete the normal provision of services); such as tar, cp, xtrabackup, lvm snapshot, etc.

Online hot standby

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

Mmurs

M-Sn

M-S1-S2

M-M-Sn

Backup tool

Backup tools in the Community Edition installation package

Mysqldump

Both enterprise and community editions contain

Essentially use SQL statements to describe databases and data and export

Lock tables on MYISAM engine, lock rows on Innodb engine

It is not recommended when the amount of data is large.

Mysqlhotcopy

Both enterprise and community editions contain

A script written by perl that essentially uses a lock table statement to copy data

Only MYISAM data engine is supported

Backup tools in the Enterprise installation package

Mysqlbackup

Online backup

Incremental backup

Partial backup

A backup of a consistent state at a particular time

Third-party backup tool

XtraBackup and innobackupex

Xtrabackup is a data backup tool for InnoDB. It supports online hot backup (backup does not affect data reading and writing). It is a good substitute for commercial backup tool InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

Xtrabackup can only back up two kinds of data tables, InnoDB and XtraDB, and cannot back up tables of type myisam or data table structures.

Innobackupex is a perl script that encapsulates Xtrabackup, so you can back up the storage engine that processes innodb and myisam at the same time, but you need to add a read lock when dealing with myisam

Mydumper

Mydumper multithreaded backup tool

Https://launchpad.net/mydumper/mydumper-0.9.1.tar.gz

Backup method

Full backup

Incremental backup

Differential backup

Differential incremental backup

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.

Cumulative incremental backup

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.

Backup methods that need to be mastered:

Import and export of logical data (into outfile), mysqldump, mysqlhotcopy, xtrabackup and innobackupex

Lvm-snapshot 、 mysqlbackup

On MySQ to achieve data backup methods to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.