开源数据库MySQL DBA运维实战 第5章 备份

关于备份

备份原因：数据丢失了和误删了

备份目标 

1. 数据的一致性   

2. 服务的可用性

备份技术 ：物理备份/冷备份 

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

1.tar :打包压缩备份

2.cp（copy）命令

功能说明： 复制文件或目录
语法 cp [-abdfilpPrRsuvx][-S <备份字尾字符串>][-V <备份方式>][–help][–spares=<使用时机>][–version][源文件或目录][目标文件或目录] [目的目录]

3.scp:

scp是 secure copy的缩写, scp是linux系统下基于ssh登陆进行安全的远程文件拷贝命令。linux的scp命令可以在linux服务器之间复制文件和目录.
scp命令的用处：
scp在网络上不同的主机之间复制文件，它使用ssh安全协议传输数据，具有和ssh一样的验证机制，从而安全的远程拷贝文件。

 复制文件：
 .将本地文件拷贝到远程
       scp 文件名 用户名@计算机IP:远程路径
 从远程将文件拷回本地
       scp 用户名@计算机IP:文件名 本地路径

拷贝数据，  优点快，缺点服务停止。

逻辑备份/热备份

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。

mysqldump,mydumper

效率相对较低

备份种类

完全备份

完全备份（full backup）：

每天对自己的系统进行完全备份。例如，星期一用一盘磁带对整个系统进行备份，星期二 再用另一盘磁带对整个系统进行备份，依此类推。这种备份策略的好处是：当发生数据丢失的灾难时，只要用一盘磁带（即灾难发生前一天的备份磁带），就可以恢 复丢失的数据。然而它亦有不足之处，首先，由于每天都对整个系统进行完全备份，造成备份的数据大量重复。这些重复的数据占用了大量的磁带空间，这对用户来 说就意味着增加成本。其次，由于需要备份的数据量较大，因此备份所需的时间也就较长。对于那些业务繁忙、备份时间有限的单位来说，选择这种备份策略是不明 智的。

增量备份

增量备份(incremental backup)：

星期天进行一次完全备份，然后在接下来的六天里只对当天新的或被修改过的数据进行备 份。这种备份策略的优点是节省了磁带空间，缩短了备份时间。但它的缺点在于，当灾难发生时，数据的恢复比较麻烦。例如，系统在星期三的早晨发生故障，丢失 了大量的数据，那么现在就要将系统恢复到星期二晚上时的状态。这时系统管理员就要首先找出星期天的那盘完全备份磁带进行系统恢复，然后再找出星期一的磁带 来恢复星期一的数据，然后找出星期二的磁带来恢复星期二的数据。很明显，这种方式很繁琐。另外，这种备份的可靠性也很差。在这种备份方式下，各盘磁带间的 关系就象链子一样，一环套一环，其中任何一盘磁带出了问题都会导致整条链子脱节。比如在上例中，若星期二的磁带出了故障，那么管理员最多只能将系统恢复到 星期一晚上时的状态。

连续回复

特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长。

差异备份

管理员先在星期天进行一次系统完全备份，然后在接下来的几天里，管理员再将当天所有 与星期天不同的数据（新的或修改过的）备份到磁带上。差分备份策略在避免了以上两种策略的缺陷的同时，又具有了它们的所有优点。首先，它无需每天都对系统 做完全备份，因此备份所需时间短，并节省了磁带空间，其次，它的灾难恢复也很方便。系统管理员只需两盘磁带，即星期一磁带与灾难发生前一天的磁带，就可以 将系统恢复。

在实际应用中，备份策略通常是以上三种的结合。例如每周一至周六进行一次增量备份或差分备份，每周日进行全备份，每月底进行一次全备份，每年底进行一次全备份

跳跃恢复

特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后 一次的差异版本(包含所有的差异)，恢复速度介于完整备份和增量备份之间。

实战案例1：percona-xtrabackup

简介

logo

它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份；
为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载。
    percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本--percona Server。并围绕
percona Server还发布了一系列的mysql工具。 

获得软件包

官方站点:https://www.percona.com/

选择版本

 

安装

安装YUM仓库:

安装percona需要的mysql包

mysql官方源：yum install -y  https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

yum工具： yum install -y yum-utils

查看mysql可用版本： yum repolist all | grep mysql

禁用80：yum-config-manager --disable mysql80-community

启用57：yum-config-manager --enable mysql57-community

yum  install mysql-community-libs-compat -y

 安装percona-xtrabackupyum ：install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum -y install percona-xtrabackup-24.x86_64            安装percona

查询安装结果 ：rpm -ql percona-xtrabackup-24 

1-1 完全备份流程

innobackupex --user=root --password='QianFeng@123' /xtrabackup/full   //连接数据库，开始备份。

ls /xtrabackup/full/

ls /xtrabackup/full/2020-08-29_11-03-48/
 

观看二进制日志位置

1-2 完全恢复流程

停止数据库:systemctl stop mysqld

清理环境（模拟损坏）:rm -rf /var/lib/mysql/*

                                     rm -rf /var/log/mysqld.log 

生成回滚日志

[root@localhost ~]# innobackupex --apply-log/xtrabackup/full/2020-08-29_11-03-48/
 

恢复文件

innobackupex --copy-back /xtrabackup/full/2020-08-29_11-03-48/

登陆验证

ls /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql       //授权

# systemctl start mysqld                  //启动数据库程序

mysql -uroot -p'QianFeng@123'   //登录数据库

mysql> select * from aaa.t1;                //查看一下之前的库和表里面的信息是否存在

2-1 增量备份流程

准备工作

使用指定的数据库：

create database testdb;

use testdb;

create table test(id int);

 insert into test values (1);

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
完整备份：周一

rm -rf /xtrabackup/*                    //删除上个实验完全备份的文件以免在这个实验中出现错误

date 08310000                            //修改时间，时间建议靠后设置。

[root@localhost ~]# innobackupex --user=root --passwrod='QianFeng@123'/xtrabaxkup     //备份

[root@localhost ~]# ll /xtrabackup/

增量备份：周二

date 09010000                    //周二

mysql -uroot -p'QianFeng@123' -e  'insert into testdb.test values (2)'      //插入周二数据

[root@localhost ~]# mysql -uroot -p'QianFeng@123' -e 'select * from testdb.test'   //查看数据

[root@localhost ~]# innobackupex --user=root --password='QianFeng@123' --increme--incremental /xtrabackup/  --incremental-basedir=/xtrabackup/2020-08-31_00-06-07                //basedir基于周一的备份。会生成一个今天的。

ls  /xtrabackup/

增量备份：周三

date 09020000    //周三

mysql -uroot -p'QianFeng@123' -e  'insert into testdb.test values (3)'           //插入周三的信息
innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2020-09-01_00-31-35                               //basedir基于周2的备份。会生成一个今天的。

周四。。周五。。周六.......之后的操作都是一样的都要基于上一次备份。

2-2 增量恢复流程

停止数据库：systemctl stop mysql     

 清理环境:rm -rf /var/lib/mysql/*

恢复周一：nnobackupex --apply-log --redo-only /xtrabackup/2020-08-31_00-06-07/

周二 : innobackupex --apply-log --redo-only /xtrabackup/2020-08-31_00-06-07 
--incremental-dir=/xtrabackup/2020-09-01_00-31-35

周三：# innobackupex --apply-log --redo-only /xtrabackup/2020-08-31_00-06-07

                     --incremental-dir=/xtrabackup/2020-09-02_00-03-41

恢复：innobackupex --copy-back /xtrabackup/2020-08-31_00-06-07   //

chown -R mysql.mysql /var/lib/mysql

systemctl start mysqld

mysql -uroot -p'QianFeng@123' -e 'select * from testdb.test'                 //查看一下数据是否恢复

实战案例2：mysqldump + binlog

优势

1 自动记录日志position位置。（show master  status\G;）

2 可用性，一致性  锁表机制。

语法： mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql

库的范围

-A, --all-databases    所有库

school    数据库名

school stu_info t1    是指school数据库的表stu_info、t1

-B, --databases bbs test mysql    多个数据库

 --single-transaction                 #InnoDB 一致性 服务可用性

--master-data=1|2    #该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释

高级选项

 --opt         #同时启动各种高级选项

-R, --routines             #备份存储过程和存储函数

 -F, --flush-logs        #备份之前刷新日志，截断日志。备份之后新binlog。

--triggers        #备份触发器

备份实战

请准备两套root密码

密码1：QianFeng@123  //配置到当前数据库中。

 密码2:QianFeng@1234    //备用

准备库1

注意请清理掉之前的test库。实验后半部分，需要继续创建test库，避免混淆。

创建testdb1库并在它下面创建一张t1表

mysql> select * from testdb1.t1 插入数据如下
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

执行备份

先创建一个文件夹：mkdir /backup/

mysqldump -p'QianFeng@123' \
--all-databases --single-transaction \
 --master-data=2 \                                                                                           //master-data=2 注释掉日志记录
--flush-logs \
>  /backup/`date +%F-%H`-mysql-all.sql

观察备份细节

[root@localhost ~]# vim /backup/2020-09-02-01-mysql-all.sql

LOCK TABLES `user` WRITE;             //观察各种锁机制，用来保证数据一致性

备份后的，数据变更行为

在testdb2.t2 中插入数据1，2,3；

   mysql> select * from testdb2.t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

创testdb3库:create database testb3
切断二进制日志（重启数据库）

systemctl restart mysqld
在testdb2.t2 中插入数据4；

mysql> insert into testdb2.t2 values(4);
删testdb3库

mysql> drop database testdb3;
在testdb2.t2 中插入数据5；

mysql> insert into testdb2.t2 values(5);

恢复实战

1 备份二进制日志文件

cp /var/lib/mysql/*bin* ~

2. 停止数据库

systemctl stop mysqld

3. 清理环境

rm -rf /var/lib/mysql/*

4.启动数据库

systemctl start mysqld

grep 'password' /var/log/mysqld.log 

找密码，再改一下密码。请使用密码2

改一下密码。请使用密码2

mysqladmin -uroot -p'*7m0IA_hu3)r' password  'QianFeng@1234'

5.mysql恢复数据

[root@localhost ~]# mysql -uroot -p'QianFeng@1234' < /backup/2020-09-02-01-mysql-all.sql

mysql -p'QianFeng@1234' -e 'flush privileges'        //请使用备份时密码 

登陆并验证数据恢复结果。请思考恢复的数据库有几个？:

6.二进制日志恢复

观察二进制截取记录

vim /backup/2020-09-02-01-mysql-all.sql

mysqlbinlog localhost-bin.000002 localhost-bin.000003                  //二进制日志恢复
 --start-position=154  | mysql -p'QianFeng@123'      

注意后续有多少日志，要跟多少日志名字。

7.观察数据

请看考数据是否恢复完整。