1. 通用日志 **
-
作用 :
记录MySQL发生过的所有命令. 一般可以用作审计.
-
1.1 配置
general_log=ON general_log_file=/data/mysql/data/db01.log
2. 错误日志
- 作用:
记录启动\关闭\日常运行过程中,状态信息,警告,错误
-
2.2 配置
默认就是开启的: /数据路径下/主机名.err 手工设定: vim /etc/my.cnf log_error=/var/log/mysql.log log_timestamps=system 重启生效 show variables like 'log_error';
-
2.3 如何查看?
perror 报错号
获取精确错误信息
关注 日志中[ERROR],上下文
-
3. 二进制日志
- 作用:(1)
备份恢复必须依赖二进制日志
(2)主从环境必须依赖二进制日志
-
3.1 配置
注意:MySQL(5.7)默认是没有开启二进制日志的。 基础参数查看: 开关: [(none)]>select @@log_bin; 日志路径及名字 [(none)]>select @@log_bin_basename; server id: [(none)]>select @@server_id; 二进制日志格式: [(none)]>select @@binlog_format; 双一标准之二:在事务提交时,立即刷新binlog到磁盘 [(none)]>select @@sync_binlog; #### 创建日志目录 mkdir /data/binlog chown -R mysql.mysql /data/binlog #### 修改配置文件 vim /etc/my.cnf server_id=6 ----->5.6中,单机可以不需要此参数 log_bin=/data/binlog/mysql-bin binlog_format=row #### 重启数据库生效 [root@db01 mysql]# /etc/init.d/mysqld restart
-
3.2 binlog
- 日志记录格式
DDL
:原封不动的记录当前DDL(statement语句方式)。
DCL
:原封不动的记录当前DCL(statement语句方式)。
DML
:只记录已经提交的事务DML
- DML三种记录方式
- binlog_format参数控制
(1)
statement
(5.6默认)SBR
(statement based replication) :语句模式.
执行什么就记录什么
(2)ROW
(5.7 默认值)RBR
(ROW based replication) :行模式.
记录数据行的变化.
(3)mixed(混合)MBR(mixed based replication)模式 :混合模式.自动判断用什么方式记录
- binlog_format参数控制
- 日志记录格式
-
SBR与RBR模式的对比
SBR:日志量少,可读性强. 有可能出现记录错误.
RBR:日志量大,可读性弱. 记录更严谨.
-
3.3 事件(events)
- 事件简介
binlog中最小的记录单元是事件(event).
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
- 事件简介
-
3.4 事件的组成
- 三部分构成:
(1)事件的开始标识
(2)事件内容
(3)事件的结束标识
Position:某个事件在binlog中的相对位置号,为了方便我们截取事件
- 三部分构成:
-
2.5 二进制日志管理
#### 查看binlog状态 mysql> show variables like '%log_bin%'; #### 二进制日志基本信息查看 mysql> show binary logs; mysql> show master status ; #### 二进制日志事件查看(events) mysql> show binlog events in 'mysql-bin.000002'; Log_name:binlog文件名 Pos:开始的position ***** Event_type:事件类型 Format_desc:格式描述,每一个日志文件的第一个事件,多用户没有意义,MySQL识别binlog必要信息 Server_id:mysql服务号标识 End_log_pos:事件的结束位置号 ***** Info:事件内容***** [root@db01 /data/binlog]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000002' "|grep drop #### 二进制日志内容详细查看 [root@db01 /data/binlog]# mysqlbinlog /data/binlog/mysql-bin.000002 [root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000002 |grep -v SET [root@db01 /data/binlog]# mysqlbinlog -d xxx /data/binlog/mysql-bin.000002 |grep -v SET
-
2.5 二进制日志截取及数据恢复
思路: 1. 分析日志: 得到截取日志起点和终点. mysql> show binlog events in 'mysql-bin.000002'; 起点: 找到建库的位置 : 219 终点: 删除之前位置点 : 787 2. 进行截取 mysqlbinlog --start-position=219 --stop-position=787 /data/binlog/mysql-bin.000002 >/tmp/binlog.sql 3. 恢复日志 mysql> set sql_log_bin=0; ----> 临时binlog日志记录 mysql> source /tmp/binlog.sql; mysql> set sql_log_bin=1; ##### 需要的日志在多个日志文件怎么截取? 方法1:使用GTID 方法2 : --start-datetime=xxxx --stop-datetime=xxxxx file1 file >aa.sql ##### 以上案例,如果xxx库在2年前创建的,怎么恢复? 最近的一次全备+备份之后一直到误删除之前的所有日志 full+binlog
-
4. 基于GTID方式记录的二进制日志管理
-
4.1 GTID介绍
从MySQL 5.6 版,加入的新特性.
唯一编号
,用来标识每一个二进制日志"事务".
事务1: begin; dml1;dml2;commit; 会产生一个GTID编号.
事务2: DDL或者DCL,一个event就是一个事务.会产生一个GTID编号. -
4.2 GTID 编号格式
server_uuid:TID [root@db01 /data/mysql/data]# cat auto.cnf [auto] server-uuid=79f1839c-0439-11ea-83bb-000c29d354c0
-
4.3 GTID开启
gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON
-
4.4 gtid模式日志管理
(1) 查看事件 mysql> show binlog events in 'mysql-bin.000003'; SET @@SESSION.GTID_NEXT= '79f1839c-0439-11ea-83bb-000c29d354c0:1' (2) 截取日志 mysqlbinlog --include-gtids='79f1839c-0439-11ea-83bb-000c29d354c0:1-3' --exclude-gtids='79f1839c-0439-11ea-83bb-000c29d354c0:2' --skip-gtids
注意: 开了gtid模式 ,一定要添加–skip-gtids ,因为mysql会自动检查gtid幂等性
- 4.5 二进制日志的其他操作
-
4.5.1 二进制日志滚动
(1) 重启MySQL (2) flush logs; (3) max_binlog_size=1073741824 达到1G
-
每天备份日志: flush logs; – >cp
-
4.5.2 二进制日志删除
(1) 配置自动删除 mysql> select @@expire_logs_days; 至少预留一轮全备周期+1的过期时间 生产一般建议保留至少两轮全备周期+1的时间 (2) 手工删除 Examples: PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; (3) 全部删除 mysql> reset master; 注意:不要手工 rm binlog文件
5. 慢日志 ******
-
作用:
记录MySQL运行过程中执行较慢的语句. 辅助管理员优化语句.
数据库较慢的场景: 1. 应急性 show processlist; -----> select -----> explain 2. 持续性 slowlog ---> SQL ----> select ----> explain
-
5.1 配置
mysql> show variables like '%slow%'; slow_query_log=ON slow_query_log_file=/data/mysql/db01-slow.log long_query_time=0.1 log_queries_not_using_indexes
-
5.2 模拟慢语句,分析
[root@db01 /data/mysql]# mysqldumpslow -s c -t 10 /data/mysql/db01-slow.log
-
5.3 推荐工具:
pt-query-digest + Anemometer
地址:https://www.cnblogs.com/xuanzhi201111/p/4128894.html
pt-query-digest /data/mysql/db01-slow.log
ELK