“这是我参与8月更文挑战的第8天，活动详情查看：8月更文挑战”
1. 错误日志
1.1 作用
排查 MySQL 运行过程的故障。
1.2 配置
-
默认就开启了
-
默认路径和名字：datadir/hostname.err
-
主要关注 [ERROR] 的错误日志
1.3 认为定义错误日志
- 查看错误日志位置：
mysql> select @@log_error;
+---------------------+
| @@log_error |
+---------------------+
| ./VM-0-3-centos.err |
+---------------------+
1 row in set (0.00 sec)
复制代码- 在配置文件中添加如下信息，重启生效
log_error=/tmp/mysql3306.log
复制代码- 重启 MySQL
# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.Logging to '/tmp/mysql3306.log'.
SUCCESS!
复制代码- 再次测试
mysql> select @@log_error;
+--------------------+
| @@log_error |
+--------------------+
| /tmp/mysql3306.log |
+--------------------+
1 row in set (0.00 sec)
复制代码2. 二进制日志（binlog）
2.1 作用
- 主从要依赖二进制日志
- 数据恢复时需要依赖二进制日志
2.2 如何配置
-
默认没有开启
-
需要设置 server_id（1~65535）：
- server_id=6
-
需要设置 log_bin：
- log_bin=/data/binlog/mysql-bin
- log_bin=1 为开启二进制日志，但是日志路径为默认路径
- mysql-bin：二进制日志文件名的前缀，例如：mysql-bin.000001、mysql-bin.000002
- /data/binlog：提前定制好的目录，而且要有 mysql.mysql 的权限
- log_bin=/data/binlog/mysql-bin
-
需要设置 binlog_format
- binlog_format=row。
- 5.7 版本默认是 row，可以忽略
- binlog_format=row。
配置二进制日志，在 /etc/my.cnf 文件下添加下面配置：
# 服务器 ID（1-65535）
server_id=6
# 错误日志位置
log_error=/tmp/mysql3306.log
# 开启 binlog 日志，并设置日志路径
log_bin=/data/binlog/mysql-bin
# binlog 日志格式
binlog_format=row
复制代码创建日志目录，并重启：
# cd /data/
# mkdir binlog
# chown mysql.mysql binlog/
# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
复制代码查看日志文件：
-rw-r----- 1 mysql mysql 465 1月 30 13:58 mysql-bin.000001
-rw-r----- 1 mysql mysql 30 1月 30 13:57 mysql-bin.index
复制代码2.3 二进制日志记录了什么？
- 概括地说，记录了数据库所有变更类的操作日志：
- DDL
- DCL
- DML
2.3.1 DDL 和 DCL
- 以语句的方式，原模原样的记录
2.3.2 DML
-
记录的是已提交的事务
-
DML 记录格式：，通过
binlog_format=row参数控制- statement
- row
- mixed
-
说明：
- statement：SBR，语句模式记录日志，做什么命令，记录什么命令。
- row：RBR，行模式，数据行的变化
- mixed：MBR，混合模式，一般不使用，由 MySQL 控制
面试问题：SBR 和 RBR 有什么区别？怎么选择？
-
SBR：可读性较强，对于范围操作日志量较少，节省空间，但是可能会出现记录不准确的情况。
- update student set age = 24 where id > 3; 节省空间
- insert into t1 values(1,’hh’,now()); now() 时间会发生变化
-
RBR：可读性较弱，对于范围操作日志打，但是不会出现记录错误。
- 部分高可用环境中的新特性要依赖于 RBR
- 默认也是 RBR
2.4 二进制日志记录单元
2.4.1 event 事件
- 二进制日志的最小单元：事件
- DDL：create database order；对于 DDL 等语句是每一个语句就是一个事件
- DML：一个事务包含了多个语句
- begin；事件 1
- a； 事件 2
- b； 事件 3
- commit；事件 4
2.4.2 event 事件的开始和结束
- 作用：方便我们从日志中截取我们想要的日志事件。
2.5 二进制日志的管理
2.5.1 二进制的位置查看
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| log_bin_index | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)
复制代码2.5.2 查看所有已存在的二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 465 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 512 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
3 rows in set (0.00 sec)
复制代码flush logs; 参数的含义是：滚动一个二进制日志文件。
2.5.3 查看正在使用的二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
复制代码2.5.4 查看二进制事件
- 数据准备：
mysql> create database goods;
Query OK, 1 row affected (0.00 sec)
mysql> use goods;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)
复制代码- 查看二进制事件
mysql> show binlog events in 'mysql-bin.000003' limit 20;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 6 | 316 | create database goods |
| mysql-bin.000003 | 316 | Anonymous_Gtid | 6 | 381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 381 | Query | 6 | 480 | use `goods`; create table t1(id int) |
| mysql-bin.000003 | 480 | Anonymous_Gtid | 6 | 545 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 545 | Query | 6 | 618 | BEGIN |
| mysql-bin.000003 | 618 | Table_map | 6 | 664 | table_id: 109 (goods.t1) |
| mysql-bin.000003 | 664 | Write_rows | 6 | 704 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000003 | 704 | Xid | 6 | 735 | COMMIT /* xid=27 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
11 rows in set (0.00 sec)
复制代码- 下面的日志表示：二进制日志的头格式，每个日志都会有。
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
复制代码POS 表示日志的开始位置，End_log_pos 表示日志的结束位置。当我们需要截取日志是，我们可以用这两个参数。
2.5.5 查看二进制内容
- 查看二进制文件属性：
# file /data/binlog/mysql-bin.000003
/data/binlog/mysql-bin.000003: MySQL replication log
复制代码- 查看二进制内容：
# mysqlbinlog /data/binlog/mysql-bin.000003
......
# at 545
#210130 14:18:01 server id 6 end_log_pos 618 CRC32 0xc1249ccd Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1611987481/*!*/;
BEGIN
/*!*/;
# at 618
#210130 14:18:01 server id 6 end_log_pos 664 CRC32 0x3e7f888f Table_map: `goods`.`t1` mapped to number 109
# at 664
#210130 14:18:01 server id 6 end_log_pos 704 CRC32 0x8471e736 Write_rows: table id 109 flags: STMT_END_F
BINLOG '
GfoUYBMGAAAALgAAAJgCAAAAAG0AAAAAAAEABWdvb2RzAAJ0MQABAwABj4h/Pg==
GfoUYB4GAAAAKAAAAMACAAAAAG0AAAAAAAEAAgAB//4BAAAANudxhA==
'/*!*/;
# at 704
#210130 14:18:01 server id 6 end_log_pos 735 CRC32 0x7865534a Xid = 27
COMMIT/*!*/;
......
复制代码之前的二进制日志信息我们看不懂，那么我们给他解码一下，再看一下，使用如下命令：
# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003
复制代码我们提取部分信息：
### INSERT INTO `goods`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 704
复制代码上述代码的含义是：在表 t1 插入数据，在第一列插入 1。
只查看某一个库下的二进制文件：
mysqlbinlog -d database_name mysql-bin.000003
复制代码2.5.6 截取二进制日志
- 命令
mysqlbinlog --start-position=545 --stop-position=704 mysql-bin.000003
复制代码- 保存下来
mysqlbinlog --start-position=545 --stop-position=704 mysql-bin.000003 > /tmp/a.sql
复制代码2.5.7 通过 binlog 恢复数据
- 模拟数据
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
复制代码- 模拟故障
mysql> drop database test;
Query OK, 1 row affected (0.02 sec)
复制代码- 基于 binlog 日志恢复
- 首先确认使用哪个日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1950 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
复制代码- 找到起点和终点
mysql> show binlog events in 'mysql-bin.000003';
复制代码- 分析和截取
mysqlbinlog --start-position=1276 --stop-position=1858 mysql-bin.000003 > /tmp/b.sql
复制代码- 恢复 binlog
注意，因为我们使用 binlog 恢复日志，那么这些 SQL 还会再执行一遍，再次产生日志，这些日志是没有必要的，因此我们就不让他写日志。临时将 sql_log_bin 设置为 0
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
复制代码mysql> source /tmp/b.sql
复制代码注意：恢复完数据，需要将 sql_log_bin 设置为 1，恢复回来。
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
复制代码测试一下：
mysql> use test;
Database changed
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
复制代码2.6 binlog 的 gtid 记录模式的管理
2.6.1 GTID 介绍
-
对于 binlog 中的每一个事务，队徽生成一个 GTID 号码。
-
DDL，DCL 一个 event 就是一个事务，就会有一个 GTID 号。
-
DML 语句来讲，begin 到 commit，是一个事务，就是一个 GTID 号
2.6.2 GTID 的组成
- server_uuid:TID
server_uuid 在 MySQL 初始化日志文件的时候就有了。在数据文件目录下，名称叫做 auto.cnf。
[root@VM-0-3-centos data]# ll
总用量 123124
drwxr-x--- 2 mysql mysql 4096 1月 27 21:46 account
-rw-r----- 1 mysql mysql 56 1月 13 22:25 auto.cnf
drwxr-x--- 2 mysql mysql 4096 1月 30 14:17 goods
-rw-r----- 1 mysql mysql 428 1月 30 13:57 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 1月 30 22:11 ibdata1
-rw-r----- 1 mysql mysql 50331648 1月 30 22:11 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 1月 13 22:25 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 1月 30 13:57 ibtmp1
drwxr-x--- 2 mysql mysql 4096 1月 13 22:25 mysql
drwxr-x--- 2 mysql mysql 4096 1月 13 22:25 performance_schema
drwxr-x--- 2 mysql mysql 12288 1月 13 22:25 sys
drwxr-x--- 2 mysql mysql 4096 1月 30 22:11 test
-rw-r----- 1 mysql mysql 199982 1月 30 12:15 VM-0-3-centos.err
-rw-r----- 1 mysql mysql 6 1月 30 13:57 VM-0-3-centos.pid
复制代码TID 是一个自增长的数据，从 1 开始
1f713392-55ab-11eb-aca4-525400ea4205:1-15
2.6.3 GTID 的幂等性
如果拿有 GTID 的日志去恢复时，检查当前系统中是否有相同 GTID 号，有相同的则跳过。
2.6.4 GTID 的开启和配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
service mysqld restart
复制代码2.6.5 查看 GTID 信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
复制代码此时，Executed_Gtid_Set 值还不存在。
mysql> create database gtid;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 | 313 | | | 1f713392-55ab-11eb-aca4-525400ea4205:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
复制代码可以看到 Executed_Gtid_Set 里面有值了，Executed_Gtid_Set 表示执行了 GTID 事务了
此时我们查看一下 binlog 日志，发现有 gtid 数据了：
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000005 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '1f713392-55ab-11eb-aca4-525400ea4205:1' |
| mysql-bin.000005 | 219 | Query | 6 | 313 | create database gtid |
| mysql-bin.000005 | 313 | Gtid | 6 | 378 | SET @@SESSION.GTID_NEXT= '1f713392-55ab-11eb-aca4-525400ea4205:2' |
| mysql-bin.000005 | 378 | Query | 6 | 474 | use `gtid`; create table t(id int) |
| mysql-bin.000005 | 474 | Gtid | 6 | 539 | SET @@SESSION.GTID_NEXT= '1f713392-55ab-11eb-aca4-525400ea4205:3' |
| mysql-bin.000005 | 539 | Query | 6 | 631 | drop database gtid |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
8 rows in set (0.00 sec)
复制代码2.6.6 基于 GTID binlog 恢复
- 截取日志
# cd /data/binlog
# mysqlbinlog --include-gtids='1f713392-55ab-11eb-aca4-525400ea4205:1-2' mysql-bin.000005 > /tmp/gtid.sql
复制代码- 恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
复制代码- 报错
ERROR 1049 (42000): Unknown database 'gtid'
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
复制代码为什么报错？
- 因为幂等性的检查，1-2 号事务已经做过了，所以就不做了。
- 正确的做法？
mysqlbinlog --skip-gtids --include-gtids='1f713392-55ab-11eb-aca4-525400ea4205:1-2' mysql-bin.000005 > /tmp/gtid.sql--skip-gtids作用：在导出时，忽略原有的 GTID 信息，恢复时生成最新的 GTID 信息
- 恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
复制代码- 测试恢复是否成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| account |
| goods |
| gtid |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
8 rows in set (0.00 sec)
mysql> use gtid;
Database changed
mysql> show tables;
+----------------+
| Tables_in_gtid |
+----------------+
| t |
+----------------+
1 row in set (0.00 sec)
复制代码2.6.7 GTID 相关的参数
--skip-gtids：导出时不导出 gtid，不然恢复数据时会失败--include-gtids：选择要导出的数据，是个范围，如果想要多个范围，用逗号隔开- –include-gtids=’1f713392-55ab-11eb-aca4-525400ea4205:1-2,1f713392-55ab-11eb-aca4-525400ea4205:4
exclude-gtids：去掉不想导出的数据，多个用逗号隔开- –exclude-gtids=’1f713392-55ab-11eb-aca4-525400ea4205:3,1f713392-55ab-11eb-aca4-525400ea4205:5
2.6.8 查看 gtid 相关命令
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
复制代码# mysqlbinlog --help | grep 'gtid'
复制代码3. 慢日志（slow_log）
3.1 作用
- 记录 MySQL 运行过程中执行较慢的语句。
3.1 如何配置
# vim /etc/my.cnf
# 慢日志开关
slow_query_log=1
# 文件位置及名字
slow_query_log_file=/data/mysql/slow.log
# 设置慢查询时间
long_query_time=0.1
# 没走索引的语句也记录
log_queries_not_using_indexes
# service mysqld restart
复制代码默认慢日志时间：
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
复制代码3.3 模拟慢查询
3.4 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
复制代码取出出现次数最多的慢日志
查看慢日志参数
mysql> show variables like "%slow%";
+---------------------------+----------------------+
| Variable_name | Value |
+---------------------------+----------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/slow.log |
+---------------------------+----------------------+
5 rows in set (0.00 sec)
mysql> show variables like "%long%";
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 0.100000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+----------+
5 rows in set (0.00 sec)
复制代码4. 二进制日志清理
4.1 自动
mysql> show variables like "%expire%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON |
| expire_logs_days | 0 |
+--------------------------------+-------+
2 rows in set (0.00 sec)
复制代码0 代表永不过期。
-
expire_logs_days=30
-
设置的依据：至少 1 轮全备中期长度的过期时间
4.2 手动
mysql> help purge;
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
复制代码mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 512 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 1973 |
| mysql-bin.000004 | 177 |
| mysql-bin.000005 | 654 |
| mysql-bin.000006 | 194 |
+------------------+-----------+
6 rows in set (0.00 sec)
mysql> purge binary logs to 'mysql-bin.000005';
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 654 |
| mysql-bin.000006 | 194 |
+------------------+-----------+
2 rows in set (0.00 sec)
复制代码4.3 如何滚动日志
flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 654 |
| mysql-bin.000006 | 194 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 654 |
| mysql-bin.000006 | 241 |
| mysql-bin.000007 | 194 |
+------------------+-----------+
3 rows in set (0.01 sec)
复制代码- 数据库重启
# service mysqld restart;
复制代码mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 654 |
| mysql-bin.000006 | 241 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 194 |
+------------------+-----------+
复制代码© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
![[桜井宁宁]COS和泉纱雾超可爱写真福利集-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/4d3cf227a85d7e79f5d6b4efb6bde3e8.jpg)
![[桜井宁宁] 爆乳奶牛少女cos写真-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/d40483e126fcf567894e89c65eaca655.jpg)
