The best way to count MySQL backup is to select the applicable

What do we need to back up?

In general, the data that we need to back up is divided into the following

• Data

• Binary log, InnoDB transaction log

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

• Server configuration file

Backup tools

Here we list several commonly used backup tools
mysqldump: Logical Backup tool for all storage engines, support Win Bei, full backup, partial backup, hot standby for InnoDB storage Engine
cp, tar 等归档复制工具: Physical backup tool for all storage engines, cold, full backup, partial backup
lvm2 snapshot: Almost hot standby with file System management tools for backup
mysqlhotcopy: A misnomer tool, almost cold, supports only MyISAM storage Engine
xtrabackup: A very powerful innodb/xtradb hot standby tool that supports full backup, incremental backup, percona provided by

Design the right backup strategy

For different scenarios, we should make a different backup strategy to backup the database, in general, the backup strategy is generally the following three kinds of

• Directly Cp,tar copy database files

• mysqldump+ Copy Bin LOGS

• LVM2 snapshot + Copy bin LOGS

• Xtrabackup

Several of the above solutions for different scenarios

1. If the amount of data is small, you can copy the database file directly using the first method

2. If the amount of data is OK, you can use the second method to make a full backup of the database using Mysqldump, and then periodically back up the binary log to achieve an incremental backup effect

3. If the amount of data is general, and does not unduly affect the operation of the business, you can use a third way, using lvm2 a snapshot of the data file backup, and then regularly back up the binary log to achieve incremental backup effect

4. If the amount of data is large and does not unduly affect the operation of the business, you can use the fourth way to use xtrabackup regular xtrabackup incremental or differential backups after using a full backup

The backup of the database is an extremely important thing. If you do not have a backup, you will be crazy if you encounter the following conditions:

UPDATE or DELETE whitout where ...

Table was DROPPed accidentally ...

INNODB was corrupt ...

Entire datacenter loses power ...

From the data security point of view, the server disk will do Raid,mysql itself also has master-slave, DRBD and other disaster-tolerant mechanism, but they can not completely replace the backup. Disaster tolerance and high availability help us to cope with physical, hardware, and mechanical failures, but we can do nothing about the logic mistakes we make. Each logic error occurs at a very low probability, but when multiple possibilities overlap, small probability events amplify into large security risks, when the need for backup is highlighted. So, in the many ways of MySQL backup, which one is suitable for us?

Common ways to back up

MySQL itself provides us with mysqldump, Mysqlbinlog Remote Backup tool, Percona also provides us with a powerful xtrabackup, plus open source Mydumper, and based on master-slave synchronization of delayed backup, from the library cold, etc. and file system snapshot based backup, in fact, the choice has been more dazzling. While the backup itself is to restore, so that we can quickly and accurately recover after the failure of the backup mode, is the most suitable for us, of course, at the same time can save money, convenient, it is perfect. Here are a few of the backup tools I understand to compare and discuss their respective scenarios.

I. Mysqldump & Mydumper

Mysqldump is the simplest logical way to backup. When backing up the MyISAM table, if you want to get consistent data, you need to lock the table, simple and rude. While backing up the InnoDB table, add the –master-data=1–single-transaction option, record the Binlog Pos point at the beginning of the transaction, and then use MVCC to get consistent data, because it is a long transaction, On databases with large volumes of writes and updates, very much undo will be generated, significantly impacting performance, so use caution.

• Advantages: Simple, can be used for single-table backup, in the full amount of export table structure is particularly useful.

• Cons: Simple rough, single threaded, slow backup and slow recovery, cross-IDC may encounter time zone problems.

Mydumper is an enhanced version of mysqldump. Compared to mysqldump:

• Built-in support for compression saves up to 2-4 times more storage space.

• Supports parallel backup and recovery, so it is much faster than mysqldump, but because it is a logical backup, it is still not very fast.

Two. File system-based snapshots

A file system-based snapshot, which is a physical backup. There are some complex settings that need to be made before the backup, take a snapshot at the beginning of the backup and record the Binlog Pos point, and then dump the snapshot in a similar copy-on-write manner. The dump snapshot itself consumes a certain amount of IO resources, and in the case of a large write pressure, the previous impression of saving the changed chunks consumes Io, resulting in a decrease in overall performance. And the server also has to reserve more disk space for copy-on-write snapshots, which itself is a waste of resources. So we don't use a lot of this backup method.

Three. Xtrabackup

This is perhaps the most extensive way to backup. Percona is a household name, Xtrabackup should work. It is actually a combination of physical backup + logical backup. When backing up the InnoDB table, it copies IBD files and constantly monitors redo log changes, append to its own transaction log files. In the process of copying IBD files, the IBD file itself may be written "flower", this is not a problem, because in the first prepare phase after the copy is completed, Xtrabackup a method similar to the InnoDB crash recovery, the data file to the same state as the log file, and rollback of uncommitted transactions. If you need to back up files such as the MyISAM table and the INNODB table structure at the same time, you need to use flush tables with lock to obtain a global lock, start copying those files that no longer change, get the Binlog location, release the lock after the copy ends, and stop the redo Log for monitoring.

It works as follows:

Because MySQL inevitably contains the MyISAM table, while Innobackup does not back up the table structure and other files, so want to complete backup MySQL instance, it is necessary to execute the flush tables with read lock, and this statement will be any query ( Includes select) blocking, which in turn blocks any query (including select) during blocking. If you happen to have a long query on the backup instance before flush tables with read lock execution, the database will hang. And when flush tables with read lock gets a global lock, although the query can be executed but still blocks the update, we want the flush tables with read lock to last from the start to the end, the shorter the longer the better.

To solve this problem, there are two more effective methods:

1. Try not to myisam the table.

2. Xtrabackup added the –rsync option to reduce the time to hold a global lock through two rsync.

The optimized backup process is as follows:

• Advantages: On-line hot standby, full-ready + add-on + flow-ready, support speed limit, support compression, support encryption.

• Cons: Need to get global lock, if you encounter long query, wait time will be not controllable, so do a good job of monitoring, kill long queries or commit suicide if necessary, if you encounter large instances, the backup process is long, redo log too can affect the recovery speed, in which case it is best to use a deferred backup.

Four. Mysqlbinlog 5.6

All of the above backup methods can only restore the database to a point in time of backup: Mysqldump and Mydumper, and snapshot is the point of time when the backup started; Xtrabackup is the point in time at which the backup ends. You must also back up Binlog if you want to achieve a restore of point in time. At the same time, Binlog is also a valuable resource for achieving replenishment.

Fortunately, MySQL 5.6 gives us the option of remote backup Binlog:

Mysqlbinlog--raw--read-from-remote-server--stop-never

It will disguise itself as MySQL from the library, get binlog from the remote and then dump it. This is useful for scenarios where the on-line main library capacity is not enough to hold more binlog. But it's not like real MySQL from a library instance, state monitoring and synchronization need to be deployed separately. Therefore, it is a better choice to use Blackhole to back up the full amount of binlog. The author has implemented a self-built blackhole from the library of tools, a little modification, you can build a perfect blackhole from the library. Once synchronized, basically once and for all, rarely problems, master and slave switching when the line is cut.

Tips:

• Do not underestimate the Binlog backup. When 5.6 of multi-threaded replication is used massively, the time taken to catch the main library command point from the library is greatly shortened, so we change the daily full-time backup to every 3 days, even weekly full-time backups, and continuous binlog incremental backups. Replay 3 or 5 days of Binlog is also extremely fast when data recovery is required for a fault. The most immediate benefit of reducing backup frequency is to save money and hassle.

blackhole is excellent for backup binlog. On the one hand can be long-term backup binlog for the recovery of the database, on the other hand, the configuration of semi-synchronous replication, can effectively prevent the binlog loss of the main library.

Summarize

Backup methods are not the same, but for us, in the face of thousands of instances, choose the right backup tools to achieve unified configuration, unified planning, the construction of intelligent scheduling backup cloud platform is the king. After all, there is no way to ignore the cost of operations that coexist in multiple backup ways.

From the experience point of view, it is a better choice to use Xtrabackup to fully prepare the data, add Binlog with Blackhole, and verify the validity of the backup data regularly.

The best way to count MySQL backup is to select the applicable