MySQL系列文章包含了软件安装、具体使用、备份恢复等内容,主要用于记录个人的学习笔记,主要使用的MySQL版本为5.7.28,服务器系统版本为CentOS 7.5。本章节内容为InnoDB核心特性的binlog记录和恢复。
错误日志错误日志主要是记录MySQL从启动依赖,所有的状态:警告、错误等。为用户定位数据库问题提供帮助。
错误日志默认是开启状态,默认情况下在用户指定的datadir目录下,可用以下方式进行查询
select @@datadir;
错误日志文件具体路径为:
@@datadir/${hostname}.err
如果需要将错误日志放置在其它目录,可以在my.cnf中进行设置:
vi /etc/my.cnf[mysqld] log_error=/目录/文件
请确保该目录有读写权限
慢日志slowlog慢日志的作用是记录MySQL运行过程中较慢的语句,通过一个文本的文件记录下来,帮助我们进行语句优化工具查询。
慢日志默认是没有开启的,可以通过以下语句进行查询慢日志的状态:
select @@slow_query_log;
值为0表示没有开启,值为1表示开启。
用户可以使用以下命令临时开启慢日志记录:
set global slow_query_log=1;
如需要慢查询日志配置永久生效,可在my.cnf中添加以下内容即可:
vi my.cnfslow_query_log=1 # 启动慢日志
slow_query_log_file=/data/3306/db01-slow.log #设置慢日志文件位置
long_query_time=0.1 # 操作时间超过0.1s的事件记录到慢日志中
log_queries_not_using_indexes=1 # 记录没有使用到索引的查询语句
配置后,应重启mysqld服务生效。
查询慢日志文件所在路径:
select @@slow_query_log_file;
slowlog
模拟慢日志
导入测试表t100w(微信公众号发送t100w获取该文件)
source t100w.sqluse test;show tables;
模拟慢查询:
select * from t100w where id>10 and id <20;
该语句执行用了0.42s,超过了我们定义的0.1s,所以会被记录到慢日志用。
查看慢日志:
mysqldumpslow db01-slow.log
如果慢日志过多的时候,可以使用以下命令统计出现最多个5个慢语句类别:
mysqldumpslow -s c -t 5 db01-slow.log
如:select * from t100w where id > 10 and id < 20 和 select * from t100w where id >100 and id < 200是同类别的。
binlogbinlog的作用是记录数据库变化(DDL,DCL、DML)性质的日志,binlog是逻辑层性质日志。在数据恢复,主从复制中应用。
在mysql8.0之前的版本,binlog默认是没有开启的,生产中建议开启binlog,并将日志信息保存到导读的盘或者分区中。
查看binlog的状态:
mysql> select @@log_bin;+-----------+| @@log_bin |+-----------+| 0 | # 0为未开启,1未开启+-----------+1 row in set (0.00 sec)
开启binlog:
vi my.cnf[mysqld]server_id=6 # 设置服务器idlog_bin=on # 启动binloglog_bin=/binlog/3306/mysql-bin # 设置文件位置和文件前缀sync_binlog=1 # 每次事务提交,必然保证binlog cache中的日志落到磁盘binlog_format=row # 以row方式记录
重启mysqld服务即可生效。
MySQL“双一模式”:
sync_binlog=1;
取值为0:log buffer每秒写入日志文件log file并刷新flush到磁盘。这种情况下,MySQL的日志刷写操作和事务提交操作没有关系。因此MySQL的性能是最好的时刻。不过不安全
取值为1:每次事务提交时,log buffer会被写入到日志文件并且还要刷写到磁盘上。由于每次事务都要提交到I/O设备,因此会慢一点,不过是最安全的。
取值为2:0和1的中间效果,即每次的事务提交会写入log buffer,而刷写到磁盘则是一秒进行一次。性能属于一般。
innodb_flush_log_at_trx_commit=1;
取值为0:MySQL自己不主动同步,依赖操作系统本身不定期把文件内容刷新到磁盘。性能最佳
取值为1:每次事务提交后将binlog_cache中的数据强制写入磁盘bin log日志中,是最慢的,但是最安全
取值 >1:当进行n次事务提交后,MySQL将binlog_cache中的数据强制写入磁盘中。
当两个参数都设为1,这适用于那些数据安全要求比较高的场合,同时要求磁盘等也比较好才行。
临时关闭当前会话的binlog记录:
set sql_log_bin=0;
binlog
binlog_format
binlog_format参数设置了记录内容的方式
1、statement(5.6默认)SBR(statement based replication):语句模式原封不动的记录当前DML
2、row(5.7默认值)RBR(ROW base replication):记录数据行的变化(用户看不懂,需要工具分析)
3、mixed(混合)MBR(mixed based replication)模式:以上两种模式的混合
SBR和RBR模式的对比
statement:可读性比较高,日志量比较少,但是不够严谨
row:可读性低,日志量大,足够严谨
比如update t1 set xxx=xxx where id>1000 此时statement只记录了该命令,而row会记录这些行的变化
为什么说row模式严谨
id name intime
insert into t1 values(1,'zs',now()) 如果是statement,now会是当前时间,而row记录了当时的时间
binlog
event事件
事件是二进制日志的最小记录单元,对于DDL,DCL,一个语句就是event,对于DML来讲,只记录已提交的事务。
事件由三部分构成:
1、事件的开始标识
2、事件内容
3、事件的结束标识
Position:开始标识:at 194结束标识:eng_log_pos 254开始和结束标识,是否个事件在binlog的相对位置号位置号(position)的作用是为了方便我们截取事件
以下例子,就被分为了4个event
startposition(开始) stopposition(结束)begin; 120 - 340DML1 340 - 460DML2 460 - 550commit; 550 - 760
binlog
查询binlog
查看binlog文件:
mysql> select @@log_bin_basename;+------------------------+| @@log_bin_basename |+------------------------+| /binlog/3306/mysql-bin |+------------------------+
查看所有的binlog文件:
mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 201 || mysql-bin.000002 | 1931 || mysql-bin.000003 | 1599 || mysql-bin.000004 | 1337 || mysql-bin.000005 | 70048 || mysql-bin.000006 | 21103050 || mysql-bin.000007 | 194 |+------------------+-----------+7 rows in set (0.00 sec)
查看当前使用的binlog文件:
mysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql-bin.000007 | 194 | | | |+------------------+----------+--------------+------------------+--------------------------------------------+
查看某个binlog文件的内容:
show binlog events in 'mysql-bin.000002';
查看文件内容:
mysqlbinlog mysql-bin.000004 > /tmp/123 # 每个at都是一个事件的开始# DDL可读模式,仅限读mysqlbinlog --base64-output=decode-row -vvv mysql-bin.000004 > /tmp/456
binlog
binlog维护操作
查看默认日志大小设置(超过会写到下一个文件)
mysql> select @@max_binlog_size;+-------------------+| @@max_binlog_size |+-------------------+| 1073741824 |+-------------------+1 row in set (0.00 sec)
删除日志
# 手动删除purge binary logs to 'mysql-bin.00010' # 删除1-9reset master; # 全部清空,如果在主库上操作,主从必宕# 设置自动删除 默认是0天,代表永不删除mysql> select @@expire_logs_days;+--------------------+| @@expire_logs_days |+--------------------+| 0 |+--------------------+1 row in set (0.00 sec)set global expire_logs_days=7; # 设置删除周期为7天,生产中请设置合适的值
滚动日志
# 生成一个新的日志flush logs;
end
![c5405e774454c5c50cc3a2f19bfe85c8.gif](https://i-blog.csdnimg.cn/blog_migrate/38c7324ea191eb02c7db85869286b7eb.gif)