上节回顾：
- Undo :
1. rollback
2. ACSR 实现的是回滚的动作
3. 快照功能是Undo
- 各种读：
1. 脏读 读到了未提交的数据
2. 不可重复读: 在事务开始前,结束后,其他事务看到的数据时不一样的.
3. 幻读:
(1) RC模式下开启两个事务会发
(2) 1号事务中,进行范围修改)(例如 update t1 set name='zs' where id<5)
(3) 此时 ,INnoDB会将所有id小于5的表中存在的行进行行锁
(4) 假设表中没有id=3的行 ,在2号事务中手工添加id=3的行,能够正常插入，并且提交.
(5) 1号事务进行提交.按照1号事务的业务功能来讲, 所有id<5的数据都应该是zs.
(6) 1号事务查询此表数据时,发先出现了有幻行出现,没有被更新为目标值.
- 双一 和 刷写策略
innodb_flush_log_at_trx_commit=1 # 默认
等于 1. 在每次事务提交时,都立即刷写os buffer,立即fsync到磁盘
等于 0. 每秒 OS buffer fsync到磁盘
等于 2. 每次事务 进行 OS Buffer 每秒 Fsync
innodb_flush_method=o_direct
1. buffer pool 直接fsync
2. redo buffer 先os buffer 再写磁盘
降低内存开销
- 日志中的binlog
1. 参数
server_id
log_bin=/data/binlog/mysql-bin
binlog_format=row
SBR : 可读性高 日志量少 数据误差
RBR : 可读性差 ......大 没有误差
2. 命令
show binary logs;
show master status ;
show binlog events in 'xxx';
第六章 日志管理
1. 错误日志
1.1 作用
MySQL 启动及工作过程中,状态\报错\警告.
1.2 怎么配置?
oldguo[(none)]>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 怎么配置
2.2.1 修改配置文件
vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin
2.2.2 创建目录授权
mkdir -p /data/binlog -p
chown -R mysql.mysql /data/*
2.2.3 重启数据库
/etc/init.d/mysqld restart
查看
[root@db01 /data/3306/data]# strings /data/binlog/mysql-bin.000001
5.7.26-log
2.3 二进制日志记录了什么？
2.3.1 引入
除了查询类的语句，都会记录。
索引数据库变更类的语句。
2.3.2 记录语句的种类
DDL
DCL
DML
2.3.3 不同语句的记录格式说明
DDL,DCL：直接以语句(statement)方式记录
DML语句：insert update delete
oldguo[(none)]>select @@binlog_format;
SBR : statement ,做什么记录什么
RBR : row 记录数据行的变化 {默认模式，推荐}√
MBR : mixed 自动判断记录模式
面试题：说明SBR和RBR的区别？
SBR : statement , 做什么记录什么,记录的就是SQL.可读性较强.日志量相对较少。日志记录可能不准确
RBR : row , 记录数据行的变化. 默认模式,也是我们推荐的,可读性差,日志量大,日志记录准确.
update t1 set name='a' where id<1000;
update t1 sedt date=now() where id<1000;
2.3.4 binlog events(二进制日志时间)
(1) 简介
二进制日志内容以事件为最小记录单元
对于DDL和DCL,一个DDL语句就是一个事件.
对于DML(标准的事务语句)：只记录已提交的事务的DML语句
begin; 事件1
a 事件2
b 事件3
commit; 事件4
(2) 事件的构成
mysql[(none)]>create database oldgirl; # 创建一个库
Query OK, 1 row affected (0.01 sec)
mysqlbinlog /data/binlog/mysql-bin.000004 # 执行命令
or
mysqlbinlog --base64-output=decode-rows -v /data/binlog/mysql-bin.000004
# at 219
#190814 18:46:51 server id 6 end_log_pos 322 CRC32 0xa98122ba Query thread_id=3 exec_time=0 error_code=0
create database oldgirl
# at 219 事件开始的位置(position)
#190814 18:46:51 事件发生的时间
end_log_pos 322 事件结束的位置(position)
create database oldgirl 事件内容
2.3.5 二进制日志的基本查看
(1) 查看二进制日志的配置信息
show variables like '%log_bin%';
| log_bin | ON
| log_bin_basename | /data/binlog/mysql-bin
| sql_log_bin | ON
(2) 二进制日志基本信息
show binary logs;
show master status ;
(3) 查看二进制日志的事件信息
show master status ;
show binlog events in 'mysql-bin.000001';
2.4 内容查看和截取
2.4.1 内容查看命令
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000005
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000005
2.4.2 日志的截取
修改配置文件并生效
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
secure-file-priv=/tmp
log_error=/data/3306/data/mysql.log
innodb_flush_method=O_DIRECT
server_id=6
log_bin=/data/binlog/mysql-bin
[mysql]
socket=/tmp/mysql.sock
prompt=mysql[\\d]>
[root@db01 ~]# /etc/init.d/mysqld restart
--start-position
--stop-position
语法:
mysqlbinlog --start-position=xxx --stop-position=xxx /data/binlog/mysql-bin.000005>/data/bin.sql
# 演练:
# (1) 准备数据（默认自动begin）
mysql[(none)]>create database binlog charset utf8mb4;
mysql[(none)]>use binlog;
mysql[binlog]>create table t1(id int)engine=innodb charset=utf8mb4;
mysql[binlog]>insert into t1 values(1),(2),(3);
mysql[binlog]>commit;
mysql[binlog]>insert into t1 values(11),(12),(13);
mysql[binlog]>commit;
mysql[binlog]>update t1 set id=10 where id>10;
mysql[binlog]>commit;
mysql[binlog]>select * from t1; #查询表
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |
| 10 |
+------+
6 rows in set (0.00 sec)
#(2) 用来测试，直接删库
mysql[binlog]>drop database binlog;
mysql[(none)]>select * from t1;
ERROR 1046 (3D000): No database selected
#(3) 数据恢复
1. 确认日志的起点和终点
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 1845 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql[(none)]>mysql[(none)]>show binlog events in 'mysql-bin.000005';
...
起点：219
| mysql-bin.000005 | 219 | Query | 6 | 335 | create database binlog charset utf8mb4
...
终点：1747
| mysql-bin.000005 | 1747 | Query | 6 | 1845 | drop database binlog
#(4) 截取日志(命令行执行)
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1747 /data/binlog/mysql-bin.000005>/data/bin.sql
[root@db01 ~]# ls /data/bin.sql
/data/bin.sql
#(5) 恢复日志
mysql[(none)]>set sql_log_bin=0; 临时关闭当前会话的binlog记录,不影响其他会话日志记录
mysql[binlog]>source /data/bin.sql;
mysql[binlog]>set sql_log_bin=1;
#(6) 查看恢复成功的库
mysql[binlog]>show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
mysql[binlog]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |
| 10 |
+------+
6 rows in set (0.00 sec)
#(7) 扩展
1.
mysqlbinlog -d binlog --start-position=219 --stop-position=1412 /data/binlog/mysql-bin.000006>/data/bin.sql
可以借助中间库
2.5 扩展：基于 GTID 的binlog管理(传统管理方式)
https://blog.csdn.net/weixin_33804990/article/details/91697873#commentBox
2.5.0 引入
5.6 版本以后,binlog加入了新的日志记录方式,GTID
主要作用:
简化binlog截取
提供在主从复制中的高级功能
5.7版本之后，进行了GTID增强
主从性能，高可用环境，集群
2.5.1 什么是gtid (Global Transaction ID)
全局唯一的事务编号
幂等性
GTID:Server-uuid:Tx_id
545fd699-be48-11e9-8f0a-000c2980e248:1-10
mysql[binlog]>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)
2.5.2 配置
gtid_mode=on # 开关
enforce_gtid_consistency=true # 强制GTID一致性
log_slave_updates=1 # 主从复制中从库记录binlog,并统一GTID信息
修改配置文件并生效
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
secure-file-priv=/tmp
log_error=/data/3306/data/mysql.log
innodb_flush_method=O_DIRECT
server_id=6
log_bin=/data/binlog/mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates=1
[mysql]
socket=/tmp/mysql.sock
prompt=mysql[\\d]>
[root@db01 ~]# /etc/init.d/mysqld restart
2.5.3 基于gtid截取日志
mysql[(none)]>create database db1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000008 | 326 | | | a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
DDL ,DCL 一个操作就是GTID
DML ,一个完整的事务就是一个GTID
beigin;
xxx
xxx
commit
============================
mysql[(none)]>use db1;
Database changed
mysql[db1]>create table t1 (id int) charset utf8mb4;
Query OK, 0 rows affected (0.07 sec)
mysql[db1]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000008 | 503 | | | a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql[db1]>insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql[db1]>insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql[db1]>insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)
mysql[db1]>commit;
Query OK, 0 rows affected (0.01 sec)
mysql[db1]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000008 | 922 | | | a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql[db1]>show binlog events in 'mysql-bin.000008';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000008 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000008 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1' |
| mysql-bin.000008 | 219 | Query | 6 | 326 | create database db1 charset utf8mb4 |
| mysql-bin.000008 | 326 | Gtid | 6 | 391 | SET @@SESSION.GTID_NEXT= 'a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:2' |
| mysql-bin.000008 | 391 | Query | 6 | 503 | use `db1`; create table t1 (id int) charset utf8mb4 |
| mysql-bin.000008 | 503 | Gtid | 6 | 568 | SET @@SESSION.GTID_NEXT= 'a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:3' |
| mysql-bin.000008 | 568 | Query | 6 | 639 | BEGIN |
| mysql-bin.000008 | 639 | Table_map | 6 | 683 | table_id: 108 (db1.t1) |
| mysql-bin.000008 | 683 | Write_rows | 6 | 723 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000008 | 723 | Table_map | 6 | 767 | table_id: 108 (db1.t1) |
| mysql-bin.000008 | 767 | Write_rows | 6 | 807 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000008 | 807 | Table_map | 6 | 851 | table_id: 108 (db1.t1) |
| mysql-bin.000008 | 851 | Write_rows | 6 | 891 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000008 | 891 | Xid | 6 | 922 | COMMIT /* xid=14 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
2.5.4 基于gtid截取日志
--include-gtids=
--exclude-gtids=
--skip-gtids
#截取1—3号事务 导入到/data/下
[root@db01 ~]# mysqlbinlog --include-gtids='a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1-3' /data/binlog/mysql-bin.000008 >/data/gtid.sql
#截取 1—10 gtid事务 ,跳过6号和8号事务.
[root@db01 ~]# mysqlbinlog --include-gtids='a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1-10
--exclude-gtids='0a6ccfa-b73a-11e9-8f61-000c29c6fa61:6,
0a6ccfa-b73a-11e9-8f61-000c29c6fa61:8' /data/binlog/mysql-bin.000008'
/data/binlog/mysql-bin.000009>/data/gtid.sql
2.5.5 演练
(1) 准备环境
mysql[(none)]>create database gtid charset utf8mb4;
mysql[(none)]>use gtid
mysql[gtid]>create table t1 (id int) engine=innodb charset=utf8mb4;
mysql[gtid]>insert into t1 values(1),(2),(3);
mysql[gtid]>commit;
mysql[gtid]>insert into t1 values(11),(22),(33);
mysql[gtid]>commit;
mysql[gtid]>select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
+------+
(2) 搞破坏 ,删库
mysql[gtid]>drop database gtid;
(3) 找起点和终端(gtid)
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000008 | 2623 | | | a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:1-12 |
+------------------+----------+--------------+------------------+-------------------------------------------+
mysql[(none)]>show binlog events in 'mysql-bin.000008'; （8-11）
...
| mysql-bin.000008 | 1572 | Gtid | 6 | 1637 | SET @@SESSION.GTID_NEXT= 'a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:8'
| mysql-bin.000008 | 1637 | Query | 6 | 1747 | create database gtid charset utf8mb4
...
| mysql-bin.000008 | 2466 | Gtid | 6 | 2531 | SET @@SESSION.GTID_NEXT= 'a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:12'
| mysql-bin.000008 | 2531 | Query | 6 | 2623 | drop database gtid
(4) 截取日志 (命令行执行)
mysqlbinlog --skip-gtids --include-gtids='a0a6ccfa-b73a-11e9-8f61-000c29c6fa61:8-11' /data/binlog/mysql-bin.000008 >/data/gtid.sql
(5) 恢复数据
mysql[(none)]>set sql_log_bin=0;
mysql[(none)]>source /data/gtid.sql
mysql[(gitd)]>set sql_log_bin=1;
(6) 查看恢复的数据
mysql [gtid]>select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
+------+
6 rows in set (0.00 sec)
2.6二进制日志其他操作
2.6.1 自动清理日志
show variables like '%expire%';
expire_logs_days 0
自动清理时间,是要按照全备周期+1
set global expire_logs_days=8;
永久生效:
vim my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog
2.6.2 手工清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000009';
注意:不要手工 rm binlog文件
1. my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
*reset master; 主从关系中，主库执行此操作，主从环境必崩
2.6.3 binlog的滚动机制
flush logs;
重启数据库
select @@max_binlog_size;
备份时,某些参数会触发.
3. 慢日志(slow-log)
3.1 简介
记录运行较慢的语句记录slowlog中.
功能是辅助优化的工具日志.
应激性的慢 ----> show processlist;
一段时间慢 ----> slow记录,统计
3.2 配置
slow_query_log=1
slow_query_log_fil=/data/3306/data/db01-slow.log
show variables like '%query%';
select @@long_query_time;
点鼠标 -----其他因素20-30%-----------------数据库因素70-80%------------> 登录成功
添加此下参数到配置文件并重启:
slow_query_log=1
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
/etc/init.d/mysqld restart
3.3 慢语句模拟
#上传之前的100万行的数据
[root@db01 ~]# ls t100w.sql
t100w.sql
# 先临时关闭日志
set sql_log_bin=0;
#导入t100w数据包
source /root/t100w.sql;
#恢复日志
set sql_log_bin=1;
模拟查询，记录响应时间
mysql [test]>select k1,count(k2) from t100w group by k1 order by k1 desc;
...
| 00 | 258 |
+------+-----------+
1225 rows in set (1.33 sec)
mysql [test]>select * from t100w where k1='gg' order by k2 limit 10;
...
| 536612 | 63946 | Gg | 23ij | 2019-08-12 11:47:32 |
+--------+--------+------+------+---------------------+
10 rows in set (0.64 sec)
[root@db01 ~]# vim /data/3306/data/db01-slow.log
3.4 分析处理慢语句
mysqldumpslow -s c -t 5 /data/3306/data/db01-slow.log
Reading mysql slow query log from /data/3306/data/db01-slow.log
Count: 18 Time=0.18s (3s) Lock=0.00s (0s) Rows=7.2 (130), root[root]@localhost
select * from t100w where k1 ='S' limit N
Count: 10 Time=0.61s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select * from t100w where k2 ='S' order by k2 limit N
3.5 自己扩展一下
MySQL慢日志分析WEB页面Box Anemometer+pt-query-digest
https://www.cnblogs.com/xuanzhi201111/p/4128894.html
pt -query-digest /data/3306/data/db01-slow.log
集成：pt -query-digest+anemometer=WEB方式分析慢日志，二进制日志，错误日志
