MySQL Backup and recovery

I. Purpose of the backup

• Disaster recovery. Recovering corrupted data in the event of a disaster
• Audit. What is the database at a certain point in time?
• Test. Test if new features are available

Second, the classification of backup 1. Physical backups (physical backup)

Direct copy of data files, packaging archives, no need for additional tools to directly archive commands, but cross-platform capability is poor; If the amount of data exceeds dozens of G, it is suitable for physical backup

• Cold (Cold Backup): Server offline, read and write operations are not possible
• Win Bei (warm backup): Server online, global application of shared locks, can only read can not write
• Hot backup: Database online, backup, read and write still

Note: MyISAM does not support hot-standby, InnoDB supports hot-standby, but requires specialized tools

2. Logical backups (logical Backup)

The data is extracted and saved in the SQL script, can be edited using a text editor, and easy to import, directly read the SQL statement, but the logical backup recovery time is slow, occupy large space, can not guarantee the precision of floating point number, and rebuild the index after the database is restored

Third, the way of backup

• Full backup: Backs up all libraries and tables in MySQL with large footprint and is a precondition for incremental and differential backups
• Incremental backups (incremental backup): Save space by backing up data content that is different from the last full, incremental, and differential backup. Less backup data, faster backups, slower recovery
• Differential backup (differential backup): Backups from the current point of time to the last full backup between different data content, backup data, slow backup speed, fast recovery

Iv. Backup Strategy 1. Factors to consider for backup

Backup methods, backup practices, backup costs, lock time, backup duration, performance overhead, recovery costs, recovery time, and tolerable amount of lost data

2. Solutions for different environments

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
• VM2 snapshot + Copy bin LOGS
• Xtrabackup

(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, the second way, you can use mysqldump to make a full backup of the database, and then regularly back up the binary log to achieve incremental backup effect

(3) If the amount of data in general, without undue impact on the operation of the business, you can use the third way, using lvm2 snapshot of the data file backup, and then regularly back up 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, after using Xtrabackup for a full backup, regular use of xtrabackup for incremental or differential backup

3. Contents of the Backup

• Data in the database
• Configuration file for the database
• Code in MySQL: stored procedures, stored functions, triggers
• OS-related configuration files, backup policy scripts in Chrontab
• If it is a master-slave copy of the scene, copy the relevant information
• Binary log files need to be backed up regularly, and if problems occur with binary files, a full backup of the data is required immediately

4.MySQL Common backup Tools

• Mysqldump: Logical Backup tool for all storage engines, support for Win Bei, full backup, partial backup, hot standby for InnoDB storage engine
• Xtrabackup (usually with Innobackupex tool): Powerful INNODB/XTRADB hot spare tool, support full backup, incremental backup, provided by Percona, physical backup, fast
• Lvm-snapshot: A tool that is close to hot spare, because it is required to request a global lock, then create a snapshot, and then release the global lock after the snapshot is created, it is difficult to achieve incremental backups, and the request global waits for a period of time, especially on busy servers, almost Need to backup with file system Management tools
• Mysqldumper: Multi-threaded mysqldump
  SELECT clause into OUTFILE '/path/to/somefile ' LOAD DATA INFILE '/path/from/somefile '
• Mysqlhotcopy: Nearly cold standby, basic useless, support only MyISAM storage engine
• Archive replication tools such as CP, TAR: Physical backup tool for all storage engines, cold, full backup, partial backup

V. Backup case 1. Use tar or CP cold backup and Restore (1) Data simulation

mysql -u root -p                                        //登录MySQLmysql>create database hiahia;          //创建名为hiahia的数据库mysql>create table hiahia.user (user_name char(12),user_phone char(11),primary key (user_phone));//在hiahia库下创建user表，表中两个字段，其中user_phone为主键mysql>insert into hiahia.user values (‘Zhangsan‘,‘2691376416‘);mysql>insert into hiahia.user values (‘Lisi‘,‘2691376417‘);mysql>select * from hiahia.user;        //查看表中的数据

(2) Backup and restore "CP command"

mysql>flush tables with read lock;          //向所有表施加读锁，防止过程中有写入mkdir /Backup/                                    //创建备份数据库存放目录chown -R mysql:mysql /Backup/       //更改所有者及所有组cp -a /usr/local/mysql/data/* /Backup/   //保留权限拷贝源数据文件.rm -rf /usr/local/mysql/data/*????????????????? //删除数据库的所有文件，模拟故障mysql -u root -pmysql>select * from hiahia.user;             //验证数据情况cp -a /Backup/* /usr/local/mysql/data/  //将备份的数据文件拷贝回去mysql -u root -pmysql>select * from hiahia.user;             //验证数据情况

(3) Backup and restore "tar command"

mysql>flush tables with read lock;          //向所有表施加读锁，防止过程中有写入mkdir /Backup/                                    //创建备份数据库存放目录chown -R mysql:mysql /Backup/       //更改所有者及所有组cd /usr/local/mysql/data/                          //进入MySQL数据存储目录tar Jcvpf /Backup/mysql-$(date +%F).tar.xz ./*//使用xz压缩当前目录下所有内容并将压缩文件放入/Backup目录，以日期取名文件rm -rf /usr/local/mysql/data/*                  //删除数据库的所有文件，模拟故障mysql -u root -pmysql>select * from hiahia.user;             //验证数据情况tar -Jxvpf /Backup/mysql-2018-03-21.tar.xz -C /usr/local/mysql/data///通过tar备份文件恢复数据到/usr/local/mysql/data/目录下mysql -u root -pmysql>select * from hiahia.user;             //验证数据情况

2. Using mysqldump full-scale backup recovery (1) Data simulation

mysql -u root -p                                        //登录MySQLmysql>create database hiahia;          //创建名为hiahia的数据库mysql>create table hiahia.user (user_name char(12),user_phone char(11),primary key (user_phone));//在hiahia库下创建user表，表中两个字段，其中user_phone为主键mysql>insert into hiahia.user values (‘Zhangsan‘,‘2691376416‘);mysql>insert into hiahia.user values (‘Lisi‘,‘2691376417‘);mysql>select * from hiahia.user;        //查看表中的数据

(2) mysqldump full-scale backup

mkdir /Backup/                                           //创建备份数据库存放目录chown -R mysql:mysql /Backup/              //更改所有者及所有组

(2.1) Backup Library

mysqldump -u 用户名 -p 密码 数据库名 >/备份路径/备份文件名mysqldump -u root -p hiahia >/Backup/hiahia-$(date +%F).sql//备份hiahia库，并将导出的sql语句文件重定向导出到/Backup目录下

(2.2) tables under the Backup library

mysqldump -u 用户名 -p 密码 数据库名 表名 >/备份路径/备份文件名mysqldump -u root -p hiahia user >/Backup/hiahia-user-$(date +%F).sql//备份hiahia库下的user表，并将导出的sql语句文件重定向导出到/Backup目录下

(2.3) Backing up multiple libraries

mysqldump -u 用户名 -p 密码 --databases 库名1 [库名2] ... >/备份路径/备份文件名mysqldump -u root -p --databases hiahia mysql >/Backup/hiahia-mysql-$(date +%F).sql//备份hiahia、mysql两个库，并将导出的sql语句文件重定向导出到/Backup目录下

(2.4) Backing up the entire library

mysqldump -u 用户名 -p 密码 --all-databases >/备份路径/备份文件名mysqldump -u root -p --all-databases >/Backup/all-$(date +%F).sql//将这个MySQL库备份，并将导出的sql语句文件重定向导出到/Backup目录下

(2.5) Backup table structure

mysqldump -u 用户名 -p [密码] -d 数据库名 表名 >/备份路径/备份文件名mysqldump -u root -p -d hiahia user >/Backup/hiahia-desc-user-$(date +%F).sql//只备份hiahia库下user表的结构（即表的头部），并将导出的sql语句文件重定向导出到/Backup目录下

(3) Mysqldump Full volume recovery (3.1) using source recovery

• Log in to MySQL database

• Execute source Backup SQL script file path

(3.2) Restore with mysql command

mysql -u root -p [密码] </库备份脚本路径

(3.3) Recovery form

mysql -u root -p                                        //登录MySQL数据库，密码为123mysql>drop table hiahia.user;                 //删除user表，模拟表故障mysql>select * from hiahia.user;             //验证数据情况mysql>use hiahia;                               //进入要恢复的库mysql>source /Backup/hiahia-user-2018-03-21.sql//通过备份的表sql文件恢复数据mysql>select * from hiahia.user;             //验证数据情况

(3.4) Recovery Library

mysql -u root -p                                        //登录MySQL数据库，密码为123mysql>drop database hiahia;                   //删除hiahia库，模拟库故障mysql>show databases;                      //验证数据情况

Note: When using the MySQL recovery library, if the library does not already exist, you need to create a library of the same name yourself, restore it through the backup library file, and specify which library to restore to when you restore

mysql -u root -p                                        //登录MySQL数据库，密码为123mysql>create database hiahia;          //创建库mysql -u root -p hiahia </Backup/hiahia-2018-03-21.sql//通过备份库sql文件恢复库数据到hiahia库下mysql -u root -p                                        //登录MySQL数据库，密码为123mysql>show databases;                      //验证数据情况

Vi. MySQL incremental backup and recovery

Mysqldump the drawback of full backup: Duplicate data in backup data, long backup time and recovery time

1. Incremental backup

Backup files or content that have been added or changed since the last backup

Characteristics

• Duplicate data, small amount of backup, short time
• But recovery is troublesome and requires all incremental backups after the last full and full backup to recover, and to reverse-push all incremental backups individually
• However, the incremental backup method is not directly available in MySQL, but it can be indirectly implemented via the binary log (binary logs) provided by MySQL

2. Binary Log

• To save all Update database operations
• Start logging after MySQL starts and automatically re-creates new log files after you reach the Max_binlog_size option value or after you receive the Flush logs command
• So just run the flush logs command regularly and save the new build log file to a secure location to complete this time-lapse incremental backup

3. Case: MySQL incremental backup (1) Data simulation

mysql -u root -p                                        //登录MySQLmysql>create database hiahia;          //创建名为hiahia的数据库mysql>create table hiahia.user (user_name char(12),user_phone char(11),primary key (user_phone));//在hiahia库下创建user表，表中两个字段，其中user_phone为主键mysql>insert into hiahia.user values (‘Zhangsan‘,‘2691376416‘);mysql>insert into hiahia.user values (‘Lisi‘,‘2691376417‘);mysql>select * from hiahia.user;        //查看表中的数据

(2) Turn on MySQL binary log

• Mode one: Edit the my.cnf configuration file under [mysqld] to add Log-bin=filepath, restart the MYSQLD service (default is enabled)

• Mode two: Run "mysqld--log-bin=filepath" and restart the MYSQLD service

Note: filepath is a binary file store path, if you do not specify a path specifying only the name, the default is stored in the Data directory (source default:/usr/local/mysql/data/;rpm/yum:/var/lib/mysql)

(3) Full backup

增量备份前，必须有完整备份才可以mysqldump -u root -p hiahia user >/Backup/hiahia-user-$(date +%F).sql//备份hiahia库下的user表，并将导出的sql语句文件重定向导出到/Backup目录下

(4) Incremental backup

mkdir /Backup/                                           //创建备份数据库存放目录chown -R mysql:mysql /Backup/              //更改所有者及所有组ls -l /usr/local/mysql/data///增量备份前，查询原二进制日志文件名，以便区分新旧二进制日志文件mysql -u root -p                                               //登录MySQL，密码为123mysql>flush logs;                                        //生成新的二进制日志，下面操作全部记录到新日志文件中，方便增量备份mysql>insert into hiahia.user values (‘hehe‘,‘13452231231‘);//模拟数据更改mysql> insert into hiahia.user values (‘haha‘,‘13456223123‘);mysql>insert into hiahia.user values (‘huohuo‘,‘12345621231‘);mysql>insert into hiahia.user values (‘heihei‘,‘136741234312‘);mysqladmin -u root -p flush-logs//通过命令，再次生成新的日志文件，以便将新增数据记录日志截取为独立的日志文件ls -l /usr/local/mysql/data///查询新增二进制日志文件，新增日志文件里即记录新增量数据情况mysqlbinlog /usr/local/mysql/data/mysql-bin.000005//使用mysqlbinlog命令查看新记录二进制日志内容

Note: If "Unknown variable ..." appears when viewing Mysqlbinlog, you can use the Mysqlbinlog--no-defaults binary log file to view

cp /usr/local/mysql/data/mysql-bin.000005 /Backup/

4. Case: MySQL Incremental recovery

Incremental recovery Scenario

• Destroying a database by human SQL statements
• Loss of database data due to a system failure before the next full-scale backup
• Master-Library data failure in primary and subordinate architectures

Data loss classification

• Only data after a full backup is lost
• All data, including full backups

(1) Only the data after the full backup is lost (recovery process)

mysqladmin -u root -p flush-logs//生成新的二进制日志，避免恢复时将所有的操作都记录，导致数据出错（建议备份前和恢复前都输入一次）mysqlbinlog 二进制日志文件 | mysql -u root -p//将二进制增量文件逐个推倒恢复数据库，直到恢复到完整备份时刻

(2) All data including full backups (recovery process)

mysqladmin -u root -p flush-logs//生成新的二进制日志，避免恢复时将所有的操作都记录，导致数据出错（建议备份前和恢复前都输入一次）mysql -u root -p 数据库名 </库备份脚本路径.//恢复完整备份数据mysqlbinlog 二进制日志文件 | mysql -u root -p

(3) Recovery based on point-in-time and location

默认增量恢复是恢复整个二进制日志中内容，当然也可以利用二进制日志实现基于某个时间点及位置的恢复，达到精确恢复

(3.1) Point-in-time recovery

指定导入停止时间点，可只恢复到该时间点前面数据，从而跳过某个发生错误的时间点实现数据恢复；也可指定导入开始时间点，从该时间点到日志文件结尾全部导入mysqlbinlog --stop-datetime=‘日期 时间‘ 二进制日志文件 | mysql -u root -pmysqlbinlog --start-datetime=‘日期 时间‘ 二进制日志文件 | mysql -u root -p

(3.2) Location-based recovery

如采用基于时间点恢复，可能出现某个时间点既同时存在正确操作又存在错误操作的情况，因此基于位置的恢复可以更好的控制每个操作都会记录一个end_log_pos值，使用基于位置恢复可以基于某条语句恢复mysqlbinlog --stop-position=‘end_log_pos‘ 二进制日志文件 | mysql -u root -pmysqlbinlog --start-position=‘end_log_pos‘ 二进制日志文件 | mysql -u root -p

5. Case: MySQL incremental recovery (after lost full backup) (1) Loss of data after full backup

mysql -u root -p                                               //登录MySQL，密码为123mysql>delete from hiahia.user where user_name=‘hehe‘;//模拟增量数据丢失mysql>delete from hiahia.user where user_name=‘haha‘;mysql> delete from hiahia.user where user_name=‘huohuo‘;mysql>delete from hiahia.user where user_name=‘heihei‘;mysql>select * from hiahia.user;                           //验证数据情况mysqladmin -u root -p flush-logs                  //生成新的二进制日志mysqlbinlog /Backup/mysql-bin.000005 | mysql -u root -p//通过备份的增量二进制日志文件恢复增量数据，如有多个增量日志文件逐个恢复mysql -u root -p                                               //登录MySQL，密码为123mysql>select * from hiahia.user;                    //验证数据情况

(2) Loss of full backup data

mysql -u root -p                                               //登录MySQL，密码为123mysql>drop table hiahia.user;                        //删除user表，模拟表故障mysql>select * from hiahia.user;                    //验证数据情况mysql>mysql -u root -p hiahia </Backup/hiahia-user-2018-03-21.sql//通过备份表sql文件恢复表数据到hiahia库下mysql -u root -p                                               //登录MySQL，密码为123mysql>select * from hiahia.user;                    //验证数据情况，完整备份恢复mysqladmin -u root -p flush-logs                  //生成新的二进制日志mysqlbinlog /Backup/mysql-bin.000005 | mysql -u root -p//通过备份的增量二进制日志文件恢复增量数据，如有多个增量日志文件逐个恢复mysql -u root -p                                               //登录MySQL，密码为123mysql>select * from hiahia.user;                    //验证数据情况

(3) Point-in-time recovery

Mysql-u root-p//login mysql, password is 123mysql>delete from Hiahia.user where user _name= ' hehe ';//analog incremental data loss mysql>delete from Hiahia.user where user_name= ' haha ';mysql> delete from Hiahia.user where                    User_name= ' Huohuo '; mysql>delete from Hiahia.user where user_name= ' Heihei '; Mysql>select * from Hiahia.user; Verify data condition mysqladmin-u root-p flush-logs//Generate new binary log mysqlbinlog/backup/mysql-bin.000005//by viewing The binary log confirms the recovery point, such as "180321 19:58:40" mysqlbinlog/backup/mysql-bin.000005--stop-datetime= ' 2018-03-21 19:58:40 ' | Mysql-u root-p//only restore ' 2018-03-21 19:58:40 ' before data mysql-u root-p//login mysql, password for                    123mysql>select * from Hiahia.user; Verify the data Condition mysqlbinlog/backup/mysql-bin.000005//confirm the recovery point by looking at the binary log, if it is from "180321 19:58:45" mysqlbinlog/backup/ mysql-bin.000005--start-datetime= ' 2018-03-21 19:58:45 ' | Mysql-u root-p//only recovers ' 2018-03-21 19:58:45 ' after data mysql-u root-p                                               Log in to MySQL with a password of 123mysql>select * from Hiahia.user; Verifying data conditions

Note: "Stop-datetime or Start-datetime" Do not import multiple times at the same point in time and will be imported repeatedly

(4) Location-based recovery

Mysql-u root-p//login mysql, password is 123mysql>delete from Hiahia.user where user _name= ' hehe ';//analog incremental data loss mysql>delete from Hiahia.user where user_name= ' haha ';mysql> delete from Hiahia.user where                           User_name= ' Huohuo '; mysql>delete from Hiahia.user where user_name= ' Heihei '; Mysql>select * from Hiahia.user; Verifying data conditions Mysqladmin-u root-p flush-logs//Generate new binary log Mysqlbinlog/backup/mysql-bi n.000005//Restore POS by viewing the binary log, as it is from "577" mysqlbinlog/backup/mysql-bin.000005--stop-position= ' 577 ' |  Mysql-u root-p//only restore ' 577 ' before data mysql-u root-p//login to MySQL, password is 123mysql>select *                           From Hiahia.user; Verify the data condition mysqlbinlog/backup/mysql-bin.000005//by looking at the binary log to confirm the recovery point, as if it were from "718" mysqlbinlog/backup/mysql-bin.000005-- start-position= ' 718 ' | Mysql-u root-p//only restore ' 718 ' after data mysql-u root-p//login to MySQL, password for 123MYSQL>select * from Hiahia.user; Verifying data conditions

MySQL backup and restore