1.二进制日志(Binary Log)
- 包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE);
- 包含关于每个更新数据库(DML)的语句的执行时间信息
- 不包含没有修改任何数据的语句,如果需要启用该选项,需要开启通用日志功能
- 主要目的是尽可能的将数据库恢复到数据库故障点,因为二进制日志包含备份后进行的所有更新
- 用于在主复制服务器上记录所有将发送给从服务器的语句
- 启用该选项数据库性能降低1%,但保障数据库完整性,对于重要数据库值得以性能换完整。有些类似于Oracle开启归档模式。
2.启动和设置二进制日志
默认是关闭,可以通过修改 my.ini 开启,在 [mysqld] 下添加设置:
#定义开启,可以为其设置路径,默认路径在 /var/lib/mysql 即数据库目录,mysql-bin是自定义名称,
#文件名称为 mysql-bin.000001,mysql-bin.000002,mysql-bin.index
log_bin = mysql-bin
#版本5.7之后需要设置,随意设定,但集群环境下要配置不同的值,不能重复
server_id = 47
#清除过期日志时间,默认是0,即需手动删除
expire_logs_days = 10
#定义单个文件的限制大小
max_binlog_size = 100M
# 有三种格式 row、statment、mixed
binlog_format=row # binlog 日志格式
binlog_rows_query_log_events = 1 # 将原始的操作sql记录写入事件中
3.查看是否已经启动
#mysql服务器下执行,结果可以查看日志位置 log_bin_basename,log_bin_index,log_bin
show variables like 'log_%';
4.查看文件个数和文件名
#mysql服务器下执行
#每启动一次mysql服务,将会产生一个新的日志文件
show BINARY logs;
#查看当前日志
show master status;
#刷新日志,生成新的
flush logs;
6.查看二进制日志文件
#命令行下执行
mysqlbinlog /var/lib/mysql/mysql-bin.000001
#如果乱码则使用
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000001
mysql的命令行查看日志最原始的insert sql语句
mysql> show binlog events in 'mysql-bin.000216';
6.删除二进制日志文件
#mysql服务器下执行
#清除所有的二进制日志文件
reset master;
#删除文件名编号比指定文件名编号小的所有日志文件
purge MASTER logs TO "mysql-bin.000003";
#删除指定日期以前的所有日志文件
#删除2018.3.22之前的日志文件
purge MASTER logs before '20180322';
7.使用二进制文件恢复数据库
#恢复数据到 2018-03-22 17:05:34 的状态
shell> mysqlbinlog --stop-datetime="2018-03-22 17:05:34" /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
--start-datetime 起始时间 时间指那行有时间且最后有 Xid 的那行的时间
--stop-datetime 结束时间
--start-position 起始位置 位置指文件 at 后面的数字
--stop-position 结束位置
8.暂时停止二进制日志功能
#暂停
mysql> set sql_log_bin = 0;
#恢复
mysql> set sql_log_bin = 1;
9.binlog恢复数据的几种情况
1.数据库丢失或者drop
- 恢复系统自动备份
- 用binlog恢复系统备份时间到此刻的的数据(drop操作需要binlog删除drop记录)
2.误操作update,delete
- 恢复系统自动备份
- 在binlog日志中删除误操作的操作语句,执行binlog恢复其他的数据