文章目录
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日志
-
进入mysql的配置文件:
my.cnf -
方式一：
#第一种方式: #开启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 -
方式二：
#第二种方式: #此一行等同于上面log_bin三行 log-bin=/var/lib/mysql/mysql-bin #配置serverid server-id=1 -
注意：配置后，记得重启mysql服务。
-
解析：
这里的
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 = 512m3. binlog文件开启binlog后，会在数据目录（默认）生产host-bin.n（具体binlog信息）文件及host-bin.index索引文件（记录binlog文件列表）。当binlog日志写满(binlog大小max_binlog_size，默认1G),或者数据库重启才会生产新文件，但是也可通过手工进行切换让其重新生成新的文件（flush logs）；另外，如果正使用大的事务，由于一个事务不能横跨两个文件，因此也可能在binlog文件未满的情况下刷新文件。
-
-
binlog日志模式区别
-
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日志
show binlog events in 'mysql-bin.000001';- 日志里面包含我们所执行的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操作
- 查看详情：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日志
-
查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值:
- 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) -
先备份该日志，防止之后的操作对该日志产生影响
- 进入该日志所在的文件夹 cd /var/lib/mysql
- 备份文件到 /logback 文件夹 cp -v /var/lib/mysql/mysql-bin.000002 /logback/
-
刷新日志，之后的操作不再在000002中操作（方便我们之后恢复数据时阅读000002）：flush logs;
-
查看日志：
- 方式一：进入日志所在文件夹查看:mysqlbinlog mysql-bin.000002
- 方式二：show binlog events in ‘mysql-bin.000002’(推荐) 或者： show binlog events in ‘mysql-bin.000002’\G;
-
-
通过备份文件恢复上次备份之前的数据
-
进入数据库管理系统创建数据库binlog_demo（因为之前备份时，没有备份创建数据库语句）
-
选择数据库：use binlog_demo;
-
执行备份sql: source /root/binlog_demo.sql
-
查看数据库（此时数据库中的数据已经恢复到上次备份之前）
-
-
通过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，进行数据恢复
- 执行sql语句：mysql -uroot -p binlog_demo </logback/binlog_000002.sql
-
3. 常用指令
-
binlog相关
-
查看是否开启：SHOW VARIABLES LIKE ‘log_bin’;
-
查看所有binlog日志情况：show variables like ‘%log_bin%’
-
查看所有binlog日志列表：show master logs;
-
查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值：show master status;
-
刷新log日志，自此刻开始产生一个新编号的binlog日志文件：flush logs;
- 注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
-
重置(清空)所有binlog日志：reset master;
-
查看某个binlog日志内容，常用有两种方式：
-
查看某个binlog日志内容，常用有两种方式：
-
把binlog日志文件转存为sql文件
mysqlbinlog "文件名" > "test.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
-
-
删除文件
- 删除指定文件：purge binary logs to ‘mysqlbinlog.000001’
- 删除指定时间之前的文件：purge binary logs before ‘2020-04-28 00:00:00’;
- 清除所有文件：reset master;
- 可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉（参考目录1开启日志章节）
-
-
mysql相关
- 备份数据库：mysqldump -u root -p binlog_demo > /root/binlog_demo.sql
2255
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
