mysql建立 t日志_Mysql 日志管理

错误日志

1.默认错误日志是开启状态

2.默认的路径是MySQL的数据目录 $datadir

3.默认的名字是 '主机名'.err

4.查看错误日志的语法：

mysql> show  variables  like 'log_error';       #库内查看

[root@db01 ~]# mysql -uroot -p123 -e "show variables like 'log_error'"     #库外查看

5.配置错误日志 (二选一)

[root@db01 ~]# vim  /etc/my.cnf

[mysqld]

a:  log_error=/usr/local/mysql/data/mysql.err      #推荐绝对路径，便于查找

b：log_error=mysql.err              #相对路径

6.作用:

记录mysql数据库的一般状态信息及报错信息，对于数据库常规报错处理的常用日志。

一般查询日志

1.默认一般查询日志是关闭状态

2.如果开启默认的路径是 MySQL的数据目录 $datadir

3.默认的名字是 '主机名'.log

4.查看一般查询日志的语法：

mysql>  show variables like "general_log%";     #库内查看

[root@db01 ~]# mysql -uroot -p123 -e "show variables like 'general_log%'"      #库外查看

5.配置错误日志

[root@db01 ~]# vim /etc/my.cnf

[mysqld]

general_log=on      #开启

general_log_file=/usr/local/mysql/data/db01.log           #文件存放路径

注意：一般情况我们不会开启一般查询日志

因为所有的操作都会被记录，第一不安全，第二会浪费磁盘空间

二进制日志

1.二进制日志管理

1)开启二进制日志

[root@db01 ~]# vim /etc/my.cnf

[mysqld]

server_id=1                              #mysql5.6中可以不加，但是mysql5.7中必须加

log_bin=mysql-bin                     #mysql5.6中可以写log-bin和log_bin，但是mysql5.7只能写log_bin

2)查看二进制日志

#物理查看

[root@db01 ~]# ll /usr/local/mysql/data/

-rw-rw---- 1 mysql mysql 665 10月 30 20:14 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 10月 30 20:07 mysql-bin.index

#数据命令查看

[root@db01 ~]# mysql -uroot -p -e "show variables like 'log_bin'"

[root@db01 ~]# mysql -uroot -p -e "show binary logs"

Enter password:

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 665 |

+------------------+-----------+

3)生成新的binlog

1.重启数据库

2.刷新binlog flush logs

3.二进制日志达到1G，默认会生成新的binlog

[root@db01 ~]# mysql -uroot -p123 -e "show variables like '%binlog_size%'"

+-----------------+------------+

| Variable_name | Value |

+-----------------+------------+

| max_binlog_size | 1073741824 |

+-----------------+------------+

4)删除binlog

1.原则:

1)在存储能力范围内，能多保留则多保留

2)基于上一次全备前的可以选择删除

2.删除七天前的数据

1)临时生效

SET GLOBAL expire_logs_days = 7;

2)永久生效

[root@db01 data]# vim /etc/my.cnf

[mysqld]

expire_logs_days = 7

3.只保留三天的binlog

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.删除指定binlog之前的所有binlog

PURGE BINARY LOGS TO 'mysql-bin.000010';

4.使用reset master，重置binlog

mysql> reset master;

2.二进制日志的作用

1.记录已提交的DML事务语句，并拆分为多个事件(event)来进行记录

2.记录所有DDL、DCL等语句

3.总之，二进制日志会记录所有对数据库发生修改的操作

#作用：

1.数据库备份

2.数据库恢复

3.数据库复制

事件的介绍：

1.在binlog中最小的记录单元为event

2.一个事务会被拆分成多个事件(event)

​

#事件(event)特性

1.每个event都有一个开始位置(start position)和结束位置(stop position)

2.所谓的位置就是event对整个二进制的文件的相对位置

3.对于一个二进制日志中，前120个position是文件格式信息预留空间

4.MySQL第一个记录的事件，都是从120开始的。

3.二进制日志数据恢复操作

1)创建原始数据

#1.查看binlog信息

mysql> show master status;

#2.创建库

mysql> create database binlog;

#3.创建表

mysql> use binlog;

mysql> create table binlog(id int);

#4.插入数据

mysql> insert binlog values(1),(2),(3);

#5.查看数据

mysql> select * from binlog;

2)误删除数据库

#删除数据库

mysql> drop database binlog;

3)恢复数据

#1.查看binlog找到起始位置点和结束位置点

[root@db01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

#2.取出位置点之间的语句导出sql

[root@db01 ~]# mysqlbinlog --start-position=120 --stop-position=548 /usr/local/mysql/data/mysql-bin.000002 > /tmp/binlog.sql

#3.将数据重新倒回数据库

[root@db01 ~]# mysql -uroot -p < /tmp/binlog.sql

Enter password: 123

#4.回到数据库查看数据

4.二进制日志的工作模式

1)查看二进制日志工作模式

#查看

[root@db01 ~]# mysql -uroot -p -e "show variables like 'binlog_format'"

Enter password:

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

#工作模式：

1.STATEMENT 语句模式

2.row行级模式           #企业推荐模式

3.混合模式

2)STATEMENT 语句模式

STATEMENT 语句模式 mysql5.6 默认的模式

​

[root@db01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

... ...

create database binlog

/*!*/;

# at 220

... ...

create table binlog(id int)

/*!*/;

# at 325

... ...

BEGIN

/*!*/;

# at 408

... ...

insert binlog values(1),(2),(3)

/*!*/;

# at 517

... ...

COMMIT/*!*/;

# at 548

... ...

drop database binlog

... ...

​

#优缺点：

1.不严谨，不安全

2.语句容易理解

3.日志文件相较于row模式比较小，不会占用太多的磁盘空间

3)行级模式 row模式

行级模式 row模式 mysql5.7 默认的模式

#1.配置数据库binlog为row模式

[root@db01 ~]# vim /etc/my.cnf

[mysqld]

server_id=1

log_bin=mysql-bin

binlog_format=row

[root@db01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

# at 120

... ...

create database row

/*!*/;

# at 211

... ...

use `row`/*!*/;

... ...

create table row(id int)

# at 307

... ...

BEGIN

# at 378

... ...

# at 423

... ...

BINLOG '

YGKfXxMBAAAALQAAAKcBAAAAAEYAAAAAAAEAA3JvdwADcm93AAEDAAEhwVV2

YGKfXx4BAAAAMgAAANkBAAAAAEYAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAADOknRQ=

'/*!*/;

# at 473

... ...

COMMIT/*!*/;

... ...

#查看row模式的binlog命令

[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /usr/local/mysql/data/mysql-bin.000003

... ...

BEGIN

/*!*/;

# at 378

#201102 9:35:28 server id 1 end_log_pos 423 CRC32 0x7655c121 Table_map: `row`.`row` mapped to number 70

# at 423

#201102 9:35:28 server id 1 end_log_pos 473 CRC32 0x149da433 Write_rows: table id 70 flags: STMT_END_F

### INSERT INTO `row`.`row`

### SET

### @1=1 /* INT meta=0 nullable=1 is_null=0 */

### INSERT INTO `row`.`row`

### SET

### @1=2 /* INT meta=0 nullable=1 is_null=0 */

### INSERT INTO `row`.`row`

### SET

### @1=3 /* INT meta=0 nullable=1 is_null=0 */

# at 473

#201102 9:35:28 server id 1 end_log_pos 504 CRC32 0xc25f74b1 Xid = 16

COMMIT/*!*/;

... ...

#分析

update binlog.binlog_table

set

@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22

where

@1=2 --------->@1表示binlog_table中的第一列,集合表结构就是id=2

#优缺点：

1.严谨，安全

2.语句不容易理解

3.日志文件相较于STATEMENT模式比较大，比较占用磁盘空间

5.数据库数据恢复

#日常全备

[root@db01 ~]# mysqldump -uroot -p123 -A > /tmp/full.sql

#新的一天写入数据

[root@db01 ~]# mysql -uroot -p123

mysql> use row

mysql> insert row values(5);

mysql> insert row values(6);

mysql> insert row values(7);

mysql> update row set id=100 where id=3;

mysql> select * from row;

+------+

| id |

+------+

| 1 |

| 2 |

| 100 |

| 5 |

| 6 |

| 7 |

+------+

6 rows in set (0.00 sec)

#删库

mysql> drop database row;

#先查找新一天binlog的起始位置点和结束位置点

[root@db01 ~]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000003'" | grep drop

[root@db01 ~]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000003'" | grep 'create table'

[root@db01 ~]# mysqlbinlog --start-position=307 --stop-position=504 /usr/local/mysql/data/mysql-bin.000003 > /tmp/huifu.sql

[root@db01 ~]# mysqlbinlog --start-position=785 --stop-position=1539 /usr/local/mysql/data/mysql-bin.000003 > /tmp/huifu2.sql

#数据恢复

[root@db01 ~]# mysql -u root -p < /tmp/full.sql

Enter password:

[root@db01 ~]# mysql -uroot -p < /tmp/huifu.sql

Enter password:

[root@db01 ~]# mysql -uroot -p < /tmp/huifu2.sql

Enter password:

#查看数据

慢查询日志

1.慢日志的作用

1.是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件

2.通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

2.配置慢日志

[root@db01 ~]# vim /etc/my.cnf

[mysqld]

#指定是否开启慢查询日志

slow_query_log = 1

#指定慢日志文件存放位置(默认在data)

slow_query_log_file=/usr/local/mysql/data/slow.log

#设定慢查询的阀值(默认10s)

long_query_time=5

#不使用索引的慢查询是否记录到日志

log_queries_not_using_indexes

#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

min_examined_row_limit=100(鸡肋)

3.测试慢查询日志

#复制创建一个新表

mysql> use world;

mysql> create table t1 select * from city;

#反复插入数据

mysql> insert t1 select * from t1;

mysql> insert t1 select * from t1;

mysql> insert t1 select * from t1;

mysql> insert t1 select * from t1;

mysql> insert t1 select * from t1;

#查看慢日志

[root@db01 ~]# less /usr/local/mysql/data/slow.log

[root@db01 ~]# mysqldumpslow -s c -t 10 /usr/local/mysql/data/slow.log

-s: 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙

-t: 是top n的意思，即为返回前面多少条的数据

-g: 后边可以写一个正则匹配模式，大小写不敏感