MySQL分析与整理 — 备份与恢复

清疚 2023-02-28 01:56 33阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

备份对于数据库而言是至关重要的。当数据文件发生损坏、MySQL 服务出现错误、系统内核崩溃、计算机硬件损坏或者数据被误删等事件时，使用一种有效的数据备份方案，就可以快速解决以上所有的问题。MySQL 按不同类别的区分，分为以下几类备份：

**物理备份 和 逻辑备份**

*  物理备份，备份的是完整的数据库目录和数据文件。采用该模式会进行大量的 IO 操作，但不含任何逻辑转换，因此备份和恢复速度通常都比较快。
 *  逻辑备份，是通过数据库结构和内容信息来进行备份。因此其速度较慢，并且在以文本格式保存时，不会包括日志文件、配置文件等与数据库无关的内容。

**全备份 和 增量备份**

*  全备份，也叫全量备份，备份服务器在给定时间点上的所有数据。
 *  增量备份，备份在给定时间（从一个时间点到另一个时间点）对数据所做的更改。

**在线备份 和 离线备份**

*  在线备份，也就说平时说的热备，数据库服务在运行状态下进行备份。此时其他客户端依旧可以连接到数据库，但为了保证数据的一致性，在备份期间可能会对数据进行加锁，此时客户端的访问依然会受限。
 *  离线备份，是在数据库服务停机状态下进行备份。此备份过程简单，也就是所谓的冷备。

### 1 如何备份 ###

备份数据库最直接的就是使用数据库的备份工具，其实常用的 MySQL 数据库支持的备份工具有很多，不管是用可视化管理工具备份还是用自带的命令行形式备份，都可以达到备份的要求。这里就列出常用的几个工具吧：

*  mysqldump，它是 MySQL 数据库自带的备份工具，其采用的是逻辑备份的像欧美感受，支持全库备份、单库备份、单表备份。由于其采用的是逻辑备份，所以生成的备份文件比物理备份的大，且所需恢复时间也比较长。
 *  mysqlpump，这个应该是 MySQL 5.7+ 新增的备份工具，是在 mysqldump 的基础上进行了功能的扩展，支持多线程备份，支持对备份文件进行压缩，能够提高备份的速度和降低备份文件所需的储存空间。
 *  Xtrabackup，这是 Percona 公司开发的实时热备工具，能够在不停机的情况下进行快速可靠的热备份，并且备份期间不会间断数据库事务的处理。它支持数据的全备和增备，并且由于其采用的是物理备份的方式，所以恢复速度比较快。但是它只能备份 InnoDB 和 xtraDB 两种数据引擎的表，而不能备份 MyISAM 数据表。

### 2 mysqldump ###

#### 2.1 基本用法 ####

备份数据库或数据库中的指定表
    mysqldump [options] db_name [tbl_name ...] > 文件名（包含路径）.sql
    备份一个或多个指定的数据库
    mysqldump [options] --databases db_name [db_name ...] > 文件名（包含路径）.sql
    备份当前所有数据库
    mysqldump [options] --all-databases > 文件名（包含路径）.sql

其中 options 代表各种各样的参数，常用的如下（参数很多，只列举常用的）：

指定服务器地址
    –host=host_name 或 -h host_name
    
    指定用户名
    –user=user_name 或 -u user_name
    
    指定密码，也可以不指定命令行的密码，因为它是明文的，按照提示输入即可
    –password[=password] 或 -p[password]
    
    连接数据库端口号，默认3306
    --port 或 -P
    
    导出文本使用的字符集，默认为utf8
    –default-character-set=charset_name
    
    备份包含数据库中的事件
    –events 或 -E
    
    不需要进行备份的表，必须使用数据库和表名来共同指定。也可以是视图
    –ignore-table=db_name.tbl_name
    
    备份包含数据库中的存储过程和自定义函数
    –routines 或 -R
    
    备份包含数据库中的触发器
    –triggers
    
    在对单表进行导出时候，可以指定过滤条件
    例如指定用户名 --where="user='lee'" 或用户范围 -w"id>1"
    –where="where_condition" 或 -w "where_condition"
    
    锁定所有数据库中的所有表，以保证数据的一致性
    此选项自动关闭 --single-transaction 和 --lock-tables
    –lock-all-tables 或 -x
    
    锁定当前数据库中所有表，能够保证当前数据库中表的一致性
    但不能保证全局的一致性
    –lock-tables 或 -l
    
    事务隔离模式设置为repeatable read并开启一个事务，保证备份数据的一致性
    注意 --single-transaction 选项与 --lock-tables 选项是互斥的
    --single-transaction 选项与 --quick 选项组合使用 。
    –single-transaction
    
    主要用于备份大表，经常和–single-transaction一起用
    –quick 或 -q
    
    只导出数据，而不添加create database语句
    --no-create-db 或 -n
    
    只导出数据，而不添加create table语句
    --no-create-info 或 -t
    
    不导出任何数据，只导出数据库表结构
    --no-data 或 -d
    
    如需其它操作可以通过“mysqldump --help”进行查看

#### 2.2 全备份与恢复 ####

mysqldump 的全备份与恢复的操作都比较简单，作者也认为这可能也是临时常用的备份操作（这里只是讲的逻辑备份，如果是物理备份更暴力可以直接把数据库文件拷走），示例如下：

某个库
    备份test0的库
    mysqldump -u root -p --databases test0 > test0_bak.sql
    
    恢复test0的库
    mysql -u root -p < test0_bak.sql

某个表
    备份test0库里的tbl_test1表
    mysqldump -u root -p test0 tbl_test1 > test0_test1_bak.sql
    
    恢复，登录并进入相应的库，
    mysql> use test0
    mysql> source test0_test1_bak.sql;

以上的备份和恢复，恢复的时候还得需要根据具体的备份参数应对不同恢复方式，还需要注意备份文件的路径。

#### 2.3 增量备份与恢复 ####

> 提示：
> 
> 下面的代码演示示例全是在 InnoDB 存储引擎下进行的
> 
> 如果是SQL语句命令均是在sql格式下贴的代码
> 
> 没有格式的均是命令行代码
> 
> 我也有注释提示，请各位知悉

mysqldump 并不能直接进行增量备份，需要通过开启并分析二进制日志的方式来完成。开启二进制日志，找到 MySQL 的配置文件 my.ini（my.cnf），在 \[mysqld\] 最后加入以下代码：

log-bin=mysql-logbin
    binlog-format=statement
    server-id=1

设置结束保存，重启MySQL服务。当然还可以通过“set sql\_log\_bin = 1;”开启，不过服务器重启它就失效了。以上设置参数解释：

*  **log-bin=mysql-logbin**，其中“mysql-logbin”是自定义的二进制文件名（可根据要求自定义），”log-bin=“后是可以指定 path 路径的，不指定默认是为 MySQL 的 data 目录。二进制文件名也可以不用定义，默认会以主机名生成日志清单。
 *  **binlog-format**，日志记录格式。（statement | row | mixed），statement 是以最初基于 SQL 语句日志记录日志，row 表始终使用主键以确保行可以有效的标识，mixed 是兼顾前两者的混合日志记录。
 *  **server-id=1**，MySQL5.7+ 如果开启了二进制日志，则 server-id 必须指定，否则会不允许服务启动。server-id 具体可以干什么用的以后文章会提到，这里不细讨论了。
 *  还有其它几个配置参数，\*\*expire\_logs\_days \*\*和 **max\_binlog\_size**。\*\*expire\_logs\_days \*\*是用来定义了 MySQL 清除过期日志的时间，即二进制日志自动删除的天数，默认值为0，表示不自动删除。**max\_binlog\_size**定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生回滚（关闭当前文件，重新打开一个新的日志文件）。默认1GB, 不能大于1GB或小于4096B。

重启 MySQL 服务之后会在 MySQL 目录的 data 目录下生成两个文件：

mysql-logbin.000001
    mysql-logbin.index

其中“mysql-logbin.000001”就是二进制日志文件，先进行一次全备份。模拟示例演示：

## 命令行操作（Windows操作系统）
    示例库名为test0，备份其中的tbl_test1表数据
    mysqldump -u root -p test0 tbl_test1 > test0_test1_bak.sql
    刷新二进制日志文件
    mysqladmin -u root -p flush-logs

刷新完二进制日志文件之后，会在 MySQL 目录的 data 目录下生成“mysql-logbin.000002”文件，接下来的操作会保存在“mysql-logbin.000002”文件中。随便开启一个新的会话客户端，执行几个操作：

-- sql命令
    -- 进入示例测试库test0
    use test0;
    -- 查询其中的一个表tbl_test1（主键自增长）
    select * from tbl_test1;
    +----+------+------+
    | id | num  | info |
    +----+------+------+
    |  1 | 20   | val  |
    |  2 | 11   | val  |
    |  3 | 33   | val  |
    |  4 | 15   | val  |
    |  5 | 2    | val  |
    |  6 | 123  | val  |
    |  7 | 001  | val  |
    |  8 | 1001 | val  |
    |  9 | 2222 | val  |
    | 10 | 3333 | val  |
    +----+------+------+
    -- 插入数据
    insert into tbl_test1 values (null, '9991', 'val');
    -- 删除数据（假设这是个错误的操作）
    delete from tbl_test1 where id = 10;
    -- 再插入数据
    insert into tbl_test1 values (null, '9992', 'val');
    -- 查询（id=10的数据被删除了）
    select * from tbl_test1;
    +----+------+------+
    | id | num  | info |
    +----+------+------+
    |  1 | 20   | val  |
    |  2 | 11   | val  |
    |  3 | 33   | val  |
    |  4 | 15   | val  |
    |  5 | 2    | val  |
    |  6 | 123  | val  |
    |  7 | 001  | val  |
    |  8 | 1001 | val  |
    |  9 | 2222 | val  |
    | 11 | 9991 | val  |
    | 12 | 9992 | val  |
    +----+------+------+

这个时候再刷新一下二进制日志文件：

## 命令行操作（Windows操作系统）
    刷新二进制日志文件
    mysqladmin -u root -p flush-logs

再刷新完二进制日志文件之后，会在 MySQL 目录的 data 目录下生成“mysql-logbin.000003”文件，说到这里您大概知道这个刷新二进制日志文件的作用（可以粗浅的理解把之前的操作记录一下，开启新的日志文件进行操作记录）。我们去查看一下刚才的“mysql-logbin.000002”文件（记得注意文件的路径问题）：

## 命令行操作（Windows操作系统）
    mysqlbinlog --no-defaults ../data/mysql-logbin.000002
    关于命令中的路径解释
    我这里是Windows操作系统
    因为从MySQL的bin目录下执行的命令行程序
    mysql-logbin.000002在data目录中
    所以路径是 ../data/mysql-logbin.000002

-- 相关内容知识点和本文讨论无关
    /*
    其实可以通过sql命令查询相关信息
    
    通过如下命令查看有哪些二进制日志文件
    show master logs;
    
    通过如下命令可以截断日志文件，重新定向到新的日志文件中
    一般情况下做增加备份都会flush logs下
    flush logs;
    
    查询出这些日志文件保存的路径
    show variables like '%datadir%';
    
    通过以下命令查看指定二进制日志文件的内容
    show binlog events in '二进制日志文件名';
    */
    /*===================================================*/
    /*
    关于二进制日志文件的删除（删除虚谨慎！！！！！）
    除了上面介绍的二进制日志自动删除的天数进行自动删除
    还可以通过sql命令删除手动二进制日志文件
    
    删除所有二进制日志文件。并重新创建二进制文件，扩展名从000001开始
    reset master;
    
    删除指定的二进制日志文件
    purge master logs to '二进制日志文件名';
    例如：purge master logs to 'mysql-logbin.000002';
    
    删除指定日期以前的所有日志文件
    purge master logs before 'date';
    例如：
    purge master logs before '2016-02-28 13:00:00'; //清除2016-02-28 13:00:00前的日志
    purge master logs before date_sub(now(), interval 3 day); //清除3天前的bin日志
    */

二进制日志文件内容比较杂和乱，稍微整理了一下，并贴出刚才操作的相关的内容：

# 第一个操作：位置点是332，时间点2020-07-19 2:00:28
    ...
    # at 332
    #200719  2:00:28 server id 1  end_log_pos 332 CRC32 0x3124b97f  Intvar
    SET INSERT_ID=11/*!*/;
    #200719  2:00:28 server id 1  end_log_pos 458 CRC32 0x470ae0f2  Query   thread_i
    d=4     exec_time=0     error_code=0
    use `test0`/*!*/;
    SET TIMESTAMP=1595095228/*!*/;
    insert into tbl_test1 values (null, '9991', 'val')
    ...
    
    # 第二个操作：位置点是635，时间点2020-07-19 2:00:34
    ...
    # at 635
    #200719  2:00:34 server id 1  end_log_pos 746 CRC32 0xaa14e6b7  Query   thread_i
    d=4     exec_time=0     error_code=0
    SET TIMESTAMP=1595095234/*!*/;
    delete from tbl_test1 where id = 10
    ...
    
    # 第三个操作：位置点是955，时间点2020-07-19 2:00:41
    ...
    # at 955
    #200719  2:00:41 server id 1  end_log_pos 955 CRC32 0x4ea86d9f  IntvarSET INSERT_ID=12/*!*/;
    #200719  2:00:41 server id 1  end_log_pos 1081 CRC32 0x92b2b8a4         Querythread_id=4     exec_time=0     error_code=0SET TIMESTAMP=1595095241/*!*/;
    insert into tbl_test1 values (null, '9992', 'val')
    ...

根据以上二进制日志文件内容，可以采用两种方式进行恢复。

##### **第一种，采用基于“时间点”进行断点恢复数据** #####

-- sql命令
    -- 删除表
    drop table tbl_test1;
    -- 恢复刚才全备的文件内容（请注意路径问题）
    source test0_test1_bak.sql
    -- 查询
    select * from tbl_test1;
    +----+------+------+
    | id | num  | info |
    +----+------+------+
    |  1 | 20   | val  |
    |  2 | 11   | val  |
    |  3 | 33   | val  |
    |  4 | 15   | val  |
    |  5 | 2    | val  |
    |  6 | 123  | val  |
    |  7 | 001  | val  |
    |  8 | 1001 | val  |
    |  9 | 2222 | val  |
    | 10 | 3333 | val  |
    +----+------+------+

以上看到已经恢复先前最早的数据记录，下面我们利用“时间点”进行恢复之前两个“insert”操作：

## 命令行操作（Windows操作系统）
    恢复时间点200719 2:00:34之前的操作
    mysqlbinlog --no-defaults --stop-datetime="2020-07-19 2:00:34" ../data/mysql-logbin.000002 | mysql -u root -p

恢复第一条 insert 操作，查看表恢复成功：

-- sql命令
    -- 查询
    select * from tbl_test1;
    +----+------+------+
    | id | num  | info |
    +----+------+------+
    |  1 | 20   | val  |
    |  2 | 11   | val  |
    |  3 | 33   | val  |
    |  4 | 15   | val  |
    |  5 | 2    | val  |
    |  6 | 123  | val  |
    |  7 | 001  | val  |
    |  8 | 1001 | val  |
    |  9 | 2222 | val  |
    | 10 | 3333 | val  |
    | 11 | 9991 | val  |
    +----+------+------+

跳过 delete 操作，继续恢复第二个 insert 操作：

## 命令行操作（Windows操作系统）
    恢复时间点200719 2:00:41之后的操作
    mysqlbinlog --no-defaults --start-datetime="2020-07-19 2:00:41" ../data/mysql-logbin.000002 | mysql -u root -p

执行成功查看数据：

这样就是利用“时间点”进行了两次 insert 的操作，略过了 delete 操作。

##### 第二种，采用基于“位置点”进行断点恢复数据 #####

-- sql命令
    -- 继续删除表
    drop table tbl_test1;
    -- 还是恢复刚才全备的文件内容（请注意路径问题）
    source test0_test1_bak.sql
    -- 查询
    select * from tbl_test1;
    +----+------+------+
    | id | num  | info |
    +----+------+------+
    |  1 | 20   | val  |
    |  2 | 11   | val  |
    |  3 | 33   | val  |
    |  4 | 15   | val  |
    |  5 | 2    | val  |
    |  6 | 123  | val  |
    |  7 | 001  | val  |
    |  8 | 1001 | val  |
    |  9 | 2222 | val  |
    | 10 | 3333 | val  |
    +----+------+------+

这次使用“位置点”的形式进行数据恢复，从上面贴出的日志文件看到“delete”的操作位置点是“635”，第二次“insert”的操作位置点是“955”，逐个恢复：

## 命令行操作（Windows操作系统）
    mysqlbinlog --no-defaults --stop-position="635" ../data/mysql-logbin.000002 | mysql -u root -p
    mysqlbinlog --no-defaults --start-position="955" ../data/mysql-logbin.000002 | mysql -u root -p

查看表也能恢复成功：

增量恢复的几个参数命令：

时间点开始与结束
    --start-datetime
    --stop-datetime
    位置点开始与结束
    --stop-position
    --start-position

#### 2.4 增量备份全部恢复 ####

mysqlbinlog --no-defaults 二进制日志文件路径和名字 | mysql -uroot -p

通过 binlog 二进制日志文件备份和恢复 MySQL 的数据误操作，还是经常用到的。如果再配合操作系统的定时任务或者是事件，每隔时间点进行定时备份，对误删操作非常实用。

### 3 mysqlpump ###

mysqlpump 在 mysqldump 的基础上进行了扩展增强，mysqlpump 能够并行处理数据库及其中的对象，从而可以加快备份进程，能够更好地控制数据库及数据库对象（表，存储过程，用户帐户等），能够直接对备份文件进行压缩，备份时能够显示备份的进度信息，备份用户时生成的是 create user 与 grant 语句，可以方便用户按需恢复。

mysqlpump 主要介绍部分新增的可选项：

–default-parallelism=N
    每个并行处理队列的默认线程数。默认值为2。

–parallel-schemas=[N:]db_list
    用于并行备份多个数据库
    db_list是一个或多个以逗号分隔的数据库名称列表
    N为使用的线程数，如果没有设置，则使用 --default-parallelism 参数的值

–users
    将用户信息备份为create user与grant语句
    如果想要只备份用户信息，使用以下命令
    mysqlpump --exclude-databases=% --users

–compress-output=algorithm
    默认情况下，mysqlpump不对备份文件进行压缩
    可以使用该选项指定压缩格式，当前支持LZ4和ZLIB两种格式
    mysqlpump --compress-output=LZ4 > xxx.lz4
    mysqlpump --compress-output=ZLIB > xxx.zlib
    压缩后的文件可以占用更少的存储空间，却不能直接用于备份恢复，需要先进行解压
    lz4_decompress xxx.lz4 xxx.sql
    zlib_decompress xxx.zlib xxx.sql

还有一些其它的参数选项，就不在详细说了（其实作者用的也不多），有兴趣的朋友可以查阅官网。

另外，作者认为经常用到的最大的区别就是：

*  如果用 mysqldump 备份一个带有索引的表，备份文件中的关于表的内容是会先将表的结构创建（带有索引），然后 insert 表中的数据，而 mysqlpump 备份的文件，会先创建最基本的表结构（没有索引信息），然后是 insert 表的数据，最后再创建之前没创建的索引。很明显索引是为了加快查询，但是片面的影响了更新操作，mysqlpump 这样做可以减少输入插入的时间。
 *  第二个区别就是 mysqlpump 备份时，是将视图，触发器，自定义函数，存储过程以及事件都会进行备份，mysqldump 备份还得需要特定的参数设置才可以。

最后如果想定时备份二进制日志文件，使用操作系统的定时任务进行备份（例如 Linux 的 cp 或 scp），也可以通过 mysqlbinlog 自带的功能来实现远程备份，将远程服务器上的二进制日志文件复制到本机，如下：

mysqlbinlog --read-from-remote-server
    --raw --stop-never \
    --host=主机名 --port=3306 \
    --user=用户名 --password=密码 复制时的日志文件名

### 4 Xtrabackup ###

请转到作者单独的一篇有关于 Xtrabackup 备份与恢复的文章中进行讨论和分析。

### 结语 ###

当作者想继续写关于 Xtrabackup 备份与恢复的时候，作者忽然想起手头并没有 Linux 系统的环境，于是搜了搜网络上有关于 Windows 系统下的 Xtrabackup 使用，很遗憾不是年代久远的信息就是说 Xtrabackup 不支持 Windows系统（作者从没曾想过它会不支持 Windows，其实是不太相信它会不支持 Windows）。所以最后决定搭建完了自用的 Linux 系统环境之后，再做相应的示例讲解。作者一向是坚持相应的代码自撸测试成功没问题之后，发布到文章中，包括示例的各种各样演示。当然您可以搜网络其它内容，毕竟都是为了学习和讨论。所以各位朋友您可以在作者相应分类文章中找到有关于“Xtrabackup备份与恢复”的文章。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/8a199939cb464ddb8f4872e7b180f306.png