首页 / 日志 / 专职DBA-MySQL日志管理2
专职DBA-MySQL日志管理2
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了专职DBA-MySQL日志管理2，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15047字，纯文字阅读大概需要22分钟。
内容图文
MySQL日志管理2
[root@db01 ~]# mysqld --defaults-file=/data/mysql/3306/my.cnf & [root@db01 ~]# ps -ef | grep mysql [root@db01 ~]# netstat -lnp | grep mysql [root@db01 ~]# mysql -S /data/mysql/3306/mysql.sock -p 错误日志(log_error): 文本形式记录MySQL启动，关闭，日常运行过程中所有状态信息，警告，错误。 错误日志配置，默认就是开启的：/数据目录下/hostname.err 手工设定： mysql> select @@log_error; +----------------------------+ | @@log_error | +----------------------------+ | /data/mysql/3306/error.log | +----------------------------+ 1 row in set (0.01 sec) mysql> show global variables like "log_error"; [root@db01 ~]# grep "log_error" /data/mysql/3306/my.cnf log_error = /data/mysql/3306/error.log # /usr/local/mysql/data/localhost.localdomain.err log_timestamps = system 日志内容查看，主要关注[ERROR]看上下文 [root@db01 ~]# grep "ERROR" /data/mysql/3306/error.log binlog(binary logs) 二进制日志 二进制形式,记录MySQL工作过程中,所有变更类(除了select和show)的操作日志。 可以理解为记录的是SQL语句,逻辑性质日志。 (1).数据恢复 备份恢复必须依赖binlog日志。 (2).主从复制 主从环境必须依赖binlog日志。 binlog配置 (5.7必须加server_id) 注意：MySQL默认是没有开启二进制日志的。 基础参数查看： mysql> show global variables like "%log_bin%"; mysql> show global variables like "%binlog%"; 开关 mysql> select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) 日志路径及名字 mysql> select @@log_bin_basename; +---------------------------------+ | @@log_bin_basename | +---------------------------------+ | /data/mysql/3306/logs/mysql-bin | +---------------------------------+ 1 row in set (0.00 sec) 服务ID号 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 113306 | +-------------+ 1 row in set (0.00 sec) 二进制日志格式 mysql> select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 1 row in set (0.00 sec) 双一标准之二: mysql> select @@sync_binlog; +---------------+ | @@sync_binlog | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) my.cnf配置文件 [root@db01 ~]# vim /data/mysql/3306/my.cnf server_id = 113306 log_bin = /data/mysql/3306/logs/mysql-bin # off binlog_format = row sync_binlog = 1 #双1标准,每次事务提交立即刷写binlog 参数说明 server_id=113306 主要是在主从复制过程中必须要加的，但是在5.7版本中，要用以下参数(log_bin)，开启binlog日志，即使是单机也是必加的。 log_bin = /data/mysql/3306/logs/mysql-bin (1).开启二进制日志功能。 (2).设置二进制日志目录及名称前缀 binlog_format = row binlog的记录格式？？ 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的SQL语句本身。 (2).ROW(5.7 默认值) RBR(ROW based replication) 记录数据行的变化(用户看不懂，需要工具分析) (3).mixed(混合)MBR (mixed based replication) 以上两种模式的混合。 记录模式: STATEMENT(SBR): 语句模式 ,记录的就是SQL语句本身. ROW (RBR): 行模式, 记录的是数据行的变化. Mixed (MBR): 混合模式,自动判断用什么模式存储日志. 我们的建议是:row（> = 5.7.7 默认ROW） 面试题： SBR与RBR模式的对比： STATEMENT：可读性较高，日志量少，但是不够严谨。 ROW：可读性很低，日志量大，足够严谨。 update t1 set xxx=xxx where id>1000 ? -->一共500w行，row模式怎么记录的日志 为什么row模式严谨？ id name intime insert into t1 values(1,‘zhang3‘,now()); 我们建议使用：row记录模式。 MySQL一些新特性, 必须要基于ROW模式,GTID,GC MTS event（事件）是什么？ 事件的简介：binlog记录的最小单元。 对于DDL,DCL,一个语句就是一个event。 对于DML语句来讲，只记录已提交的事务。 start position (文件中的相对位置号) 开始位置 事件内容: DDL DCL DML stop position (文件中的相对位置号) 结束位置 例如以下列子，就被分为了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中的相对位置号。位置号的作用是什么？为了方便我们截取事件。 日志文件查看 查看日志的开启情况 log_bin参数设置的路径，可以找到二进制日志。 mysql> show variables like "%log_bin%"; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /data/mysql/3306/logs/mysql-bin | | log_bin_index | /data/mysql/3306/logs/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 mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec) 查看mysql正在使用的binlog日志文件 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) File：当前MySQL正在使用的文件名 Position：最后一个事件的结束位置号 查看binlog事件信息 mysql> show binlog events in ‘mysql-bin.000002‘; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 113306 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 113306 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec) Log_name：binlog文件名 Pos：事件开始的位置点position Event_type：事件类型 Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息 Server_id：mysql服务号标识 End_log_pos：事件的结束位置点position Info：事件内容 mysql> help show binlog events; Name: ‘SHOW BINLOG EVENTS‘ Description: Syntax: SHOW BINLOG EVENTS [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count] [root@db01 ~]# mysql -S /data/mysql/3306/mysql.sock -p -e "show binlog events in ‘mysql-bin.000002‘;" | grep drop binlog文件内容详细查看 [root@db01 ~]# mysqlbinlog /data/mysql/3306/logs/mysql-bin.000002 [root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/3306/logs/mysql-bin.000002 [root@db01 ~]# mysqlbinlog -d school /data/mysql/3306/logs/mysql-bin.000002 [root@db01 ~]# mysqlbinlog --start-datetime=‘2019-05-06 17:00:00‘ --stop-datetime=‘2019-05-06 17:01:00‘ /data/mysql/3306/logs/mysql-bin.000002 基于Position号进行日志截取 核心就是找截取的起点和终点 --start-position=219 --stop-position=512 [root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=512 /data/mysql/3306/logs/mysql-bin.000002 >/tmp/bin.sql 案例：使用binlog日志进行数据恢复 模拟 mysql> create database binlog charset=utf8mb4; mysql> use binlog; mysql> create table t1(id int); mysql> insert into t1 values(1); mysql> commit; mysql> insert into t1 values(2); mysql> commit; mysql> drop database binlog; 恢复 mysql> show master status; mysql> show binlog events in ‘mysql-bin.000002‘; [root@db01 ~]# mysqlbinlog --skip-gtids --start-position=219 --stop-position=1176 /data/mysql/3306/logs/mysql-bin.000002 >/tmp/bin.sql [root@db01 ~]# mysql -S /data/mysql/3306/mysql.sock -p mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql; mysql> set sql_log_bin=1; 面试案例： 1.备份策略每天全备，有全量的二进制日志。 2.业务中一共10个库，其中一个被误drop了。 3.需要在其他9个库正常工作过程中进行数据恢复。 补充: 200G每天(23:00)全备,每天备份binlog,周三上午10误删的表t1. 恢复思路: 1.停业务,挂维护页. 2.找临时库,恢复全备. 3.截取全备之后,一直到删表之前的所有binlog 4.导出表,导入到生产. binlog日志的GTID新特性。 GTID 介绍 5.6版本新加的特性，5.7中做了加强。 5.6中不开启，没有这个功能。 5.7中的GTID即使不开也会有自动生成。 SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘ GTID(Global Transaction ID) (MySQL5.6.9版本) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。每次累加数字。 在传统模式的基础上,加入对于每个事务唯一的编号. DDL,DCL ,一条语句就是一个事务.会分配一个唯一的GTID号码 DML, begin;xxx;commit作为一个完整的事务,分配一个唯一的GTID号 这些号码,唯一的,连续的,具备幂等性的. 它的官方定义如下： GTID = source_id:transaction_id 7E11FA47-31CA-19E1-9E56-C43AA21293967:29 mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 6f02ba92-d1b7-11e9-bf6f-000c29ca1344 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like ‘%gtid%‘; mysql> show variables like ‘%gtid%‘; 重要参数介绍： [root@db01 ~]# vim /data/mysql/3306/my.cnf gtid_mode = on enforce_gtid_consistency = on create database gtid charset utf8mb4; use gtid create table t1(id int); create table t2(id int); create table t3(id int); begin; insert into t1 values(1); commit; begin; insert into t2 values(1); commit; mysql> show master status; 基于GTID进行查看binlog 具备GTID后，截取查看某些事务日志： --include-gtids 包含 --exclude-gtids 排除 [root@db01 ~]# mysqlbinlog --include-gtids=‘dff98809-55c3-11e9-a58b-000c2928f5dd:1-6‘ --exclude-gtids=‘dff98809-55c3-11e9-a58b-000c2928f5dd:5‘ /data/mysql/3306/logs/mysql-bin.000002 GTID的幂等性：开启GTID后，MySQL恢复Binlog时，重复GTID的事务不会再执行了。 就想恢复？怎么办？ --skip-gtids [root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids=‘dff98809-55c3-11e9-a58b-000c2928f5dd:1-6‘ --exclude-gtids=‘dff98809-55c3-11e9-a58b-000c2928f5dd:5‘ /data/mysql/3306/logs/mysql-bin.000002 > /tmp/1.sql set sql_log_bin=0; source /tmp/binlog.sql set sql_log_bin=1; 结论:在开启GTID模式的binlog截取时,都要加--skip-gtids. 使用二进制日志恢复数据案例 故障环境介绍 创建了一个库db,导入了表t1,t1表中录入了很多数据。 一个开发人员,drop database db; 没有备份,日志都在,怎么恢复？ 思路：找到建库语句到删库之前所有的日志，进行恢复(开启了GTID模式) 故障案例模拟： drop database if exists db; create database db charset utf8mb4; 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; ============================================= 运行以上语句，模拟故障场景 需求：将数据库恢复到以下状态（提示第10步和第14步是误操作，其他都是正常操作） 恢复过程(无GTID时的恢复) 查看当前使用的 binlog文件 mysql> show master status; 查看事件 mysql> show binlog events in ‘mysql-bin.000002‘; 截取第一段 [root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=512 /data/mysql/3306/logs/mysql-bin.000002 >/tmp/bin1.sql 截取第二段 [root@db01 ~]# mysqlbinlog --start-position=768 --stop-position=1025 /data/mysql/3306/logs/mysql-bin.000002 >/tmp/bin2.sql 恢复 mysql> set sql_log_bin=0; mysql> source /tmp/bin1.sql mysql> source /tmp/bin2.sql mysql> set sql_log_bin=1; mysql> select * from t1; 有GTID的截取 mysqlbinlog --skip-gtids --include-gtids=‘dff98809-55c3-11e9-a58b-000c2928f5dd:1-18‘ --exclude-gtids=‘dff98809-55c3-11e9-a58b-000c2928f5dd:9‘ /data/mysql/3306/logs/mysql-bin.000002 > /tmp/bin3.sql 恢复 mysql> set sql_log_bin=0; mysql> source /tmp/bin3.sql mysql> set sql_log_bin=1; mysql> select * from t1; 二进制日志其他操作 自动清理日志，binlog删除，什么时候可以删除binlog？ 保证有一份可用的全备.看全备周期. 自动清理： mysql> show global variables like "%days%"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 7 | +------------------+-------+ 1 row in set (0.00 sec) 自动清理时间，是要按照全备周期+1 mysql> set global expire_logs_days=8; 永久生效写入配置文件/data/mysql/3306/my.cnf expire_logs_days = 15 企业建议至少保留两个全备周期+1天的binlog 7+7+1=15 手工清理 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; PURGE BINARY LOGS BEFORE ‘2008-04-02 22:46:26‘; PURGE BINARY LOGS TO ‘mysql-bin.000010‘; 注意：不要手工rm -rf binlog文件 1.my.cnf binlog关闭掉,启动数据库。 2.把数据库关闭,开启binlog,启动数据库。 删除所有binlog,并从000001开始重新记录日志。 mysql> reset master; 主从关系中，主库执行此操作，主从环境必崩。 binlog文件是怎么滚动切割 1.flush logs; 2.重启mysql也会自动滚动一个新的。 3.日志文件达到1G大小(max_binlog_size) mysql> show global variables like "%binlog_size%"; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | +-----------------+------------+ 1 row in set (0.00 sec) 4.数据备份时加入参数也可以自动滚动 slow_log 慢日志 以文本格式追加记录慢SQL语句的日志，定位低效SQL语句的工具日志。 辅助我们排查SQL性能问题的工具日志. mysql> show variables like ‘%slow%‘; mysql> show variables like ‘%index%‘; slow_query_log = on # off slow_query_log_file = /data/mysql/3306/slow.log # hostname.log 开启慢日志(默认没开启) /data/mysql/3306/my.cnf 开关 slow_query_log = on # off 文件位置及名字 slow_query_log_file = /data/mysql/3306/slow.log # hostname.log 设定慢查询时间 long_query_time=0.1 没走索引的语句也记录 log_queries_not_using_indexes=on mysqldumpslow 分析慢日志 [root@db01 ~]# mysqldumpslow -s c -t 3 /data/mysql/3306/slow.log 第三方分析工具(自己扩展) https://www.percona.com/downloads/percona-toolkit/LATEST/ [root@db01 ~]# yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 percona-toolkit工具包中的命令： [root@db01 ~]# pt-query-diagest /data/mysql/3306<span style="color: #80 本文系统来源：https://www.cnblogs.com/zhouwanchun/p/11517945.html
内容总结
以上是互联网集市为您收集整理的专职DBA-MySQL日志管理2全部内容，希望文章能够帮你解决专职DBA-MySQL日志管理2所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】