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

select table_name,data_length,index_length,(data_length&＃43;index_length) as length,table_rows,concat(round((data_length&＃43;index_length)/1024/1024/1024,3),&＃39;GB&＃39;) as totalSize FROM information_schema.tables
WHERE table_schema &＃61; &＃39;pt_query&＃39;
ORDER BY length DESC;


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

a、DDL

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

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

b、DML

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

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

2、binlog日志文件的配置

查看binlog日志的信息&＃xff1a;

show global variables like "%log_bin%";


未开启binlog日志

&＃43;---------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;---------------------------------&＃43;-------&＃43;
| log_bin | OFF | 用于控制会话级别二进制日志功能的开启或关闭
| log_bin_basename | | 二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。
| log_bin_index | |二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
&＃43;---------------------------------&＃43;-------&＃43;

如果要开启binlog日志可以在my.cnf文件中添加&＃xff08;一般在/etc 目录下&＃xff0c;加完之后需重启mysql&＃xff09;

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


加完开启binlog日志之后

3、查看binlog文件

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


在mysql执行一条更改语句

去mysql 的bin目录下&＃xff0c;查看对应执行语句

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


更推荐

show binlog events in &＃39;mysql-bin.000001&＃39;;
show binlog events in &＃39;mysql-bin.000001&＃39; from 6599 limit 5;


show binlog events in &＃39;mysql-bin.000003&＃39;/G;


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

5、重置&＃xff08;清空&＃xff09;所有binlog日志
reset master;

6、通过binlog恢复其数据

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

演示&＃xff1a;

a、展示现有数据&＃xff08;测试数据库为data_recover&＃xff09;

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

./mysqldump -uroot -proot -B data_recover -F -R -x --master-data&＃61;2 |gzip >/tmp/data_recover_$(date &＃43;%F).sql.gz


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

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

-x, --lock-all-tables&＃xff1a;锁定所有表

-l, --lock-tables&＃xff1a;锁定备份的表

–single-transaction&＃xff1a;启动一个大的单一事务实现备份

-B, --databases db_name1 db_name2 …&＃xff1a;备份指定的数据库

-C, --compress&＃xff1a;压缩传输&＃xff1b;

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

–flush-logs, -F&＃xff1a;锁定表之后执行flush logs命令&＃xff1b;&＃xff08;这个需要注意&＃xff0c;这个时候binlog日志更新一个新的一份&＃xff09;

c、在此基础上进行增删查改

进行增删查改之后的数据为&＃xff1a;

我们利用之前备份数据&＃xff0c;恢复到目前数据&＃xff08;假设delete from tb_areas where id&＃61;2; 为误操作&＃xff09;

d、对新的binlog日志进行备份&＃xff0c;防止新的数据产生影响

e、对之前的备份的压缩文件进行解压

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


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

./mysql -uroot -S /tmp/mysql.sock -proot

可以看到数据已经恢复为备份时候的数据了


g、恢复数据

show binlog events in &＃39;mysql-bin.000003&＃39;/G;


可以

可以看到我们想要恢复的数据&＃xff0c;但不想要删除那个语句。&＃xff08;恢复时候不执行 1371~1523&＃xff09;

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


可以看到数据已经恢复正常


show global variables like &＃39;%log%&＃39;;


| slow_query_log | ON | 查询日志的开关
| slow_query_log_file | /data/mysql/query-slow.log | 慢查询日志的路径

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

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


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