关于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表中的记录
根据登录日志可查询频繁登录的用户或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
统计结果如下:
统计库表使用空间
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:结果数量
再通过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
结果如下:
索引预警
查询未添加索引(包含主键)的表,所有表最好都定义显示主键或索引:
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.