开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Myql数据备份 -> 正文阅读

[大数据]Myql数据备份

文章目录

1. binlog日志

1. binlog日志

mysql的binlog用来记录DDL以及DML操作，主要用来做数据增量恢复和数据库的主从复制（主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据）。要想获得binlog日志，首先要开启日志记录。一般来说开启binlog日志大概会有1%的性能损耗。

DDL数据库定义语言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

1. 开启binlog

开启binlog日志

查看是否开启binlog日志

登录数据库管理系统，执行：SHOW VARIABLES LIKE 'log_bin';

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

如果没有开启，则Value显示为OFF。需要开启。

查看所有有关binlog的文件： show variables like '%log_bin%';

mysql>  show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

进入文件夹查看

开启binlog日志
1. 进入mysql的配置文件:my.cnf
2. 方式一：
```
#第一种方式:
#开启binlog日志
log_bin=ON
#binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index
#配置serverid
server-id=1
```
3. 方式二：
```
#第二种方式:
#此一行等同于上面log_bin三行
log-bin=/var/lib/mysql/mysql-bin
#配置serverid
server-id=1
```
4. 注意：配置后，记得重启mysql服务。
5. 解析：
  
  这里的 log-bin 是指以后生成各 Binlog 文件的前缀，比如上述使用mysql-bin，那么文件就将会是mysql-bin.000001、mysql-bin.000002 等。log-bin-index 则指 binlog index 文件的名称，这里我们设置为mysql-bin.index，可以不配置。

binlog的配置

MySQL配置文件my.cnf文件中的mysqld节的配置：

[mysqld]
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写
log-bin = /var/lib/mysql/logs
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m

   		3. binlog文件开启binlog后，会在数据目录（默认）生产host-bin.n（具体binlog信息）文件及host-bin.index索引文件（记录binlog文件列表）。当binlog日志写满(binlog大小max_binlog_size，默认1G),或者数据库重启才会生产新文件，但是也可通过手工进行切换让其重新生成新的文件（flush logs）；另外，如果正使用大的事务，由于一个事务不能横跨两个文件，因此也可能在binlog文件未满的情况下刷新文件。

binlog日志模式区别
2. 详细请参考：MySQL如何开启binlog？binlog三种模式的分析 - 简书 (jianshu.com)

2. binlog找回数据

1. 创建数据库

查看是否开启binlog日志

SHOW VARIABLES LIKE 'log_bin'

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

查看所有的binlog日志

show master status;查看所有的binlog日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

show binary logs; 列出服务器上的二进制日志文件。.

mysql>  show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

查看binlog日志:show binlog events in ‘mysql-bin.000001’;

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |    123454 |         123 | Server ver: 5.7.29-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |    123454 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

我创建一个数据库binlog_demo,并新建两张表role和student

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `type` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '超级管理员', 1);
INSERT INTO `role` VALUES (2, '管理员', 0);
INSERT INTO `role` VALUES (3, '普通用户', 0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `age` int(255) NOT NULL DEFAULT 0,
  `gender` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 'Tome', 13, b'0');
INSERT INTO `student` VALUES (2, '王红', 20, b'1');
INSERT INTO `student` VALUES (3, '李四', 15, b'0');
INSERT INTO `student` VALUES (4, '赵六', 17, b'1');
INSERT INTO `student` VALUES (5, '恩东', 12, b'0');
INSERT INTO `student` VALUES (6, '光亮', 21, b'0');
INSERT INTO `student` VALUES (7, '王运', 15, b'0');

SET FOREIGN_KEY_CHECKS = 1;

查看binlog日志
1. show binlog events in 'mysql-bin.000001';
2. 日志里面包含我们所执行的sql语句

2. 备份数据库并删除

数据库创建之后，为防止意外，我们需要进行数据库的备份。我们采取定时备份。

1. 手动备份sql文件

备份文件夹到/root/目录下：mysqldump -u root -p binlog_demo > /root/binlog_demo.sql;

在数据备份之前或者之后执行flush logs重新生成一个binlog日志用来记录备份之后的所有增删改操作(重新生成日志更好找pos点):flush logs

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3827 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

此时新增了一个log文件

此时，在student和role表中执行以下sql语句

INSERT INTO student VALUE (NUll,"张三",18,1);
update student set name = "赵XX" WHERE id = 1;
update student set name = "张XX" WHERE id = 2;
DELETE student where id = 3;
INSERT INTO role value (NULL,"低级管理员",3);

查看此时的binlog日志

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3827 |
| mysql-bin.000002 |      1822 |
+------------------+-----------+
2 rows in set (0.00 sec)

其中包含了上述的五条增删改的sql操作
1. 查看详情：show binlog events in ‘mysql-bin.000002’

删除数据库:drop database binlog_demo

2. 定时备份

[MySQL定时备份（全量备份+增量备份） - 刘一二 - 博客园 (cnblogs.com)](https://www.cnblogs.com/haicheng92/p/10106517.html#:~:text=定时备份执行命令 crontab -e ，添加如下配置 %23 每个星期日凌晨3%3A00执行完全备份脚本 0,* * 1-6 %2Fbin%2Fbash -x %2Froot%2Fbash%2FMysql-DailyBak.sh >%2Fdev%2Fnull 2>%261)
windows环境下利用mysqldump对远程数据库实现定时备份：https://blog.csdn.net/problemRecord/article/details/118331678

3. 恢复数据库信息

发现数据库数据丢失后，我们要做的第一步就是先尽可能还原数据库。

我们每天都进行了数据库sql的备份，因此可以通过备份的sql还原到上一次备份sql，通过这样可以恢复大部分数据。

上次备份到丢失之间的数据，可以通过最后一次的binlog日志恢复，因为每当sql备份一次后，就会新创建一个binlog日志。

备份最后一次的binlog日志
1. 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值:
  1. show master status
```
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1822 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```
2. 先备份该日志，防止之后的操作对该日志产生影响
  1. 进入该日志所在的文件夹 cd /var/lib/mysql
  2. 备份文件到 /logback 文件夹 cp -v /var/lib/mysql/mysql-bin.000002 /logback/
3. 刷新日志，之后的操作不再在000002中操作（方便我们之后恢复数据时阅读000002）：flush logs;
4. 查看日志：
  1. 方式一：进入日志所在文件夹查看:mysqlbinlog mysql-bin.000002
  2. 方式二：show binlog events in ‘mysql-bin.000002’(推荐) 或者： show binlog events in ‘mysql-bin.000002’\G;
通过备份文件恢复上次备份之前的数据
1. 进入数据库管理系统创建数据库binlog_demo（因为之前备份时，没有备份创建数据库语句）
2. 选择数据库：use binlog_demo;
3. 执行备份sql: source /root/binlog_demo.sql
4. 查看数据库（此时数据库中的数据已经恢复到上次备份之前）

通过binlog恢复上次备份到现在修改的数据

实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；

恢复相关命令

从binlog日志恢复数据
恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
--------------------------------------------------------
常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
--------------------------------------------------------
不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志

通过binlog恢复数据：

方式一：指定pos点，执行语句（操作没报错，但是无效）

/usr/bin/mysqlbinlog --start-position=219 --stop-position=1822 --database=binlog_demo /var/lib/mysql/mysql-bin.000002 | /usr/bin/mysql -uroot -p -v binlog_demo

方式二：将备份的000002的binlog日志转化为sql语句执行

 # 全部导出
 mysqlbinlog /logback/mysql-bin.000002  > /root/binlog_000002.sql
 # 指定pos位置
 mysqlbinlog  --start-position=219 --stop-position=1822  /logback/mysql-bin.000002  > /logback/binlog_000002.sql
 # 指定时间
 mysqlbinlog  --start-date="2022-05-18 15:00:19" --stop-date="2022-05-18 16:30:19"  /logback/mysql-bin.000002  > /logback/binlog_000002.sql      
 -d 参数可以指定数据库的名称
 
 # 如果binlog日志文件记录为ROW格式(将row格式的binlog日志文件base64解析后转入文件，-v代表换行展示)
mysqlbinlog  --base64-output=decode-rows -v  --start-date="2022-05-18 15:00:19" --stop-date="2022-05-18 16:30:19" /logback/mysql-bin.000002 > /logback/binlog_000002.sql

执行binlog转化后的sql，进行数据恢复
1. 执行sql语句：mysql -uroot -p binlog_demo </logback/binlog_000002.sql

3. 常用指令

binlog相关
1. 查看是否开启：SHOW VARIABLES LIKE ‘log_bin’;
2. 查看所有binlog日志情况：show variables like ‘%log_bin%’
3. 查看所有binlog日志列表：show master logs;
4. 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值：show master status;
5. 刷新log日志，自此刻开始产生一个新编号的binlog日志文件：flush logs;
  1. 注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
6. 重置(清空)所有binlog日志：reset master;
7. 查看某个binlog日志内容，常用有两种方式：
8. 查看某个binlog日志内容，常用有两种方式：
10. 把binlog日志文件转存为sql文件 mysqlbinlog "文件名" > "test.sql"
  1. ```
  # 全部导出
  mysqlbinlog /logback/mysql-bin.000002  > /root/binlog_000002.sql
  # 指定pos位置
  mysqlbinlog  --start-position=219 --stop-position=1822  /logback/mysql-bin.000002  > /logback/binlog_000002.sql
  # 指定时间
  mysqlbinlog  --start-date="2022-05-18 15:00:19" --stop-date="2022-05-18 16:30:19"  /logback/mysql-bin.000002  > /logback/binlog_000002.sql      
  -d 参数可以指定数据库的名称
  
  # 如果binlog日志文件记录为ROW格式(将row格式的binlog日志文件base64解析后转入文件，-v代表换行展示)
  mysqlbinlog  --base64-output=decode-rows -v  --start-date="2022-05-18 15:00:19" --stop-date="2022-05-18 16:30:19" /logback/mysql-bin.000002 > /logback/binlog_000002.sql       
```
11. 删除文件
  1. 删除指定文件：purge binary logs to ‘mysqlbinlog.000001’
  2. 删除指定时间之前的文件：purge binary logs before ‘2020-04-28 00:00:00’;
  3. 清除所有文件：reset master;
  4. 可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉（参考目录1开启日志章节）
mysql相关
1. 备份数据库：mysqldump -u root -p binlog_demo > /root/binlog_demo.sql