MySQL 日志
目录
错误日志（Error log）
# 错误日志默认是关闭的
# 默认路径是 $datadir/，默认的名字是'主机名.err'
# 配置方式
[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
# 查看方式
[root@db02 ~]# mysql -e "show variables like '%log_err%'"
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_error | /usr/local/mysql/data/mysql.err |
+---------------------+---------------------------------+
一般日志（General log）
开启 General log 将所有到达 MySQL Server 的 SQL语句 记录下来 。
一般不会开启开功能，因为日志的量会非常庞大，个别情况下可能会临时的开一会 General log 以供排障使用 。
# 默认是关闭的
# 默认路径是 $datadir/，默认的名字是'主机名.log'
# 配置方式
[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
# 查看方式
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)
二进制日志（Binlog）
二进制日志用途：
记录已提交的 DML 事务语句，并拆分为多个事件（Event）来进行记录
记录所有 DDL、DCL 等语句
可以用来做数据的备份恢复
可以用来做数据库的复制
Binlog 的配置
# 注意：
1）binlog生成默认大小是120
2）binlog的大小也是 binlog 的当前位置点
# 二进制日志默认是关闭的
# 默认路径是 $datadir/，以'mysql-bin.000001...N 保存
# 配置方式
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id # mysql5.7必须配置 server_id
log_bin=/usr/local/mysql/data/mysql-bin
# 查看方式
mysql> show variables like '%log_bin%';
Binlog 的位置
# 物理查看
[root@db02 data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 167 7月 14 18:22 mysql-bin.000001
-rw-rw---- 1 mysql mysql 2636 7月 14 19:07 mysql-bin.000002
# 数据库查看
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 167 |
| mysql-bin.000002 | 2636 |
+------------------+-----------+
# 查看binlog事件
mysql> show binlog events in 'mysql-bin.000016';
Binlog 的事件
# 什么是事件（event）
1）在binlog中最小的记录单元为 event
2）一个事务会被拆分成多个事件（event）
# 事件（event）的特性
1）每个 event 都有一个开始位置（start position）和结束位置（stop position）
2）所谓的位置就是event对整个二进制的文件的相对位置
3）对于一个二进制日志中，前 120 个 position 是文件格式信息预留空间
4）MySQL 第一个记录的事件，都是从 120 开始的
刷新 Binlog
# 刷新 binlog 命令
mysql> flush logs;
# 重启数据库时会刷新
# 二进制日志大小的上限，默认 1G（配置 max_binlog_size）
mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
删除 Binlog
注意：绝对不能使用 rm 命令删除 Binlog 日志
1.根据存在时间删除日志
# 临时生效
SET GLOBAL expire_logs_days = 7;
# 永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
# 使用purge命令删除
mysql> PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
# 根据文件名删除，以下例子会删除 000010 以前的所有 binlog 日志
mysql> PURGE BINARY LOGS TO 'mysql-bin.000010';
# 危险：会影响主从复制
mysql> reset master;
二进制日志作用
1.记录已提交的 DML 事务语句，并拆分为多个事件（event）来进行记录
2.记录所有 DDL、DCL、DML 等语句，总之，二进制日志会记录所有对数据库发生修改的操作
3.如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
4.数据的备份与恢复
5.数据的复制
数据库的备份与恢复
添加数据：
# 添加数据
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)
删除数据：
# 误删除两条数据
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)
想要恢复被删除的数据：
# 通过 Binlog 恢复数据
# 查看二进制日志找到位置点
[root@db02 data]# mysqlbinlog mysql-bin.000002
# 将位置点之间的数据取出
[root@db02 data]# mysqlbinlog --start-position=631 --stop-position=978 mysql-bin.000002 > /tmp/recovery.sql
# 将数据导入回去
[root@db02 data]# mysql < /tmp/recovery.sql
数据库的升级
# 准备一台新的数据库，版本为5.6.38
# 旧数据库备份数据
[root@db03 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B ku linux9 myisam qiudao qiudaodsb tmp world xiangqing >/tmp/full.sql
# 将备份的数据库传到新数据库
[root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
# 修改sql中的存储引擎
[root@db02 data]# sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
# 将修改后的sql文件导入新数据
[root@db02 data]# mysql < /tmp/full.sql
# 将代码中的数据库地址修改为新的数据库地址
# 通过 binlog 将数据迁移过程中新生成的数据取出
[root@db03 data]# mysqlbinlog -uroot -p123 --start-position=120 --stop-position=465 mysql-bin.000014 > /tmp/recovery.sql
[root@db03 data]# scp /tmp/bu.sql 172.16.1.52:/tmp/
# 将新数据导入新库
[root@db02 data]# mysql < /tmp/recovery.sql
二进制日志工作模式
工作模式种类
- SBR（Statement-Based Replication），语句模式
- RBR（Row-Based Replicaiton），行级模式
- MBR（Mixed-Based Replication），混合模式
查看工作模式
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
Statement-Based Replication（SBR）语句模式
# 语句模式，MySQL5.6 默认的模式
记录数据库中操作过得所有sql语句
# 查看
[root@db03 data]# mysqlbinlog mysql-bin.000014
# 优缺点
1.易读
2.不安全
3.相对于行级模式占用磁盘空间小
Row-Based Replicaiton（RBR）行级模式
# 行级模式，MySQL5.7 默认的模式
记录的是数据的变化过程
# 配置行级模式
[root@db03 data]# vim /etc/my.cnf
server_id = 1
log_bin=/usr/local/mysql/data/mysql-bin
binlog_format=row
# 查看方式
[root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000016
#优缺点：
1.安全
2.不易读
3.相对于语句模式占用磁盘大
Mixed-Based Replication 混合模式（略）
慢日志（Slow Log）
Slow Log 慢日志将 MySQL 服务器中影响数据库性能的相关 SQL 语句记录到日志文件，通常需要对这些特殊的 SQL 语句分析、改进，以达到提高数据库性能的目的
Slow Log 配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# 指定是否开启慢查询日志
slow_query_log = 1
# 指定慢日志文件存放位置（默认在 $basedir/data ）
slow_query_log_file=/service/mysql/data/slow.log
# 设定慢查询的阀值(默认10s)
long_query_time=0.05
# 不使用索引的 SQL 是否记录到日志
log_queries_not_using_indexes
# 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）
# 添加以下内容
[root@db01 ~]# vim /etc/my.cnf
slow_query_log = 1
slow_query_log_file = /service/mysql/data/slow.log
long_query_time = 3
# log_queries_not_using_indexes 不使用索引的所有 SQL 都会记录到慢日志，即使小于 long_query_time，为了测试，需要先关闭
Slow Log 测试
# 建表
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@dbtest01 data]# less slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.46-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 200722 18:56:47
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 4.530273 Lock_time: 0.030911 Rows_sent: 1305280 Rows_examined: 1305280
SET timestamp=1595415407;
select * from world.slowlog;
mysqldumpslow 命令分析慢日志
# 输出记录次数最多的 10条 SQL语句
[root@dbtest01 data]# mysqldumpslow -s c -t 10 /usr/local/mysql/data/slow.log
Reading mysql slow query log from /usr/local/mysql/data/slow.log
Count: 1 Time=4.50s (4s) Lock=0.03s (0s) Rows=1305280.0 (1305280), root[root]@localhost
select * from world.slowlog
Died at /usr/local/mysql/bin/mysqldumpslow line 167, <> chunk 1.
# mysqldumpslow 命令选项
-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
第三方工具分析慢日志
第三方推荐：
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
记录成长过程
