第十四章 MySQL日志

一、错误日志

1.错误日志默认是开启的
2.默认存储 $datadir 下面，默认的名字是 '主机名'.err
3.查看错误日志
	[root@db01 ~]# mysql -uroot -p -e "show variables like 'log_error'"
4.配置错误日志
	[root@db01 ~]# vim /etc/my.cnf
	[mysqld]
	log_error=/usr/local/mysql/data/mysql.err

二、一般查询日志

1.一般查询日志默认是关闭的
2.默认存储 $datadir 下面，默认的名字是 '主机名'.log
3.查看一般查询日志
	[root@db01 ~]# mysql -uroot -p -e "show variables like 'general%'"
4.配置一般查询日志
	[root@db01 ~]# vim /etc/my.cnf
	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 logs3.二进制日志达到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）事件的介绍

1.在binlog中最小的记录单元为event2.一个事务会被拆分成多个事件（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.sqlEnter 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=1log_bin=mysql-binbinlog_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 'YGKfXxMBAAAALQAAAKcBAAAAAEYAAAAAAAEAA3JvdwADcm93AAEDAAEhwVV2YGKfXx4BAAAAMgAAANkBAAAAAEYAAAAAAAEAAgAB//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 = 16COMMIT/*!*/;... ...#分析update binlog.binlog_tableset@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22where@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 -p123mysql> use rowmysql> 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:#查看数据

四、结合binlog进行数据库升级

#1.准备一台新机器搭建数据库，同步binlog模式[root@db02 ~]# vim /etc/my.cnf[mysqld]binlog_format=row[root@db02 ~]# systemctl start mysqld#2.将旧的数据库数据导出[root@db01 ~]# mysqldump -uroot -p -B mysql > /tmp/mysql.sqlEnter password: [root@db01 ~]# mysqldump -uroot -p -B binlog events row world --master-data=2 > /tmp/full.sqlEnter password:#3.将旧库导出的sql传输到新库[root@db01 ~]# scp /tmp/mysql.sql 172.16.1.52:/tmp/[root@db01 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/#4.模拟新数据写入旧库mysql> use binlog;mysql> insert binlog values(1000);#5.新库导入数据[root@db02 ~]# mysql < /tmp/mysql.sql[root@db02 ~]# mysql -e "flush privileges"[root@db02 ~]# sed -i s#MyISAM#InnoDB#g /tmp/full.sql[root@db02 ~]# mysql -uroot -p < /tmp/full.sql#6.查看新库数据mysql> use binlog;mysql> select * from binlog;#7.把业务切换到新库#8.导出切换过程中新产生的数据[root@db01 ~]# mysqlbinlog --start-position=316 --stop-position=1100 /usr/local/mysql/data/mysql-bin.000001 > /tmp/new.sql#9.将新数据的sql推送到新库[root@db01 ~]# scp /tmp/new.sql 172.16.1.52:/tmp#10.将新数据导入新库[root@db02 ~]# mysql -uroot -p123 < /tmp/new.sql

五、慢查询日志（慢日志）

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: 后边可以写一个正则匹配模式，大小写不敏感的；