Linux mysql数据库备份与恢复详解和操作

数据库备份与恢复

（1）备份类型

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

###1.热备份（hot backup）

--在线备份，数据库出于运行状态，这种备份方法依赖于数据库的日志文件，对应用基本无影响，（但是性能也是会有下降）

###2.冷备份（cold backup）

--备份数据文件，需要停机，是在关闭数据库的时候进行的。  ——备份datadir目录下的所有文件

###3.温备份（warm backup）

--针对myisam的备份（myisam不支持热备份），备份是实例只读不可写，数据库锁定表格的状态下进行的，对应用影响很大。

2.逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

###优点：备份文件相对较小，只备份表中的数据与结构

###缺点：恢复速度慢（需要重建索引，存储过程等）

###对业务影响：I/O负载加大

（2）备份工具

ibbackup --官方备份公具--收费--物理备份

xtrabackup--开源社区备份工具--开源免费--物理备份

mysqldump --官方自带备份工具  --开源免费 --逻辑备份（速度慢）

mysqlbackup--官方备份工具--innodb引擎的表mysqlbackup可以进行热备

        非innodb表mysqlbackup就只能温备 --物理备份，备份还原速度快 --适合大规模数据使用

（3）物理备份方式

1.完全备份

每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快
缺点：占用空间大，备份速度慢

2.增量备份

每次备份上一次备份到现在产生的新数据

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化.

特点：因而备份的数据量小，占用空间小，备份速度快。

缺点：恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。

3.差异备份

只备份跟完整备份不一样的

备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点是从第一次的完整备份起，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。

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

 （4）安装xtrabackup

# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# rpm -ivh percona-release-latest.noarch.rpm

# yum -y install percona-xtrabackup-24.x86_64  #一定要下载这个版本

        如果安装不上报错：

        Transaction check error:

        file /etc/my.cnf from install of Percona-Server-shared-56-5.6.46-rel86.2.1.el7.x86_64 conflicts with file from package         mysql-community-server-5.7.28-1.el7.x86_64
        Error Summary  #说是冲突

        解决方法：

        1.先安装yum install mysql-community-libs-compat -y #安装包

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

        参考：https://www.cnblogs.com/EikiXu/p/10217931.html

# innobackupex -v     ##查看信息

 （5）物理备份--完全备份

创建备份目录：

#mkdir  /xtrabackup       //用来存放备份文件

备份之前先在数据库中存入一些数据

MySQL root@(none):(none)> create database ceshi;

MySQL root@(none):(none)> use ceshi

MySQL root@(none):ceshi> create table t1(id int);

MySQL root@(none):ceshi> insert into t1 values(1);

 退出mysql，进行完全备份

[root@b-2 ~]# innobackupex  -uroot  -p'Lz@147369'  /xtrabackup/
查看备份文件----备份完成

 完全备份恢复流程：

模拟数据丢失

1.停止mysqld服务

[root@b-2 ~]# systemctl  stop  mysqld

2.设置数据丢失

[root@b-2 ~]# rm -rf  /var/lib/mysql/*

 3.重演回滚--

[root@b-2 ~]# innobackupex  --apply-log  /xtrabackup/2023-08-09_22-05-51/
     230809 22:13:50 completed OK!      即为回滚成功

 ###恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里

       # cat /etc/my.cnf

        [mysqld]

        datadir=/var/lib/mysql

4.恢复数据

[root@b-2 ~]# innobackupex  --copy-back  /xtrabackup/2023-08-09_22-05-51/

5.修改权限

[root@b-2 ~]# chown mysql.mysql /var/lib/mysql  -R

6.重启mysqld服务

[root@b-2 ~]# systemctl start mysqld

7.确认数据是否恢复，登录mysql查看

 数据已经恢复

（6）增量备份

原理：每次备份上一次备份到现在产生的新数据

先在数据库上建一个测试的库然后添加信息

MySQL root@(none):ceshi> select * from t1
+----+
| id |
+----+
| 1  |
+----+

1 row in set

1.完整备份：周一

[root@b-2 ~]# innobackupex -uroot -p'Lz@147369' /xtrabackup/

查看备份是否存在

[root@b-2 ~]# ls /xtrabackup/
2023-08-12_21-03-45

2.增量备份     基于周一  插入数据模拟周二

MySQL root@(none):ceshi> insert into t1 values(2);
MySQL root@(none):ceshi> select * from t1
+----+
| id |
+----+
| 1  |
| 2  |
+----+

[root@b-2 ~]# innobackupex -uroot -p'Lz@147369'  --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2023-08-12_21-03-45/

[root@b-2 ~]# ls /xtrabackup/
2023-08-12_21-03-45  2023-08-12_21-18-14

3.增量备份周三   基于周二 插入数据模拟周三

MySQL root@(none):ceshi> insert into t1 values(3);
MySQL root@(none):ceshi> select * from t1
+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
+----+

[root@b-2 ~]# innobackupex -uroot -p'Lz@147369'  --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2023-08-12_21-18-14/
[root@b-2 ~]# ls /xtrabackup/
2023-08-12_21-03-45  2023-08-12_21-18-14  2023-08-12_21-21-17

4.模拟数据丢失  然后重演回滚

回滚周一的全备份

[root@b-2 ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-12_21-03-45/

回滚周二

[root@b-2 ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-12_21-03-45/  --incremental-dir=/xtrabackup/2023-08-12_21-18-14/

回滚周三  

[root@b-2 ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-12_21-18-14/  --incremental-dir=/xtrabackup/2023-08-12_21-21-17/

（7）差异备份流程

1.清理之前的备份信息

插入周一的数据

[root@b-2 ~]# rm -rf /xtrabackup/*


MySQL root@(none):ceshi> insert into t1 values(1);
Query OK, 1 row affected
Time: 0.002s
MySQL root@(none):ceshi> select * from t1
+----+
| id |
+----+
| 1  |
+----+

2.完整备份周一

[root@b-2 ~]# innobackupex --user=root --password='Lz@147369'  /xtrabackup



[root@b-2 ~]# ls /xtrabackup/
2023-08-17_15-03-14

3.模拟插入周二的数据，然后备份----基于周一

MySQL root@(none):ceshi> insert into t1 values(2);
Query OK, 1 row affected
Time: 0.002s
MySQL root@(none):ceshi> select * from t1
+----+
| id |
+----+
| 1  |
| 2  |
+----+

[root@b-2 ~]# innobackupex -uroot -p'Lz@147369' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2023-08-17_15-03-14/

[root@b-2 ~]# ls /xtrabackup/
2023-08-17_15-03-14  2023-08-17_15-08-16

4.模拟周三插入数据，然后备份----基于周一

MySQL root@(none):ceshi> insert into t1 values(3);
Query OK, 1 row affected
Time: 0.002s
MySQL root@(none):ceshi> select * from t1
+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
+----+

[root@b-2 ~]# innobackupex -uroot -p'Lz@147369' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2023-08-17_15-03-14/

[root@b-2 ~]# ls /xtrabackup/
2023-08-17_15-03-14  2023-08-17_15-08-16  2023-08-17_15-11-42

5.模拟数据丢失

[root@b-2 ~]# systemctl stop mysqld
[root@b-2 ~]# rm -rf /var/lib/mysql/*

6.恢复周一的完整数据

[root@b-2 ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-17_15-03-14/

[root@b-2 ~]# chown mysql.mysql /var/lib/mysql