Mysql之日志管理
1. mysql日志类型
1. error.log
记录mysql服务启动，运行，停止错误。默认启动。
配置文件中，使用log-error指定路径
2. general query log
生产环境不启动，影响mysql性能
3. 二进制日志
记录所有更改数据的语句，可以用于数据复制和数据恢复
4. slow log
mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的sql,会被记录到慢查询日志中。
2. mysql日志配置
1. error.log
1. 开启错误日志
[mysqld] log-error=/var/log/mysqld.log
2. 查看错误日志
show variables like "log_error"; 通过此命令查看错误日志的位置
查看日志内容，直接用linux命令就可以。
3. 删除错误日志
手动可以直接删除错误日志文件，但是需要执行一个命令，才能重新生成一个错误日志文件
mysqladmin -uroot -p123.com flush-logs
或者登陆到服务器上，执行flush logs;
2. general query log
1. 开启
set global general_log=1;
或者修改配置文件my.cnf
[mysqld] log=/var/lib/mysql/hostname.log
2. 结果输出到表
set global log_output='TABLE';
3. 查看日志
select * from mysql.general_log;
也可以直接用linux命令查看
4. 删除通用查询日志
可以直接删除，删除完以后需要重新生成新的文件。
mysqladmin -uroot -p123.com flush-logs
3. 二进制日志
1. 启动和设置二进制日志
默认情况下是关闭的，通过修改mysql的配置文件来启动和设置二进制日志,在my.cnf文件的mysqld组中，添加以下内容
binlog_format = mixed log-bin = /var/lib/mysql/logs/mysql-bin 此参数表示开启binlog功能，并指定路径名称 log-bin-index = /var/lib/mysql/logs/mysql-bin.index 此参数表示二进制索引文件的路径与名称 expire_logs_days = 10 表示日志保留10天，自动删除10天前的日志 binlog_do_db =student,bbs 表示只记录指定数据库的二进制日志 binlog_ignore_db =student,bbs 表示不记录指定数据库的二进制日志 max_binlog_cache_size=40G 表示二进制日志缓存的最大大小 binlog_cache_size= 40G 表示二进制日志使用的缓存大小，默认大小32k binlog_cache_use=100 表示二进制日志缓存的事务数量 binlog_cache_disk_use=100 使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
通过上面两个参数，来判断binlog_cache_size的大小是否合适。show global status like 'binlog_cache%';临时文件使用次数越少binlog_cache_size大小越合适 max_binlog_size = 1G 默认binlog文件大小为1G，超过该值会产生一个新的binlog文件 sync_binlog=0 表示mysql不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。性能最高，但是风险最大，服务器宕机，数据会有丢失。 sync_binlog=1 表示每次事务提交，mysql都会把binlog刷新到磁盘上。服务器宕机，只会丢失一个事务的数据，数据安全性最大，但是性能最差，影响服务器IO.
log-slave-update 从库不添加这个参数，表示从库从主库接收的更新不会记录到从库的binlog中
添加完成后，需要重新启动mysql进程
查看一下，启动二进制日志是否生效。 show variables like "log_%";
2. 查看二进制日志内容
1. 查看二进制文件内容，使用mysqlbinlog命令
1. 把binlog内容导出一下。
mysqlbinlog mysql-bin.000016 >all.sql 把所有库的所有表的修改导出
mysqlbinlog -d yangjianbo mysql-bin.000016 >yangjianbo.sql 指定库的修改导出
2. 指定开始位置和结束位置
mysqlbinlog mysql-bin.000016 --start-position=123 --stop-position=456 -r a.sql
3. 指定开始时间和结束时间
mysqlbinlog mysql-bin.000016 --start-datetime='2018-07-23 0:36:50' --stop-datetime='2018-07-23 05:03:50' -r b.sql
4. binlog日志内容，截取一个片段分析
#221031 23:13:14 server id 200 end_log_pos 183569656 CRC32 0x68f89d45 Query thread_id=25223902 exec_time=0 error_code=0
SET TIMESTAMP=1667229194/*!*/;
insert into gt_orders_log (log_id, type, order_id,
status, sub_status, user_id,
express_id, op_type, memo,
formerly, op_time, ip,
show_type)
values (0, 0, 2115528,
0, 0, 2032106,
0, 1, '订单已提交',
'', UNIX_TIMESTAMP(), '',
0)
/*!*/;
# at 183569656
#221031 23:13:14 server id 200 end_log_pos 183569687 CRC32 0x80446fe2 Xid = 3005339063
COMMIT/*!*/;
server id 200 服务器的id
end_log_pos 183569687 sql结束时的pos节点
thread_id=25223902 线程号
CRC32 字节流校验
0x80446fe2 存放校验码
xid 事务ID
2. 使用更方便查看的方式
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] ：偏移量(不指定就是0)
row_count ：查询总条数(不指定就是所有行)
1. 查看binlog文件中所有的语句
show binlog events in 'mysql-bin.000284'\G;
2. 从某个pos开始查询
show binlog events in 'mysql-bin.000231' from 154\G;
3. 从某个pos开始查询，查询10条
show binlog events in 'mysql-bin.000231' from 154 limit 1 \G;
3. binlog什么时候被截断
1. 重启mysql，会截断binlog
2. flush logs,会产生一个新的binlog文件，原来的binlog还存在
3. reset master 删除所有binlog 切记使用这个命令
4. 删除部分日志
purge binary logs to 'mysql-bin.000005'; mysql-bin.000005之前的binlog都会被删除，会删除比指定编号小的所有日志文件
purge binary logs before '2013-04-22 22:00'; 删除2013-04-22 22点之前的binlog.
4. 使用二进制日志恢复数据库
语法： mysqlbinlog [option] filename |mysql -uuser -ppass
option: --start-date 起始时间点
--stop-date 结束时间点
--start-position 开始位置
--stop-position 结束位置
例子：
mysqlbinlog --stop-date="2016-12-30 00:10:53" /var/lib/mysql/logs/mysql-bin.000012 | mysql -uroot -p123.com
/usr/bin/mysqlbinlog --start-position=538 --stop-position=646 --database=ops /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123.com
5. 暂时停止二进制日志功能
登录到mysql中，执行命令：
set sql_log_bin=0; 关闭binlog记录，只针对当前会话，其它用户连接mysql不受影响
set sql_log_bin=1; 开启binlog记录
6. binlog的格式
1. Statement
只记录执行的SQL,不需要记录每一行数据的变化，极大的减少了binlog的日志量，避免了大量的IO操作，提升了系统的性能
由于只记录SQL,如果SQL中包含一些函数，可能会导致结果不一致
2. ROW
不记录SQL语句上下文，只记录某一条记录被修改成什么样子
日志量太大，会带来IO性能问题
3. Mixed
Statement和ROW的结合体
4. 修改binlog格式
set binlog_format='MIXED'; 只对当前会话有效,重启mysql失效
set global binlog_format='MIXED'; 全局有效，重启mysql失效
修改my.cnf配置文件，添加binlog_format=MIXED
7. 常用binlog日志操作命令
1. 查看所有binlog日志列表
show master logs;
show binary logs;
2. 查看master状态
show master status;
显示binlog日志的名称和最后一个操作事件positon值
3. flush刷新log日志
flush logs;
产生一个新的binlog日志文件
4. 清空所有binlog日志
reset master;
8. binlog的删除
1. 自动删除
show variables like 'expire_logs_days';
set global expire_logs_days=3; 表示日志保留3天
2. 手动删除
reset master; 删除所有的binlog日志
reset slave; 删除slave的中继日志
purge master logs before '2019-10-30 17:00:00'; 删除指定日期之前的binlog日志文件
purge master logs to 'binlog.000002'; 删除所有小于000002的binlog日志
4. slow log
1. 默认mysql的慢查询日志是没有开启的。
2. 查看慢查询日志是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time%'; 查看超过多少秒算慢查询语句
3. 开启慢查询日志
set global slow_query_log=1; 这是只对当前mysql生效，mysql重启会失效。
set global long_query_time=1; 设置慢查询时间（大于1s的sql语句就会被记录到日志中），默认这个参数值为10s。
set global log_queries_not_using_indexes=on; 记录没有使用索引的查询
4. 慢查询永久生效
修改配置文件my.cnf，添加如下内容：
long_query_time = 1 超过1s记录 slow_query_log = 1 slow_query_log_file = /server/mysql/log/slow.log
5. 日志内容分析
# Time: 2021-05-21T16:06:01.825934+08:00
# User@Host: rw_all_db[rw_all_db] @ [192.168.1.231] Id: 58138736
# Query_time: 1.578944 Lock_time: 0.000264 Rows_sent: 58897 Rows_examined: 164619
SET timestamp=1621584361;
SELECT p.product_id,
p.status,
(SELECT SUM(s.stock-s.fix_stock) FROM
gt_product_spec s WHERE s.product_id = p.product_id) available_stock
FROM gt_products p
WHERE (p.is_stop = 0 OR p.is_stop is null)
AND p.is_check = 1
AND p.status IN (0,1);
6. mysql日志分析工具mysqldumpshow
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
例子：
1. 得到返回记录集最多的10个SQL
/opt/mysql-5.7.25-linux-glibc2.12-x86_64/bin/mysqldumpslow -s r -t 10 slow.log-20200514 | more
2. 得到访问次数最多的10个SQL
/opt/mysql-5.7.25-linux-glibc2.12-x86_64/bin/mysqldumpslow -s c -t 10 slow.log-20200514 | more
3. 得到按照时间排序的前10条里面含有左连接的查询语句
/opt/mysql-5.7.25-linux-glibc2.12-x86_64/bin/mysqldumpslow -s t -t 10 -g "left join" slow.log-20200514 | more
7. 删除慢查询日志
可以直接删除，删除完以后需要重新生成新的文件。
mysqladmin -uroot -p123.com flush-logs
浙公网安备 33010602011771号