MySQL 备份总结(2)

=================================================================================

1.BinLog 日志内容

---

1. 引起 MySQL 服务器改变的任何操作（增 删 改）

2. 复制功能依赖于此日志（MySQL 主从）

3. Slave 服务器通过 Master 服务器的二进制日志完成主从复制，在执行之前保持于中继日志 relay log 中。

4. Slave 服务器通常可以关闭二进制日志以提升性能。

2.BinLog 日志文件的表现形式

---

1）默认在 /usr/local/mysql/data/ 目录下，存在 mysql-bin.000001 mysql-bin.000002 的二进制文件

[root@MySQL ~]# sed -i ‘/[mysqld]/a log-bin=mysql-bin’ /etc/my.cnf

[root@MySQL ~]# sed -i ‘/[mysqld]/a server-id=1’ /etc/my.cnf

[root@MySQL ~]# head -3 /etc/my.cnf

什么时候二进制日志会重新生成：

1. 重启 MySQL 服务：systemctl restart mysqld

2. 在 MySQL 数据库中：flush logs;

3. 根据 max_binlog_size 设置 MySQL 二进制文件最大值，达到最高值会自动创建。

[root@MySQL ~]# systemctl restart mysqld

[root@MySQL ~]# ls -l /usr/local/mysql/data/ | grep ‘mysql-bin’

• 注意：如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使 MySQL 管理混乱。

3.BinLog 日志文件查看相关 MySQL 命令

---

1）查看正在使用的二进制日志文件

mysql> show master status;

2）手动滚动二进制日志文件

mysql> flush logs;

3）显示所有的二进制日志文件

mysql> show binary logs;

4.MySQL 二进制文件读取工具

---

格式：mysqlbinlog [参数] log-files

有以下四种参数选择：

• --start-position：用来指定二进制日志文件的起始位置；--stop-position：用来指定二进制日志文件的结束位置。

• --start-datetime：用来指定二进制日志文件的起始日期；--stop-datetime：用来指定二进制日志文件的结束日期。

使用 mysqlbinlog 工具来对 MySQL 进行增量备份：MySQL 增量备份与恢复

5.二进制日志格式

---

• 由 bin_log_format={ statement | row | mixed } 定义（5.7 版本之前是 statement，5.7 版本之后是 row）

1）statement：

• 基于语句，记录生成数据的语句。

• 缺点：如果当插入信息为函数时，有可能不同时间点执行结果不一样。如 insert into test values(CURRENT_DATE())

2）row：

• 基于行数据，缺点在于，有时候数据量会过大。

3）mixed：

• 混合模式，由 MySQL 自行决定何时使用 statement 和 row 模式。

查看当前二进制日志记录格式

mysql> show variables like ‘binlog_format’;

• 如果想要永久修改需：sed -i '/\[mysqld]/a binlog_format=模式' /etc/my.cnf 来进行修改。

6.备份类型

---

1）根据备份时，MySQL 服务是否在线

• 冷备：停掉 MySQL 这个服务，读写操作都不能进行。

• 温备：全局施加共享锁，只能读不能写。

• 热备：不停掉 MySQL 这个服务，读写照样进行。

2）根据备份时的接口

• 物理备份（Physical Backup）：直接复制数据文件，打包归档。简单来说就是对 MySQL 的数据存放路径进行打包。

• 逻辑备份（Logical Backup）：把数据抽取出来保存在 SQL 脚本中，mysqldump 就属于逻辑备份。

各自特点：

• 物理：不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个 G，则适用于物理备份。

• 逻辑：导入方便，直接读取 SQL 语句即可；逻辑备份恢复时间慢，占用空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引。

7.根据备份整个数据还是变化数据

---

1）完全备份：每次对数据进行完整的备份，即对整个数据库的备份（是差异备份与增量备份的基础）

• 优点：备份与恢复操作简单方便。

• 缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长。

2）差异备份：

• 备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完全备份起，备份数据量越来越大。

• 恢复数据时，只需要恢复上次的完全备份与最近的一次差异备份。

3）增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份。

• 优点：占用空间小，备份速度快。

• 缺点：如果中间某次的备份数据损坏，将导致数据丢失。

8.MySQL 最常用的三种备份工具

---

1）mysqldump：通常为数据小的情况下备份；单线程恢复比较慢。

• InnoDB：热备，温备。

• MyISAM，Aria：温备。

2）xtrabackup（通常用 innobackupex 工具）：备份 MySQL 大数据；属于物理备份，速度快。

• InnoDB：热备，增量备份。

• MyISAM：温备，不支持增量备份，只有完全备份。

3）lvm-snapshot：接近于热备的工具，因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；

• 使用 cp tar 等工具进行物理备份；

• 备份和恢复速度较快。

• 很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此。

两者对比如下：

| 工具名称 | mysqldump | xtrabackup |

| — | — | — |

| 备份方式 | 逻辑备份 | 物理备份 |

| 数据保存方式 | SQL 脚本 | 二进制文件 |

| 是否支持热备份 | 是 | 是 |

| 是否支持增量备份 | 是 | 是 |

| 备份过程 | 会锁表 | 不锁表 |

| 是否影响正常业务 | 影响较大 | 影响较小 |

| 备份和恢复性能 | 耗时较长 | 耗时较短 |

| 占用空间 | 占用空间小 | 占用空间大 |

二、使用 LVM-Snapshot 工具备份

=========================================================================================

基于快照备份注意事项：

1. 事务日志跟数据日志必须在同一个卷上。

2. 创建快照卷之前，要请求 MySQL 的全局锁；在快照创建完成之后释放锁。

3. 请求全局锁完成之后，做一次日志滚动；做二进制日志文件及位置标记（手动进行）

1.准备 LVM 卷，并将 MySQL 数据迁移到 LVM 卷上

---

1）创建分区

[root@localhost ~]# fdisk -l /dev/sdb

2）创建 PV、VG、LVM，并格式化

[root@localhost ~]# pvcreate /dev/sdb1 /dev/sdb2 /dev/sdb3

[root@localhost ~]# vgcreate test /dev/sdb1 /dev/sdb2 /dev/sdb3

[root@localhost ~]# lvcreate -L +10G -n zhangsan test

[root@localhost ~]# mkfs.xfs /dev/test/zhangsan

3）创建目录，并挂载逻辑磁盘

[root@localhost ~]# mkdir /var/zhangsan

[root@localhost ~]# mount /dev/test/zhangsan /var/zhangsan/

[root@localhost ~]# df -hT

4）确认服务处于关闭状态，然后将数据迁移到 /var/zhangsan

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# cd /usr/local/mysql/data/

[root@localhost data]# tar -cf - . | tar xf - -C /var/zhangsan/

5）重新挂载 zhangsan 到 MySQL 数据库的主目录 /usr/local/mysql/data

[root@localhost ~]# umount /var/zhangsan/

[root@localhost ~]# mount /dev/test/zhangsan /usr/local/mysql/data/

6）审核权限并启动服务

[root@localhost ~]# systemctl start mysqld

[root@localhost ~]# mysql -uroot -p123123

mysql> show master status\G

mysql> flush logs;

mysql> flush tables with read lock;

[root@localhost ~]# lvcreate -L +4G -s -n mysql /dev/test/zhangsan

[root@localhost ~]# mysql -uroot -p123123 -e “unlock tables;”

7）开启 MySQL 的二进制日志，并重启 MySQL 服务

[root@localhost ~]# sed -i ‘/[mysqld]/a log-bin=mysql-bin’ /etc/my.cnf

[root@localhost ~]# sed -i ‘/[mysqld]/a server-id=1’ /etc/my.cnf

[root@localhost ~]# systemctl restart mysqld

mysql> create database Coco;

mysql> use Coco;

mysql> create table test(ID int);

mysql> insert into test values(1),(2),(3);

[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/

模拟数据库故障

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# umount /dev/test/zhangsan

恢复

[root@localhost ~]# mount /dev/test/zhangsan /usr/local/mysql/data/

[root@localhost ~]# systemctl start mysqld

[root@localhost ~]# mysql -uroot -p123123 -e “select * from Coco.test;”

三、使用 XtraBackup 工具备份

=======================================================================================

Percona XtraBackup（简称 PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQL、Percona Server 和 MariaDB，并且全部开源。

1.原理

---

• 通信方式：2 个工具之间的交互和协调是通过控制文件的创建和删除来实现的。

• 主要文件有：xtrabackup_suspended_1 xtrabackup_suspended_2 xtrabackup_log_copied

备份时 xtrabackup_suspended_2 协调这两个工具进程过程如下：

1. ib 在启动 xb 进程后，会一直等 xb 备份完 InnoDB 文件，方式就是等待 xb_suspended_2 这个文件被创建出来。

2. xb 在备完 InnoDB 数据后，就在指定目录下创建出这个文件，然后等这个文件被 ib 删除。

3. ib 检测到文件 xb_suspended_2 被创建出来后，就继续往下走。

4. ib 在备份完非 InnoDB 表后，删除 xb_suspended_2 这个文件，这样就通知 xb 可以继续了，然后等待 xb_log_copied 被创建。

5. xb 检测到 xb_suspended_2 文件删除后，就可以继续往下了。

备份过程：

• innobackupex 在启动后，会先 fork 一个进程，来启动 xtrabackup 进程，然后等待 xtrabackup 备份完 InnoDB 数据文件；

• 当 xtrabackup 备份完时，会通过创建文件来通知 innobackupex 进程，接着 innobackupex 进程会去备份非 InnoDB 数据文件。

• Redo 文件：是存储引擎层（InnoDB）生成的日志，主要为了保证数据的可靠性。

• Ibd 文件：InnoDB 引擎开启的表空间，用来存储表的数据和索引。

2.安装 XtraBackup 工具

---

[root@MySQL ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5

[root@MySQL ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm

[root@MySQL ~]# tar xf percona-xtrabackup2.4.5-Linux-x86_64.tar.gz -C /usr/src/

[root@MySQL ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/

[root@MySQL percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/

XtraBackup 中主要包含两个工具：

xtrabackup（Perl 脚本）：

• 用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互。

innobackupex（C/C++ 编译的二进制）：

• 用来备份非 InnoDB 表的，同时会调用 xtrabackup 命令来备份 InnoDB 表；

• 还会和 MySQL Server 发送命令来进行交互，如加读锁 ftwrl、获取位点 show slave status 等。

简单来说就是 innobackupex 在 xtrabackup 之上做了一层封装。

| 常用选项 | 作用 |

| :-- | :-- |

| --host | 指定主机 |

| --user | 指定用户名 |

| --password | 指定密码 |

| --port | 指定端口 |

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
--host | 指定主机 |

| --user | 指定用户名 |

| --password | 指定密码 |

| --port | 指定端口 |

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

[外链图片转存中…(img-d1gkQaxq-1715843294330)]

[外链图片转存中…(img-KuPd6vjJ-1715843294330)]

[外链图片转存中…(img-hzfrv9c1-1715843294331)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！