代码编织梦想

一、BinLog 二进制日志

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 版本之前是 statement5.7 版本之后是 row

1)statement

  • 基于语句,记录生成数据的语句。
  • 缺点:如果当插入信息为函数时,有可能不同时间点执行结果不一样。如 insert into test values(CURRENT_DATE())

2)row

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

3)mixed

  • 混合模式,由 MySQL 自行决定何时使用 statementrow 模式。

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

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 等工具进行物理备份;
  • 备份和恢复速度较快。
  • 很难实现增量备份,并且请求全局需要等待一段时间,在繁忙的服务器上尤其如此。

两者对比如下:

工具名称mysqldumpxtrabackup
备份方式逻辑备份物理备份
数据保存方式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 中主要包含两个工具:

xtrabackupPerl 脚本):

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

innobackupexC/C++ 编译的二进制):

  • 用来备份非 InnoDB 表的,同时会调用 xtrabackup 命令来备份 InnoDB 表;
  • 还会和 MySQL Server 发送命令来进行交互,如加读锁 ftwrl、获取位点 show slave status 等。

简单来说就是 innobackupexxtrabackup 之上做了一层封装。

常用选项作用
--host指定主机
--user指定用户名
--password指定密码
--port指定端口
--databases指定数据库
--incremental创建增量备份
--incremental-basedir指定包含完全备份的目录
--incremental-dir指定包含增量备份的目录
--apply-log对备份进行预处理操作
--redo-only不回滚未提交事务
--copy-back恢复备份目录

2.完全备份与恢复

[root@MySQL ~]# innobackupex --user=root --password=123123 /backups

查看备份数据

[root@MySQL ~]# ls /backups/
[root@MySQL ~]# ls -l /backups/2021-05-19_22-31-36/

在这里插入图片描述
注解:

  • backup-my.cnf:备份用到的配置选项信息文件。
  • xtrabackup_binlog_info:MySQL 当前正在使用的二进制日志文件和此时二进制日志文件时间的位置信息文件。
  • xtrabackup_checkpoints:备份的类型、状态和 LSN 状态信息文件。
  • xtrabackup_logfile:备份的日志文件。

删除原有的数据

[root@MySQL ~]# rm -rf /usr/local/mysql/data/*

合并数据,使数据文件处于一致性的状态

[root@MySQL ~]# innobackupex --apply-log /backups/2021-05-19_22-31-36/

恢复数据

[root@MySQL ~]# innobackupex --copy-back /backups/2021-05-19_22-31-36/
[root@MySQL ~]# ls -l /usr/local/mysql/data/
[root@MySQL ~]# chown -R mysql:mysql /usr/local/mysql/data/

在这里插入图片描述
总结:

  1. innobackupex 完全备份,并指定备份目录路径。
  2. 在恢复前,需要使用 --apply-log 参数先进行合并数据文件,确保数据的一致性要求。
  3. 在恢复时,直接使用 --copy-back 参数进行恢复,需要注意的是,在 my.cnf 中要指定数据文件目录的路径。

3.增量备份与恢复

[root@MySQL ~]# innobackupex --user=root --password=123123 /backups
  • 注意:增量备份前提是需要有完全备份(因为上面已经做过了,所以不再配置)
[root@MySQL ~]# mysql -uroot -p123123
mysql> create database Coco;
mysql> use Coco;
mysql> create table test(ID int);
mysql> insert into test values(1),(2),(3);

使用 innobackupex 进行增量备份

[root@MySQL ~]# innobackupex --user=root --password=123123 \
--incremental /backups/ \
--incremental-basedir=/backups/2021-05-19_23-00-37/

查看备份数据

[root@MySQL ~]# ll /backups/
[root@MySQL ~]# cat /backups/2021-05-19_23-00-37/xtrabackup_checkpoints
[root@MySQL ~]# cat /backups/2021-05-19_23-23-45/xtrabackup_checkpoints

在这里插入图片描述
删除数据库中所有数据

[root@MySQL ~]# rm -rf /usr/local/mysql/data/*

合并全备数据目录,确保数据的一致性

[root@MySQL ~]# innobackupex --apply-log --redo-only /backups/2021-05-19_23-00-37/

将增量备份数据合并到全备数据目录当中

[root@MySQL ~]# innobackupex --apply-log --redo-only /backups/2021-05-19_23-00-37/ \
--incremental-dir=/backups/2021-05-19_23-23-45/

在这里插入图片描述
恢复数据

[root@MySQL ~]# innobackupex --copy-back /backups/2021-05-19_23-00-37/
[root@MySQL ~]# chown -R mysql:mysql /usr/local/mysql/data/
[root@MySQL ~]# mysql -uroot -p123123 -e "select * from Coco.test;"

在这里插入图片描述
总结:

  1. 增量备份需要使用 --incremental 参数来指定需要备份到哪个目录,使用 incremental-dir 参数来指定全备目录。
  2. 进行数据备份时,需要使用 --apply-log redo-only 参数来先合并全备数据目录数据,确保全备数据目录的一致性。
  3. 再将增量备份数据使用 --incremental-dir 参数来合并到全备数据当中。
  4. 最后通过全备数据来进行恢复数据,注意,如果有多个增量备份,需要逐一合并到全备数据当中,再进行恢复。
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_46902396/article/details/117016250

mysql数据库备份(一)------数据库备份和表备份_以墨言智的博客-爱代码爱编程_数据库备份

一、Mysql中的数据备份: Mysql中数据备份使用的命令是:mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。mysqldump命令的 工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。然后通过这些语句,就 能

mysql中备份和恢复重点总结_妖精小狗的博客-爱代码爱编程

MySQL的备份: 数据库备份必要性: 保证重要数据不丢失数据转移   MySQL数据库备份方法: 1. mysqldump备份工具(mysqldump它是和mysql同一级别的命令) 将CREATE和INSERT INTO语句保存到文本文件 属于DOS命令 作用: 转储数据库搜集数据库进行备份将数据转移到另一个SQL服务器(不一定是MyS

8种手动和自动备份mysql数据库的方法-爱代码爱编程

作为流行的开源数据库管理系统,MySQL的使用者众多,为了维护数据安全性,数据备份是必不可少的。本文就为大家介绍几种适用于企业的数据备份方法。 使用MySQLDump进行MySQL备份 mysqldump是MySQL内置的工具,允许用户将数据库指定不同的选项备份到文件、服务器,甚至是压缩gzip文件中。mysqldump

Mysql 之 完全备份+增量备份+备份恢复-爱代码爱编程

文章目录 一、数据备份的重要性二、数据库备份的分类2.1、从物理与逻辑的角度2.2、从数据库的备份策略角度三、完全备份3.1、完全备份的优点3.2、完全备份的缺点四、mysqldump备份库4.1、备份单个库4.2、备份多个库4.3、备份所有库4.4、mysqldump备份表五、数据库的恢复5.1、source命令恢复5.2、mysql命令恢复六、

使用达梦数据库的disql工具给数据库做热备份-爱代码爱编程

使用disql工具做热备份 热备份就是数据库实例的服务是开启的 [dmdba@localhost bin]$ ./DmServiceDMSERVER status DmServiceDMSERVER (pid 5107) is running. 1.整库备份: SQL> backup database full to “disql_quanbei”

学习MySQL备份一篇就够了!!!(完全备份、增量备份、备份恢复)-爱代码爱编程

  数据对于企业的重要性,不言而喻。所以在使用Mysql数据库的过程中,经常需要使用到数据库的备份和导出功能,非常重要!!! 文章目录 一:备份的分类1:物理备份 文件2:逻辑备份二:热备份的重要性三:常见的备份方法3.1:物理冷备3.2:专用备份工具mydump或mysqlhotcopy3.3:启用二进制日志进行增量备份3.4:第三方工具备份四:M

python django mysql安装_Django安装配置mysql的方法步骤-爱代码爱编程

近期做那个python的开发,今天就来简单的写一下Django安装配置mysql的方法步骤 安装mysql 首先安装pymysql pip install pymysql 然后安装mysqlclient pip install mysqlclient 在安装过程中可能会报类似以下错误(因为我的已经安装成功了,找了类似的图): 在win

mysql 备份数据_mysql数据备份和还原-爱代码爱编程

备份结构 1.备份表结构 mysqldump -u root -p -d dbname table1 table2 ... > a.sql 2.备份数据库的所有表结构 mysqldumo -u root -p -d dbname > b.sql 3.备份多个数据库的所有表结构 mysqldump -u root -p -d --

mysql数据库备份方法有几种_MySQL几种方法的数据库备份-爱代码爱编程

MySQL有几个方法来备份 最近博客一直想写点。可是不知道写什么,感觉自己近期的知识没有什么添加,今天想到了一篇能够写的博客。曾经试过依据data目录备份MySQL。可是从来没有成功过。前几天帮助朋友还原MySQL,最终成功的将备份的data目录还原成功了。 MySQL数据库算是经常使用的数据库中最好使用的数据库了,对于备份的操作也不例外。 所以

mysql数据备份方式_分享mysql备份的三种方式-爱代码爱编程

一、备份的目的 做灾难恢复:对损坏的数据进行恢复和还原 需求改变:因需求改变而需要把数据还原到改变以前 测试:测试新功能是否可用 二、备份需要考虑的问题 可以容忍丢失多长时间的数据; 恢复数据要在多长时间内完; 恢复的时候是否需要持续提供服务; 恢复的对象,是整个库,多个表,还是单个库,单个表。 三、备份的类型 1、根据是否需要数据

mysql 的数据备份_MySQL 数据备份与还原-爱代码爱编程

一、数据备份 1、使用mysqldump命令备份 mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。 mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。然后通过这些语句,就能够创建表并插