一.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