mysql的binlog的操作

mysql的binlog日志

binlog日志的好处，binlog可以使我们进行数据恢复和主从复制的操作。
目前我使用的版本是mysql 5.7高版本的可能会有一些不同。但是基本都是一样的。

开启binlog日志

1、 查看是否开启了binlog日志

show variables like "%log_bin%"

查看log_bin 默认情况下是off（关闭）

2、 编辑配置文件

vim /etc/my.cnf

[mysqld]
# 设置唯一id
server_id = 2
#设置日志路径，注意路经需要mysql用户有权限
log-bin=mysql-bin
# 设置日志格式
binlog_format = MIXED
#设置binlog清理时间
expire_logs_days = 30
#binlog每个日志文件大小
max_binlog_size = 100M
#binlog缓存大小
binlog_cache_size = 4M
#最大binlog缓存大小
max_binlog_cache_size = 512M

扩展： binlog日志的各个级别详情
简单描述：
Row Level： 记录的方式是行，即如果批量修改数据，记录的不是批量修改的SQL语句事件，而是每条记录被更改的SQL语句
Statement Level： 记录每一条修改数据的SQL语句
Mixed： MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
查看binlog类型：

show global variables like "%binlog_format%";

详情: https://blog.youkuaiyun.com/qq_38826019/article/details/119718031
3、 重启mysql

systemctl restart mysqld

log_bin 变为on 表示成功。

show variables like "%bin_%"

通过binlog实现数据恢复

1、查看binlog当前在使用那个文件

show master status;

2、截断日志文件，生成新的日志文件。

sql命令行：
flush-logs

日志文件从 mysql-bin.000025 变为 mysql-bin.000026，而 mysql-bin.000015 则记录着刚刚 insert 命令的日志。

3、 查看日志文件的内容
注意：在查看日志内容的时候，不建议查看当前数据库正在操作的日志文件，可能会出现问题。所以一定要记着flush-logs

show binlog events in 'mysql-bin.000025'; 

4、 通过Binlog恢复数据

linux命令行：
/usr/bin/mysqlbinlog --database=要恢复的数据库名 /var/lib/mysql/bin-log-1.000025 | /usr/bin/mysql -uxxx -pxxxx -v 要恢复的数据库名

/usr/bin/mysqlbinlog 为binlog命令
--database=hello 指定数据库为hello的日志
/var/lib/mysql/bin-log-1.000001 为binlog日志
| 为管道符
/usr/bin/mysql -uroot -p123456zy -v 要恢复的数据库名 
 连接mysql、并指定需要恢复的数据库 -v 为显示详细信息

5、 通过指定的位置恢复数据

linux命令行：
/usr/bin/mysqlbinlog --start-position=573 --stop-position=718 --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello

--start-position=573 开始位置
--stop-position=718  结束位置

6、 通过指定时间恢复数据

linux命令行：
/usr/bin/mysqlbinlog --start-datetime="2021-06-27 20:58:18" --stop-datetime="2021-06-27 20:58:35" --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello

--start-datetime="2021-06-27 20:58:18" 开始时间

--stop-datetime="2021-06-27 20:58:35" 结束时间

查看时间信息：

linux命令行：
/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/bin-log-1.000001