欢迎大家来到IT世界,在知识的湖畔探索吧!
错误日志(log_error)
–记录启动\关闭\日常运行过程中,状态信息,警告,错误
默认就是开启的: /数据路径下/hostname.err手工设定:Master [(none)]>select @@log_error;vim /etc/my.cnflog_error=/var/log/mysql.loglog_timestamps=system重启生效show variables like 'log_error';
欢迎大家来到IT世界,在知识的湖畔探索吧!
默认位置:
DATADIR/hostname.err
配置方式
vim .etc/my.cnf
log_error=/data/mysql/data/mysql.log
查看:
欢迎大家来到IT世界,在知识的湖畔探索吧!mysql> select @@log_error;+----------------------+| @@log_error |+----------------------+| /data/3307/mysql.log |+----------------------+1 row in set (0.00 sec)
binlog(binary logs):二进制日志 *****
(1)备份恢复必须依赖二进制日志(2)主从环境必须依赖二进制日志
binlog配置 (5.7必须加server_id)
欢迎大家来到IT世界,在知识的湖畔探索吧!注意:MySQL默认是没有开启二进制日志的。基础参数查看:开关:[(none)]>select @@log_bin;日志路径及名字[(none)]>select @@log_bin_basename;服务ID号:[(none)]>select @@server_id;二进制日志格式:[(none)]>select @@binlog_format;双一标准之二:[(none)]>select @@sync_binlog;
log_error=/data/mysql/data/mysql.log
log_bin=/data/binlog/3306/mysql-bin #/data/binlog/3306/ :目录 mysql-bin:文件名
[root@localhost 3306]# pwd/data/binlog/3306[root@localhost 3306]# ll总用量 8-rw-r----- 1 mysql mysql 154 9月 4 13:51 mysql-bin.000001-rw-r----- 1 mysql mysql 35 9月 4 13:51 mysql-bin.index
查看配置
mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------+| log_bin | ON || log_bin_basename | /data/binlog/3306/mysql-bin || log_bin_index | /data/binlog/3306/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)
binlog记录了什么?
—–binlog是SQL层的功能。记录的是变更SQL语句,不记录查询语句。
记录SQL语句种类
DDL :原封不动的记录当前DDL(statement语句方式)。
DCL :原封不动的记录当前DCL(statement语句方式)。
DML :只记录已经提交的事务DML
DML三种记录方式binlog_format(binlog的记录格式)参数影响
(1)statement(5.6默认)SBR(statement based replication) :语句模式原封不动的记录当前DML。
(2)ROW(5.7 默认值)RBR(ROW based replication) :记录数据行的变化(用户看不懂,需要工具分析)
(3)mixed(混合)MBR(mixed based replication)模式 :以上两种模式的混合
mysql> show variables like 'bnlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+1 row in set (0.00 sec)
面试题
SBR与RBR模式的对比STATEMENT:可读性较高,日志量少,但是不够严谨ROW :可读性很低,日志量大,足够严谨update t1 set xxx=xxx where id>1000 ? -->一共500w行,row模式怎么记录的日志为什么row模式严谨?id name intimeinsert into t1 values(1,'zs',now())我们建议使用:row记录模式
event(事件)是什么?
事件的简介
二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin; 120 – 340
DML1 340 – 460
DML2 460 – 550
commit; 550 – 760
event的组成
三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用是什么?
为了方便我们截取事件
查看一共多少个binlog
Master [(none)]>show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 154 |+------------------+-----------+1 row in set (0.01 sec)Master [(none)]>flush logs;Query OK, 0 rows affected (0.03 sec)Master [(none)]>flush logs;Query OK, 0 rows affected (0.01 sec)Master [(none)]>show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 201 || mysql-bin.000002 | 201 || mysql-bin.000003 | 154 |+------------------+-----------+3 rows in set (0.00 sec)Master [(none)]>
通过执行flush logs命令之后,再次查询binary log信息,发现已经使用了一个新的bin log文件了。
flush logs命令的作用就是关闭当前使用的binary log,然后打开一个新的binary log文件,binlog文件的序号递增加1
查看mysql正在使用的日志文件
Master [(none)]>show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+Master [(none)]>
file:当前MySQL正在使用的文件名
Position:最后一个事件的结束位置号
日志内容查看
event查看
Master [binlog]>show binlog events in 'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+----------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+----------------+-----------+-------------+----------------------------------------+| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-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 | 319 | create database binlog || mysql-bin.000003 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' || mysql-bin.000003 | 384 | Query | 6 | 486 | use `binlog`; create table t1 (id int) |+------------------+-----+----------------+-----------+-------------+----------------------------------------+Log_name:binlog文件名Pos:开始的position *****Event_type:事件类型Format_desc:格式描述,每一个日志文件的第一个事件,多用户没有意义,MySQL识别binlog必要信息Server_id:mysql服务号标识End_log_pos:事件的结束位置号 *****Info:事件内容*****补充:SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count][root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop
binlog文件内容详细查看
mysqlbinlog /data/mysql/mysql-bin.000006mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003mysqlbinlog -d binlog /data/binlog/mysql-bin.000003[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004
基于Position号进行日志截取
核心就是找截取的起点和终点--start-position=321--stop-position=513 mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql案例: 使用binlog日志进行数据恢复模拟:1. [(none)]>create database binlog charset utf8;2. [(none)]>use binlog;[binlog]>create table t1(id int);3. [binlog]>insert into t1 values(1);[binlog]>commit;[binlog]>insert into t1 values(2);[binlog]>commit;[binlog]>insert into t1 values(3);[binlog]>commit;4. [binlog]>drop database binlog;恢复:[(none)]>show master status ;[(none)]>show binlog events in 'mysql-bin.000004';[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql[(none)]>set sql_Log_bin=0;#设为0后,在Master数据库上执行的语句临时都不记录binlog,退出窗口可以恢复[(none)]>source /tmp/bin.sql
-----上述通过pos位置恢复 需要关闭gitd----经过测试发现 开起来gitd,通过pos位置无法恢复数据
mysqlbinlog --start-position=219 --stop-position=1012 /data/binlog/3306/mysql-bin.000002 >/tmp/bin.sql
binlog日志的GTID新特性
GTID 介绍
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’
GTID(Global Transaction ID)
是对于一个已提交事务的编号,并且是一个全局唯一的编号。
它的官方定义如下:GTID = source_id :transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
重要参数介绍:
vim /etc/my.cnfgtid-mode=onenforce-gtid-consistency=truesystemctl restart mysqldMaster [(none)]>create database gtid charset utf8;Query OK, 1 row affected (0.01 sec)Master [(none)]>show master status ;+------------------+----------+--------------+------------------+----------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+----------------------------------------+| mysql-bin.000004 | 326 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1 |+------------------+----------+--------------+------------------+----------------------------------------+1 row in set (0.00 sec)Master [(none)]>use gtidDatabase changedMaster [gtid]>create table t1 (id int);Query OK, 0 rows affected (0.01 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 | 489 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>create table t2 (id int);Query OK, 0 rows affected (0.01 sec)Master [gtid]>create table t3 (id int);Query OK, 0 rows affected (0.02 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 | 815 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>begin;Query OK, 0 rows affected (0.00 sec)Master [gtid]>insert into t1 values(1);Query OK, 1 row affected (0.00 sec)Master [gtid]>commit;Query OK, 0 rows affected (0.00 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 | 1068 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>begin;Query OK, 0 rows affected (0.00 sec)Master [gtid]>insert into t2 values(1);Query OK, 1 row affected (0.00 sec)Master [gtid]>commit;Query OK, 0 rows affected (0.01 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 | 1321 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)
基于GTID进行查看binlog
具备GTID后,截取查看某些事务日志:--include-gtids--exclude-gtidsmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004跳过多个事务idmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4,dff98809-55c3-11e9-a58b-000c2928f5dd:5' /data/binlog/mysql-bin.000004
GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了就想恢复?怎么办?--skip-gtidsmysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 > /tmp/binlog.sqlset sql_log_bin=0;source /tmp/binlog.sqlset sql_log_bin=1;
使用二进制日志恢复数据案例
故障环境介绍
创建了一个库 db, 导入了表t1 ,t1表中录入了很多数据一个开发人员,drop database db;没有备份,日志都在.怎么恢复?思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)故障案例模拟:(0) drop database if exists db ;(1) create database db charset utf8; (2) use db;(3) create table t1 (id int);(4) insert into t1 values(1),(2),(3);(5) insert into t1 values(4),(5),(6);(6) commit(7) update t1 set id=30 where id=3;(8) commit;(9) delete from t1 where id=4;(10)commit;(11)insert into t1 values(7),(8),(9);(12)commit;(13)drop database db;========================drop database if exists db ;create database db charset utf8; use db;create table t1 (id int);insert into t1 values(1),(2),(3);insert into t1 values(4),(5),(6);commit;update t1 set id=30 where id=3;commit;delete from t1 where id=4;commit;insert into t1 values(7),(8),(9);commit;drop database db;=======运行以上语句,模拟故障场景需求:将数据库恢复到以下状态(提示第9步和第13步是误操作,其他都是正常操作)
恢复过程(无GTID时的恢复)
1,查看当前使用的 binlog文件
oldguo [db]>show master status ;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000006 | 1873 | | | |+------------------+----------+--------------+------------------+-------------------+2.查看事件:第一段:| mysql-bin.000006 | 813 | Query | 1 | 907 | use `db`; create table t1 (id int) || mysql-bin.000006 | 907 | Query | 1 | 977 | BEGIN || mysql-bin.000006 | 977 | Table_map | 1 | 1020 | table_id: 77 (db.t1) || mysql-bin.000006 | 1020 | Write_rows | 1 | 1070 | table_id: 77 flags: STMT_END_F || mysql-bin.000006 | 1070 | Table_map | 1 | 1113 | table_id: 77 (db.t1) || mysql-bin.000006 | 1113 | Write_rows | 1 | 1163 | table_id: 77 flags: STMT_END_F || mysql-bin.000006 | 1163 | Xid | 1 | 1194 | COMMIT /* xid=74 */ || mysql-bin.000006 | 1194 | Query | 1 | 1264 | BEGIN || mysql-bin.000006 | 1264 | Table_map | 1 | 1307 | table_id: 77 (db.t1) || mysql-bin.000006 | 1307 | Update_rows | 1 | 1353 | table_id: 77 flags: STMT_END_F || mysql-bin.000006 | 1353 | Xid | 1 | 1384 | COMMIT /* xid=77 */ mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql
第二段:
| mysql-bin.000006 | 1568 | Query | 1 | 1638 | BEGIN || mysql-bin.000006 | 1638 | Table_map | 1 | 1681 | table_id: 77 (db.t1) || mysql-bin.000006 | 1681 | Write_rows | 1 | 1731 | table_id: 77 flags: STMT_END_F || mysql-bin.000006 | 1731 | Xid | 1 | 1762 | COMMIT /* xid=81 */ mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql
3,恢复
set sql_log_bin=0;source /tmp/bin1.sqlsource /tmp/bin2.sqlset sql_log_bin=1;oldguo [db]>select * from t1;+------+| id |+------+| 1 || 2 || 30 || 4 || 5 || 6 || 7 || 8 || 9 |
有GTID的恢复:
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql
恢复
set sql_log_bin=0;
source /tmp/bin.sql
二进制日志其他操作
自动清理日志
show variables like '%expire%';expire_logs_days 0 自动清理时间,是要按照全备周期+1set global expire_logs_days=8;永久生效:修改配置文件vim my.cnfexpire_logs_days=15;企业建议,至少保留两个全备周期+1的binlog
手工清理(危险)
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;PURGE BINARY LOGS TO 'mysql-bin.000010';注意:不要手工 rm binlog文件1. my.cnf binlog关闭掉,启动数据库2.把数据库关闭,开启binlog,启动数据库删除所有binlog,并从000001开始重新记录日志
reset master; 主从关系中,主库执行此操作,主从环境必崩
日志是怎么滚动
flush logs; 重启mysql也会自动滚动一个新的日志文件达到1G大小(max_binlog_size)| max_binlog_size | 1073741824 备份时,加入参数也可以自动滚动
slow_log 慢日志
记录慢SQL语句的日志,定位低效SQL语句的工具日志
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/data/localhost-slow.log |+---------------------------+-------------------------------------+5 rows in set (0.00 sec)
开启慢日志(默认没开启)
开关:slow_query_log=1 文件位置及名字 slow_query_log_file=/data/mysql/slow.log设定慢查询时间:long_query_time=0.1没走索引的语句也记录:log_queries_not_using_indexes #不走索引vim /etc/my.cnfslow_query_log=1 slow_query_log_file=/data/mysql/slow.loglong_query_time=0.1log_queries_not_using_indexessystemctl restart mysqld
mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log# 第三方工具(自己扩展)https://www.percona.com/downloads/percona-toolkit/LATEST/yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5toolkit工具包中的命令:./pt-query-diagest /data/mysql/slow.logAnemometer基于pt-query-digest将MySQL慢查询可视化
常用的参数
-s:是表示按照何种方式排序
参数值 |
说明 |
c |
访问计数 |
l |
锁定时间 |
r |
返回记录 |
t |
查询时间 |
al |
平均锁定时间 |
ar |
平均返回记录数 |
at |
平均查询时间 |
-t:是top n的意思,即为返回前面多少条的数据
-g:后边可以写一个正则匹配模式,大小写不敏感的
Mysql慢查询日志分析及percona-toolkit的使用
相关文献:
https://blog.csdn.net/qq_41600067/article/details/112952251
https://naibawu.com/1250.html
https://www.cnblogs.com/yaomaomao/p/12672283.html
本文来自博客园,作者:孙龙-程序员,转载请注明原文链接:https://www.cnblogs.com/sunlong88/p/16655005.html
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://itzsg.com/18617.html