MySQL备份（xtrabackup）全备

特点：
​ (1)备份过程快速、可靠;
​ (2)备份过程不会打断正在执行的事务;
​ (3)能够基于压缩等功能节约磁盘空间和流量;
(4)自动实现备份检验;
​ (5)还原速度快;

流程

安装工具
[root@mysql_master]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
[root@zhaohan ~]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

[root@mysql_master ~]# yum install -y perl-DBD-MySQL perl-DBI perl-Time-HiRes libaio*

[root@mysql_master ~]# yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm （可省略）

##全量备份
[root@mysql_master ~]#
innobackupex --defaults-file=/etc/my.cnf --user=root --password="1qaz2WSX#" --backup .
#最后有一个点    点的意思是备份到当前路径下
##恢复
[root@mysql_master ~]# ls .
2019-10-31_17-49-23

恢复

先删除相应的表
注：这里需要注意的是没有删除库，因为删除库后恢复时会报错，因为它找不到相应的库，要新建出来相应名字的库再恢复
[root@zhaohan tmp]# mysql -uroot -p1qaz2WSX#
mysql> use zhaohan;
mysql> drop table Teacher;

[root@zhaohan tmp]# innobackupex  --defaults-file=/etc/my.cnf  --copy-back /tmp/2019-10-31_17-49-23
恢复报错
innobackupex version 2.4.15 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 544842a)
Original data directory /var/lib/mysql is not empty!

[root@zhaohan tmp]# rm -rf /var/lib/mysql
删除后继续恢复 成功
[root@zhaohan tmp]# innobackupex  --defaults-file=/etc/my.cnf  --copy-back /tmp/2019-10-31_17-49-23
[root@zhaohan mysql]# cd /var/lib/mysql
[root@zhaohan ~]# chown -R mysql:mysql /var/lib/mysql

[root@zhaohan ~]# systemctl restart mysqld
密码进入恢复成功
如果不成功还有可能是防火墙问题
[root@zhaohan ~]# mysql -uroot -p

##增量备份(在全量备份的基础之上)
[root@mysql_master ~]# mkdir /var/mysql_backup/xtracebackup/{incramental_backup,full_backup}
[root@mysql_master ~]# innobackupex --user=root --password="(Fcuk..0411)" --port=3306 --socket=/usr/local/mysqld/tmp/mysql.sock /var/mysql_backup/xtracebackup/full_backup/

[root@mysql_master ~]# mysql -uroot -p"(Fcuk..0411)"
mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| Book_Class              |
| Cloud_Class_Grade       |
| Cloud_Class_Information |
| mysql                   |
| performance_schema      |
| sys                     |
+-------------------------+
7 rows in set (0.00 sec)

mysql> create database Test;
Query OK, 1 row affected (0.00 sec)

mysql> use Test;
Database changed
mysql> create table backup(id int, name varchar(200))charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into backup (id,name) values(1,"bavduer001");
Query OK, 1 row affected (0.01 sec)

mysql> insert into backup (id,name) values(1,"bavduer002");
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye
[root@mysql_master ~]# 


[root@mysql_master ~]# innobackupex --user=root --password="(Fcuk..0411)" --port=3306 --socket=/usr/local/mysqld/tmp/mysql.sock --incremental /var/mysql_backup/xtracebackup/incramental_backup/ --incremental-basedir=/var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/

##恢复
[root@mysql_master ~]# innobackupex --apply-log --redo-only --use-memory=1G /var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/

[root@mysql_master ~]# innobackupex --apply-log --redo-only /var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/ --incremental-dir=/var/mysql_backup/xtracebackup/incramental_backup/2019-04-14_20-31-30/

[root@mysql_master ~]# mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@mysql_master ~]# rm -rf /usr/local/mysqld/data/*
[root@mysql_master ~]# innobackupex --copy-back /var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/

[root@mysql_master ~]# chown -R mysql:mysql /usr/local/mysqld/*
[root@mysql_master ~]# mysql.server start
Starting MySQL.. SUCCESS!