Linux云计算：数据备份操作详解

喜欢ヅ旅行 2023-10-06 15:18 2阅读 0赞

#### 一、逻辑备份 ####

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

**本质：**导出的是SQL语句文件

**优点：**不论是什么存储引擎，都可以用mysqldump备成SQL语句

**缺点：**速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份。

提供三种级别的备份，表级，库级和全库级

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

数据一致，服务可用。:如何保证数据一致，在备份的时候进行锁表会自动锁表。锁住之后在备份。

本身为客户端工具:
    远程备份语法: # mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
    本地备份语法: # mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql

#### **1.常用备份选项** ####

***-A, --all-databases***  
备份所有库

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

**--no-data，-d**  
不导出任何数据，只导出数据库表结构。

#### 2.备份表 ####

语法: # mysqldump -u root -p1 db1  t1 > /db1.t1.bak   
    [root@mysql-server ~]# mkdir /home/back  #创建备份目录
    [root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 > /home/back/company.employee5.bak
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    备份多个表：
    语法: mysqldump -u root -p1 db1 t1   t2 > /db1.t1_t2.bak
    [root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2  > /home/back/company.new_t1_t2.bak

#### 3、备份库 ####

备份一个库：相当于将这个库里面的所有表全部备份。
    语法: # mysqldump -u root -p1 db1 > /db1.bak
    [root@mysql-server ~]# mysqldump -uroot -p'qf123' company > /home/back/company.bak
    备份多个库（-B）：
    语法：mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.bak
    [root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb > /home/back/company_testdb.bak
    备份所有的库：
    语法：# mysqldump  -u root -p1 -A > /alldb.bak
    [root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak

到目录下面查看一下：

![c0830b5710a7ff717cd495a219ca9540.png][]

#### 4、恢复数据库和表 ####

**为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。**

为实验效果先将刚才备份的数据库和表删除了。登陆数据库：

\[root@mysql-server ~\]\# mysql -uroot -pqf123

mysql> show databases;

![83234b29f610c86371937421fde761f3.png][]

mysql> drop database company; mysql> \\q

#### 5、恢复库 ####

登陆mysql创建一个库
    mysql> create database company;
    恢复：
    [root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak

#### 6、恢复表 ####

登陆到刚才恢复的库中将其中的一个表删除掉
    mysql> show databases;
    mysql> use company
    mysql> show tables;
    +-------------------+
    | Tables_in_company |
    +-------------------+
    | employee5         |
    | new_t1            |
    | new_t2            |
    +-------------------+
    mysql> drop table employee5;
    开始恢复:
    mysql> set sql_log_bin=0;   #停止binlog日志（可选）
    Query OK, 0 rows affected (0.00 sec)
    mysql> source /home/back/company.employee5.bak;  -------加路径和备份的文件 
    恢复方式二：
    # mysql -u root -p1  db1  < db1.t1.bak
                         库名    备份的文件路径

#### 7、备份及恢复表结构 ####

1.备份表结构：
    语法：mysqldump  -uroot -p123456 -d databasename tablename > dump.sql
    [root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 > /home/back/emp.bak
    恢复表结构：
    登陆数据库创建一个库
    mysql> create database t1;
    语法：# mysql -u root -p1 -D db1  < db1.t1.bak
    [root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak

登陆数据查看：

![32871b0fd2074807bf1256b43caeb396.png][]

**8、在库里数据的导入导出。没有表结构。**

**表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。**

mysql> show variables like "secure_file_priv";  ----查询导入导出的目录。（保证数据安全做共享）

![bd20380524a2c73167063a2e1419f206.png][]

修改安全文件目录：
    1.创建一个目录：mkdir  路径目录
    [root@mysql-server ~]# mkdir /sql
    2.修改权限
    [root@mysql-server ~]# chown mysql.mysql /sql
    3.编辑配置文件：
    vim /etc/my.cnf
    在[mysqld]里追加
    secure_file_priv=/sql
    4.重新启动mysql.
    1.导出数据
    登陆数据查看数据
    mysql> show databases;    #找到test库
    mysql> use test   #进入test库
    mysql> show tables;  #找到它t3表
    mysql> select * from t3 into outfile '/sql/test.t3.bak';
    添加修饰的：
    mysql> select * from t3 into outfile '/sql/test.t3.bak1' fields terminated by ',' lines terminated by '\n';
    注：
    fields terminated by ',' ：字段以逗号分割
    lines terminated by '\n'：结尾换行
    2.数据的导入
    先将原来表里面的数据清除掉，只保留表结构
    mysql> delete from t3;
    mysql> load data infile '/sql/test.t3.bak' into table t3;
    如果将数据导入别的表，需要创建这个表并创建相应的表结构。

#### 二、Mysql bin-log日志恢复数据 ####

需要开启binlog日志功能

![51cb36d1dcc24272897618a4e16b5631.png][]

加上配置之后重启服务，使其生效

[root@mysql-1 mysql]# systemctl restart mysqld

![9da4b48cabe0403abb49becd7ff40167.png][]

mysql> flush logs;              #刷新binlog日志，使下面的语句存放到下一个binlog日志中
    
    mysql> create table testdb.t2(id int);

![8308935dfff74b56a2a27a6e1bb00ff2.png][]

根据位置恢复

找到要恢复的sql语句的起始位置、结束位置

[root@mysql-1 mysql]# mysqlbinlog mysql-bin.000002          #查看binlog日志内容

![d2107fc1c1454e968c09b0fce0c187b0.png][]

mysql>drop table testdb.t2(id int);             //删除表格。注意：增删改都会记录到binlog日志中。
    
    [root@mysql-1 mysql]# mysqlbinlog --start-position 219 --stop-position  321 mysql-bin.000002 |mysql -uroot -p123        #恢复语句

作业：

根据binlog日志的时间点恢复

找到要恢复sql语句的起始时间、结束时间

mysqlbinlog --start-datetime='2019-8-21 23:55:54' --stop-datetime='2019-8-21 23:56:541' mylog.000001 | mysql -u root -p1

#### 三、物理备份 ####

percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本--percona Server。并围绕percona Server还发布了一系列的mysql工具。

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

1.完全备份-----完整备份：

每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较。

特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

2.增量备份: 每次备份上一次备份到现在产生的新数据

在第一次完整备份之后，第二次开始每次都将添加了存档属性的文件进行备份，并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢？这就是为了下一次备份的时候判断是否有文件变化，因为用户在每次备份以后修改这些被清除存档属性的文件，存档属性就会自动加上，相当于用户告诉系统，这些文件有变化，你下一次就备份这些文件，其他没有存档属性的就不需要备份，这就是增量备份的工作机制。

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

1.  差异备份:只备份跟完整备份不一样的

在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。因此，差异备份在备份完毕之后不需要清除文件的存档属性，因为这些文件和下一次备份没有什么关系，它仅仅和第一次完整备份的数据进行比较（第一次完整备份之后是清除存档属性的）。

（相当于第一次机器人把地板打扫干净了，你踩过，就会有脚印，机器人就把脚印记录下   
         来，但不打扫，下次你又有踩脏的，机器人就把你这几次所有踩脏的地方都记录下来，始终   
         不打扫，每次都这样。机器人每次记录的内容就相当于差异备份的内容）

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

简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。

热备份

数据库启动同时给客户端提供服务的情况下

冷备份 数据库要关掉或者不能给客户端提供服务

官网：[Percona – The Database Performance Experts][Percona _ The Database Performance Experts]

#### 1.安装xtrabackup ####

安装xtrabackup
    # wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
    # rpm -ivh percona-release-0.1-4.noarch.rpm
    [root@mysql-server yum.repos.d]# vim percona-release.repo

修改如下内容：将原来的1改为0

![7d2016847c7b49df938ddb366e69dc4a.png][]

[root@mysql-server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64

#### 2.完全备份流程: ####

创建备份目录：
    [root@mysql-server ~]# mkdir /xtrabackup/full -p
    备份：
    [root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup/full

![d99988006e4ef4e16d36f40120699cae.png][]

可以查看一下:
    [root@mysql-server ~]# cd /xtrabackup/full/
    [root@mysql-server full]# ls
    2019-08-20_11-47-49
    =======================================================
    完全备份恢复流程
    1. 停止数据库
    2. 清理环境
    3. 重演回滚－－> 恢复数据
    4. 修改权限
    5. 启动数据库
    1.关闭数据库：
    [root@mysql-server ~]# systemctl stop mysqld
    [root@mysql-server ~]# rm -rf /var/lib/mysql/*
    [root@mysql-server ~]# rm -rf /var/log/mysqld.log           #可选操作
    [root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log  #可选操作
    2.恢复之前的验证恢复:
    [root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2019-08-20_11-47-49
    3.确认数据库目录：
    恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里
    # cat /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    4.恢复数据：
    [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49
    5.修改权限：
    [root@mysql-server ~]# chown mysql.mysql  /var/lib/mysql  -R
    启动数据库:
    [root@mysql-server ~]# systemctl start mysqld

#### 3.增量备份流程 ####

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

1.在数据库上面创建一个测试的库

![763494c89e274939963ea94b03bc5eca.png][]

**1.完整备份:周一**

[root@mysql-server ~]# rm -rf /xtrabackup/*
    [root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
    [root@mysql-server ~]# cd /xtrabackup/
    [root@mysql-server xtrabackup]# ls
    2019-08-20_14-51-35
    [root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/
    [root@mysql-server 2019-08-20_14-51-35]# ls
    backup-my.cnf  ib_buffer_pool  mysql               sys   testdb                  xtrabackup_info
    company        ibdata1         performance_schema  test  xtrabackup_checkpoints  xtrabackup_logfile

2、增量备份：周二　——　周三

在数据库中插入周二的数据:
    mysql> insert into testdb.t1 values(2);    #模拟周二
    [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_14-51-35/
    [root@mysql-server ~]# cd /xtrabackup/
    [root@mysql-server xtrabackup]# ls
    2019-08-20_14-51-35  2019-08-20_15-04-29    ---相当于周二的增量备份
    在数据库中插入周三的数据:
    mysql> insert into testdb.t1 values(3);   #模拟周三
    [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_15-04-29/      #基于前一天的备份为目录
    [root@mysql-server ~]# cd /xtrabackup/
    [root@mysql-server xtrabackup]# ls
    2019-08-20_14-51-35  2019-08-20_15-04-29  2019-08-20_15-10-56   ---相当于周三的增量备份
    查看一下备份目录:
    [root@mysql-server ~]# ls /xtrabackup/
    2019-08-20_14-51-35  2019-08-20_15-04-29  2019-08-20_15-10-56
        全备周一             增量周二               增量周三
    增量备份恢复流程
    1. 停止数据库
    2. 清理环境
    3. 依次重演回滚redo log－－> 恢复数据
    4. 修改权限
    5. 启动数据库
    6. binlog恢复
    [root@mysql-server ~]# systemctl stop mysqld
    [root@mysql-server ~]# rm -rf /var/lib/mysql/*
    依次重演回滚redo log:
    [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35
    周二 ---  周三
    [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29
    [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/
    恢复数据:
    [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/
    修改权限
    [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
    [root@mysql-server ~]# systemctl start mysqld
    登陆上去看一下:b

![0da57dc81c1f4bc7a4613bea0fd3f169.png][]

**4.差异备份流程**

清理备份的环境:

[root@mysql-server ~]# rm -rf /xtrabackup/*
    登陆数据库，准备环境
    mysql> create database testdb;
    mysql> delete from testdb.t1;
    mysql> insert into testdb.t1 values(1);    #插入数据1，模拟周一
    mysql> select * from testdb.t1;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    mysql> \q
    查看时间:
    [root@mysql-server ~]# date
    Tue Aug 20 15:39:59 CST 2019
    1、完整备份：周一
    [root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
    2、差异备份：周二　——　周三
    语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录（周一）
    3.修改时间：
    [root@mysql-server ~]# date 08211543
    Wed Aug 21 15:43:00 CST 2019
    4.在登陆mysql：
    mysql> insert into testdb.t1 values(2);  #插入数据2，模拟周二
    差异备份周二的
    [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/  #备份目录基于周一的备份
    5.再次登陆mysql
    mysql> insert into testdb.t1 values(3);  #插入数据，模拟周三
    6.在次修改时间
    [root@mysql-server ~]# date 08221550
    Thu Aug 22 15:50:00 CST 2019
    7.再次差异备份
    [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/  #还是基于周一的备份
    8.延伸到周四
    mysql> insert into testdb.t1 values(4);
    9.修改时间
    [root@mysql-server ~]# date 08231553
    Fri Aug 23 15:53:00 CST 2019
    10.差异备份周四
    [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/   #还是基于周一的备份
    11.查看一下备份目录
    [root@mysql-server ~]# ls /xtrabackup/
    2019-08-20_15-42-02  2019-08-21_15-46-53  2019-08-22_15-51-15  2019-08-23_15-53-28
       周一                   周二                   周三               周四
    差异备份恢复流程
    1. 停止数据库
    2. 清理环境
    3. 重演回滚redo log（周一，某次差异）－－> 恢复数据
    4. 修改权限
    5. 启动数据库
    6. binlog恢复
    停止数据库
    [root@mysql-server ~]# systemctl stop mysqld
    [root@mysql-server ~]# rm -rf /var/lib/mysql/*
    1.恢复全量的redo log
    语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）
    [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/
    2.恢复差异的redo log
    语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份
    这里我们恢复周三的差异备份
    [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ --incremental-dir=/xtrabackup/2019-08-22_15-51-15/   #我们恢复周三的差异备份
    3.恢复数据
    语法:# innobackupex --copy-back /xtrabackup/完全备份目录（周一）
    [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_15-42-02/
    修改权限：
    [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
    [root@mysql-server ~]# systemctl start mysqld

登陆mysql查看一下:

![911aa62c9bbb49cba4451e549015f46c.png][]

只有123.因为我们恢复的是周三的差异备份。

[c0830b5710a7ff717cd495a219ca9540.png]: https://img-blog.csdnimg.cn/img_convert/c0830b5710a7ff717cd495a219ca9540.png
[83234b29f610c86371937421fde761f3.png]: https://img-blog.csdnimg.cn/img_convert/83234b29f610c86371937421fde761f3.png
[32871b0fd2074807bf1256b43caeb396.png]: https://img-blog.csdnimg.cn/32871b0fd2074807bf1256b43caeb396.png
[bd20380524a2c73167063a2e1419f206.png]: https://img-blog.csdnimg.cn/img_convert/bd20380524a2c73167063a2e1419f206.png
[51cb36d1dcc24272897618a4e16b5631.png]: https://img-blog.csdnimg.cn/51cb36d1dcc24272897618a4e16b5631.png
[9da4b48cabe0403abb49becd7ff40167.png]: https://img-blog.csdnimg.cn/9da4b48cabe0403abb49becd7ff40167.png
[8308935dfff74b56a2a27a6e1bb00ff2.png]: https://img-blog.csdnimg.cn/8308935dfff74b56a2a27a6e1bb00ff2.png
[d2107fc1c1454e968c09b0fce0c187b0.png]: https://img-blog.csdnimg.cn/d2107fc1c1454e968c09b0fce0c187b0.png
[Percona _ The Database Performance Experts]: http://www.percona.com
[7d2016847c7b49df938ddb366e69dc4a.png]: https://img-blog.csdnimg.cn/7d2016847c7b49df938ddb366e69dc4a.png
[d99988006e4ef4e16d36f40120699cae.png]: https://img-blog.csdnimg.cn/img_convert/d99988006e4ef4e16d36f40120699cae.png
[763494c89e274939963ea94b03bc5eca.png]: https://img-blog.csdnimg.cn/763494c89e274939963ea94b03bc5eca.png
[0da57dc81c1f4bc7a4613bea0fd3f169.png]: https://img-blog.csdnimg.cn/0da57dc81c1f4bc7a4613bea0fd3f169.png
[911aa62c9bbb49cba4451e549015f46c.png]: https://img-blog.csdnimg.cn/911aa62c9bbb49cba4451e549015f46c.png