Summary of MySQL database backup methods

A little time today, I summarize the MySQL backup several ways to check!

Data in production environment is undoubtedly the lifeblood of a company, as an operations engineer can not guarantee the security of enterprise data, then is a unqualified engineer. How do we ensure that data is not lost or lost and quickly recovered? The MySQL database backup and recovery method is described below.

MySQL Basic knowledge Summary

1. Why do I need to back up data?

Our database in the build environment may suffer from a variety of contingencies leading to data loss, presumably in several cases (hardware failure, software failure, natural disaster, hacker attack, misoperation (maximum)) so, in order to recover data after data loss, we must regularly back up the data, The strategy of backing up data is tailored to different enterprise scenarios, and we can customize how our own enterprise's backup strategy is based on these factors (e.g., database size, tolerable data loss, tolerable recovery time, data that needs to be recovered)

2. Type of data backup

Full and partial backups (incremental and differential backups) based on the integrity of the database

Full backup: Backs up the entire database set,

Partial backup: Backing up part of a database set (for example, backing up a database, or a table)

Incremental backup: Backs up data that has changed since the last backup (incremental or complete); specific: Save space, restore more trouble.

Differential backup: Backs up data that has changed since the last full backup; specific: Wasted space, restores are simpler than incremental restores.

3. mysql Backup mode

Hot backup: Refers to database backup, database read and write not affected

Warm backup: Refers to the database backup, the database is read-only, not write operations

Cold backup: Refers to the database backup, the database can not read and write operations, that is, the database to go offline.

Different storage engines for the database support different backup methods

MyISAM: Support Win Bei, cold

InnoDB: Support Hot spares, Win Bei, cold

Physical Backup: The data files of the replicated database are packaged directly to achieve the effect of backup by TAR,CP and other commands.

Logical backup: Export data or SQL statements from a database with specific tools and save backups (accuracy of data loss)

4, backup needs to consider the problem

(1) What data do we need to back up?

data, binary logs, INNODB transaction logs, code (stored procedures, function libraries, triggers, schedulers), configuration files

(2) How the Backup tool chooses

Mysqldump: Logical Backup tool for all storage engines, supports warm backup, full backup and partial backup, corresponding INNODB supports hot backup.

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

Xtrabackup: A very powerful innodb/xtradb hot backup tool that supports full backup, incremental backup, provided by Perconat

5, design the appropriate backup strategy

For different enterprise scenarios, make a different backup strategy to backup the database, the backup strategy is generally three kinds:

Direct Cp\tar Copy database files (for smaller databases, direct replication)

mysqldump+ copy binlogs (data volume generally, first mysqldump to complete the data, and then regularly back up the log text)

LVM2 snapshot + copy binlogs (the amount of data in general, does not unduly affect the operation of the business, using LVM2 snapshot complete, incremental backup log)

Xtrabackup (large data volume, does not unduly affect business operations after a full backup using Xtrabackup, periodic incremental or differential backups)

Various backup combat drills

1. Use CP for Backup

Yum installs MySQL, default database path/VAR/LIB/MSYQL, as follows

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/98/F6/wKiom1lCPK7w-h3eAAAi8uzt1wY844.png-wh_500x0-wm_ 3-wmp_4-s_2347851270.png "style=" Float:none; "title=" 1.png "alt=" Wkiom1lcpk7w-h3eaaai8uzt1wy844.png-wh_50 "/>

View the current database, our database is Yema;

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/98/F6/wKiom1lCPK-DJPRHAAAdWbSAMtA553.png-wh_500x0-wm_ 3-wmp_4-s_4190565878.png "style=" Float:none; "title=" 2.png "alt=" Wkiom1lcpk-djprhaaadwbsamta553.png-wh_50 "/>

View all tables in the Yema database, ym_001, ym_002

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/98/F6/wKioL1lCPLDDrBiQAAAY6D6pPpM906.png-wh_500x0-wm_ 3-wmp_4-s_3411742713.png "style=" Float:none; "title=" 3.png "alt=" Wkiol1lcplddrbiqaaay6d6pppm906.png-wh_50 "/>

To apply a read lock to the database

Summary of MySQL database backup methods