mysql日志
MySQL 支持丰富的日志类型,如下:
-
事务日志:transaction log
事务日志的写入类型为"追加",因此其操作为"顺序IO";通常也被称为:预写式日志 write ahead logging事务日志文件: ib_logfile0, ib_logfile1
-
错误日志 error log
-
通用日志 general log
-
慢查询日志 slow query log
-
二进制日志 binary log
-
中继日志 reley log,在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件
语法:
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type
例子:
delimiter // CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED) BEGIN SELECT * FROM students WHERE stuid = id; END// delimiter ; call selectById(2);
事务日志:transaction log
-
redo log:实现 WAL(Write Ahead Log) ,数据更新前先记录redo log
-
undo log:保存与执行的操作相反的操作,用于实现rollback
事务型存储引擎自行管理和使用,建议和数据文件分开存放
Innodb事务日志相关配置:
show variables like '%innodb_log%'; innodb_log_file_size 50331648 #每个日志文件大小 字节 innodb_log_files_in_group 2 #日志组成员个数 innodb_log_group_home_dir ./ #事务文件路径 ll -h /var/lib/mysql
事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2 select @@innodb_flush_log_at_trx_commit; #查看默认值 1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性 0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务 2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系统或停电可能导致最后一秒的交易丢失
级别 | 0 | 1 | 2 |
---|---|---|---|
安全性 | 较高 | 最高 | 最高 |
性能 | 最高 | 最差 |
双 1 调优
高并发业务行业最佳实践,是使用第三种折衷配置(=2):
2.配置为2和配置为0,安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,只要操作系统不奔溃,也绝对不会丢数据
双1设置:
说明:
-
设置为1,同时sync_binlog = 1表示最高级别的容错 (二进制日志)
-
innodb_use_global_flush_log_at_trx_commit=0 时,将不能用SET语句重置此变量( MariaDB 10.2.6 后废弃)
演示:
[root@localhost ~]# mysql -uroot -p'自己的密码' 随便数据库 < testlog.sql #生成测试环境 (root@localhost) [hellodb]> call sp_testlog; #生成 一百万条数据 修改参数: set global innodb_flush_log_at_trx_commit=1; select @@innodb_flush_log_at_trx_commit; call sp_testlog;
2.错误日志
错误日志
-
mysqld启动和关闭过程中输出的事件信息
-
mysqld运行中产生的错误信息
-
event scheduler运行一个event时产生的日志信息
-
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误文件路径
SHOW GLOBAL VARIABLES LIKE 'log_error' ; yum 安装 cat /var/log/mysqld.log
记录哪些警告信息至错误日志文件*
#CentOS7 mariadb 5.5 默认值为1 #CentOS8 mariadb 10.3 默认值为2 log_warnings=0|1|2|3... #MySQL5.7之前 log_error_verbosity=0|1|2|3... #MySQL8.0
例子:
SHOW GLOBAL VARIABLES LIKE 'log_warnings';
通用日志:记录对数据库的通用操作,包括:错误的SQL语句
通用日志可以保存在:file(默认值)或 table(mysql.general_log表)
通用日志相关设置
general_log=ON|OFF general_log_file=HOSTNAME.log log_output=TABLE|FILE|NONE
例子:
范例: 启用通用日志并记录至文件中 select @@general_log; #默认没开启 set global general_log=1; #开启 SHOW GLOBAL VARIABLES LIKE 'log_output'; #默认通用日志存放在文件中 select @@general_log_file; #通用日志存放的文件路径
慢查询日志:记录执行查询时长超出指定时长的操作
慢查询相关变量
slow_query_log=ON|OFF #开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件 long_query_time=N #慢查询的阀值,单位秒,默认为10s slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件 log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan, query_cache,query_cache_miss,tmp_table,tmp_table_on_disk #上述查询类型且查询时长超过long_query_time,则记录日志 log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语 句是否记录日志,默认OFF,即不记录 log_slow_rate_limit = 1 #多少次查询才记录,mariadb特有 log_slow_verbosity= Query_plan,explain #记录内容 log_slow_queries = OFF #同slow_query_log,MariaDB 10.0/MySQL 5.6.1 版后已删除 set global slow_query_log=1; #开启 set long_query_time=1; select sleep(10)
-
记录导致数据改变或潜在导致数据改变的SQL语句
-
记录已提交的日志
-
不依赖于存储引擎类型
功能:通过"重放"日志文件中的事件来生成数据副本
注意:建议二进制日志和数据文件分开存放
二进制日志记录三种格式
基于"语句"记录:statement,记录语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
基于"行"记录:row,记录数据,日志量较大,更加安全,建议使用的格式,MySQL8.0默认格式
混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上
show variables like 'binlog_format';
二进制日志文件格式
有两类文件 1.日志文件:mysql|mariadb-bin.文件名后缀,二进制格式,如: on.000001,mariadb-bin.000002 2.索引文件:mysql|mariadb-bin.index,文本格式,记录当前已有的二进制日志文件列表
二进制日志相关的服务器变量:
sql_log_bin=ON|OFF: #是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项 log_bin=mysql-bin 默认是关闭 #指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以 binlog_format=STATEMENT|ROW|MIXED: #二进制日志记录的格式,mariadb5.5默认STATEMENT max_binlog_size=1073741824: #单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G #说明:文件达到上限时的大小未必为指定的精确值 binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接) max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。 sync_binlog=1|0: #设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘 expire_logs_days=N: #二进制日志可以自动删除的天数。 默认为0,即不自动删除
在线查看 二进制
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 例子: show binlog events in 'mysql-bin.000001' from 6516 limit 2,3
离线查看二进制日志
mysqlbinlog [OPTIONS] log_file… --start-position=# 指定开始位置 --stop-position=# --start-datetime= #时间格式:YYYY-MM-DD hh:mm:ss --stop-datetime= --base64-output[=name] -v -vvv # at 328 #151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1446712300/*!*/; CREATE TABLE tb1 (id int, name char(30)) /*!*/; 事件发生的日期和时间:151105 16:31:40 事件发生的服务器标识:server id 1 事件的结束位置:end_log_pos 431 事件的类型:Query 事件发生时所在服务器执行此事件的线程的ID:thread_id=1 语句的时间戳与将其写入二进制文件中的时间差:exec_time=0 错误代码:error_code=0 事件内容: GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID
例子:
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -v mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01- 30 20:35:22" mariadb-bin.000003 -vvv
二进制日志事件的格式:
# at 328 #151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1446712300/*!*/; CREATE TABLE tb1 (id int, name char(30)) /*!*/; 事件发生的日期和时间:151105 16:31:40 事件发生的服务器标识:server id 1 事件的结束位置:end_log_pos 431 事件的类型:Query 事件发生时所在服务器执行此事件的线程的ID:thread_id=1 语句的时间戳与将其写入二进制文件中的时间差:exec_time=0 错误代码:error_code=0 事件内容: GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID