关于MySQL安全相关的监控和优化，以及数据运营。

5.5以后的版本添加了审计功能（类似于general_log，但是记录更详细），时时的审计会消耗一定的性能，因此离线分析也必不可少。

 



登录日志

  • 创建登录日志表：
CREATE TABLE test.t_access_log (
    id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    connection_id INT(11) NOT NULL,
    localname VARCHAR(30),
    matchname VARCHAR(30)
    login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
) ENGINE=MEMORY DEFAULT CHARSET=UTF8;
  • 编辑my.cnf，加入以下行后重启mysqld：
init-connect='INSERT INTO test.t_access_log(connection_id,localname,matchname) VALUES(CONNECTION_ID(),USER(),CURRENT_USER());
  • 注意 USER() 和 CURRENT_USER()

USER()：当前连接的用户名和客户端地址

CURRENT_USER()：当前认证的服务端用户名和host，对应mysql.user表中的记录

参考：function_current-user

  • 根据登录日志可查询频繁登录的用户或IP
SELECT SUBSTR(localname,1,LOCATE('@',localname)-1) user,SUBSTR(localname,LOCATE('@',localname)+1) host,COUNT(1) c FROM t_access_log WHERE login_time>NOW()-INTERVAL 1 DAY GROUP BY 1,2 ORDER BY 3 DESC LIMIT 10;

可通过在连接侧使用连接池，或者设置系统 TIME_WAIT状态的保持时间来优化：

sysctl -w net.ipv4.tcp_fin_timeout=100
grep net.ipv4.tcp_fin_timeout /etc/sysctl.conf

 

 



binlog 解析

随机选取一个时段的binlog文件，或者选取所有binlog做解析：

ls mysql-bin.0* | sort -R | head -1

然后通过mysqlbinlog工具解析：

mysqlbinlog --set-charset=UTF8 --base64-output=decode-rows -vv mysql-bin.XX

最后统计出执行量最大的一些SQL：

# 过滤单行SQL，对于大事务或跨多行的SQL暂未解析
egrep -i '^(update|delete|replace|insert)'\
# 过滤空格，替换实际数据为“?”
 | sed -re "s/\\\['\"]//g" -e "s/'[^']*'/?/g" -e 's/"[^"]*"/?/g' -e 's/\t/ /g' -e 's/ +/ /g' -e 's/ ?([!=+,<>*(]) ?/\1/g' -e 's/([=,])?-?[0-9]+(,)?/\1?\2/g' -e 's/[A-Z]/\l&/g' -e 's/`//g'\
# 替换NULL、NOW()等值，保留IFNULL()函数
 -e 's/ifnull/IFNULL/g' -e 's/null|now\(\)/?/g' -e 's/\?[,.]\?/?/g' -e 's/\?[,.]\?/?/g' -e 's/\?[,.]\?/?/g' -e 's/,\(\?\)//g'\
# 由于sed的正则引擎不支持反复替换，所以临时用perl
 | perl -pe "s/'[^']*'/?/g"\
# 排序取最大前十个SQL
 | sort | uniq -c | sort -rnk1 | head -10

统计结果如下：

mysql 配置全部ip监听_连接数

 

 



统计库表使用空间

SELECT table_schema '库名',table_name '表名',engine '引擎',IF(data_length>1024*1024*1024,CONCAT(CAST(data_length/1024/1024/1024 AS DECIMAL(8,2)),' G'),CONCAT(CAST(data_length/1024/1024 AS DECIMAL(8,2)),' M')) '数据大小',IF(index_length>1024*1024*1024,CONCAT(CAST(index_length/1024/1024/1024 AS DECIMAL(8,2)),' G'),CONCAT(CAST(index_length/1024/1024 AS DECIMAL(8,2)),' M')) '索引大小' FROM tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') ORDER BY data_length DESC,index_length DESC LIMIT 10

结果集包含如下字段：

库名

表名

引擎

数据大小

索引大小

可以直接用mysql命令导出邮件或csv格式输出：

mysql --default-character-set=UTF8 information_schema -He "$SQL"

 

 



slow-query统计

  • 需要借助percona的工具箱来完成
/usr/local/percona-toolkit/bin/pt-query-digest --history localhost -D test --order-by Query_time:sum --limit 10 --no-report slow_query.log

--history：保存结果到表中，默认是 query_review_history，“-D”指定写入数据库

--order-by：结果排序。语法是：字段名:统计方式（包含sum、min、max、cnt）

--no-report：不在终端显示结果

--limit：结果数量

参考：pt-query-digest

  • 再通过SQL查询导出结果集
SELECT sample 'SQL',ts_min '首次执行时间',ts_max '最后执行时间',ts_cnt '总次数',Query_time_sum '总耗时',Query_time_min '最小耗时',Query_time_max '最大耗时',Query_time_pct_95 '95% 耗时',Query_time_median '平均耗时',Lock_time_sum '总锁时间',Lock_time_min '最小锁时间',Lock_time_max '最大锁时间',Lock_time_pct_95 '95% 锁时间',Lock_time_median '平均锁时间',Rows_sent_sum '总查询行数',Rows_sent_min '最小查询行数',Rows_sent_max '最大查询行数',Rows_sent_pct_95 '95% 查询行数',Rows_sent_median '平均查询行数',Rows_examined_sum '总检索行数',Rows_examined_min '最小检索行数',Rows_examined_max '最大检索行数',Rows_examined_pct_95 '95% 检索行数',Rows_examined_median '平均检索行数' FROM test.query_history ORDER BY ts_cnt DESC,Query_time_sum DESC LIMIT 10

结果集包含如下字段：

SQL

首次执行时间

最后执行时间

总次数

总耗时

最小耗时

最大耗时

95% 耗时

平均耗时

总锁时间

最小锁时间

最大锁时间

95% 锁时间

平均锁时间

总查询行数

最小查询行数

最大查询行数

95% 查询行数

平均查询行数

总检索行数

最小检索行数

最大检索行数

95% 检索行数

平均检索行数

  • mysql也可直接将慢查询日志写入表 mysql.slow_log，但是结果不够齐全

启用命令如下：

SET GLOBAL log_output='TABLE';

 

 



字符集检测

使用不符合规范的字符集的库表：

SELECT TABLE_COLLATION,GROUP_CONCAT(DISTINCT TABLE_SCHEMA) dbs,GROUP_CONCAT(DISTINCT TABLE_NAME) tbs FROM TABLES WHERE TABLE_TYPE='BASE TABLE' GROUP BY TABLE_COLLATION\G

结果如下：

mysql 配置全部ip监听_mysql_02

 

 



索引预警

查询未添加索引（包含主键）的表，所有表最好都定义显示主键或索引：

SELECT a.TABLE_SCHEMA,a.TABLE_NAME FROM information_schema.TABLES a LEFT JOIN information_schema.STATISTICS b ON a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME WHERE TABLE_TYPE='BASE TABLE' AND (INDEX_NAME IS NULL OR INDEX_NAME='');

 

 



引擎分布

检测各类引擎的表数量，根据用途来判断引擎是否合理：

SELECT ENGINE,GROUP_CONCAT(DISTINCT TABLE_SCHEMA) dbs,GROUP_CONCAT(DISTINCT TABLE_NAME) tbs,COUNT(1) c FROM TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','test') GROUP BY ENGINE\G

 

 



弱密码检测

密码为空或者是简单密码，通常会有一个弱密码表，用于比对：

SELECT IFNULL(GROUP_CONCAT(CONCAT(user,'@',host) SEPARATOR ', '),'') FROM mysql.user WHERE PASSWORD(user) IN (password,PASSWORD(CONCAT(user,' ')),PASSWORD('123')) AND user!='';

注意5.6之后的版本password字段改成了authentication_string。

 

 



连接数监控

连接数使用率较高时，需要预警和优化：

SELECT COUNT(IF(COMMAND NOT IN ('Sleep','Binlog Dump','Connect','Binlog Dump GTID') AND TIME>1,1,NULL)) '执行超过1秒的SQL数',COUNT(1) '当前连接数' FROM information_schema.PROCESSLIST;

SELECT SUM(c) '连接最多的IP数',m '最大连接数',ip '连接最多的IP',c '当前连接数' FROM (SELECT COUNT(1) c,@@max_connections m,SUBSTR(HOST,1,LOCATE(':',HOST)-1) ip FROM information_schema.PROCESSLIST GROUP BY 3 ORDER BY 1 DESC) X;

EOF.