MySQL学习笔记
文章目录
错误日志（log_error）
作用
记录启动，关闭，日常运行过程中，状态信息，警告，错误
配置
默认是开启的：/数据路径下/hostname.err
手工设定：
vim /etc/my.cnf
log_error=/var/log/mysql.err
log_timestamps=system 时区，设置为系统时间
重启生效
binlog（binary logs） 二进制日志*****
作用
1.备份恢复必须依赖二进制日志
2.主从环境必须依赖二进制日志基础参数：
基础参数
开关
3306 [(none)]>select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
日志路径及名字
3306 [(none)]>select @@log_bin_basename;
+------------------------+
| @@log_bin_basename |
+------------------------+
| /data/binlog/mysql-bin |
+------------------------+
1 row in set (0.00 sec)
服务ID号
3306 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
二进制日志格式
3306 [(none)]>select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
双一标准之二
3306 [(none)]>select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
配置
注意，mysql默认是没有开启二进制日志的
创建日志目录
mkdir /data/binlog -p
chown -R mysql.mysql /data/binlog
修改配置文件
vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin 此处可以直接写路径
binlog_format=row
重启生效
1.server_id
主要是在主从复制过程中必须加的，但是在5.7版本中，要用一下参数，开启binlog日志，即使是单机，也是必须加的
2.log_bin=/data/binlog/mysql-bin
开启二进制日志功能
设置二进制日志目录及名称前缀
3.binlog_format=row
binlog的记录格式
binlog记录了什么
binlog是sql层的功能，记录的是变更sql语句，不记录查询语句
记录SQL语句种类
DDL：原封不动的记录当前DDL（statement语句方式）
DCL：原封不动的记录当前DCL（statement语句方式）
DML：只记录已经提交的事务DML
DML三种记录格式
binlog_format=
1.statement（5.6默认），SBR（statement based replication）：原封不动的记录当前DML
2.ROW（5.7默认）RBR（ROW based replication）：记录数据行的变化（用户看不懂，需要工具分析）
3.mixed（混合）MBR（mixed based replication）：以上两种模式的混合
SBR和RBR模式的对比
STATEMENT：可读性较高，日志量少，但是不够严谨
ROW：可读性很低，日志量大，足够严谨
建议使用ROW记录模式
event（事件）
简介
二进制日志的最小记录单元
对于DDL,DCL，一个语句就是一个event
对于DML语句讲，只记录已提交的事务
event组成
1.事件的开始标识
2.事件内容
3.事件的结束标识
position号
开始标识：at 111
结束标识：end_log_pos 222
某个事件在binlog中的相对应位置号
为了方便截取日志
日志操作
查看有多少个binlog
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2256 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 690524 |
| mysql-bin.000004 | 1501 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 6168 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 194 |
+------------------+-----------+
8 rows in set (0.00 sec)
重启一个新binlog
3306 [(none)]>flush logs;
Query OK, 0 rows affected (0.02 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2256 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 690524 |
| mysql-bin.000004 | 1501 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 6168 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 241 |
| mysql-bin.000009 | 194 |
+------------------+-----------+
9 rows in set (0.00 sec)
查看正在使用那个文件
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
查看某个日志内容
3306 [(none)]>show binlog events in 'mysql-bin.000009';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000009 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000009 | 123 | Previous_gtids | 6 | 194 | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)
Log_name：binlog文件名
Pos：开始的position号
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：MySQL服务号标识
End_log_pos：事件的结束位置号
Info：事件内容
日志查看
查看某个文件
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000006 |head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup
ROLLBACK/*!*/;
BINLOG '
DogJXQ8GAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAOiAldEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATlGdnQ=
'/*!*/;
# at 123
#190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs
转换上面命令乱码信息
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000006|head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup
ROLLBACK/*!*/;
# at 123
#190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs
# 8d397b30-8791-11e9-acbd-000c2902fc28:1-28
查看日志中指定库的内容
[root@db01 ~]# mysqlbinlog -d world /data/binlog/mysql-bin.000006
查看指定时间内的日志
[root@db01 ~]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000006
根据position号截取日志
--start-position=xxx
--stop-position=xxx
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
binlog日志的GTID新特性
介绍
5.6版本新家的特性，在5.7中做了加强
5.6版本中没有这个功能
5.7版本中，即使不开启也会自动生成
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-51
配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启数据库
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
3306 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000010 | 353 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-52 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
具备GTID后，截取查看某些事物日志
--include-gtids截取几号到几号
--exclude-gtids排除几号
3306 [(none)]>mysqlbinlog --include-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:1-52' --exclude-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:10' /data/binlog/mysql-bin.00004046
GTID的幂等性
开启GTID后，MySQL恢复binlog是，重复的GTID事务不会在执行
就像恢复，怎么办？
mysqlbinlog时加上--skip-gtids
进行source恢复时先设置set sql_log_bin=0
在source
恢复后切记改回来sql sql_log_bin=1
二进制日志自动清理
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
默认是不清理
企业建议，至少保留两个全备周期+1天的binlog
set global expire_logs_days=15
永久生效：
vim /etc/my.cnf
expire_logs_days=15
手动清理
3306 [(none)]> purge master logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 353 |
+------------------+-----------+
1 row in set (0.00 sec)
注意：不要手工rm binlog文件
1.my.cnf binlog关闭，启动
2.把数据库关闭，开启binlog启动数据库
删除所有binlog，从000001开始重新记录
！！！！reset master,在主从关系中，主库执行此操作，主从环境必崩！！！！！！
日志是怎么滚动的
flush logs；手动滚动
重启mysql也会重新滚动一个
日志文件大小到达设定值（max_binlog_dize）
slow_log慢日志
作用
记录慢SQL语句的日志，定位低效SQL语句的工具日志
配置
开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/slow/slow.log
设置慢查询的时间标准
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes
查看
mysqldumpslow -s c -t 10 /data/slow/slow.log
-s：表示按何种方式排序，c,t,l,r分别是按照记录次数，时间，查询时间，返回的记录数，来排序，ac,at,al,ar表示相应的倒序
-t：是top n的意思
-g：后边可以写一个正则匹配模式，大小写不敏感
作用
1.备份恢复必须依赖二进制日志
2.主从环境必须依赖二进制日志基础参数：
基础参数 开关 3306 [(none)]>select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) 日志路径及名字 3306 [(none)]>select @@log_bin_basename; +------------------------+ | @@log_bin_basename | +------------------------+ | /data/binlog/mysql-bin | +------------------------+ 1 row in set (0.00 sec) 服务ID号 3306 [(none)]>select @@server_id; +-------------+ | @@server_id | +-------------+ | 6 | +-------------+ 1 row in set (0.00 sec) 二进制日志格式 3306 [(none)]>select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 1 row in set (0.00 sec) 双一标准之二 3306 [(none)]>select @@sync_binlog; +---------------+ | @@sync_binlog | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec)
配置 注意，mysql默认是没有开启二进制日志的 创建日志目录 mkdir /data/binlog -p chown -R mysql.mysql /data/binlog 修改配置文件 vim /etc/my.cnf server_id=6 log_bin=/data/binlog/mysql-bin 此处可以直接写路径 binlog_format=row 重启生效 1.server_id 主要是在主从复制过程中必须加的，但是在5.7版本中，要用一下参数，开启binlog日志，即使是单机，也是必须加的 2.log_bin=/data/binlog/mysql-bin 开启二进制日志功能 设置二进制日志目录及名称前缀 3.binlog_format=row binlog的记录格式
binlog记录了什么
binlog是sql层的功能，记录的是变更sql语句，不记录查询语句
记录SQL语句种类
DDL：原封不动的记录当前DDL（statement语句方式）
DCL：原封不动的记录当前DCL（statement语句方式）
DML：只记录已经提交的事务DML
DML三种记录格式
binlog_format=
1.statement（5.6默认），SBR（statement based replication）：原封不动的记录当前DML
2.ROW（5.7默认）RBR（ROW based replication）：记录数据行的变化（用户看不懂，需要工具分析）
3.mixed（混合）MBR（mixed based replication）：以上两种模式的混合
SBR和RBR模式的对比
STATEMENT：可读性较高，日志量少，但是不够严谨
ROW：可读性很低，日志量大，足够严谨
建议使用ROW记录模式
event（事件）
简介
二进制日志的最小记录单元
对于DDL,DCL，一个语句就是一个event
对于DML语句讲，只记录已提交的事务
event组成
1.事件的开始标识
2.事件内容
3.事件的结束标识
position号
开始标识：at 111
结束标识：end_log_pos 222
某个事件在binlog中的相对应位置号
为了方便截取日志
日志操作
查看有多少个binlog
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2256 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 690524 |
| mysql-bin.000004 | 1501 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 6168 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 194 |
+------------------+-----------+
8 rows in set (0.00 sec)
重启一个新binlog
3306 [(none)]>flush logs;
Query OK, 0 rows affected (0.02 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2256 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 690524 |
| mysql-bin.000004 | 1501 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 6168 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 241 |
| mysql-bin.000009 | 194 |
+------------------+-----------+
9 rows in set (0.00 sec)
查看正在使用那个文件
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
查看某个日志内容
3306 [(none)]>show binlog events in 'mysql-bin.000009';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000009 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000009 | 123 | Previous_gtids | 6 | 194 | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)
Log_name：binlog文件名
Pos：开始的position号
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：MySQL服务号标识
End_log_pos：事件的结束位置号
Info：事件内容
日志查看
查看某个文件
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000006 |head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup
ROLLBACK/*!*/;
BINLOG '
DogJXQ8GAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAOiAldEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATlGdnQ=
'/*!*/;
# at 123
#190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs
转换上面命令乱码信息
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000006|head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup
ROLLBACK/*!*/;
# at 123
#190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs
# 8d397b30-8791-11e9-acbd-000c2902fc28:1-28
查看日志中指定库的内容
[root@db01 ~]# mysqlbinlog -d world /data/binlog/mysql-bin.000006
查看指定时间内的日志
[root@db01 ~]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000006
根据position号截取日志
--start-position=xxx
--stop-position=xxx
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
binlog日志的GTID新特性
介绍
5.6版本新家的特性，在5.7中做了加强
5.6版本中没有这个功能
5.7版本中，即使不开启也会自动生成
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-51
配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启数据库
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
3306 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000010 | 353 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-52 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
具备GTID后，截取查看某些事物日志
--include-gtids截取几号到几号
--exclude-gtids排除几号
3306 [(none)]>mysqlbinlog --include-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:1-52' --exclude-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:10' /data/binlog/mysql-bin.00004046
GTID的幂等性
开启GTID后，MySQL恢复binlog是，重复的GTID事务不会在执行
就像恢复，怎么办？
mysqlbinlog时加上--skip-gtids
进行source恢复时先设置set sql_log_bin=0
在source
恢复后切记改回来sql sql_log_bin=1
二进制日志自动清理
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
默认是不清理
企业建议，至少保留两个全备周期+1天的binlog
set global expire_logs_days=15
永久生效：
vim /etc/my.cnf
expire_logs_days=15
手动清理
3306 [(none)]> purge master logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 353 |
+------------------+-----------+
1 row in set (0.00 sec)
注意：不要手工rm binlog文件
1.my.cnf binlog关闭，启动
2.把数据库关闭，开启binlog启动数据库
删除所有binlog，从000001开始重新记录
！！！！reset master,在主从关系中，主库执行此操作，主从环境必崩！！！！！！
日志是怎么滚动的
flush logs；手动滚动
重启mysql也会重新滚动一个
日志文件大小到达设定值（max_binlog_dize）
slow_log慢日志
作用
记录慢SQL语句的日志，定位低效SQL语句的工具日志
配置
开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/slow/slow.log
设置慢查询的时间标准
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes
查看
mysqldumpslow -s c -t 10 /data/slow/slow.log
-s：表示按何种方式排序，c,t,l,r分别是按照记录次数，时间，查询时间，返回的记录数，来排序，ac,at,al,ar表示相应的倒序
-t：是top n的意思
-g：后边可以写一个正则匹配模式，大小写不敏感
简介
二进制日志的最小记录单元
对于DDL,DCL，一个语句就是一个event
对于DML语句讲，只记录已提交的事务
event组成
1.事件的开始标识
2.事件内容
3.事件的结束标识
position号
开始标识：at 111
结束标识：end_log_pos 222
某个事件在binlog中的相对应位置号
为了方便截取日志
日志操作
查看有多少个binlog
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2256 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 690524 |
| mysql-bin.000004 | 1501 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 6168 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 194 |
+------------------+-----------+
8 rows in set (0.00 sec)
重启一个新binlog
3306 [(none)]>flush logs;
Query OK, 0 rows affected (0.02 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2256 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 690524 |
| mysql-bin.000004 | 1501 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 6168 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 241 |
| mysql-bin.000009 | 194 |
+------------------+-----------+
9 rows in set (0.00 sec)
查看正在使用那个文件
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
查看某个日志内容
3306 [(none)]>show binlog events in 'mysql-bin.000009';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000009 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000009 | 123 | Previous_gtids | 6 | 194 | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)
Log_name：binlog文件名
Pos：开始的position号
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：MySQL服务号标识
End_log_pos：事件的结束位置号
Info：事件内容
日志查看
查看某个文件
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000006 |head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup
ROLLBACK/*!*/;
BINLOG '
DogJXQ8GAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAOiAldEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATlGdnQ=
'/*!*/;
# at 123
#190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs
转换上面命令乱码信息
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000006|head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup
ROLLBACK/*!*/;
# at 123
#190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs
# 8d397b30-8791-11e9-acbd-000c2902fc28:1-28
查看日志中指定库的内容
[root@db01 ~]# mysqlbinlog -d world /data/binlog/mysql-bin.000006
查看指定时间内的日志
[root@db01 ~]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000006
根据position号截取日志
--start-position=xxx
--stop-position=xxx
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
binlog日志的GTID新特性
介绍
5.6版本新家的特性，在5.7中做了加强
5.6版本中没有这个功能
5.7版本中，即使不开启也会自动生成
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-51
配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启数据库
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
3306 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000010 | 353 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-52 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
具备GTID后，截取查看某些事物日志
--include-gtids截取几号到几号
--exclude-gtids排除几号
3306 [(none)]>mysqlbinlog --include-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:1-52' --exclude-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:10' /data/binlog/mysql-bin.00004046
GTID的幂等性
开启GTID后，MySQL恢复binlog是，重复的GTID事务不会在执行
就像恢复，怎么办？
mysqlbinlog时加上--skip-gtids
进行source恢复时先设置set sql_log_bin=0
在source
恢复后切记改回来sql sql_log_bin=1
二进制日志自动清理
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
默认是不清理
企业建议，至少保留两个全备周期+1天的binlog
set global expire_logs_days=15
永久生效：
vim /etc/my.cnf
expire_logs_days=15
手动清理
3306 [(none)]> purge master logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 353 |
+------------------+-----------+
1 row in set (0.00 sec)
注意：不要手工rm binlog文件
1.my.cnf binlog关闭，启动
2.把数据库关闭，开启binlog启动数据库
删除所有binlog，从000001开始重新记录
！！！！reset master,在主从关系中，主库执行此操作，主从环境必崩！！！！！！
日志是怎么滚动的
flush logs；手动滚动
重启mysql也会重新滚动一个
日志文件大小到达设定值（max_binlog_dize）
slow_log慢日志
作用
记录慢SQL语句的日志，定位低效SQL语句的工具日志
配置
开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/slow/slow.log
设置慢查询的时间标准
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes
查看
mysqldumpslow -s c -t 10 /data/slow/slow.log
-s：表示按何种方式排序，c,t,l,r分别是按照记录次数，时间，查询时间，返回的记录数，来排序，ac,at,al,ar表示相应的倒序
-t：是top n的意思
-g：后边可以写一个正则匹配模式，大小写不敏感
查看某个文件 [root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000006 |head -20 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup ROLLBACK/*!*/; BINLOG ' DogJXQ8GAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAOiAldEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA ATlGdnQ= '/*!*/; # at 123 #190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs 转换上面命令乱码信息 [root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000006|head -20 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190619 8:55:42 server id 6 end_log_pos 123 CRC32 0x74764639 Start: binlog v 4, server v 5.7.20-log created 190619 8:55:42 at startup ROLLBACK/*!*/; # at 123 #190619 8:55:42 server id 6 end_log_pos 194 CRC32 0x6d9bb0ea Previous-GTIDs # 8d397b30-8791-11e9-acbd-000c2902fc28:1-28 查看日志中指定库的内容 [root@db01 ~]# mysqlbinlog -d world /data/binlog/mysql-bin.000006 查看指定时间内的日志 [root@db01 ~]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000006
根据position号截取日志
--start-position=xxx
--stop-position=xxx
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
binlog日志的GTID新特性
介绍
5.6版本新家的特性，在5.7中做了加强
5.6版本中没有这个功能
5.7版本中，即使不开启也会自动生成
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-51
配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启数据库
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
3306 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000010 | 353 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-52 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
具备GTID后，截取查看某些事物日志
--include-gtids截取几号到几号
--exclude-gtids排除几号
3306 [(none)]>mysqlbinlog --include-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:1-52' --exclude-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:10' /data/binlog/mysql-bin.00004046
GTID的幂等性
开启GTID后，MySQL恢复binlog是，重复的GTID事务不会在执行
就像恢复，怎么办？
mysqlbinlog时加上--skip-gtids
进行source恢复时先设置set sql_log_bin=0
在source
恢复后切记改回来sql sql_log_bin=1
二进制日志自动清理
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
默认是不清理
企业建议，至少保留两个全备周期+1天的binlog
set global expire_logs_days=15
永久生效：
vim /etc/my.cnf
expire_logs_days=15
手动清理
3306 [(none)]> purge master logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 353 |
+------------------+-----------+
1 row in set (0.00 sec)
注意：不要手工rm binlog文件
1.my.cnf binlog关闭，启动
2.把数据库关闭，开启binlog启动数据库
删除所有binlog，从000001开始重新记录
！！！！reset master,在主从关系中，主库执行此操作，主从环境必崩！！！！！！
日志是怎么滚动的
flush logs；手动滚动
重启mysql也会重新滚动一个
日志文件大小到达设定值（max_binlog_dize）
slow_log慢日志
作用
记录慢SQL语句的日志，定位低效SQL语句的工具日志
配置
开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/slow/slow.log
设置慢查询的时间标准
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes
查看
mysqldumpslow -s c -t 10 /data/slow/slow.log
-s：表示按何种方式排序，c,t,l,r分别是按照记录次数，时间，查询时间，返回的记录数，来排序，ac,at,al,ar表示相应的倒序
-t：是top n的意思
-g：后边可以写一个正则匹配模式，大小写不敏感
介绍
5.6版本新家的特性，在5.7中做了加强
5.6版本中没有这个功能
5.7版本中，即使不开启也会自动生成
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-51
配置 vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true 重启数据库 3306 [(none)]>3306 [(none)]>show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) 3306 [(none)]>create database test; Query OK, 1 row affected (0.00 sec) 3306 [(none)]>show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000010 | 353 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-52 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec)
具备GTID后，截取查看某些事物日志
--include-gtids截取几号到几号
--exclude-gtids排除几号
3306 [(none)]>mysqlbinlog --include-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:1-52' --exclude-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:10' /data/binlog/mysql-bin.00004046
GTID的幂等性
开启GTID后，MySQL恢复binlog是，重复的GTID事务不会在执行
就像恢复，怎么办？
mysqlbinlog时加上--skip-gtids
进行source恢复时先设置set sql_log_bin=0
在source
恢复后切记改回来sql sql_log_bin=1
二进制日志自动清理
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
默认是不清理
企业建议，至少保留两个全备周期+1天的binlog
set global expire_logs_days=15
永久生效：
vim /etc/my.cnf
expire_logs_days=15
手动清理 3306 [(none)]> purge master logs to 'mysql-bin.000010'; Query OK, 0 rows affected (0.00 sec) 3306 [(none)]>show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000010 | 353 | +------------------+-----------+ 1 row in set (0.00 sec) 注意：不要手工rm binlog文件 1.my.cnf binlog关闭，启动 2.把数据库关闭，开启binlog启动数据库 删除所有binlog，从000001开始重新记录 ！！！！reset master,在主从关系中，主库执行此操作，主从环境必崩！！！！！！
日志是怎么滚动的
flush logs；手动滚动
重启mysql也会重新滚动一个
日志文件大小到达设定值（max_binlog_dize）