数据库插件慢日志分析，备份恢复，审计

1、备份恢复

1.1、逻辑备份及恢复

用MySQL自带的mysqldump工具备份恢复—适合数据量不大的备份情况

1.1.1、备份业务库

nohup mysqldump -hip -Pport -uroot -ppwd --master-data=2 --single-transaction --set-gtid-purged=off --databases yewudb > /data/mysql/backup/yewudb-date +%Y%m%d.sql 2>/data/mysql/backup/yewudb-date +%Y%m%d.log &

1.1.2、恢复业务库

> create database new_yewudb;

nohup mysql -hip -Pport -uroot -ppwd < /data/mysql/backup/yewudb-date +%Y%m%d.sql > /data/mysql/backup/import_yewudb-date +%Y%m%d.log 2>&1 &

nohup mysql -h10.86.8.218 -P3306 -uroot -pmysql < /data/mysql/backup/sbtest-20220107.sql > /data/mysql/backup/import_yewudb-date +%Y%m%d.log 2>&1 &

1.1.3、补充是GTID环境备份时没有加--set-gtid-purged=off，导入会报错

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

1.2、物理备份及恢复

1.2.1、下载安装配置Percona Xtrabackup

MySQL5.7.x版本

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/tarball/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz -P /opt

MySQL8.0.x版本（根据不同的glibc版本包选择下载文件）

wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/tarball/percona-xtrabackup-8.0.22-15-Linux-x86_64.glibc2.12.tar.gz -P /opt

wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/tarball/percona-xtrabackup-8.0.22-15-Linux-x86_64.glibc2.17.tar.gz -P /opt

1.2.2、解压安装配置到环境变量

tar -zxvf /opt/percona-xtrabackup-2.4.24-Linux-x86_64

tar -zxvf /opt/percona-xtrabackup-8.0.22-15-Linux-x86_64

mv /opt/percona-xtrabackup-2.4.24-Linux-x86_64 /opt/pxb57

mv /opt/percona-xtrabackup-8.0.22-15-Linux-x86_64 /opt/pxb80

选择对应版本就好，否则会冲突

echo "export PATH=$PATH:/opt/pxb57/bin">>/etc/profile

source /etc/profile

xtrabackup --help有相关参数输出表示成功

1.2.3、源端进行全备操作

--并行备份

nohup innobackupex --defaults-file=/etc/my.cnf -Hip -Pport -uroot -ppwd --backup /data/mysql/backup --compress --compress-threads=4 --parallel=4 >/data/mysql/backup/full_backupdate +%Y%m%d.log 2>&1 &

--备份完成后，还不能用于恢复，一些未提交的事物需要恢复，需要恢复redo logo的数据，确保数据一致.preparing，undo撤销未提交的事务，重放redo log

nohup innobackupex --apply-log /data/mysql/backup/bakupdatedir/ --parallel=4 >/data/mysql/backup/applylogdate +%Y%m%d.log 2>&1 &

--复制备份文件到要恢复的服务器

tar -czvf /data/mysql/backup/bakupdatedir.tar.gz /data/mysql/backup/bakupdatedir/

scp bakupdatedir.tar.gz user@ip:/path

scp /etc/my.cnf user@ip:/path

1.2.4、目标端进行恢复操作（需要有对应版本的MySQL，解压就行不用安装）

--开始在恢复机恢复数据及操作

tar -zxvf /path/bakupdatedir.tar.gz

①、新建实例用来恢复备份的实例

mkdir /data/mysq/{data,logs,etc,socket,tmp,backup} -p

cp /path/my.cnf /data/mysql/etc/my.cnf

②、复制应用日之后的数据库信息，注意非mysql用户操作需要注意权限问题

cd /data/mysql/data/

nohup innobackupex --defaults-file=/data/mysql/etc/my.cnf --copy-back /path/bakupdatedir/ >/data/mysql/backup/recoverydate +%Y%m%d.log 2>&1 &

chown -R mysql:mysql /data/mysql

③、启动mysql新实例，更新gtid信息

mysqld --defaults-file=/data/mysql/etc/my.cnf &

mysql -S /data/mysql/socket/mysql.sock -p

cat /path/bakupdatedir/xtrabackup_binlog_info

mysql-bin.000003 36533 34f7b70e-fd0b-11e8-91c4-001a4aa1488a:1-198

> show master status\G;

> reset master;

> set global gtid_purged='34f7b70e-fd0b-11e8-91c4-001a4aa1488a:1-198'

> show master status\G;

2、慢日志分析

Percona-Toolkit——pt-query-digest安装及使用，前提是数据库开启慢日志参数

[mysqld]

slow_query_log=0.1

log_timestamps=SYSTEM

slow_query_log_file=/data/mysql/data/slow.log

2.1、安装配置

2.1.1、下载二进制安装包

cd /opt

wget https://www.percona.com/downloads/percona-toolkit/3.2.0/binary/tarball/percona-toolkit-3.2.0_x86_64.tar.gz

2.1.2、解压

tar zxvf percona-toolkit-3.2.0_x86_64.tar.gz

cd percona-toolkit-3.2.0/

2.1.3、安装

perl Makefile.PL PREFIX=/usr/local/percona-toolkit

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.

BEGIN failed--compilation aborted at Makefile.PL line 1.

yum -y install perl-CPAN

Checking if your kit is complete…

Looks good

Warning: prerequisite DBD::mysql 3 not found.

Warning: prerequisite DBI 1.46 not found.

Writing Makefile for percona-toolkit

yum -y install perl-DBD-MySQL

Writing Makefile for percona-toolkit

make && make install

/usr/local/percona-toolkit/bin/pt-query-digest --version

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/percona-toolkit/bin/pt-query-digest line 2492.

BEGIN failed--compilation aborted at /usr/local/percona-toolkit/bin/pt-query-digest line 2492.

yum -y install perl-Digest-MD5

/usr/local/percona-toolkit/bin/pt-query-digest --version

pt-query-digest 3.2.0

2.2、参数及分析结果介绍

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。

--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。

--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析

--limit 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。

--host mysql服务器地址

--user mysql用户名

--password mysql用户密码

--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。

--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。

--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。

--since 从什么时间开始分析，值为字符串，可以是指定的某个"yyyy-mm-dd [hh:mm:ss]"格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。

--until 截止时间，配合—since可以分析一段时间内的慢查询。

pt-query-digest输出结果

2.2.1、总体统计结果

total：语句总数量，unique：唯一的语句数量，QPS每秒查询率，concurrency并发数，Time Range时间范围，95%是最具有参考价值的 2、查询分组结果

Rank：本次分析中按查询时间降序排列的排名 Response time：SQL总响应时间，占总响应时间的百分比，Calls：执行次数 R/Call：每次执行的平均响应时间 V/M：响应时间的方差均值比，变异数对平均数比，可说明样本的分散程度. 这个值大,往往是值得考虑优化的对象 3、每一种查询的详细统计结果

202671827表示在文本中的偏移量，可以利用偏移量到慢查询日志里定位具体的sql语句：tail -c +202671827 20200717.log "head

2.3、使用

1、直接分析慢日志

pt-query-digest slow.log > slow_report.log

2、分析最近12小时内的查询

pt-query-digest --since=12h slow.log > slow_report2.log

3、分析指定时间范围内的查询

pt-query-digest --since '2020-08-03 18:00:00' --until '2020-08-03 19:00:00' slow.log> slow_report3.log

4、分析指含有select语句的慢查询

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

5、针对某个用户的慢查询

pt-query-digest --filter '($event->{user} "" "") =~ m/^root/i' slow.log> slow_report5.log

6、查询所有所有的全表扫描或full join的慢查询

pt-query-digest --filter '(($event->{Full_scan} "" "") eq "yes") ""(($event->{Full_join} "" "") eq "yes")' slow.log> slow_report6.log

7、把查询保存到query_review表，之前数据不会被覆盖

pt-query-digest -hlocalhost -P3306 -uslow -pslow --review D=slowlog,t=query_review slow.log>slow_report7.log

8、把查询保存到query_history表，之前数据不会被覆盖

pt-query-digest --review -hlocalhost -P3306 -uslow -pslow D=slowlog,t=query_history slow.log>slow_report8.log

pt-query-digest --history h=192.168.1.202,P=3306,u=slow,p=slow,D=slowlog,t=query_review_history 20200717.log>slow_report81.log

9、通过tcpdump抓取mysql的tcp协议数据，然后再分析

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

10、分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql

pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

11、分析general log

pt-query-digest --type=genlog localhost.log > slow_report11.log

12、综合分析

pt-query-digest -h192.168.1.202 -P3306 -uslow -pslow --review D=slowlog,t=query_history --since '2020-07-21 12:00:00' --until '2020-08-01 12:00:00' --filter '$event->{fingerprint} =~ m/^update/i' 20200717.log >update.log

13、打印特定查询，去掉查询id中0x

pt-query-digest 20200717.log --no-report --output slowlog --filter '$event->{fingerprint} && make_checksum($event->{fingerprint}) eq "DABEEAF32C72B35E0CC6009DEEBF10C2"'

2.4、做成定时任务，通过pt-query-digest把慢SQL存到数据库表中

#!/bin/bash

#1、参数

SUPER_USER='root'

SUPER_PWD='mysql'

DB_IP='ip'

DB_USER='slow'

DB_PWD='slow'

DB_DBS='slowlog'

DB_PORT=3306

LOG_PATH='/data/mysql/mysql5730/slowlog/'

DB_TIME1= `date +%Y%m%d%H%M%S`

DB_TIME=`date +%Y-%m-%d %H:%M:%S`

# 分析前先将之前的分析结果表重命名

ntn=$"query_review_${DB_TIME1}"

rtnsql="ALTER TABLE query_review RENAME TO ${ntn};";

echo "-----------$DB_TIME:慢日志割接分析信息----------">>$LOG_PATH/slow_gjfx.log

/usr/local/mysql5730/bin/mysql -h${DB_IP} -u${DB_USER} -p${DB_PWD} ${DB_DBS} -e "${rtnsql}"

if [ $? -eq 0 ]; then

echo $DB_TIME":query_review rename success">>$LOG_PATH/slow_gjfx.log

else

echo $DB_TIME":query_review rename fail">>$LOG_PATH/slow_gjfx.log

# 将日志文件mv到新的文件并重新指定mysql的slowlog新文件路径

logpath='/data/mysql/mysql5730/data/slow.log'

baklogpath="/data/mysql/mysql5730/slowlog/slow_${DB_TIME1}.bak.log"

echo "baklogpath:${baklogpath}">>$LOG_PATH/slow_gjfx.log

mv ${logpath} ${baklogpath}

if [ $? -eq 0 ]; then

echo $DB_TIME":Slow Log Mv Success">>$LOG_PATH/slow_gjfx.log

else

echo $DB_TIME":Slow Log Mv Fail">>$LOG_PATH/slow_gjfx.log

/usr/local/mysql5730/bin/mysql -h$DB_IP -P$DB_PORT -u$SUPER_USER -p$SUPER_PWD -e "set global slow_query_log_file='${logpath}'"

if [ $? -eq 0 ]; then

echo $DB_TIME":slow log set success">>$LOG_PATH/slow_gjfx.log

else

echo $DB_TIME":slow log set fail">>$LOG_PATH/slow_gjfx.log

# 使用pt-query-digest分析并将结果上报到指定数据库内参数可以参考官方文档

/opt/ptool320/bin/pt-query-digest --user=${DB_USER} --password=${DB_PWD} --history h=${DB_IP},D=${DB_DBS},t=query_review --create-review-table ${baklogpath}

if [ $? -eq 0 ]; then

echo $DB_TIME":pt-query-digest success">>$LOG_PATH/slow_gjfx.log

else

echo $DB_TIME":pt-query-digest fail">>$LOG_PATH/slow_gjfx.log

3、审计

3.1、审计插件安装卸载（社区版）

3.1.1、插件安装（社区版）

插件下载地址：

wget https://github.com/mcafee/mysql-audit/releases/download/v1.1.10/audit-plugin-mysql-5.7-1.1.10-980-linux-x86_64.zip -P /opt

解压：unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip

查看MySQL的插件目录：

> show variables like 'plugin_dir';

+---------------+------------------------------+

" Variable_name " Value "

+---------------+------------------------------+

" plugin_dir " /usr/local/mysql/lib/plugin/ "

+---------------+------------------------------+

复制库文件到MySQL库目录下：

cp /opt/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/

chmod a+x /usr/local/mysql/lib/plugin/libaudit_plugin.so

chown mysql:mysql /usr/local/mysql/lib/plugin/libaudit_plugin.so

进入mysql命令窗口，安装插件：

> install plugin audit soname 'libaudit_plugin.so';

Query OK, 0 rows affected (0.06 sec)

查看mysql当前已经加载了哪些插件：

> show plugins;

+----------------------------+----------+--------------------+--------------------+---------+

" Name " Status " Type " Library " License "

+----------------------------+----------+--------------------+--------------------+---------+

" binlog " ACTIVE " STORAGE ENGINE " NULL " GPL "

…………

" AUDIT " ACTIVE " AUDIT " libaudit_plugin.so " GPL "

+----------------------------+----------+--------------------+--------------------+---------+

查看版本：

> show global status like '%audit%';

+------------------------+------------+

" Variable_name " Value "

+------------------------+------------+

" Audit_protocol_version " 1.0 "

" Audit_version " 1.1.10-980 "

+------------------------+------------+

3.1.2、开启Audit功能及配置

> SET GLOBAL audit_json_file=ON;

Query OK, 0 rows affected (0.00 sec)

执行任何语句（默认会记录任何语句，有语法错误的不会记录），然后去mysql数据目录查看mysql-audit.json文件（默认为该文件）。

可以查看插件有哪些可配置的参数：

> SHOW GLOBAL VARIABLES LIKE '%audi%';

其中需要关注的参数有：

① audit_json_file

是否开启audit功能。

②audit_json_log_file

记录文件的路径和名称信息（默认放在mysql数据目录下）。

③audit_record_cmds

audit记录的命令，默认为记录所有命令。可以设置为任意dml、dcl、ddl的组合。如：audit_record_cmds=select,insert,delete,update。还可以在线设置set global audit_record_cmds=NULL。(表示记录所有命令)

④audit_record_objs

audit记录操作的对象，默认为记录所有对象，可以用SET GLOBAL audit_record_objs=NULL设置为默认。也可以指定为下面的格式：audit_record_objs=,test.,mysql.,information_schema.*。

⑤ audit_whitelist_users

用户白名单。

3.1.3、配置参数

为了保证重启数据库，配置不丢失，修改my.cnf 配置文件，将下面的配置添加到[mysqld]中，所以在配置文件中my.cnf加入参数：

audit_json_file=on #保证mysql重启后自动启动插件

plugin-load=AUDIT=libaudit_plugin.so #防止删除了插件，重启后又会加载

audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate' #要记录哪些命令语句,因为默认记录所有操作；

3.1.4、插件卸载

直接执行 UNINSTALL PLUGIN AUDIT 卸载会报错：

> uninstall plugin AUDIT;

ERROR 1148 (42000): Uninstall AUDIT plugin disabled

> set audit_uninstall_plugin=on;

ERROR 1238 (HY000): Variable 'audit_uninstall_plugin' is a read only variable

需要在 my.cnf 中 [mysqld] 下添加 audit_uninstall_plugin=1，重启mysql。重启完毕后执行两次 UNINSTALL PLUGIN AUDIT; 即可卸载。

> UNINSTALL PLUGIN AUDIT;

ERROR 1620 (HY000): Uninstall AUDIT plugin must be called again to complete

> UNINSTALL PLUGIN AUDIT;

Query OK, 0 rows affected, 1 warning (0.01 sec)

卸载完成后需要从 my.cnf 中删除 audit_uninstall_plugin=1 ，否则下次mysql启动会报错：[ERROR] /data/mysql/bin/mysqld: unknown variable 'audit_uninstall_plugin=1'

3.2、MariaDB server_audit 审计插件

MySQL 社区版还可以安装 MariaDB 的审计插件，安装方法同上面的基本一致。

3.2.1、下载安装

下载地址：

https://mariadb.org/mariadb/all-releases/

需压下载MariaDB对应的版本后，解压后在plugin目录下找到server_audit.so插件

MariaDB_5.5.37版本和MariaDB_10.0.10以后版本的audit插件支持MariaDB, MySQL、Percona Server使用。

MariaDB_5.x.x 和 MariaDB_10.x.x 区别：

MariaDB_5.x.x：兼容MySQL5.x.x的，接口几乎一致，只限于社区版

MariaDB_10.x.x：10.x.x使用新技术，接口会与mysql逐渐区别开来。目标就是以后想MariaDB新接口过渡。

下载解压后，复制 mariadb-x.x.xx-linux-x86_64/lib/plugin下的 server_audit.so 到 /usr/local/mysql/lib/plugin 下，添加 +x 权限。然后安装：

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

查看变量开启设置情况，默认貌似都是关闭的：

复制代码

> show variables like '%server_audit%';

+-------------------------------+-----------------------+

" Variable_name " Value "

+-------------------------------+-----------------------+

" server_audit_events " "

" server_audit_excl_users " "

" server_audit_file_path " server_audit.log "

" server_audit_file_rotate_now " OFF "

" server_audit_file_rotate_size " 1000000 "

" server_audit_file_rotations " 9 "

" server_audit_incl_users " "

" server_audit_loc_info " "

" server_audit_logging " OFF "

" server_audit_mode " 1 "

" server_audit_output_type " file "

" server_audit_query_log_limit " 1024 "

" server_audit_syslog_facility " LOG_USER "

" server_audit_syslog_ident " mysql-server_auditing "

" server_audit_syslog_info " "

" server_audit_syslog_priority " LOG_INFO "

+-------------------------------+-----------------------+

配置参数

mkdir -p /data/mysql/auditlogs/

chown mysql.mysql /data/mysql/auditlogs/ -R

可以在 my.cnf 中添加如下配置，然后重启 mysqld

server_audit_events='CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL' #备注：指定哪些操作被记录到日志文件中

server_audit_logging=on

server_audit_file_path =/data/mysql/auditlogs/ #备注：审计日志存放路径，该路径下会生成一个server_audit.log文件，就会记录相关操作记录了

server_audit_file_rotate_size=200000000

server_audit_file_rotations=200

server_audit_file_rotate_now=ON

3.2.2、可以动态开启审计功能

> set global server_audit_logging=on;

Query OK, 0 rows affected (0.00 sec)

默认在mysql的数据目录下生成文件 server_audit.log ，日志格式为：

20220113 17:56:16,CentOS6.6-Slave1,,,4,11,QUERY,,'select host,user,plugin from mysql.users',1146

20220113 18:26:00,CentOS6.6-Slave1,,,4,13,QUERY,,'insert into t values (7)',0

20220113 18:27:49,CentOS6.6-Slave1,,,4,15,QUERY,,'shows',1064 #语法错误的语句也会被记录

可以看到，MariaDB的审计插件日志要比MySQL社区插件简洁许多。

3.2.3、参数说明

（请参考：https://mariadb.com/kb/en/mariadb/server_audit-system-variables/）

server_audit_output_type：指定日志输出类型，可为SYSLOG或FILE

server_audit_logging：启动或关闭审计

server_audit_events：指定记录事件的类型，可以用逗号分隔的多个值(connect,query,table)，如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录

server_audit_file_path：如server_audit_output_type为FILE，使用该变量设置存储日志的文件，可以指定目录，默认存放在数据目录的server_audit.log文件中

server_audit_file_rotate_size：限制日志文件的大小

server_audit_file_rotations：指定日志文件的数量，如果为0日志将从不轮转

server_audit_file_rotate_now：强制日志文件轮转

server_audit_incl_users：指定哪些用户的活动将记录，connect将不受此变量影响，该变量比server_audit_excl_users优先级高

server_audit_syslog_facility：默认为LOG_USER，指定facility

server_audit_syslog_ident：设置ident，作为每个syslog记录的一部分

server_audit_syslog_info：指定的info字符串将添加到syslog记录

server_audit_syslog_priority：定义记录日志的syslogd priority

server_audit_excl_users：该列表的用户行为将不记录，connect将不受该设置影响

server_audit_mode：标识版本，用于开发测试

3.2.4、卸载server_audit插件

mysql> UNINSTALL PLUGIN server_audit;

mysql> show variables like '%audit%';

Empty set (0.00 sec)

注：

防止 server_audit 插件被卸载，需要在配置文件中添加：

[mysqld]

server_audit=FORCE_PLUS_PERMANENT

重启MySQL生效

mysql> UNINSTALL PLUGIN server_audit;

ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and can not be unloaded

值得注意的是，应该在server_audit插件被安装好，并且已经运行之后添加这些配置，否则过早在配置文件添加这个选项，会导致MySQL发生启动错误！

3.3、MySQL Enterprise Audit Plugin

MySQL 企业版的 Enterprise Edition 中自带 Audit Plugin ，名为 audit_log.so

3.3.1、安装配置

> install plugin audit_log soname 'audit_log.so';

导入审计信息，并更新mysql.audit_log_user/mysql.audit_log_filter表引擎为Innodb，验证插件

/path/bin/mysql -S /path/socket/mysql.sock -p </path/share/audit_log_filter_linux_install.sql

mysql> ALTER TABLE mysql.audit_log_user ENGINE=InnoDB;

mysql> ALTER TABLE mysql.audit_log_filter ENGINE=InnoDB;

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; --状态为ACTIVE

默认在 datadir 目录下生成 audit.log ，其格式为 xml ，下面举几个例子。

<AUDIT_RECORD>

<RECORD_ID>4_2022-01-13T13:23:12</RECORD_ID>

<CONNECTION_ID>3</CONNECTION_ID>

<STATUS_CODE>0</STATUS_CODE>

<OS_LOGIN/>

<HOST>localhost</HOST>

<IP/>

<COMMAND_CLASS>connect</COMMAND_CLASS>

<CONNECTION_TYPE>Socket</CONNECTION_TYPE>

</AUDIT_RECORD>

# 登录失败，用户名或密码错误，错误码 1045

<AUDIT_RECORD>

<RECORD_ID>5_2022-01-13T13:30:12</RECORD_ID>

<NAME>Connect</NAME>

<CONNECTION_ID>4</CONNECTION_ID>

<STATUS_CODE>1</STATUS_CODE>

<OS_LOGIN/>

<HOST>localhost</HOST>

<IP/>

<COMMAND_CLASS>connect</COMMAND_CLASS>

<CONNECTION_TYPE>Socket</CONNECTION_TYPE>

<PRIV_USER>root</PRIV_USER>

<PROXY_USER/>

<DB/>

</AUDIT_RECORD>

# 登录成功 STATUS=0

<AUDIT_RECORD>

<RECORD_ID>6_2022-01-13T13:38:12</RECORD_ID>

<NAME>Connect</NAME>

<CONNECTION_ID>5</CONNECTION_ID>

<STATUS_CODE>0</STATUS_CODE>

<OS_LOGIN/>

<HOST>localhost</HOST>

<IP/>

<COMMAND_CLASS>connect</COMMAND_CLASS>

<CONNECTION_TYPE>Socket</CONNECTION_TYPE>

<PRIV_USER>root</PRIV_USER>

<PROXY_USER/>

<DB/>

</AUDIT_RECORD>

# 执行命令 show_variables

<AUDIT_RECORD>

<RECORD_ID>9_2022-01-13T13:50:12</RECORD_ID>

<NAME>Query</NAME>

<CONNECTION_ID>5</CONNECTION_ID>

<STATUS_CODE>0</STATUS_CODE>

<USER>root[root] @ localhost []</USER>

<OS_LOGIN/>

<HOST>localhost</HOST>

<IP/>

<COMMAND_CLASS>show_variables</COMMAND_CLASS>

<SQLTEXT>show global variables like '%audit%'</SQLTEXT>

</AUDIT_RECORD>

注意：企业版 audit plugin 也会记录语法错误的sql语句。

3.3.2、标签元素解释

# 各标签元素解读

<audit>:文件的根标签为<AUDIT>，并以 </AUDIT>为结束标签

<AUDIT_RECORD> :包含一系列的必选标签和可选标签，可选标签是否出现取决于audit record类型。

<NAME>：必选，例如<NAME>Query</NAME>，可能出现的值还包含Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB,

Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh,

<RECORD_ID>：必选，例如<RECORD_ID>28743_2022-01-13T15:03:24</RECORD_ID>，包含一些列数字和时间戳，数字表示的是记录数，每增加一条记录，数字加1.

<TIMESTAMP>：必选，例如<TIMESTAMP>2022-01-13T15:03:49 UTC</TIMESTAMP>，包含时间戳和时区两部分，记录的是从客户端接收到的sql执行完时刻的时间。

# 以下标签audit record类型决定是否出现

<COMMAND_CLASS>：命令的类型。例如<COMMAND_CLASS>drop_table</COMMAND_CLASS>.

<CONNECTION_ID>：例如<CONNECTION_ID>127</CONNECTION_ID>，代表客户端连接标识符的无符号整型数字。

<DB>：mysql连接的默认数据库名称，该标签只在 <NAME>值是Connect或Change user时出现.

<HOST>：client端的主机名，该标签只在 <NAME>值是Connect，Change user或Query时出现，例如<HOST>localhost</HOST>。

<IP>：client端的IP地址，该标签只在 <NAME>值是Connect，Change user或Query时出现，例如<IP>127.0.0.1</IP>。

<MYSQL_VERSION>：mysql版本号，只在 <NAME>值是Audit时出现，例如<MYSQL_VERSION>5.7.1-m11-log</MYSQL_VERSION>

<OS_LOGIN>：外部用户，该标签只在 <NAME>值是Connect，Change user或Query时出现。

<OS_VERSION>：表示运行数据库的服务器的操作系统，只在 <NAME>值是Audit时出现，例如<OS_VERSION>x86_64-Linux</OS_VERSION>。

<PRIV_USER>：服务器认证的客户端名称。该标签只在 <NAME>值是Connect或Change user时出现。例如<PRIV_USER>root</PRIV_USER>。

<PROXY_USER>：通过proxy连接到mysql的用户。该标签只在 <NAME>值是Connect或Change user时出现。

<SERVER_ID>：mysql数据库服务器的ID号，该标签只在 <NAME>值是Audit或No Audit时出现。例如<SERVER_ID>1</SERVER_ID>。

<SQLTEXT>：实际执行的SQL语句。该标签只在 <NAME>值是 Query 或 Execute时出现。例如<SQLTEXT>DELETE FROM t1</SQLTEXT>。

<STARTUP_OPTIONS>：mysql数据库启动选项，该标签只在 <NAME>值是Audit时出现，例如<STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld --port=3306 --log-output=FILE</STARTUP_OPTIONS>

<STATUS>：代表sql命令的执行状态，0表示成功，其余表示有错误。例如<STATUS>1051</STATUS>。

<STATUS_CODE>：代表sql命令的执行状态，0表示成功，1表示有错误。例如<STATUS_CODE>0</STATUS_CODE>。

<USER>：客户端连接mysql服务器的用户名。例如<USER>root[root] @ localhost [127.0.0.1]</USER>。

<VERSION>：表示日志文件格式的版本号。该标签只在 <NAME>值是Audit时出现。例如<VERSION>1</VERSION>

配置参数

服务器启动激活插件，以及避免运行时，插件被删除配置参数audit-log

[mysqld]

audit-log=FORCE_PLUS_PERMANENT

audit_log_file=/data/mysql/data/audit.log

audit_log_format=NEW/OLD/JSON --5.7不支持json NEW格式更友好

posted @ 2022-03-16 18:17 up~up 阅读(229) 评论(0) 编辑收藏举报

刷新页面返回顶部

数据库插件 慢日志分析，备份恢复，审计

1、备份恢复

1.1、逻辑备份及恢复

1.1.1、备份业务库

1.1.2、恢复业务库

1.1.3、补充是GTID环境备份时没有加--set-gtid-purged=off，导入会报错

1.2、物理备份及恢复

1.2.1、下载安装配置Percona Xtrabackup

1.2.2、解压安装配置到环境变量

1.2.3、源端进行全备操作

1.2.4、目标端进行恢复操作（需要有对应版本的MySQL，解压就行不用安装）

2、慢日志分析

2.1、安装配置

2.1.1、下载二进制安装包

2.1.2、解压

2.1.3、安装

2.2、参数及分析结果介绍

2.2.1、总体统计结果

2.3、使用

2.4、做成定时任务，通过pt-query-digest把慢SQL存到数据库表中

3、审计

3.1、审计插件安装卸载（社区版）

3.1.1、插件安装（社区版）

3.1.2、开启Audit功能及配置

3.1.3、配置参数

3.1.4、插件卸载

3.2、MariaDB server_audit 审计插件

3.2.1、下载安装

3.2.2、可以动态开启审计功能

3.2.3、参数说明

3.2.4、卸载server_audit插件

3.3、MySQL Enterprise Audit Plugin

3.3.1、安装配置

3.3.2、标签元素解释

公告

数据库插件慢日志分析，备份恢复，审计