mysql 通过日志统计表的增删改_mysql日志总结

(1)mysql的日志有哪些？

①错误日志error_log：记录Mysql启动，运行，停止期间的问题。

②常规日志general_log：记录所有发向mysql的请求。

③慢查询日志slow_query_log: 记录符合条件的查询。

④二进制日志binary_log: 记录全部有效的数据修改日志。(非常重要，高可用，主从备份，数据恢复，增量备份等)

⑤中继日志relay_log: 用于主从复制，可以看做主服务器数据库二进制日志在从服务器的副本，其内容就是增量获取主服务器的二进制日志。

(2)我们在什么情况下使用这些日志?

(3)如何通过日志来审计用户活动？

mysql存储引擎层日志：innodb的重做日志以及回滚日志;

mysql服务层日志：二进制日志，慢查询日志，通用日志。

1 二进制日志binary_log

记录了所有对mysqsl数据库的修改事件，包括增删改查和对表结构的修改。binlog记录的日志都是成功执行的。二进制日志可以帮助我们实现主从复制，基于时间点的备份和恢复。包括innodb, myisam等其他存储引擎。

1.1基于段的二进制日志

binlog_format=STATEMENT #mysql5.7之前默认的模式

优点：

可以清楚的看出mysql执行了那些操作。记录了每一个事件执行的sql语句。日志量相对较小，节约磁盘，网络IO性能。

缺点：

必须记录上下文信息。以保证在从服务器上执行结果和在主服务器上相同。

但是对于UUID(),user(),now()这样的非确定函数还是没法复制，可能造成主从数据不一致。

show variables like 'binlog_format';

set session binlog_format=statement;

show binary logs查看binlog;

flush logs;刷新二进制日志，产生新的二进制日志binlog;

user statementdb;

insert into test(1,'1'),(2,'2');

update test set name='11' where id = 1

进入日志存放目录去查看日志

cd /home/mysql/sql_log

ls 查看有哪些日志

mysqlbinlog mysql-bin.000002

1.2基于行的日志格式 binlog_format=ROW

row格式可以避免mysql复制过程中的主从不一致问题。不记录修改sql语句，仅记录哪条数据被修改了，修改成什么样了。每修改一行都有一条日志，若批量修改基于段的日志格式只会记录这个sql语句，基于行的日志会记录所有修改每一行的数据修改日志。

优点：

使用mysql主从复制更加安全。

对每一行数据的修改比基于段的复制高效。

误操作修改了数据库中的数据，同时有没有备份恢复，那么我们就可以分析二进制日志，对日志中记录的数据修改操作反向处理已达到恢复数据的目的。

缺点：

日志量大。

解决方法：

binlog_row_image=FULL|MINIMAL|NOBLOB 默认FULL记录修改记录所在行所有列的数据，MINIMAL表示只记录修改了的前后列的数据。

1.3混合模式

以上两种模式的混合使用，又数据库来决定日志保存方式。

2 redo日志

mysql里常说的WAL技术，Write-Ahead Loggin，关键点就是先写日志，然后写磁盘。

当有一条记录要跟新，会清空缓存，同时把记录写到redo log里，并且更新内存，innodb执行引擎会在适当的时候将操作记录刷新到磁盘。redo log可以保证即使数据库发生异常，之前的提交记录都不会丢失。

重做日志记录的是修改的页信息，而且只记录Innodb存储引擎本身的事务日志。

redo日志在事务执行期间，连续写入。binlog是在事务提交之后。

事务提交有三个步骤：(1)write prepare log在预提交日志中写入xid。(2)writer binlog。(3)write commit log。

3 undo日志

为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

4中继日志(relay_log)

用于主从复制。

relay_log=filename指定日志存放位置以及文件名前缀。设置固定名防止主机名变化影响主从复制。

默认自动删除中继日志relay_log_purge=ON。

5 错误日志

5.1 错误日志的使用场景

(1)分析排除mysql运行错误：mysql服务器启动失败，异常重启，主从失败等

(2)记录未经授权的访问：错误日志中会记录异常登录信息，对登录信息进行审计。

5.2 配置

(1)log_error = $mysql/sql_log/mysql-error.log 默认将错误日志保存到数据文件中，而且错误日志名取主机名，后缀为-error.log的文件。通常情况下出于性能优化的考虑将错误日志于数据日志分离，存放到不同的文件或设备上。例如数据文件放到高速io磁盘上，错误日志放到相对性能低的磁盘上。

(2)log_error_verbosity=[1,2,3]

错误日志级别，默认值为2。1表示错误信息；2表示错误和警告信息；3表示错误和警告和提示信息note message；

(3)log_error_services=[日志服务组件;日志服务组件]  mysql8以上

show variables like ‘log_error_services’;(默认log_filter_internal, log_sink_internal)

若去掉默认的日志过滤组件，set persist log_error_services=’log_sink_internal’；

7常规日志general_log

记录从连接到退出为止，与mysql所有交互信息，会产生大量的日志，默认情况关闭。

8 慢查询日志

8.1慢查询日志配置

将执行成功并且符合条件的查询记录到日志中，找到需要优化的sql。默认关闭。

slow_query_log = ON

slow_query_log_file=$mysql/mysql_log/slowlog.log

long_query_time = xx秒

log_queries_not_using_indexes=ON

8.2慢查询日志分析工具

(1) mysqldumpslow，mysql自带工具。统计信息比较少，包括了执行sql语句，执行时间，锁定时间，发送的行数，扫描的行数。

执行命令：查询慢查询日志前10个：mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log

(2) pt-query-digest

安装：

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm

pt-query-digest --version-check 版本进行升级

wget http://percona.com/get/pt-summary

检查是否安装成功：

pt-query-digest --help

用途

查看服务器信息：

pt-summary

查看磁盘开销

pt-diskstats

查看mysql数据库信息

pt-mysql-summary --user=root --password=123456

分析慢查询日志

pt-query-digest /data/mysql/data/db-slow.log

查看mysql主从同步状态

pt-slave-find --host=localhost --user=root --password=123456

查看mysql死锁信息

pt-deadlock-logger --user=root --password=123456 localhost

查看慢查询日志中索引使用情况

pt-index-usage --user=root --password=123456 slow_20131009.log

查看数据库表中重复的索引

pt-duplicate-key-checker --host=localhost --user=root --password=123456

对比mysql配置文件差异

pt-config-diff /etc/my.cnf /etc/my_master.cnf

查看mysql表和文件的当前活动IO开销

pt-ioprofile

pt-find查看数据库中大于500g的表

pt-find --user=root --password=123456 --tablesize +500G

pt-kill 杀掉符合标准的mysql进程

查看查询时间大于60s的查询并且杀掉

pt-kill --user=root --password=123456 --busy-time 60 --print

pt-kill --user=root --password=123456 --busy-time 60 --kill

查询mysql授权

pt-show-grants --user=root --password=123456

pt-show-grants --user=root --password=123456 --separate –revoke

验证数据库复制的完整性

pt-table-checksum --user=root --password=123456