mysql 开启日志保存到数据库 mysql日志管理默认开启

您所在的位置：网站首页 › mysql安装后默认的数据库 › mysql 开启日志保存到数据库 mysql日志管理默认开启

mysql 开启日志保存到数据库 mysql日志管理默认开启

2023-07-01 19:28| 来源: 网络整理| 查看: 265

一.MySQL日志简介

mysql 开启日志保存到数据库 mysql日志管理默认开启_mysql

二.错误日志

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

默认位置：$MYSQL_HOME/data/

默认日志名：${hostname}.err

开启方式:（MySQL安装完后默认开启)

作用：记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。

默认位置：$MYSQL_HOME/data/

默认名字：${hostname}.log

开启方式:（MySQL安装完之后默认不开启）

#编辑配置文件 [root@db01 ~]# vim /etc/my.cnf [mysqld] general_log=on 或 general_log=1 general_log_file=/application/mysql/data/$hostnamel.log #查看方式 mysql> show variables like '%gen%'; +------------------+-----------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------+ | general_log | OFF | | general_log_file | /application/mysql-5.6.44/data/db01.log | +------------------+-----------------------------------------+四.二进制日志

mysql 开启日志保存到数据库 mysql日志管理默认开启_mysql 开启日志保存到数据库_02

作用：记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录记录所有DDL、DCL等语句总之，二进制日志会记录所有对数据库发生修改的操作

默认是否开启：关闭

默认位置：$datadir

默认名字：mysql-bin

mysql-bin.000001 mysql-bin.000002 mysql-bin.000003

二进制日志模式: statement：语句模式，上图中将update语句进行记录（默认模式）。 row：行模式，即数据行的变化过程，上图中Age=19修改成Age=20的过程事件。 mixed：以上两者的混合模式。企业推荐使用row模式

优缺点:

statement模式：

优点：简单明了，容易被看懂，就是sql语句，记录时不需要太多的磁盘空间。缺点：记录不够严谨，容易丢数据。

row模式：

优点：记录更加严谨。缺点：有可能会需要更多的磁盘空间，不太容易被读懂。

binlog的作用: 1）如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻 2）数据的备份恢复 3）数据的复制

二进制日志的管理操作实战

开启方式

#在MySQL5.6中 [root@db01 data]# vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format=row

注意:在mysql5.7中开启binlog必须要加上server-id。

二进制日志的操作

#物理查看 [root@db01 data]# ll /application/mysql/data/ -rw-rw---- 1 mysql mysql 285 Mar 6 2017 mysql-bin.000001 #命令行查看 mysql> show binary logs; mysql> show master status; #查看binlog事件 mysql> show binlog events in 'mysql-bin.000007'; #查看binlog [root@db01 data]# mysqlbinlog mysql-bin.000001 #查看行级模式的binlog [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002

事件介绍

1）在binlog中最小的记录单元为event 2）一个事务会被拆分成多个事件（event）

事件（event）特性

1）每个event都有一个开始位置（start position）和结束位置（stop position）。 2）所谓的位置就是event对整个二进制的文件的相对位置。 3）对于一个二进制日志中，前120个position是文件格式信息预留空间。 4）MySQL第一个记录的事件，都是从120开始的。

row模式下二进制日志分析及数据恢复

#查看binlog信息 mysql> show master status; #创建一个binlog库 mysql> create database binlog; #使用binlog库 mysql> use binlog #创建binglog_table表 mysql> create table binlog_table(id int); #查看binlog信息 mysql> show master status; #插入数据1 mysql> insert into binlog_table values(1); #查看binlog信息 mysql> show master status; #提交 mysql> commit; #查看binlog信息 mysql> show master status; #插入数据2 mysql> insert into binlog_table values(2); #插入数据3 mysql> insert into binlog_table values(3); #查看binlog信息 mysql> show master status; #提交 mysql> commit; #删除数据1 mysql> delete from binlog_table where id=1; #查看binlog信息 mysql> show master status; #提交 mysql> commit; #更改数据2为22 mysql> update binlog_table set id=22 where id=2; #查看binlog mysql> show master status; #提交 mysql> commit; #查看binlog信息 mysql> show master status; #查看数据 mysql> select * from binlog_table; #删表 mysql> drop table binlog_table; #删库 mysql> drop database binlog;

工作中做完以上操作，接下来，该怎么做呢？给大家推荐一个网站，专业解决删库问题:

单机此处，打开专业网站

恢复数据到delete之前

#查看binlog事件 mysql> show binlog events in 'mysql-bin.000013'; #使用mysqlbinlog来查看 [root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013 [root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013 ### UPDATE `binlog`.`binlog_table` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=22 /* INT meta=0 nullable=1 is_null=0 */ #分析 update binlog.binlog_table set @1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22 where @1=2 --------->@1表示binlog_table中的第一列,集合表结构就是id=2 #结果 update binlog.binlog_table set id=22 where id=2; #截取二进制日志查看二进制日志后，发现delete语句开始位置是858 [root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013 > /tmp/a.sql #导入数据 [root@db01 data]# mysql -uroot -p1 < /tmp/a.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | binlog | +--------------------+ #临时关闭binlog mysql> set sql_log_bin=0; #执行sql文件 mysql> source /tmp/binlog.sql #查看删除的库 mysql> show databases; #进binlog库 mysql> use binlog #查看删除的表 mysql> show tables; #查看表中内容 mysql> select * from binlog_table;

思考，存在问题：

数据库或表被误删除的是很久之前创建的（一年前）如果基于binlog全量恢复，成本很高 1）可以用备份恢复+短时间内二进制日志，恢复到故障之前 2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback 3）延时从库

如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到想一个办法过滤出来？ 1）grep？其他过滤方案？ 1）-d 参数接库名

模拟数据

#为了让大家更清晰看到新的操作 #刷新一个新的binlog mysql> flush logs; #创建db1、db2两个库 mysql> create database db1; mysql> create database db2; #库db1操作 mysql> use db1; #创建t1表 mysql> create table t1(id int); #插入5条数据 mysql> insert into t1 values(1),(2),(3),(4),(5); #提交 mysql> commit; #库db2操作 mysql> use db2; #创建t2表 mysql> create table t2(id int); #插入3条数据 mysql> insert into t2 values(1),(2),(3); #提交 mysql> commit; #查看binlog事件 mysql> show binlog events in 'mysql-bin.000014'; [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000001 #查看db1的操作 [root@db01 data]# mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000001 #恢复 [root@db01 data]# mysqlbinlog -d db1 --start-position=1916 --stop-position=2576 /application/mysql/data/mysql-bin.000001 > /tmp/b.sql [root@db01 data]# mysql -uroot -p1 < /tmp/b.sql ## -d 指定只查看某个库的binlog 223 1949

删除、刷新binlog

#1.刷新binlog mysql> flush logs; #2.当重启数据库的时候，binlog会刷新 /etc/init.d/mysqld restart #3.当binlog 大小达到1G的时候自动刷新

删除二进制日志 1）原则在存储能力范围内，能多保留则多保留基于上一次全备前的可以选择删除

删除方式

1.根据存在时间删除日志# 删除binlog（临时，保留7天的） mysql> SET GLOBAL expire_logs_days = 7; # 永久生效 [root@db01 data]# vim /etc/my.cnf [mysqld] expire_logs_days = 72.使用purge命令删除# 删除3天之前的binlog PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;3.根据文件名删除# 指定binlog名字删除，删除该binlog之前的所有binlog PURGE BINARY LOGS TO 'mysql-bin.000010';4.使用reset master# 清除所有binlog mysql> reset master;五.慢查询日志

作用： 1）是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件 2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

默认位置：$MYSQL_HOME/data/$hostname-slow.log

默认名字：${hostname}-slow.log

开启方式（默认没有开启）：

[root@db01 ~]# vim /etc/my.cnf [mysqld] #指定是否开启慢查询日志 slow_query_log = 1 或者 slow_query_log = ON #指定慢日志文件存放位置（默认在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（鸡肋）

模拟慢查询语句

#进入world库 mysql> use world #查看表 mysql> show tables; #将city表中所有内容加到t1表中 mysql> create table t1 select * from city; #查看t1的表结构 mysql> desc t1; #将t1表所有内容插入到t1表中（多插入几次） mysql> insert into t1 select * from t1; mysql> insert into t1 select * from t1; mysql> insert into t1 select * from t1; #提交 mysql> commit; #删除t1表中id>2000的数据 mysql> delete from t1 where id>2000; #查看慢日志 [root@db01 ~]# cat /application/mysql/data/mysql-db01

使用mysqldumpslow命令来分析慢查询日志

#输出记录次数最多的10条SQL语句 mysqldumpslow -s c -t 10 /database/mysql/slow-log

参数说明: -s: 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙； -t: 是top n的意思，即为返回前面多少条的数据； -g: 后边可以写一个正则匹配模式，大小写不敏感的；

例：

#得到返回记录集最多的10个查询 mysqldumpslow -s r -t 10 /database/mysql/slow-log #得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log

第三方推荐（扩展）：

yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

使用percona公司提供的pt-query-digest工具分析慢查询日志

[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log

有能力的可以做成可视化界面：

Anemometer基于pt-query-digest将MySQL慢查询可视化

https://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载

https://github.com/box/Anemometer 可视化代码下载

mysql 开启日志保存到数据库 mysql日志管理默认开启_mysql_03

练习：

1.使用percona工具查看慢日志

[root@db01 ~]# yum locainstall -y percona-toolkit-3.1.0-2.el7.x86_64.rpm [root@db01 ~]# pt-query-digest /application/mysql/data/slow.log

2.尝试部署可视化慢查询web界面

3.binlog数据恢复练习

# 刷新binlog mysql> flush logs; 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.000004 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ # 创建binlog1、binlog2两个库 mysql> create database binlog1; mysql> create database binlog2; # 库binlog1添加数据 mysql> use binlog1; mysql> create table t1(id int); mysql> insert into t1 values(1),(2),(3); mysql> select * from t1; # 库binlog2添加数据 mysql> use binlog2 mysql> create table t2(id int); mysql> insert into t2 values (1),(2),(3); # 库binlog修改数据 mysql> update binlog.binlog set id=1 where id=10; mysql> insert into binlog.binlog values(3); # 库binlog2更改数据 mysql> update t2 set id=11 where id=1; # 误删除binlog2库中的t2表 mysql> drop table t2; mysql> show tables; Empty set (0.00 sec) # 误删除binlog2库 mysql> drop database binlog2; mysql> show databases; +--------------------+ | Database | +--------------------+ | binlog | | binlog1 | +--------------------+ # 查看binlog事件 [root@db01 data]# mysqlbinlog -d binlog2 --base64-output=decode-rows -vvv mysql-bin.000004 mysql> show binlog events in 'mysql-bin.000004'; +------------------+------+-------------+-----------+-------------+---------------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info +------------------+------+-------------+-----------+-------------+---------------------- | mysql-bin.000004 | 120 | Query | 1 | 223 | create database binlog1 | mysql-bin.000004 | 223 | Query | 1 | 326 | create database binlog2 | mysql-bin.000004 | 1737 | Query | 1 | 1858 | use `binlog2`; DROP TABLE `t2` /* generate | mysql-bin.000004 | 1858 | Query | 1 | 1949 | drop database binlog2 +------------------+------+-------------+-----------+-------------+---------------------- # 恢复 [root@db01 data]# mysqlbinlog -d binlog2 --start-position=223 --stop-position=1737 /application/mysql/data/mysql-bin.000004 > /tmp/d.sql [root@db01 data]# mysql -uroot -p1 < /tmp/d.sql # 查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | binlog | | binlog1 | | binlog2 | +--------------------+ mysql> use binlog2; mysql> show tables; +-------------------+ | Tables_in_binlog2 | +-------------------+ | t2 | +-------------------+ mysql> select * from t2; +------+ | id | +------+ | 11 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)

【本文地址】

公司简介

联系我们