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,即不自动删除
posted @ 2020-03-23 20:42  一叶知秋~~  阅读(1551)  评论(0编辑  收藏  举报