mysql 的日志（binlog日志和慢日志）

浅浅的花香味﹌ 2023-02-20 13:42 35阅读 0赞

### 一、查看mysql数据所占的大小 ###

select table_name,data_length,index_length,(data_length+index_length) as length,table_rows,concat(round((data_length+index_length)/1024/1024/1024,3),'GB') as totalSize FROM information_schema.tables
    WHERE table_schema = 'pt_query'
    ORDER BY length DESC;

### 二、二进制文件（binlog日志） ###

**1、介绍**  
二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。一般大小体积上限为1G。

a、DDL

\----Data Definition Language 数据库定义语言

主要的命令有create、alter、drop等，ddl主要是用在定义或改变表(table)的结构,数据类型，表之间的连接和约束等初始工作上，他们大多在建表时候使用。

b、DML

\----Data Manipulation Language 数据操纵语言

主要命令是slect,update,insert,delete,就像它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

**2、binlog日志文件的配置**

查看binlog日志的信息：

show global variables like "%log_bin%";

未开启binlog日志

如果要开启binlog日志可以在my.cnf文件中添加（一般在/etc 目录下，加完之后需重启mysql）

#指定二进制日志生成的路径
    log-bin=/data/mysql/mysql-bin
    #开启binlog日志
    server-id=1
    #指定二进制日志的类型，默认为MIXED，加入此参数才能记录到insert语句
    binlog_format=MIXED

加完开启binlog日志之后  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70]

**3、查看binlog文件**

#查看所有binlog日志列表
    show master logs;
    #查看master状态，即最后（最新）一个binlog日志的编号名称，
    #及其最后一个操作事件pos结束点(Position)值。
    show master status;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 1]

在mysql执行一条更改语句  
![在这里插入图片描述][20200627184425384.png]  
去mysql 的bin目录下，查看对应执行语句

./mysqlbinlog /data/mysql/mysql-bin.000001

![在这里插入图片描述][20200627184242737.png]  
更推荐

show binlog events in 'mysql-bin.000001';
    show binlog events in 'mysql-bin.000001' from 6599 limit 5;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 2]

show binlog events in 'mysql-bin.000003'/G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 3]

**4、flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;**  
　flush logs;  
注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；

**5、重置（清空）所有binlog日志**  
reset master;

**6、通过binlog恢复其数据**

**思路：**  
mysql数据的恢复需要依赖与在之前备份的数据，通过备份数据，执行相同的操作来进行恢复数据。无法根据现有数据，反推得到误删数据。

**演示：**

a、展示现有数据（测试数据库为data\_recover）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 4]

![在这里插入图片描述][20200628002010395.png]

b、在此基础上进行数据备份

./mysqldump -uroot -proot -B data_recover -F -R -x --master-data=2 |gzip >/tmp/data_recover_$(date +%F).sql.gz

命令的语法格式：  
mysqldump \[OPTIONS\] database \[tables\]：备份单个库，或库指定的一个或多个表  
mysqldump \[OPTIONS\] --databases \[OPTIONS\] DB1 \[DB2 DB3…\]：备份一个或多个库  
mysqldump \[OPTIONS\] --all-databases \[OPTIONS\]：备份所有库

\-A, --all-databases 备份所有表

\-x, --lock-all-tables：锁定所有表

\-l, --lock-tables：锁定备份的表

–single-transaction：启动一个大的单一事务实现备份

\-B, --databases db\_name1 db\_name2 …：备份指定的数据库

\-C, --compress：压缩传输；

\-E, --events：备份指定库的事件调度器；  
\-R, --routines：备份存储过程和存储函数；  
–master-data\[=\#\]：  
1：记录CHANGE MASTER TO语句；此语句未被注释；  
2：记录为注释语句；

**–flush-logs, -F：锁定表之后执行flush logs命令；**（这个需要注意，这个时候binlog日志更新一个新的一份）

![在这里插入图片描述][2020062800113966.png]

c、在此基础上进行增删查改  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 5]

进行增删查改之后的数据为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 6]  
我们利用之前备份数据，恢复到目前数据（假设delete from tb\_areas where id=2; 为误操作）

d、对新的binlog日志进行备份，防止新的数据产生影响  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 7]  
e、对之前的备份的压缩文件进行解压

gzip -d /tmp/data_recover_2019-10-11.sql.gz

f、恢复到备份时候的情况（如果真实环境可以修改数据库名字，等数据完全恢复正常在切换新的数据库）

./mysql -uroot -S /tmp/mysql.sock -proot </tmp/data_recover_2019-10-11.sql

可以看到数据已经恢复为备份时候的数据了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 8]

g、恢复数据

show binlog events in 'mysql-bin.000003'/G;

可以![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 9]

可以看到我们想要恢复的数据，但不想要删除那个语句。（恢复时候不执行 1371~1523）

./mysqlbinlog  --stop-position=1276 --database=data_recover /data/mysql/mysql-bin.000003 | ./mysql -uroot -proot -S /tmp/mysql.sock  -v
    ./mysqlbinlog --start-position=1523 --database=data_recover /data/mysql/mysql-bin.000003 | ./mysql -uroot -proot -S /tmp/mysql.sock  -v

可以看到数据已经恢复正常  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 10]

### 三、慢查询日志 ###

show global variables like '%log%';

如果查询日志并没有打开需要添加

#日志查询超过两秒记录
    long_query_time=2
    #开启慢查询
    slow_query_log=1
    #慢查询文件目录
    slow_query_log_file=/data/mysql/query-slow.log
    #记录未使用索引的sql
    #log_queries_not_using_indexes=1

如果使用慢查询，有个关于慢查询的工具 t-query-digest  
这里将其使用方法贴出来来参考  
http://xiaohost.com/1250.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200627181426174.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200627183230354.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[20200627184425384.png]: https://img-blog.csdnimg.cn/20200627184425384.png
[20200627184242737.png]: https://img-blog.csdnimg.cn/20200627184242737.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020062721361023.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200628000328767.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200628000940268.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[20200628002010395.png]: https://img-blog.csdnimg.cn/20200628002010395.png
[2020062800113966.png]: https://img-blog.csdnimg.cn/2020062800113966.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200628002125896.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200628002210435.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200628002450813.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200628003203108.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/2020062723534334.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200628003704411.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70