MySQL日志管理
2019-12-10 本文已影响0人
唯爱熊
一.MySQL日志简介
二.错误日志
作用：解决MySQL故障，排错
默认是否开启：开启
路径/名字：hostname.err
[root@db01 data]# mysqladmin variables|grep 'error'
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err
#查看方式
mysql> show variables like 'log_error';
三.一般查询日志
作用：记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。
默认是否开启：关闭
路径/名字：hostname.log
[root@db01 data]# mysqladmin variables |grep 'gen'
general_log OFF
general_log_file /application/mysql-5.6.40/data/db01.log
mysql> show variables like '%gen%';
+------------------+-----------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------+
| general_log | OFF |
| general_log_file | /application/mysql-5.6.40/data/db01.log |
+------------------+-----------------------------------------+
vim /etc/my.cnf
general_log=1
四.慢日志
作用：
1）是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的
默认是否开启：关闭
路径/名字：hostname-slow.log
[root@db01 ~]# mysqladmin variables |grep 'slow'
slow_query_log OFF
slow_query_log_file /application/mysql-5.6.40/data/db01-slow.log
如何开启：
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/application/mysql/data/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）
模拟慢查询
mysql> use test
mysql> create table city select * from world.city;
mysql> insert into city select * from city;
mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+--------------+-------------------------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
| 5 | root | localhost | test | Query | 9 | Sending data | insert into city select * from city |
+----+------+-----------+------+---------+------+--------------+-------------------------------------+
2 rows in set (0.01 sec)
mysql> kill 5;
Query OK, 0 rows affected (0.00 sec)
进入数据库查看慢查询时间
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
借助第三方工具进行查看
httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
[root@db01 ~]# yum localinstall -y percona-toolkit-3.0.11-1.el6.x86_64.rpm
[root@db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log
使用mysqldumpslow命令来分析慢查询日志
#输出记录次数最多的10条SQL语句$PATH/mysqldumpslow -s c -t 10 /database/mysql/slow-log
参数说明:
-s:
是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t:
是top n的意思，即为返回前面多少条的数据；
-g:
后边可以写一个正则匹配模式，大小写不敏感的；
例：
#得到返回记录集最多的10个查询
$PATH/mysqldumpslow -s r -t 10 /database/mysql/slow-log#得到按照时间排序的前10条里面含有左连接的查询语句$PATH/mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log
五.二进制日志
作用：
记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
记录所有DDL、DCL等语句
总之，二进制日志会记录所有对数据库发生修改的操作
事件介绍
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）
事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。
begin；
sql1
sql2
sql3
...
commit;
#记录所有非查询语句（DDL，DML，DCL，DTL）
默认路径:
默认是否开启：关闭
log-bin=mysql-bin
$datadir
MySQL binlog工作模式
1.statement（默认）语句模式，记录所有非DQL语句(mysql 5.6)
优点：通俗易懂，占用磁盘空间小
缺点：不严谨
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)
mysql> use binlog
Database changed
mysql> create table binlog(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into binlog values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#查看binlog日志
[root@db01 data]# mysqlbinlog mysql-bin.000029
2.row 行级模式，记录所有非DQL语句，并且记录他们的变化过程
优点：严谨
缺点：不通俗易懂，占用磁盘空间大
log-bin=mysql-bin
binlog_format=row
3.mixed 混合模式，以上两种模式的混合
二进制日志实战操作
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000030 | 864 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1638 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 143 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 143 |
| mysql-bin.000006 | 143 |
| mysql-bin.000007 | 143 |
| mysql-bin.000008 | 143 |
| mysql-bin.000009 | 143 |
| mysql-bin.000010 | 143 |
| mysql-bin.000011 | 143 |
| mysql-bin.000012 | 143 |
| mysql-bin.000013 | 143 |
| mysql-bin.000014 | 143 |
| mysql-bin.000015 | 90301 |
| mysql-bin.000016 | 507 |
| mysql-bin.000017 | 167 |
| mysql-bin.000018 | 167 |
| mysql-bin.000019 | 222 |
| mysql-bin.000020 | 634 |
| mysql-bin.000021 | 795994 |
| mysql-bin.000022 | 28834 |
| mysql-bin.000023 | 143 |
| mysql-bin.000024 | 2181 |
| mysql-bin.000025 | 1125 |
| mysql-bin.000026 | 3923 |
| mysql-bin.000027 | 143 |
| mysql-bin.000028 | 143 |
| mysql-bin.000029 | 1125 |
| mysql-bin.000030 | 864 |
+------------------+-----------+
mysql> show binlog events in 'mysql-bin.000029';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------+
| mysql-bin.000029 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| mysql-bin.000029 | 120 | Query | 1 | 220 | create database binlog |
| mysql-bin.000029 | 220 | Query | 1 | 325 | use `binlog`; create table binlog(id int) |
| mysql-bin.000029 | 325 | Query | 1 | 408 | BEGIN |
| mysql-bin.000029 | 408 | Query | 1 | 522 | use `binlog`; insert into binlog values(1),(2),(3) |
| mysql-bin.000029 | 522 | Xid | 1 | 553 | COMMIT /* xid=10 */ |
| mysql-bin.000029 | 553 | Query | 1 | 651 | drop database binlog |
| mysql-bin.000029 | 651 | Query | 1 | 757 | create database binlog
使用binlog恢复数据
mysql> reset master;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> create database binlog1;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 223 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> use binlog1
mysql> create table binlog_table(id int);
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 336 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> insert into binlog_table values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 550 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> update binlog_table set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 760 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> delete from binlog_table where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 964 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> drop table binlog_table;
mysql> drop database binlog1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
+------+
| id |
+------+
| 10|
| 2 |
| 3 |
+------+
+------+
| id |
+------+
| 10|
| 2 |
+------+
# 恢复数据
mysql> show binary logs;
mysql> show binlog events in 'mysql-bin.000001';
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000001
起始：120
结束：760
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=760 /application/mysql/data/mysql-bin.000001 > /tmp/binlog_table.sql
#临时关闭binlog
mysql> set sql_log_bin=0;
#导入
[root@db01 data]# mysql < /tmp/binlog_table.sql
mysql> use binlog1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_binlog1 |
+-------------------+
| binlog_table |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from binlog_table;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
使用binlog恢复指定库数据
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2664 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2761 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> use test1
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2861 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> insert into test1.tb1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> use test2
Database changed
mysql> create table tb2(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables from test1;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tb1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from test1.tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysql> show tables from test2;
+-----------------+
| Tables_in_test2 |
+-----------------+
| tb2 |
+-----------------+
1 row in set (0.00 sec)
mysql> insert into test2.tb2 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test2.tb2;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> update test1.tb1 set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> update test2.tb2 set id=20 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1.tb1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from test2.tb2;
+------+
| id |
+------+
| 1 |
| 20 |
+------+
2 rows in set (0.00 sec)
mysql> drop database test1;
Query OK, 1 row affected (0.00 sec)
起始：2567
结束：3752
[root@db01 data]# mysqlbinlog -d test1 --start-position=2567 --stop-position=3752 mysql-bin.000001 >/tmp/3.sql
binlog的刷新和删除
刷新binlog
1.MySQL重启会刷新binlog
[root@db01 data]# /etc/init.d/mysqld restart
2.执行，flush logs
mysql> flush logs
3.mysqladmin
[root@db01 data]# mysqladmin flush-log
4.binlog到1G会自动刷新
5.mysqldump -F
删除binlog
1.根据binlog的生存时间
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
2.使用purge命令删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 7 day;
3.根据binlog 名字删除
PURGE BINARY LOGS TO 'mysql-bin.000010';
4.reset master
