1. 错误日志
1.1 作用
MySQL 启动及工作过程中，状态\报错\警告.
1.2 如何配置
select @@log_error;
>>默认是在datadir=/data/3306/data/hostname.err
>修改方式：
vim /etc/my.cnf
log_error=/data/3306/data/mysql.log 添加一行此数据
1.3 如何查看错误日志？
关注日志文件中"[error]"的内容
2. 二进制日志
2.1 作用
数据恢复必备的日志
主从复制依赖的日志
2.2 怎么配置
>>① 修改配置文件
vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin
注释：/data/binlog是文件路径，mysql-bin是文件前缀
--- 这个最好不要和数据放在一个盘，和数据一样重要
>>② 创建目录并授权
[root@db01 data]# mkdir -p /data/binlog
[root@db01 data]# chown -R mysql.mysql /data/binlog
>>③ 重启数据库
2.3 二进制日志记录了什么？
2.3.1 引入
除了查询类的语句，都会被记录。
所有数据路变更类的语句。
2.3.2 记录语句的种类
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
2.3.3 不同语句记录格式说明
DDL，DCL：直接以语句(statement)方式记录
DML语句：insert，update，delete
mysql[world]>select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
--- 一共有三种类型
SBR(statement)：做什么记录什么
RBR(row)：记录了数据行的变化，默认模式(推荐)
MBR(mixed)：混合模式，自动判断记录模式
面试题：说明SBR和RBR的区别
SBR(statement)：做什么记录什么，记录的SQL语句，可读性强，日志量相对较少，日志记录可能不准确
RBR(row)：记录了数据行的变化，默认模式(推荐)，可读性差，日志量大，日志记录准确
2.3.4 binlog events(二进制日志事件)
(1) 简介
二进制日志内容以事件为最小记录单元。
对于DDL和DCL，一个语句就是一个事件
对于DML(标准的事务语句)：只记录已提交的事务DML语句
begin; 事件1
a; 事件2
b; 事件3
commit; 事件4
(2) 事件的构成
>>查看二进制命令
[root@db01 binlog]# mysqlbinlog mysql-bin.000001
#at 219 事件开始的位置(position)
#190814 18:46:58 事件发生的时间
create database xinixn 事件内容
# End of log file 事件结束的位置
>>中间的set可以省略
2.3.5 二进制日志的基本查看
(1) 查看二进制日志的配置信息
mysql[world]>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) 查看二进制日志基本信息
>>查看二进制文件
mysql[world]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 319 |
+------------------+-----------+
1 row in set (0.00 sec)
>>查看当前正在使用的二进制文件
mysql[world]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 319 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
(3) 查看二进制日志的事件信息
>>先查看正在使用的二进制日志
mysql[world]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 319 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
>>在查看内容信息
mysql[world]>show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 6 | 319 | create database xinixn |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
--- set 信息可以忽略不计 ---
2.4 对二进制日志内容查看和截取
2.4.1 内容查看命令
[root@db01 binlog]$ mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001
2.4.2 日志的截取
--start-position 开始日志
--stop-postion 结束日志
>>测试：
--- 测试建一个表，随便写入数据 ---
mysql[(none)]>create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql[binlog]>create table t1(id int)engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.35 sec)
mysql[binlog]>insert into t1 values(1),(2),(3),(11),(12),(13);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql[binlog]>update t1 set id=10 where id>10;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
--- 删除库 ---
mysql[binlog]>drop database binlog;
Query OK, 1 row affected (0.37 sec)
--- 查看二进制日志恢复数据库 ---
--- 确认起点和终点，查看二进制日志，从建库开始
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1423 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql[(none)]>show binlog events in 'mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 6 | 319 | create database xinixn |
| mysql-bin.000001 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 384 | Query | 6 | 500 | create database binlog charset utf8mb4 |
| mysql-bin.000001 | 500 | Anonymous_Gtid | 6 | 565 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 565 | Query | 6 | 695 | use `binlog`; create table t1(id int)engine=innodb charset=utf8mb4 |
| mysql-bin.000001 | 695 | Anonymous_Gtid | 6 | 760 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 760 | Query | 6 | 834 | BEGIN |
| mysql-bin.000001 | 834 | Table_map | 6 | 881 | table_id: 112 (binlog.t1) |
| mysql-bin.000001 | 881 | Write_rows | 6 | 946 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000001 | 946 | Xid | 6 | 977 | COMMIT /* xid=42 */ |
| mysql-bin.000001 | 977 | Anonymous_Gtid | 6 | 1042 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1042 | Query | 6 | 1116 | BEGIN |
| mysql-bin.000001 | 1116 | Table_map | 6 | 1163 | table_id: 112 (binlog.t1) |
| mysql-bin.000001 | 1163 | Update_rows | 6 | 1229 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000001 | 1229 | Xid | 6 | 1260 | COMMIT /* xid=44 */ |
| mysql-bin.000001 | 1260 | Anonymous_Gtid | 6 | 1325 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1325 | Query | 6 | 1423 | drop database binlog |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
20 rows in set (0.00 sec)
--- 起点为384-1325
--- 截取日志
[root@db01 binlog]$ mysqlbinlog --start-position=384 --stop-position=1325 /data/binlog/mysql-bin.000001 >/data/bin.sql
--- 恢复日志 ---
mysql[(none)]>set sql_log_bin=0; 临时关闭当前会话的binlog记录
Query OK, 0 rows affected (0.00 sec)
mysql[(none)]>source /data/bin.sql 使用source命令恢复
Query OK, 0 rows affected (0.00 sec)
>>扩展：如何过滤日志中某个单一的库或者表
mysqlbinlog -d binlog --start-position=384 --stop-position=1325 /data/binlog/mysql-bin.000001 >/data/bin.sql >>-d 指定某一个库
--- 可以借中间库恢复所有表，导出单一的表
--- 生产中恢复代价太大。可以配合其他备份手段恢复。
2.5 基于gtid的binlog管理(扩展)
2.5.0 引入
5.6版本以后，binlog加入了新的日志记录方式，GTID
主要作用：
- 简化binlog截取
- 提供在主从复制中的高级功能
5.7版本之后，进行了GTID增强
- 主从性能，高可用环境，集群
2.5.1 什么是gtid(Global Transaction ID)
全局唯一的事务编号
幂等性：
- GTID：Server_uuid:Tx_id
2.5.2 配置
mysql[(none)]>show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
mysql[(none)]>show variables like '%log_slave_updates%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | OFF |
+-------------------+-------+
1 row in set (0.00 sec)
vim /etc/my.cnf
gtid_mode=on gtid开关
enforce_gtid_consistency=true 强制GTID一致性
log_slave_updates=1 主从复制从库记录binlog，并同意GTID信息
2.5.3 查看gtid日志信息
DDL，DCL一个操作就是一个DTID
DML，一个完整的事务就是一个GTID
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 615 | | | c4e0cd26-b75c-11e9-b4d8-000c2992bac2:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql[binlog]>show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000004 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'c4e0cd26-b75c-11e9-b4d8-000c2992bac2:1' |
| mysql-bin.000004 | 219 | Query | 6 | 300 | drop database db |
| mysql-bin.000004 | 300 | Gtid | 6 | 365 | SET @@SESSION.GTID_NEXT= 'c4e0cd26-b75c-11e9-b4d8-000c2992bac2:2' |
| mysql-bin.000004 | 365 | Query | 6 | 469 | create database db charset utf8mb4 |
| mysql-bin.000004 | 469 | Gtid | 6 | 534 | SET @@SESSION.GTID_NEXT= 'c4e0cd26-b75c-11e9-b4d8-000c2992bac2:3' |
| mysql-bin.000004 | 534 | Query | 6 | 615 | drop database db |
| mysql-bin.000004 | 615 | Gtid | 6 | 680 | SET @@SESSION.GTID_NEXT= 'c4e0cd26-b75c-11e9-b4d8-000c2992bac2:4' |
| mysql-bin.000004 | 680 | Query | 6 | 774 | create database dbdb |
| mysql-bin.000004 | 774 | Gtid | 6 | 839 | SET @@SESSION.GTID_NEXT= 'c4e0cd26-b75c-11e9-b4d8-000c2992bac2:5' |
| mysql-bin.000004 | 839 | Query | 6 | 924 | drop database dbdb |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
12 rows in set (0.00 sec)
1 row in set (0.00 sec)
2.5.4 基于gtid截取日志
--skip-gtids 跳过检查gtid已记录信息
--include-gtids= 导入gtid记录id
--exclude-gtids= 跳过gtid记录id
>> 截取1-3号事务:
[root@db01 ~]$ mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql
>> 截取 1-10 gtid事务,跳过6号和8号事务.
[root@db01 ~]$ mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10 --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8' /data/binlog/mysql-bin.000009>/data/gtid.sql
2.5.5 演练
>>准备环境
mysql[binlog]>create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql[binlog]>use gtid
Database changed
mysql[gtid]>create table t1 (id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.18 sec)
mysql[gtid]>insert into t1 values(1),(2),(3),(11),(22),(33);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql[gtid]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
+------+
6 rows in set (0.00 sec)
mysql[gtid]>comint;
mysql[gtid]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 1570 | | | c4e0cd26-b75c-11e9-b4d8-000c2992bac2:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
>> 截取日志
mysql[(none)]>set sql_log_bin=0; 临时关闭当前日志记录
Query OK, 0 rows affected (0.00 sec)
--- 导出二进制日志文件
[root@db01 data]$ mysqlbinlog --skip-gtids --include-gtids='c4e0cd26-b75c-11e9-b4d8-000c2992bac2:12-14' /data/binlog/mysql-bin.000004 >/data/gtid.sql
--- 恢复日志
mysql[(none)]>source /data/gtid.sql;
Query OK, 0 rows affected (0.00 sec)
2.6 二进制日志其他操作
2.6.1 自动清理日志
show variables like '%exprie%';
mysql[xinxin]>select @@expire_logs_days; 查看自动清理时间
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
>>自动清理(15天的自动删除)
vim /etc/my.cnf
expire_logs_days=15
--- 企业建议，至少保留两个全备周期+1的binlog
>>手动清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000009';
2.6.2 清理mysql-bin日志文件，从头开始记录
>>清理mysql-bin日志文件，从1开始记录(不建议，主从必崩)。
reset master;
2.6.3 binlog的滚动日志
>>binlog的滚动(重启自动滚动，日志大小1G会自动滚动)
mysql[xinxin]>flush logs;
Query OK, 0 rows affected (0.06 sec)
mysql[xinxin]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1446 |
| mysql-bin.000002 | 346 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 2856 |
| mysql-bin.000005 | 194 |
+------------------+-----------+
5 rows in set (0.00 sec)
>>查看滚动日志大小
mysql[xinxin]>select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
1 row in set (0.00 sec)
--- 备份时,某些参数会触发.
3. 慢日志(slow-log)
3.1 简介
记录运行较慢的语句，记录到slowlog中。
功能是辅助优化的工具日志
应急性的慢 ----> show processlist;
一段时间慢 ----> slow记录，统计。
3.2 配置
show variables like '%slow%';
vim /etc/my.cnf
slow_query_log=1 $开启记录
slow_query_log_file=/data/3306/data/db01-slow.log $指定记录位置
long_query_time=0.1 $指定执行多久属于慢
log_queries_not_using_indexes $记录不使用索引的语句
>>查看是多长时间属于慢(默认单位秒)
mysql[xinxin]>select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
mysql[xinxin]>show variables like '%not_using_indexes%';
+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
+----------------------------------------+-------+
2 rows in set (0.00 sec)
3.3 慢语句模拟
mysql[test]>set sql_log_bin=0;
mysql[test]>source /tmp/t100w.sql
mysql[test]>set sql_log_bin=1;
3.4 分析处理慢语句
[root@db01 data]$ mysqldumpslow -s c -t 5 /data/3306/data/db01-slow.log
3.5 自己扩展一下
pt-query-digest /data/3306/data/db01-slow.log
集成: pt-query-digest+Anemometer=WEB方式:(分析慢日志,二进制日志,错误日志...)
网友评论