• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

MySQL日志

MySQL zkinogg 2年前 (2020-08-10) 250次浏览 0个评论

MySQL日志

前言

日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化;也就是说用来记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时,可以通过日志查看文件出错的原因,并且可以通过日志文件进行数据恢复。

MySQL日志分类:
二进制日志(binary log):记录数据更新的操作,mysqlbinlog 可查看二进制日志文件

错误日志(error log):记录mysql服务进程mysqld的启动、关闭和运行时的错误信息

慢查询日志(slow query log):记录执行时间超过指定时间(long_query_time)的查询操作

通用查询日志(general query log):记录客户端连接和执行的sql查询操作(一般不开启)

日志一般存放在安装目录下的data目录下

 

一、mysql错误日志

1.错误日志默认是关闭的
2.默认路径是 $datadir/,默认的名字是'主机名.err'
3.配置方式(一般场景所有配置)
    [root@db02 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=1
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/data
    port=3306
    socket=/tmp/mysql.sock
    skip-name-resolve
    log_err=/usr/local/mysql/data/mysql.err

    [mysql]
    socket=/tmp/mysql.sock
4.查看方式
    [root@db02 ~]# mysql -e "show variables like '%log_err%'"
    +---------------------+---------------------------------+
    | Variable_name       | Value                           |
    +---------------------+---------------------------------+
    | log_error           | /usr/local/mysql/data/mysql.err |
    +---------------------+---------------------------------+
    
 4.删除错误日志
 mysqladmin –u root –p flush-logs
 在mysql5.5.7之后:服务器将关闭此项功能。只能使用重命名原来的错误日志文件,手动冲洗日志创建一个新的:方式如下:

[root@db01 data]# mv stu18.magedu.com.err  stu18.magedu.com.err.old
 [root@db01 data]#  mysqladmin flush-logs
[root@db01 data]# ls
hellodb  myclass  mysql-bin.000003  mysql-bin.index           stu18.magedu.com.pid     

错误日志记录的信息


1.服务器启动和关闭过程中的信息
未必是错误信息,比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等

2.服务器运行过程中的错误信息
比如sock文件找不到,无法加载mysql数据库的数据文件,如果忘记初始化mysql或data dir路径找不到,或权限不正确等 都会记录在此

3.事件调度器运行一个事件时产生的信息
一旦mysql调度启动一个计划任务的时候,它也会将相关信息记录在错误日志中

4.在从服务器上启动从服务器进程时产生的信息
在复制环境下,从服务器进程的信息也会被记录进错误日志

二、一般查询日志

查询日志作用

查询日志在MySQL中被称为general log(通用日志),查询日志里的内容不要被"查询日志"误导,认为里面只存储select语句,其实不然,查询日志里面记录了数据库执行的所有命令,不管语句是否正确,都会被记录,具体原因如下:

insert查询为了避免数据冲突,如果此前插入过数据,当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错
update时也会查询因为更新的时候很可能会更新某一块数据
delete查询,只删除符合条件的数据
因此都会产生日志,在并发操作非常多的场景下,查询信息会非常多,那么如果都记录下来会导致IO非常大,影响MySQL性能,因此如果不是在调试环境下,是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集,执行密集的select语句对应的数据是否能够被缓存,同时也可以帮助我们分析问题,所以,我们可以根据自己的实际情况来决定是否开启查询日志。
1.查看方式
    mysql> show variables like '%general%';
    +------------------+--------------------------------+
    | Variable_name    | Value                          |
    +------------------+--------------------------------+
    | general_log      | OFF                            |
    | general_log_file | /usr/local/mysql/data/db02.log |
    +------------------+--------------------------------+
    2 rows in set (0.00 sec)
2.默认是关闭的
3.默认路径是 $datadir/,默认的名字是'主机名.log'
4.配置方式
    [root@db02 ~]# vim /etc/my.cnf

    [mysqld]
    log_err=/usr/local/mysql/data/mysql.err
    general_log=on
    general_log_file=/usr/local/mysql/data/db02.log

    [mysql]
    socket=/tmp/mysql.sock

三、二进制日志

1.二进制日志管理操作

1)开启二进制日志

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id
log_bin=/usr/local/mysql/data/mysql-bin

2)查看二进制日志

#物理查看
[root@db02 ~]# ll /usr/local/mysql/data/
-rw-rw---- 1 mysql mysql      120 Jul 21 19:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql       39 Jul 21 19:24 mysql-bin.index

#数据库查看
mysql> show variables like '%log_bin%';

3)事件

1.什么是事件
    1)在binlog中最小的记录单元为event
    2)一个事务会被拆分成多个事件(event)

2.事件(event)特性
    1)每个event都有一个开始位置(start position)和结束位置(stop position)。
    2)所谓的位置就是event对整个二进制的文件的相对位置。
    3)对于一个二进制日志中,前120个position是文件格式信息预留空间。
    4)MySQL第一个记录的事件,都是从120开始的。

4)刷新binlog

1)flush logs;
2)重启数据库时会刷新
3)二进制日志上限,默认1G(max_binlog_size)

5)删除binlog

1.根据存在时间删除日志
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2.使用purge命令删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.根据文件名删除
PURGE BINARY LOGS TO 'mysql-bin.000010';

4.使用reset master
mysql> reset master; 

2.二进制日志作用

1.记录已提交的DML事务语句,并拆分为多个事件(event)来进行记录
2.记录所有DDL、DCL等语句,总之,二进制日志会记录所有对数据库发生修改的操作
3.如果我拥有数据库搭建开始所有的二进制日志,那么我可以把数据恢复到任意时刻
4.数据的备份与恢复
5.数据的复制

1)数据库的备份与恢复

1>添加数据
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)

mysql> use binlog
Database changed

mysql> create table binlog(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert binlog values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert binlog values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert binlog values(5);
Query OK, 1 row affected (0.00 sec)
2>删除数据
#误删除两条数据
mysql> delete from binlog where id=5;
Query OK, 1 row affected (0.00 sec)

mysql> delete from binlog where id=4;
Query OK, 1 row affected (0.00 sec)
3>通过binlog恢复数据
#查看二进制日志找到位置点
[root@db02 data]# mysqlbinlog mysql-bin.000002

#将位置点之间的数据取出
[root@db02 data]# mysqlbinlog --start-position=631 --stop-position=978 mysql-bin.000002 > /tmp/hf.sql

#将数据导入回去
[root@db02 data]# mysql < /tmp/45.sql

2)使用binlog配合数据库升级

1.准备一台新的数据库,版本为5.6.38
2.旧数据库备份数据
    [root@db03 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B ku linux9 myisam qiudao qiudaodsb tmp world xiangqing >/tmp/full.sql
3.将备份的数据库传到新数据库
    [root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
4.修改sql中的存储引擎
    [root@db02 data]# sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
5.将修改后的sql文件导入新数据
    [root@db02 data]# mysql < /tmp/full.sql
6.将代码中的数据库地址修改为新的数据库地址
7.通过binlog将数据迁移过程中新生成的数据取出
    [root@db03 data]# mysqlbinlog -uroot -p123 --start-position=120 --stop-position=465 mysql-bin.000014 > /tmp/bu.sql
    [root@db03 data]# scp /tmp/bu.sql 172.16.1.52:/tmp/
8.将新数据导入新库
    [root@db02 data]# mysql < /tmp/bu.sql

3.二进制日志介绍

#注意:
    1)binlog生成默认大小是120
    2)binlog的大小也是binlog的当前位置点

1.查看方式
mysql> show variables like '%log_bin%';
2.二进制日志默认是关闭的
3.配置binlog
    [root@db02 ~]# vim /etc/my.cnf
    [mysqld]
    server_id                                   #mysql5.7必须配置server_id
    log_bin=/usr/local/mysql/data/mysql-bin         #mysql5.7只支持下划线
    #log-bin=/usr/local/mysql/data/mysql-bin        #mysql5.6都支持
4.二进制配置路径和名字由配置文件决定,一般保存在$datadir/ 以'mysql-bin.000001'命令

4.二进制日志工作模式

1)工作模式种类

1.statement  语句模式
2.row       行级模式
3.mixed     混合模式

2)查看工作模式

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

3)STATEMENT语句模式

#语句模式,mysql5.6默认的模式
记录数据库中操作过得所有sql语句

#查看
[root@db03 data]# mysqlbinlog mysql-bin.000014

#优缺点:
1.易读
2.不安全
3.相对于行级模式占用磁盘空间小

4)row行级模式

#行级模式,mysql5.7默认的模式
记录的是数据的变化过程

#配置行级模式
[root@db03 data]# vim /etc/my.cnf
binlog_format=row

#查看方式
[root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000016

#优缺点:
1.安全
2.不易读
3.相对于语句模式占用磁盘大

5)mixed混合模式

#statement模式和row模式的混合,mysql会根据执行的每一条具体的sql语句来确定记录的形式,即在statemente和row之间选择一种。新版本对row level模式做了优化,不是所有的修改都以row level来记录,如表结构的变更会以statement模式记录,如果是update或者delete还是会记录所有行的变更
## 基本不用

四、慢日志

1.作用

1.是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
2.通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的

2.配置

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置(默认在data)
slow_query_log_file=/service/mysql/data/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100(鸡肋)

slow_query_log = 1
slow_query_log_file=/service/mysql/data/slow.log
long_query_time=3
log_queries_not_using_indexes

3.慢日志测试

#建表
mysql> create table solwlog2 select * from city;
Query OK, 4079 rows affected (0.07 sec)
Records: 4079  Duplicates: 0  Warnings: 0

#反复插入
mysql> insert solwlog select * from solwlog;
Query OK, 2088448 rows affected (9.00 sec)
Records: 2088448  Duplicates: 0  Warnings: 0

#查看慢日志
[root@db03 data]# less slow.log

4.使用mysqldumpslow命令来分析慢查询日志

#输出记录次数最多的10条SQL语句
mysqldumpslow -s c -t 10 /database/mysql/slow-log

-s:
是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
-t:
是top n的意思,即为返回前面多少条的数据;
-g:
后边可以写一个正则匹配模式,大小写不敏感的;

#例子:
    #得到返回记录集最多的10个查询
    mysqldumpslow -s r -t 10 /database/mysql/slow-log

    #得到按照时间排序的前10条里面含有左连接的查询语句
    mysqldumpslow -s t -t 10 -g "left join" /database/mysql/slow-log

5.扩展

第三方推荐(扩展):
yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

使用percona公司提供的pt-query-digest工具分析慢查询日志
[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!