MYSQL日志

post thumb
Mysql
作者 Louis 发表于 2018年6月11日

摘要:MYSQL的日志,事务日志,错误日志,通用日志,慢查询日志,二进制日志的学习过程,其中的相关变量均可以参考官网配置

日志

日志分类:

  • 事务日志: transaction log
  • 中继日志: reley log
  • 错误日志: error log
  • 通用日志: general log
  • 慢查询日志: slow query log
  • 二进制日志: binary log

命令日志:]#cat .mysql.history,当前终端的命令日志不会保存,退出后自动保存至改文件

事务日志

  • 事务日志:transaction log

  • 事务型存储引擎自行管理和使用,建议和数据文件分开存放

redo log 已经做的事务日志 undo log 未做的事务日志

Innodb事务日志相关配置:

innodb_log_file_size是一个global,但不是dynamic的变量,要想生效,必须写入配置文件中。

show variables like '%innodb_log%';
innodb_log_file_size 5242880 -- 每个日志文件大小,/var/lib/mysql/ib_logfile
innodb_log_files_in_group 2  -- 日志组成员个数,建议调大
innodb_log_group_home_dir ./ -- 事务文件路径

调大日志文件,事务日志文件路径,(建议单独存放)

$ mkdir -pv /data/mysqllogs/
$ chmod mysql.mysql /data/mysqllogs/ 
$ vim /etc/my.cnf
[mysqld]
innodb_log_files_in_group=5  #仅修改日志数会导致服务起不来
innodb_log_group_home_dir=/data/mysqllogs/
$ systemctl restart mariadb
  • 中继日志:relay log

主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件

错误日志

错误日志:

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误日志相关配置SHOW GLOBAL VARIABLES LIKE 'log_error';

  • 错误文件路径,配置文件位置:/etc/my.cnf

log_error=/PATH/TO/LOG_ERROR_FILE

  • 是否记录警告信息至错误日志文件

log_warnings=1|0 默认值1

通用日志

**通用日志:**记录对数据库的通用操作,包括错误的SQL语句

  • 文件:file,默认值
  • 表:table
  • general_log: OFF,默认值,全局变量,global,dynamic
  • log_output: FILE,默认值

通用日志相关设置:

show variables like 'general_log%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | OFF           |
| general_log_file | localhost.log |
+------------------+---------------+
set global general_log=on;
show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |	 -- TABLE|FILE|NONE
+---------------+-------+
set global log_output=table;

实验

目标:生成大文件的事务日志文件,了解事务日志的原理,及相应的特殊性

准备工作

每个表单独使用一个表空间存储表的数据和索引,在配置文件/etc/my.cnf中,加入innodb_file_per_table=ON,便于日志的观察

$ vim /etc/my.cnf
[mysqld]
innodb_file_per_table=ON

生成大文件脚本

use testdb
-- 脚本内容
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure pro_testlog()
begin
declare i int;
set i = 1;
while i < 100000
do insert into testlog(name,age) values(concat('wang',i),i) ;
set i = i +1;
end while;
end$$

delimiter ;

使用相关脚本实验

start transaction;
-- 生成大数据事务
call pro_testlog; 
call pro_testlog; 
call pro_testlog; 
-- 查询数据
select count(*) from testlog; 
\! ls -lh /var/lib/mysql/testdb/testlog.ibd
-- 数据直接写入数据文件,超过了事务日志的大小。
truncate testlog;
-- 删除数据库
delete from testlog;
-- 用delete删除大文件,查询数据文件没有减少,需要optimize进行清除。
\! ls /var/lib/mysql/testdb/testlog.ibd -h 
optimize table testlog;

实验总结:

超过了事务日志的大小的数据文件,事务的操作直接写入数据文件,该操作由于事务日志文件太小导致,建议加大,增加安全;删除大文件的是,有两种方法`truncate testlog;`或者`delete from testlog;`和`optimize table testlog;`进行删除。

慢查询日志

slow_query_log,是一个global,sesion,dynamic的变量,支持动态修改

show variables like 'slow%';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_launch_time    | 2                  |
| slow_query_log      | OFF                |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------+
set global slow_query_log=ON;
-- ll /var/lib/mysql/hellodb/
select sleep(1),name from students;
-- 查询谁在占用
show processlist;

和慢查询相关的设置

use hellodb
-- 查询时间不少于10s,或者不使用索引,系统自动默认OFF
-- log_queries_not_using_indexes=OFF
select * from students;
-- 系统自动优化sql语句,可能使用索引或不使用
explain select * from students where name like 'x%'\G;
explain select * from students where name like 's%'\G;

profiling:global,sesion,dynamic,全局,会话,可动态修改的变量,可以根据show profiles,来慢查询一些相对耗时的sql语句,进行优化

set profiling=ON;
show variables like 'prof%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.00041210 | select @@profiling     |
|        2 | 0.09205547 | select * from students |
+----------+------------+------------------------+
show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000016 |
| Waiting for query cache lock   | 0.000003 |
| init                           | 0.000002 |
| checking query cache for query | 0.000144 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.090296 |
| After opening tables           | 0.000014 |
| System lock                    | 0.000045 |
| Table lock                     | 0.000013 |
| Waiting for query cache lock   | 0.000168 |
| init                           | 0.000176 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000016 |
| preparing                      | 0.000019 |
| executing                      | 0.000003 |
| Sending data                   | 0.000599 |
| end                            | 0.000010 |
| query end                      | 0.000007 |
| closing tables                 | 0.000004 |
| Unlocking tables               | 0.000015 |
| freeing items                  | 0.000007 |
| updating status                | 0.000005 |
| Waiting for query cache lock   | 0.000003 |
| updating status                | 0.000419 |
| Waiting for query cache lock   | 0.000008 |
| updating status                | 0.000003 |
| storing result in query cache  | 0.000008 |
| cleaning up                    | 0.000034 |
+--------------------------------+----------+

二进制日志

记录所有的增删改

  • 记录导致数据改变或潜在导致数据改变的SQL语句
  • 记录已提交的日志
  • 不依赖于存储引擎类型
  • 功能:通过“重放”日志文件中的事件来生成数据副本
  • 注意:建议二进制日志和数据文件分开存放

二进制日志记录格式

二进制日志记录三种格式

  • 基于“语句”记录:statement,记录语句,默认模式
  • 基于“行”记录:row,记录数据,日志量较大,建议使用这个模式
  • 混合模式:mixed, 让系统自行判定该基于哪种方式进行

格式查看show variables like '%binlog_format%';

二进制日志文件的构成

  • 日志文件:mysql|mariadb-bin.文件名后缀,二进制格式

  • 索引文件:mysql|mariadb-bin.index,文本格式

二进制日志相关的服务器变量:

mariadb-10.2.15:

sql_log_binglobal,dynamic,可以动态开启关闭

sql_log_bin=ON|OFF
-- 是否记录二进制日志,默认ON
log_bin=/PATH/BIN_LOG_FILE
-- 指定文件位置;默认ON,表示不启用二进制日志功能,上述两项都为ON才可
max_binlog_size=1073741824
-- 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G
sync_binlog=1|0
-- 设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
expire_logs_days=N
-- 二进制日志可以自动删除的天数。 默认为0,即不自动删除

二进制日志相关配置

配置相关:

show master logs;
show binary logs;
-- 查询二进制的日志文件
show master status;
update students set name='xyz' where stuid=10;
show binlog events in 'mysql-bin.000003' from 1577;
-- 重新生成二进制日志文件
flush logs;
show master status;

日志

mysqlbinlog:二进制日志的客户端命令工具

命令格式:

mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime= 
--stop-datetime=

时间格式YYYY-MM-DD hh:mm:ss--base64-output[=name]

查询mysqlbinlog --help 来帮助自己更好理解此命令

示例:

$ mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003
$ mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003;

二进制日志格式

mysqlbinlog查询的日志格式如下:

BEGIN
/*!*/;
# at 1619
#180611 14:24:50 server id 1  end_log_pos 1734 CRC32 0x7c5b65fe     
Query	thread_id=15	exec_time=0	error_code=0
SET TIMESTAMP=1528698290/*!*/;
update students set name='xyz' where stuid=10
/*!*/;
# at 1734
事件发生的日期和时间:180611 14:24:50 
事件发生的服务器标识:server id 1
事件的结束位置:end_log_pos 1734
事件的类型:Query
事件发生时所在服务器执行此事件的线程的ID:thread_id=1
语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
错误代码:error_code=0
执行的操作:update students set name='xyz' where stuid=10

清除指定二进制日志

PURGE:

  • 清除指定二进制日志:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
  • 示例:
PURGE BINARY LOGS BEFORE '2018-01-23';
PURGE BINARY LOGS BEFORE '2018-05-22 09:25:30';
  • 删除所有二进制日志,index文件重新记数

RESET MASTER [TO #]; 日志文件从#开始记数,默认从1开始,一般是master第一次启动时执行,MariaDB10.1.6开始支持TO #

  • 切换日志文件:FLUSH LOGS,后续备份的mysqldump -F,就是切换日志文件进行备份
-- 清楚000002之前的二进制文件,
pruge binary logs to "mysql-bin.000002";
show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |     10042 |
| mysql-bin.000003 |      1812 |
| mysql-bin.000004 |       385 |
+------------------+-----------+
-- 删除至000001的二进制文件
reset master to 1

备份和恢复

为什么要备份

  • 灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据

丢失场景

备份注意要点 能容忍最多丢失多少数据 恢复数据需要在多长时间内完成 需要恢复哪些数据 还原要点 做还原测试,用于测试备份的可用性 还原演练

备份类型:

完全备份,部分备份

  • 完全备份:整个数据集
  • 部分备份:只备份数据子集,如部分库或表

完全备份、增量备份、差异备份

  • 增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
  • 差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单

**注意:**二进制日志文件不应该与数据文件放在同一磁盘

冷、温、热备份

  • 冷备:读写操作均不可进行
  • 温备:读操作可执行;但写操作不可执行
  • 热备:读写操作均可执行

MyISAM:温备,不支持热备 InnoDB: 都支持

物理和逻辑备份

  • 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快

  • 逻辑备份:从数据库中“导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

备份时需要考虑的因素

温备的持锁多久 备份产生的负载 备份过程的时长 恢复过程的时长

备份什么

数据 二进制日志、InnoDB的事务日志 程序代码(存储过程、存储函数、触发器、事件调度器) 服务器的配置文件

冷备份:

服务器可以停机的情况下,备份加还原

$ systemctl stop mariadb
$ ll /var/lib/mysql/
$ tar -Jcvf /data/all.tar.xz /var/lib/mysql/
$ rm -fr /var/lib/mysql/
$ tar -xf /data/all.tar.xz /var/lib/mysql/
$ mv /var/lib/mysql/var/lib/mysql/* /var/lib/mysql/
$ rm -rf /var/lib/mysql/var
$ systemctl start mariadb

未完待续

上一篇
MYSQL并发控制