MYSQL/MARIADB Backup (xtrabackup) Restore combat

I. Overview

The previous article about MySQL installation and basic use of MySQL, this article is a follow-up supplement, mainly for MySQL or mariadb backup and restore, it is well known that the data is the most important, so usually to the enterprise data need to do backup, when the data system crashes, data loss anomalies, To recover from the backup file!
The environment of this time:
Centos7.4_x64, mysql5.7.21, Xtrabackup
MySQL installation configuration can refer to the previous series of articles, only to supplement the relevant configuration items to open, and xtrabackup installation use;
Use the demo data to import the MySQL database

[[email protected] ~]# mysql -uroot -predhat < testdb.sql或mysql> source   testdb.sqlmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || example            || mysql              || performance_schema || study              || sys                |以上study即为测试数据库包含以下测试表mysql> show tables;+-----------------+| Tables_in_study |+-----------------+| class           || course          || part            || score           || student         || tb31            || tb32            || teacher         || test1           || test2           || user_info       |+-----------------+

Test database and data table is ready to complete, before the data backup and recovery, we first understand the database backup and recovery of the relevant conceptual principles;

About backup and restore of databases
Why backup?
Mainly for disaster recovery such as: hardware failure (redundancy), software failure (bug), natural disaster, hacker attacks, misoperation, and testing needs to export data, etc.;
Restore or restore is based on the previous backup file;
Backup Type
Full backup, incremental backup, differential backup:
Full backup: A copy of the backup data (at a point in time);
Incremental backup: Backs up only the data from the variable since the last full or incremental backup;
Differential backup: Backs up only the data from the variable since the last full backup;

Physical backup, logical backup:
Physical Backup: Copy the backup of the data file;
Logical backup: Exporting data from a database exists in one or more files;

Depending on whether the data service is online:
Hot-Standby: Read and write operations can be done in a state of the backup;
Win Bei: A readable but non-writable backup;
Cold: Backups made in a state where read and write operations are not available
Each backup type above backup execution can only back up the state of the data at the time of backup, such as the state of the moment when you want to recover the database crash, need to open the binary log function, need to restore data based on backup +binary log to the state of the moment before the data crash;
Backup tool with Mysqldump (Win Bei, not suitable for large data online backup), Xtrabackup (support for INNODB hot spares, open source Professional backup data, support MYSQL/MARIADB) This paper will use mysqldump and xtrabackup to explain the data backup and recovery (offsite);

Regardless of the tool backup, the binary log is restored to the pre-crash state, so you need to configure the database to turn on the binary log function; The following can be mysql5.7.21

#cat /usr/local/mysql/etc/my.cnfserver-id       = 1log_bin         = /data1/mysqldb/mysql-bin.log

Second, mysqldump Backup and recovery

Mysqldump Instructions for use
Single-Process logical backup, full backup, partial backup;

Usage: mysqldump [OPTIONS] database [tables]         OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]         OR     mysqldump [OPTIONS] --all-databases [OPTIONS]mysqldump mydb：表级别备份(还原时库需要存在)mysqldump --databases mydb：库级别备份(库不在会自行创建库)MyISAM存储引擎：支持温备，备份时要锁定表；         -x, --lock-all-tables：锁定所有库的所有表，读锁；         -l, --lock-tables：锁定指定库所有表；InnoDB存储引擎：支持温备和热备；         -x, --lock-all-tables：锁定所有库的所有表，读锁；         -l, --lock-tables：锁定指定库所有表；        --single-transaction：创建一个事务，基于此快照执行备份；                -R, --routines：存储过程和存储函数；                --triggers      触发器                -E, --events      事件                 --master-data[=#]                        1：记录为CHANGE MASTER TO语句，此语句不被注释；                        2：记录为CHANGE MASTER TO语句，此语句被注释；                --flush-logs：锁定表完成后，即进行日志刷新操作(重新生成binlog日志)；

Backup study database based on mysqldump

热备,备份存储过程和存储函数,事件,并记得下事件位置;(便于从binlog中的位置开始恢复到故障前)#mysqldump -uroot -predhat --single-transaction  -R -E --triggers --master-data=2 --databases study >/home/san/studydb.sql

Description
Less studydb.sql
will see the following

-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000005‘, MASTER_LOG_POS=154;

This is the--master-data=2 option function, annotated, binary log dot at 154
Data modification operations after a simulated backup

Pre-Modified:mysql> select * FROM user_info;+-----+-------+------+--------+----------+| Nid | name | Age | Gender |   Part_nid |+-----+-------+------+--------+----------+| 1 |   San | 20 |        Male |   1 | | 2 |   Dong | 29 |        Male |   2 | | 4 |   Ling | 28 |        Male |   4 | | 5 |   Ling | 28 |        Male |   3 | | 6 |   Dong | 30 |        Male |   1 | | 7 |   B | 11 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 10 |   e | 22 |        Male |  3 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male | 1 |+-----+-------+------+--------+----------+11 rows in Set (0.00 sec) Add a:mysql> insert into user_info values (' Hi ', 18, ' Male ', 4); Query OK, 1 row affected (0.03 sec) Deletes a:mysql> delete from user_info where nid=1; Query OK, 1 row affected (0.01 sec) finally user_info data after the last backup as follows:mysql> SELECT * FROM user_info;+-----+-------+------+------- -+----------+| Nid | name | Age | Gender | Part_Nid |+-----+-------+------+--------+----------+| 2 |   Dong | 29 |        Male |   2 | | 4 |   Ling | 28 |        Male |   4 | | 5 |   Ling | 28 |        Male |   3 | | 6 |   Dong | 30 |        Male |   1 | | 7 |   B | 11 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 10 |   e | 22 |        Male |  3 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male |  1 | | 13 |   Hi | 18 |        Male | 4 |+-----+-------+------+--------+----------+11 rows in Set (0.00 sec) You can see that one is missing and one is added;

Simulating database corruption and recovering study database
Close MySQL and delete the study database to the data directory;

假设发现study数据已经丢失了;数据库运行正常;查看binlog位置mysql> show master logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       815 || mysql-bin.000002 |       177 || mysql-bin.000003 |       177 || mysql-bin.000004 |   1890875 || mysql-bin.000005 |       725 |+------------------+-----------+记住这里最后一个binlog文件及位置是mysql-bin.000005   725结合上面备份文件中的-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000005‘, MASTER_LOG_POS=154; 可以分析出备份时位置是154而数据库丢失前是725因此我们恢复study数据库里需要恢复上次的全备+加mysql-bin.000005中的154-725内容;模拟study丢失过程(传说溃的删库路)[[email protected] mysqldb]# service stop mysqld[[email protected] mysqldb]# pwd/data1/mysqldb[[email protected] mysqldb]# rm -rf  study/启动数据库[[email protected] mysqldb]# service stop mysqld登录数据库并查看发现study数据库已经丢失了

Restoring a Database

mysql -uroot -predhat < studydb.sqlmysql> show databases;可发现已经恢复;但是之前完整备份的到崩溃前的修改不见了;如下:mysql> select * from user_info;+-----+-------+------+--------+----------+| nid | name  | age  | gender | part_nid |+-----+-------+------+--------+----------+|   1 | san   |   20 | 男     |        1 ||   2 | dong  |   29 | 男     |        2 ||   4 | Ling  |   28 | 男     |        4 ||   5 | ling  |   28 | 男     |        3 ||   6 | dong  |   30 | 男     |        1 ||   7 | b     |   11 | 女     |        1 ||   8 | c     |   12 | 女     |        1 ||   9 | d     |   18 | 女     |        4 ||  10 | e     |   22 | 男     |        3 ||  11 | f     |   23 | 男     |        2 ||  12 | dongy |   22 | 男     |        1 |+-----+-------+------+--------+----------+11 rows in set (0.00 sec)

Combined with Binlog recovery:

  Export SQL files from Binlog [[email protected] mysqldb]# mysqlbinlog mysql-bin.000005 >/root/ Binlog.sql log in MySQL recovery process temporarily shut down Binlog record mysql> set @ @session .sql_log_bin=off;mysql> source Binlog.sql; Query OK, 0 rows affected (0.00 secmysql> SET @ @session .sql_log_bin=on;mysql> use study; mysql> SELECT * from use r_info;+-----+-------+------+--------+----------+| Nid | name | Age | Gender |   Part_nid |+-----+-------+------+--------+----------+| 2 |   Dong | 29 |        Male |   2 | | 4 |   Ling | 28 |        Male |   4 | | 5 |   Ling | 28 |        Male |   3 | | 6 |   Dong | 30 |        Male |   1 | | 7 |   B | 11 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 10 |   e | 22 |        Male |  3 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male |  1 | | 13 |   Hi | 18 |        Male | 4 |+-----+-------+------+--------+----------+11 rows in Set (0.00 sec)  

It can be seen that the study database has been restored to the state before the crash, and it is entirely possible to prepare a new database server, to transfer SQL to the new machine and to restore the data configuration parameters;

Third, xtrabackup backup and recovery

Xtrabackup Introduction
Xtrabackup is Percona an open source tool to support the INNODB,XTRADB (MARIADB) engine database hot standby;
For MyISAM: Win Bei, incremental backup is not supported; InnoDB: Hot standby, increment;
Physical backup, fast and reliable; automatically verifies that the backup result set is available after the backup is complete; fast restore speed
function introduction and Innobackup (MySQL Enterprise Edition fee) comparison reference website
The database engine uses the InnoDB engine

Xtrabackup Installation and use instructions

安装[官方](https://www.percona.com/downloads/XtraBackup/LATEST/)本次使用percona-xtrabackup-24-2.4.8-1[[email protected] ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm[[email protected] ~]# yum install ./percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm -y**获取帮助与使用:**可以通过man  xtrabackup 获取详细使用说明与实例Usage:  innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS] 备份用到的主要选项:    --defaults-file=     #mysql或mariadb配置文件        --user=                  #备份时使用的用户(对备份的数据库有备份权限)        --password=         #备份用户密码        -H | --host=            #localhost或远程主机**恢复时到的主要选项:**        --apply-log         #分析获取binary log文件生成backup_binlog_info文件        ---copy-back      #基于backup_binlog_info等文件恢复注:innobackupex是xtrabackup的软件链接;

Xtrabackup full Standby and recovery:
Note: The database is online during backup, needs to be offline when recovering, and the MySQL data directory is empty;
Backup:

创建备份目录 mkdir -pv /data/backup创建备份授权账号root(可以是其他用户最小权限)mysql> GRANT ALL ON *.* TO ‘root‘@‘127.0.0.1‘ identified by "redhat";Query OK, 0 rows affected, 1 warning (0.00 sec)[[email protected] mysqldb]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup看到类似如下信息表示备份成功:xtrabackup: Transaction log of lsn (7701576) to (7701585) was copied.180401 11:52:35 completed OK!同时在/data/backup目录中产生以时间为目录的备份目录[[email protected] backup]# ll /data/backup/drwxr-x--- 14 root root 4096 4月   1 11:52 2018-04-01_11-52-29

Modify the tables in the database study after the backup

删除student表mysql> drop table student;Query OK, 0 rows affected (0.04 sec)往user_info表中插入两行mysql> insert into user_info values(1,"san",18,"男",4),(14,"Hello",28,"女",2);Query OK, 1 row affected (0.00 sec)

Simulating a database crash

注意binlog文件备份好;如果binglog和数据目录在一起[[email protected] backup]# service mysqld stop[[email protected] backup]# rm -rf /data1/mysqldb/*

Recover data:

切换到备份数据目录[[email protected] backup]# cd /data/backup/2018-04-01_11-52-29事务回滚不提交[[email protected] 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log ./类似以下提示表示完成:InnoDB: Shutdown completed; log sequence number 7702056180401 12:13:40 completed OK!

Data restore

由于centos7默认有/etc/my.cnf文件因此需要重命名my.cnf或移除以免影响恢复;[[email protected] 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --copy-back ./类似以下提示表示恢复完成:180401 12:16:15 [01]        ...done180401 12:16:15 completed OK!

Recovering information in Binlog
View binlog information (file and location) in full standby

[[email protected] backup]# cat /data/backup/2018-04-01_11-52-29/xtrabackup_binlog_infomysql-bin.000008        14775由引可知在上次全备时的binglog文件是mysql-bin.000008位置为14775获取binlog信息[[email protected] backup]# mysqlbinlog -j 14775 mysql-bin.000008 >/data/backup/binlog.sql还原binlog中的内容(全备后的修改数据内容)切换到mysql数据目录(/data1/mysqldb)并修改权限[[email protected] mysqldb]# cd /data1/mysqldb[[email protected] mysqldb]# chown mysql.mysql *  -R启动mysql[[email protected] mysqldb]# service mysqld start

Log in to the database and import Binlog.sql

  mysql> source/data/backup/binlog.sqlquery OK, 0 rows Affected (0.00 sec) mysql> SELECT * from user_info;+ -----+-------+------+--------+----------+| Nid | name | Age | Gender |   Part_nid |+-----+-------+------+--------+----------+| 1 |   San | 18 |        Male |   4 | | 2 |   Dong | 29 |        Male |   2 | | 4 |   Ling | 28 |        Male |   4 | | 5 |   Ling | 28 |        Male |   3 | | 6 |   Dong | 30 |        Male |   1 | | 7 |   B | 11 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 10 |   e | 22 |        Male |  3 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male |  1 | | 13 |   Hi | 18 |        Male |  4 | | 14 |   Hello | 28 |        Women | 2 |+-----+-------+------+--------+----------+13 rows in Set (0.00 sec)  

Xtrabackup Incremental backup and recovery
Backup process:
The first incremental backup is based on the incremental backup made after the full backup, and subsequent incremental backups will be based on the previous incremental backup;
Recovery process:
Merge full backup transactions--then merge the first incremental transaction--> Last incremental backup +binlog log

Full backup:

[[email protected] ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup提示类似如下信息完成 :xtrabackup: Transaction log of lsn (7802468) to (7802477) was copied.180401 13:13:13 completed OK![[email protected] ~# ll /data/backup2018-04-01_13-13-10    ######完整备份目录

Modifying operations on a simulated database

删除第10行并新增一行mysql> delete from user_info where nid=10;Query OK, 1 row affected (0.01 sec)mysql> insert into user_info value(15,‘hehe‘,22,‘男‘,1);Query OK, 1 row affected (0.01 sec)

Incremental backup for the first time

[[email protected] ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-13-10/提示类似如下信息完成 :xtrabackup: Transaction log of lsn (7803424) to (7803433) was copied.180401 13:17:26 completed OK!

To simulate the modification of the database again

Mysql> SELECT * FROM user_info;+-----+-------+------+--------+----------+| Nid | name | Age | Gender |   Part_nid |+-----+-------+------+--------+----------+| 1 |   San | 18 |        Male |   4 | | 2 |   Dong | 29 |        Male |   2 | | 4 |   Ling | 28 |        Male |   4 | | 5 |   Ling | 28 |        Male |   3 | | 6 |   Dong | 30 |        Male |   1 | | 7 |   B | 11 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male |  1 | | 13 |   Hi | 18 |        Male |  4 | | 14 |   Hello | 28 |        Women |  2 | | 15 |   hehe | 22 |        Male | 1 |+-----+-------+------+--------+----------+13 rows in Set (0.01 sec) Insert a row and then delete a row mysql> insert into User_info value (16, ' Haha ', 21, ' female ', 3); Query OK, 1 row affected (0.01 sec) mysql> Delete from user_info where nid=2; Query OK, 1 row affected (0.01 sec) mysql> select * FROM user_info;+-----+-------+------+--------+----------+| Nid | name | Age | Gender |   Part_nid |+-----+-------+------+--------+----------+| 1 |   San | 18 |        Male |   4 | | 4 |   Ling | 28 |        Male |   4 | | 5 |   Ling | 28 |        Male |   3 | | 6 |   Dong | 30 |        Male |   1 | | 7 |   B | 11 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male |  1 | | 13 |   Hi | 18 |        Male |  4 | | 14 |   Hello | 28 |        Women |  2 | | 15 |   hehe | 22 |        Male |  1 | | 16 |   haha | 21 |        Women | 3 |+-----+-------+------+--------+----------+13 rows in Set (0.00 sec)

Second Incremental backup:

[[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  

Identify binlog files and information for the most recent incremental backup

cd /data/backup/2018-04-01_13-21-56[[email protected] 2018-04-01_13-21-56]# cat xtrabackup_binlog_info mysql-bin.000001    17452备份 mysql-bin.000001 到/data/backup中[[email protected] backup]# cd /data/backup[[email protected] backup]# cp /data1/mysqldb/mysql-bin.000001 .[[email protected] backup]# mysqlbinlog mysql-bin.000001 >binlog.sql

Analog Database crash data loss

[[email protected] backup]# service mysqld stop[[email protected] backup]# rm -rf /data1/mysqldb/*

Data recovery

[[email protected] backup]# ls2018-04-01_13-13-10     2018-04-01_13-17-21    2018-04-01_13-21-56     binlog.sql    mysql-bin.000001

Full full backup, first and second incremental backup directories, and Binlog files backed up with Binlog.sql
Recovery process:
First, the 1th (full backup) merge only commits the transaction does not roll back and then the 2nd directory merge commits the transaction does not roll back to the first, and then merges 3rd into the 1th; last one rollback, then the unified transaction commit; finally add binlog recovery

完整备份 的事务合并[[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/合并第一次增量事务[[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-17-21/合并第二次增量事务[[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-21-56/合并所有的事务[[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --apply-log 2018-04-01_13-13-10/提交还原事务[[email protected] backup] innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --copy-back 2018-04-01_13-13-10/

To modify the Restore data permissions with the startup database:

[[email protected] backup]chown mysq.mysql /data1/mysqldb -R[[email protected] backup] systemctl start mysqld

Binlog Transaction Recovery
Mysql> Source/data/backup/binlog.sql
Mysql> select * from User_info;
+-----+-------+------+--------+----------+
| Nid | name | Age | Gender | Part_nid |
+-----+-------+------+--------+----------+
| 1 | San | 18 | Male | 4 |
| 4 | Ling | 28 | Male | 4 |
| 5 | Ling | 28 | Male | 3 |
| 6 | Dong | 30 | Male | 1 |
| 7 | B | 11 | Women | 1 |
| 8 | C | 12 | Women | 1 |
| 9 | D | 18 | Women | 4 |
| 11 | f | 23 | Male | 2 |
| 12 | Dongy | 22 | Male | 1 |
| 13 | Hi | 18 | Male | 4 |
| 14 | Hello | 28 | Women | 2 |
| 15 | hehe | 22 | Male | 1 |
| 16 | haha | 21 | Women | 3 |
+-----+-------+------+--------+----------+
Rows in Set (0.00 sec)

To this incremental backup and recovery is complete!

Summarize:

Daily database backup is very necessary, and no matter what method to recover, the binary log is important to open, otherwise the recovery is not complete; binary log is best not with the data directory, it is recommended that the data directory and binary log directory should not be placed on the same physical disk; You also need to schedule backups and make offsite backups, so there's no fear of deleting a library run or crashing data loss! Many steps in this article, there may be omissions, if there are errors, welcome guidance;

MYSQL/MARIADB Backup (xtrabackup) Restore combat