一、mysql常见日志
二、错误日志
- 配置方法:
[mysqld]
log-error=/data/mysql/mysql.log
- 查看配置方式：
mysql> show variables like '%log%error%';
- 作用：
记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志,默认在data目录下
三、一般查询日志
- 配置方法:
[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log
- 查看配置方式：
show variables like '%gen%';
- 作用：
记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启
四、二进制日志
-
二进制日志都记录了什么？
已提交的数据记录，以event的形式记录到二进制文件中
-
二进制记录格式有哪些？
- row：行模式，即数据行的变化过程，上图中Age=19修改成 Age=20的过程事件。
- statement：语句模式，上图中将update语句进行记录。
- mixed：以上两者的混合模式。
-
三总模式有什么优缺点？
row模式记录得数据修改得过程，缺点是量级比较大，优点是恢复得数据得一致性高，建议使用此模式
statement是记录事务得执行语句，优点是量级比较小，缺点是恢复数据得一致性不高，例如果语句中有函数，如以当前时间作为某个列得值，那么他在恢复的时候还是会执行同样得语句使用当前得时间，而不是我从创建语句时候得时间，系统默认得模式
mixed：以上两者的混合模式。
- binlog的作用备份恢复、复制
4.1 binlog的查看和配置方法
- 开启二进制日志
set sql_log_bin=0 --->在会话级别修改为临时关闭
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin --->binlog的日志路径和打开binlog
sync_binlog=1 ---> 1表示每次事务提交都会刷新binlog日志到磁盘，0表示binglog-buffer满了才会刷新，建议是1
binlog_format=row -->记录二进制的日志类型
2、设置二进制日志记录格式（建议是ROW）：
- 配置文件中修改：
binlog-format=ROW
- 命令行修改
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
- 查看binlog设置
show variables like '%binlog%';
4.2 binlog实时点恢复
- 新建一个测试数据库和表，然后在表里插入数据
create database test1;
create table t1 (id int,name varchar(20));
mysql> use test1;
Database changed
mysql>
mysql> create table t1 (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into t1 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2,'zhangsan2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(3,'zhangsan4');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 查看二进制日志和正在使用的二进制日志
mysql> show binary logs; #---> 查看二进制日志
mysql> show master status; #---> 查看正在使用的二进制日志
show binlog events in 'my-bin.000002'; #--->查看二进制日志详情
+---------------+------+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+-------------+-----------+-------------+--------------------------------------------------------+
| my-bin.000002 | 4 | Format_desc | 10 | 120 | Server ver: 5.6.36-log, Binlog ver: 4 |
| my-bin.000002 | 120 | Query | 10 | 192 | BEGIN |
| my-bin.000002 | 192 | Table_map | 10 | 240 | table_id: 86 (test.t1) |
| my-bin.000002 | 240 | Write_rows | 10 | 290 | table_id: 86 flags: STMT_END_F
事件起始的position号码 事件结束的position号码 事件详情
- 模拟误操作，删除表中所有所有数据
delete from t1;
commit;
- 截取需要恢复事务的起始position号和结束的position号
mysqlbinlog --base64-output=decode-rows -v my-bin.000002 #--->查看二进制日志的详细信息
mysqlbinlog --start-position=1170 --stop-position=1497 my-bin.000002> /tmp/binlog.sql #---> 截取事务
source /tmp/binlog.sql; #---> 恢复丢失的数据
4.3 二进制日志的其他操作
删除二进制：
默认情况下，不会删除旧的日志文件。
根据存在时间删除日志：
SET GLOBAL expire_logs_days = 90; --->自动删除90天以前的二进制日志
…或者…
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; --->删除现在到前三天的日志
根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010'; ---> 根据文件名删除
reset master; --> 清空所有二进制日志
flush logs ---> 刷新二进制日志，生成新的日志文件
五、慢查询日志
5.1 慢日志的作用
slow-log，记录所有条件内的慢的SQL语句
优化的一种工具日志。帮我们定位问题。
是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。
5.2 慢日志设置
-
long_query_time 设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
-
slow_query_log： 指定是否开启慢查询日志
-
slow_query_log_file： 指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
-
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
-
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引
配置例子:
slow_query_log=1 #---> 开启慢日志功能
slow_query_log_file=/data/slow/slow.log #--->慢日志存放路径
long_query_time=0.5 #--->指定慢日志的阈值
log_queries_not_using_indexes #---> 不使用索引的被记录到慢日志
5.3 处理慢日志：
mysqldumpslow命令用于分析慢日志，帮助我们更快的找到需要优化的语句
mysqldumpslow -s c -t 10 /data/slow/slow.log ---执行次数最多的前十条语句
mysqldumpslow -s at -t 10 /data/slow/slow.log ---查询时间最长的前十条语句
mysqldumpslow参数说明
-s
是表示按照何种方式排序
c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。
ac、at、al、ar，表示相应的倒叙。
-t
是top n的意思，即为返回前面多少条的数据；
----------自己扩展
pt-query-diagest percona-toolkit
mysqlsla
5.4 创建模拟环境
create table city_bak select * from city; --->创建一个和city表一样的city_bak表
insert into city_bak select * from city_bak; ---> 翻倍查询city_bak的数据
# 模拟慢日志
select sleep(5);
select * from city_bak;
update city_bak set countrycode='chn'; --->
----------自己扩展
- pt-query-diagest percona-toolkit
- mysqlsla