摘要: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_bin
:global
,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
未完待续