mysql 日志
MySQL日志
1.错误日志
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]
log_error=/usr/local/mysql/data/mysql.err
#log_error=mysql.err
#常用的数据库配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
port=3306
socket=/tmp/mysql.sock
server_id=1
character-set-server=utf8
innodb_data_file_path=ibdata1:76M;ibdata2:12M:autoextend
log_error=/usr/local/mysql/data/mysql.err
[mysql]
socket=/tmp/mysql.sock
2.一般查询日志
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
#一般情况我们不会开启一般查询日志，因为所有的操作都会被记录，第一不安全，第二会浪费磁盘空间
3.二进制日志
1.默认二进制日志是关闭的
2.如果开启默认的路径是MySQL的数据目录 $datadir
3.一般指定的的名字是 mysql-bin.000001
4.查看二进制日志
mysql> show variables like 'log_bin';
[root@db01 ~]# mysql -uroot -p123 -e "show variables like 'log_bin'"
5.配置二进制日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1 # 在MySQL5.7中，必须配置server_id
log_bin=mysql-bin # 在MySQL5.6版本，直接配置就开启了
1）作用
1.记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
2.记录所有DDL、DCL等语句,总之，二进制日志会记录所有对数据库发生修改的操作
3.可以用恢复数据
2）事件
1.在binlog中最小的记录单元为event
2.一个事务会被拆分成多个事件（event）
#事件（event）特性:
1.每个event都有一个开始位置（start position）和结束位置（stop position）。
2.所谓的位置就是event对整个二进制的文件的相对位置。
3.对于一个二进制日志中，前120个position是文件格式信息预留空间。
4.MySQL第一个记录的事件，都是从120开始的
二进制日志
1.二进制日志管理
1.默认二进制日志是关闭的
2.如果开启默认的路径是MySQL的数据目录 $datadir
3.一般指定的的名字是 mysql-bin.000001
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）事件的介绍
1.在binlog中最小的记录单元为event
2.一个事务会被拆分成多个事件（event）
#事件（event）特性
1.每个event都有一个开始位置（start position）和结束位置（stop position）
2.所谓的位置就是event对整个二进制的文件的相对位置
3.对于一个二进制日志中，前120个position是文件格式信息预留空间
4.MySQL第一个记录的事件，都是从120开始的。
3.二进制日志数据恢复操作
#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=1
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:
#查看数据
四、结合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.sql
Enter password:
[root@db01 ~]# mysqldump -uroot -p -B binlog events row world --master-data=2 > /tmp/full.sql
Enter 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=on
#查询检查返回少于该参数指定行的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: 后边可以写一个正则匹配模式，大小写不敏感的；
