MySQL之六----MySQL数据库各种日志管理
事务日志:
事务日志的写入类型为“追加”,因此其操作为“顺序IO”;通常也被称为:预写式日志 write ahead logging
事务日志文件: ib_logfile0, ib_logfile1
日志
事务日志 transaction log 错误日志 error log 通用日志 general log 慢查询日志 slow query log 二进制日志 binary log 中继日志 relay log
事务日志:transaction log
事务型存储引擎自行管理和使用,建议和数据文件分开存放
redo log undo log
Innodb事务日志相关配置:
show variables like '%innodb_log%'; innodb_log_file_size 5242880 每个日志文件大小 innodb_log_files_in_group 2 日志组成员个数 innodb_log_group_home_dir ./ 事务文件路径 innodb_flush_log_at_trx_commit 默认为1
示例:
修改事务日志的文件存放路径,比如,我们需要将事务日志存放在/data/logs目录下,此时,我们需要新建一个目录。
[root@centos7-1~]#mkdir /data/logs 新建一个logs目录 [root@centos7-1~]#ll -d /data/logs drwxr-xr-x 2 root root 6 Nov 20 09:22 /data/logs [root@centos7-1~]#chown mysql.mysql /data/logs 修改目录的所属组和所有者 [root@centos7-1~]#ll -d /data/logs drwxr-xr-x 2 mysql mysql 6 Nov 20 09:22 /data/logs
指定事务路径:
vim /etc/my.cnf [mysqld] innodb_log_group_home_dir=/data/logs systemctl restart mariadb 重启mysql服务
查看得知,此时的事务存放文件路径已修改过来了:
innodb_flush_log_at_trx_commit
说明:设置为1,同时sync_binlog = 1表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
1默认情况下,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性 0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能,但服务器崩溃可以清除最后一秒的事务 2每次提交后都会写入日志缓冲区,但每秒都会进行一次刷新。 性能比0略好一些,但操作系统或停电可能导致最后一秒的交易丢失 3模拟MariaDB 5.5组提交(每组提交3个同步),此项MariaDB 10.0支持
查看set语句是否可以重置变量。
错误日志
mysqld启动和关闭过程中输出的事件信息 mysqld运行中产生的错误信息 event scheduler运行一个event时产生的日志信息 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置
SHOW GLOBAL VARIABLES LIKE 'log_error' 只能配置错误日志,不能修改 错误文件路径 log_error=/PATH/TO/LOG_ERROR_FILE 是否记录警告信息至错误日志文件 log_warnings=1|0 默认值1
在配置文件中可以看到错误日志的路径:
vim /etc/my.cnf
当前可以跟踪数据库的日志信息:
[root@centos7~]#tail -f /var/log/mariadb/mariadb.log InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! InnoDB: Restoring possible half-written data pages from the doublewrite buffer... 191120 9:42:03 InnoDB: Waiting for the background threads to start 191120 9:42:04 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 1685378 191120 9:42:04 [Note] Plugin 'FEEDBACK' is disabled. 191120 9:42:04 [Note] Server socket created on IP: '0.0.0.0'. 191120 9:42:04 [Note] Event Scheduler: Loaded 0 events 191120 9:42:04 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
通用日志:记录对数据库的通用操作,包括错误的SQL语句
文件:file,默认值
表:table
通用日志相关设置
general_log=ON|OFF general_log_file=HOSTNAME.log log_output=TABLE|FILE|NONE
将general_log=OFF改为ON
show variables like 'general_log%'; 查看主机名生成的LOG日志。
show variables like 'log_output';默认显示的是file文件。
set global log_output='table';将log日志改为表内。
优化思路:可以查询哪些数据库命令经常使用,如果频繁使用,就需要加上索引,减少查询慢,导致mysql服务性能下降,方便以后使用。
慢查询日志
慢查询日志:记录执行查询时长超出指定时长的操作
slow_query_log=ON|OFF 开启或关闭慢查询 long_query_time=N 慢查询的阀值,单位秒 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 新版已废弃
生产中将以下两项的慢查询开关打开即可:
- 启动slow_query_log=ON启动起此配置内容,有助于后期排错
- log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语句是否记录日志,默认OFF,即不记录。
在mysql配置文件中修改配置:
vim /etc/my.cnf
启用以下几项内容,其他作为默认值,可不修改:
slow_query_log=ON|OFF 开启或关闭慢查询 long_query_time=2 慢查询的阀值,单位秒(可以改为2秒,3秒),根据实际情况修改 log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语句是否记录日志,默认OFF,即不记录
分析哪条命令慢导致mysql查询慢的原因:
1、set profiling=ON; 开启功能
2、show profiles; 先查看当前文件的ID号。
3、show profile for query 4;查看某个ID号的具体信息。
MariaDB [hellodb]> set profiling=ON; 开启功能 Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) MariaDB [hellodb]> show index from students; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec) MariaDB [hellodb]> select * from students where age=10000; Empty set (0.00 sec) MariaDB [hellodb]> show profiles;查看当前所有执行过的mysql命令中哪个时延最长,具体看看哪个数据比较慢。 +----------+------------+-----------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------+ | 1 | 0.00235329 | select @@profiling | | 2 | 0.00398939 | select * from testlog where age=10000 | | 3 | 0.00893150 | show index from students | | 4 | 0.00510308 | select * from students where age=10000 | +----------+------------+-----------------------------------------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> show profile for query 4; 比如查看query_ID 4的内容,看看是什么原因导致当前的mysql运行慢,都耗费了多长的时间。 +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000020 | | Opening tables | 0.000017 | | System lock | 0.000003 | | Table lock | 0.000003 | | Opening tables | 0.000002 | | After opening tables | 0.000055 | | closing tables | 0.000005 | | Unlocking tables | 0.000011 | | Waiting for query cache lock | 0.000003 | | Unlocking tables | 0.000002 | | checking query cache for query | 0.000031 | | checking permissions | 0.000006 | | Opening tables | 0.000009 | | After opening tables | 0.000046 | | System lock | 0.000028 | | Table lock | 0.000009 | | Waiting for query cache lock | 0.000066 | | init | 0.000088 | | optimizing | 0.001249 | | statistics | 0.000922 | | preparing | 0.000043 | | executing | 0.000004 | | Sending data | 0.001897 | | end | 0.000062 | | query end | 0.000033 | | closing tables | 0.000009 | | Unlocking tables | 0.000012 | | freeing items | 0.000405 | | updating status | 0.000013 | | Waiting for query cache lock | 0.000004 | | updating status | 0.000029 | | Waiting for query cache lock | 0.000003 | | updating status | 0.000003 | | storing result in query cache | 0.000009 | | cleaning up | 0.000004 | +--------------------------------+----------+ 35 rows in set (0.01 sec
二进制日志
二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句 记录已提交的日志 不依赖于存储引擎类型 功能:通过“重放”日志文件中的事件来生成数据副本
注意:建议二进制日志和数据文件分开存放
中继日志:relay log
主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件
二进制日志记录格式
二进制日志记录三种格式 基于“语句”记录:statement,记录语句,默认模式 基于“行”记录:row,记录数据,日志量较大 混合模式:mixed, 让系统自行判定该基于哪种方式进行
格式配置
show variables like ‘binlog_format';
二进制日志文件的构成
有两类文件
日志文件:mysql|mariadb-bin.文件名后缀,二进制格式 如: mariadb-bin.000001 索引文件:mysql|mariadb-bin.index,文本格式
二进制日志相关的服务器变量:
sql_log_bin=ON|OFF:是否记录二进制日志,默认ON log_bin=/PATH/BIN_LOG_FILE:指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可 binlog_format=STATEMENT|ROW|MIXED:二进制日志记录的格式,默认STATEMENT 可将格式改为row格式最好。 max_binlog_size=1073741824:单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G 说明:文件达到上限时的大小未必为指定的精确值 sync_binlog=1|0:设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘 expire_logs_days=N:二进制日志可以自动删除的天数。 默认为0,即不自动删除
示例:
定义二进制文件路径和文件前缀名称,会将数据库文件和二进制程序分离存放。
mkdir /data/logbin 创建二进制文件路径 chown mysql.mysql /data/logbin
修改MYSQL配置文件,指向对应的二进制配置文件路径:
vim /etc/my.cnf
log_bin=/data/logbin/mysql-bin
重启mysql服务,查看当前的mysql二进制文件,此时已生成二进制文件。
可以临时关闭二进制程序文件,如果我们在导入大批量的数据日志,已经确定要导入,可以临时关闭二进制日志程序,提高效率。
还原数据时,不禁用二进制程序,恢复数据时,没必要再记录到生成恢复时的二进制日志程序,所以也需要禁用。
set sql_log_bin=OFF 临时关闭二进制程序,不需要重启mysql服务。
二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表,及大小 SHOW {BINARY | MASTER} LOGS 查看使用中的二进制日志文件 SHOW MASTER STATUS 查看二进制文件中的指定内容 SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT[offset,] row_count] show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3
查看mariadb自行管理使用中的二进制日志文件列表,及大小
查看使用中的二进制日志文件
查看二进制文件中的指定内容
示例: show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3 从6516开始跳过前两个看三个文件。
show binlog events in ‘mysql-bin.000001' from 6516 limit 4 从6516开始查看前四个内容。
mysqlbinlog:二进制日志的客户端命令工具
命令格式:
mysqlbinlog [OPTIONS] log_file… --start-position=# 指定开始位置 --stop-position=# --start-datetime= --stop-datetime= 时间格式:YYYY-MM-DD hh:mm:ss --base64-output[=name] -v -vvv
可以查看二进制日志的详细信息:
删除数据库中表内容恢复之
指定mysql二进制数据库路径,暂时禁用行格式的二进制日志,此时默认就是字符格式。
启用二进制日志功能,并插入一张表格内容:
此时,删除hello表格:
delete from hello;
然后通过二进制格式日志进行恢复,将最新的二进制日志文件导入到/root/mysql.log文件中。
此时并将二进制日志功能暂时关闭,避免生成恢复时的二进制日志记录:
set sql_log_bin=off;
修改 导出的二进制日志文件:
vim /root/mysql.log
找到误操作的命令,将其注释掉或者删除此行。
mysql -pmagedu hellodb < /root/mysql.log 将修改后的二进制日志文件导入到删除了的数据库内。
查看之前被删除的文件,已经恢复了误删除的文件。
二进制日志事件的格式:
# 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
清除指定二进制日志: 建议保留半个月以上的二进制日志
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
示例:
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志 PURGE BINARY LOGS BEFORE '2017-01-23'; PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
删除所有二进制日志,index文件重新记数
RESET MASTER [TO #]; 删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从1开始,一般是master主机第一次启动时执行,MariaDB10.1.6开始支持TO #
切换日志文件:
FLUSH LOGS;
示例:
清除二进制日志文件,PURGE BINARY LOGS TO 'mysql-bin.000004'; 清除的是4以前的日志文件,不包含本身。
RESET MASTER [TO #]; 删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从1开始,一般是master主机第一次启动时执行,MariaDB10.1.6开始支持TO #
总结:
以下可以在生产中进行设置参数:
sql_log_bin=ON|OFF:是否记录二进制日志,默认ON log_bin=/PATH/BIN_LOG_FILE:指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可,第一项默认开启,此项指定二进制日志路径即可。 binlog_format=row:二进制日志记录的格式,默认STATEMENT 可将格式改为row格式最好,但是日志比较大,占用内存空间。 max_binlog_size=1073741824:单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G 可以将数据设置大点,比如10G 说明:文件达到上限时的大小未必为指定的精确值 sync_binlog=1|0:设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘,为了数据安全,可以改为1,为了追求高性能改为0. expire_logs_days=N:二进制日志可以自动删除的天数。 默认为0,即不自动删除