MYSQ Data Backup

MySQL Backup overview

Question: What is the difference between backup and redundancy?

• Backup: The ability to prevent data loss due to mechanical failures and human operations, such as storing database files elsewhere.
• Redundancy: data is redundant, but not equal to backup, only to prevent the loss of data caused by mechanical failure, such as primary and Standby mode, DB cluster.

What is a backup?

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

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

Factors that must be taken into account during the backup process:

• 1. A detailed backup plan (backup frequency, point-in-time, period) must be developed (depending on the current business situation, the time of the backup and the size of the backup data need to be considered.) If the amount of data is too large, use redundant disaster).
• 2, backup data should be placed in a non-database local, and recommended that there are multiple copies
• 3, must do the data recovery drill (every time, the backup data in the test environment for the simulation recovery, to ensure that when the data disaster, the timely recovery of data. ) (Ensure data availability) after the backup is complete, perform a power-down simulation walkthrough. Test whether the database can start normally and the data can be recovered normally.
• 4. Choose the right Backup tool according to the situation and characteristics of the data application.
• 5, the consistency of data.
• 6, the availability of data.

Backup Type logical Backup

A logical backup is one that is backed up in a non-stop business situation.

The backup is the SQL statement (DDL DML DCL) executed by operations such as table, build, insert and so on, which is relatively inefficient for small and medium sized databases.

(generally performed on the premise that the database is normally serviced), such as: Mysqldump, Mydumper, into outfile (export import of tables), etc.

Physical backup

Direct copy of database files for large database environments, not limited by the storage engine, but cannot be restored to a different MySQL version.

(usually in the database completely shut down or do not complete the normal provision of services under the premise of the backup), such as: TAR, CP, Xtrabackup, LVM snapshot, etc.

Online Hot Spare

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

M-s

M-sn

M-s1-s2

M-m-sn

Backup tool in the Community Edition installation package mysqldump

• Both enterprise and Community editions contain
• Essentially use SQL statements to describe databases and data and export
• In the MyISAM engine lockout watch, the InnoDB engine is locked.
• Not recommended when data volumes are large

Mysqlhotcopy

• Both enterprise and Community editions contain
• Perl writes a script that essentially uses a lock table statement to copy the data
• Supports only MyISAM data engine

Backup tool mysqlbackup in the Enterprise Edition installation package

• Online backup
• Incremental backup
• Partial backup
• A backup of a consistent state at a specific time

Third-party backup tools Xtrabackup and Innobackupex

Xtrabackup is a innodb to do data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative

Xtrabackup has two main tools: Xtrabackup, Innobackupex

• Xtrabackup can only back up InnoDB and xtradb two data tables, cannot back up tables of the MyISAM type, and cannot back up the data table structure.
• Innobackupex is a Perl script that encapsulates xtrabackup, so you can back up the storage engine that handles InnoDB and MyISAM at the same time, but you need to add a read lock when handling MyISAM

Mydumper

Mydumper Multithreaded Backup tool

Https://launchpad.net/mydumper/mydumper-0.9.1.tar.gz 2015-11-06 (Last updated)

Backup method full Backup Incremental backup

Differential backup

Differential incremental backup

Sunday
An incremental level 0 backup backs up all blocks, that has ever been in the this database.

Monday through Saturday
On the Monday through Saturday, a differential incremental level 1 backup backs up all blocks that has 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 the blocks changed since the Mo Nday Level 1 backup, and so forth.

Cumulative Incremental backup

Sunday
An incremental level 0 backup backs up all blocks, that has ever been in the 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 is created on Sunday, and the level 1 is 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

MYSQ Data Backup