赞
踩
一个或者一组
二进制文件,通常来说存在与文件系统之上。后台进程/线程
以及一个共享内存区
组成共享内存
可以被运行的后台进程/线程
所共享操作数据库文件
的单进程多线程结构
不会影响MySQL的性能,看程序如何写。(多进程程序,进程间通信开销大于多线程)
存储引擎的概念
存储引擎
的对象就是表
INNODB
体系结构图
- |--> table1 --- | view1 |
- MySQL Instance -----> Database ----> Schema ---> |--> table2 --- | view2 |
- |--> table3 --- | View3 |
- 注意: MySQL中一个Database对应一个Schema
shell> mysqlfrm --diagnostic /data/mysql_data/aaa/.a.frm #可将frm文件转成create table的语句
slow_query_log
slow_query_log_file
my.cnf
我们已经定义为slow.log,默认是 机器名-slow.loglong_query_time
>=5.5.X
,支持毫秒。例如0.5
即为500ms
大于
该值,不包括值本身。例如该值为2,则执行时间正好等于
2的SQL语句不会记录
log_queries_not_using_indexes
没有使用索引
的SQL记录到慢查询日志
log_throttle_queries_not_using_indexes
限制每分钟内
,在慢查询日志中,去记录没有使用索引的SQL语句的次数
;版本需要>=5.6.X
min_examined_row_limit
扫描记录少于该值
的SQL不记录到慢查询日志
log_slow_admin_statements
记录超时的管理操作
SQL到慢查询日志,比如ALTER/ANALYZE TABLElog_output
>=5.5
mysql.slow_log
log_slow_slave_statements
log_timestamps
查看慢查询记录的相关参数
- root@gczheng 00:01: [(none)]> select version();
- +------------+
- | version() |
- +------------+
- | 5.7.18-log |
- +------------+
- 1 row in set (0.00 sec)
-
- root@gczheng 00:02: [(none)]> show variables like "slow%";
- +---------------------+------------------------+
- | Variable_name | Value |
- +---------------------+------------------------+
- | slow_launch_time | 2 |
- | slow_query_log | ON | --slow_query_log已打开
- | slow_query_log_file | /r2/mysqldata/slow.log | --slow日志位置
- +---------------------+------------------------+
- 3 rows in set (0.01 sec)
-
- root@gczheng 00:03: [(none)]> show variables like "long_query%";
- +-----------------+----------+
- | Variable_name | Value |
- +-----------------+----------+
- | long_query_time | 1.000000 | -- my.cnf 中该值设置为2秒
- +-----------------+----------+
- 1 row in set (0.01 sec)
-
- root@gczheng 00:03: [(none)]> show variables like "min_ex%";
- +------------------------+-------+
- | Variable_name | Value |
- +------------------------+-------+
- | min_examined_row_limit | 0 | -- my.cnf中未配置,默认值为0
- +------------------------+-------+
- 1 row in set (0.00 sec)
-
查看慢查询日志
- [root@rhel7 mysqldata]# tail -f /r2/mysqldata/slow.log
- bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
- Tcp port: 3306 Unix socket: /r2/mysqldata/mysql.sock
- Time Id Command Argument
进行模拟耗时操作
- root@gczheng 00:11: [(none)]> select sleep(8);
- +----------+
- | sleep(8) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (8.00 sec)
最终产生慢查询日志
- [root@rhel7 mysqldata]# tail -f /r2/mysqldata/slow.log
- bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
- /usr/local/mysql/bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
- Tcp port: 3306 Unix socket: /r2/mysqldata/mysql.sock
- Time Id Command Argument
- # Time: 2017-11-23T00:11:20.876205+08:00
- # User@Host: root[root] @ localhost [] Id: 3
- # Query_time: 8.000251 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
- #这个就是min_examined_row_limit
- #设置的意义。如my.cnf中设置该值为100
- #则这条语句因为Rows_examined < 100,而不会被记录
- SET timestamp=1511367080;
- select sleep(8);
注意
如果在终端A中set global min_examined_row_limit = 100;
, 然后执行select sleep(8)
,会发现该记录仍然被记录到慢查询日志中。原因是因为set global min_examined_row_limit
设置的是全局变量,此次会话不生效。
但是我们上面
set global slow_query_log = 1;
却是在线生效的,这点有所不通
- [root@rhel7 mysqldata]# mysqldumpslow slow.log
-
- Reading mysql slow query log from slow.log
- Count: 2 Time=15.88s (31s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
- select sleep(N)
-
- Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
- bin/mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
-
- [root@rhel7 mysqldata]# mysqldumpslow --help
- Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
-
- Parse and summarize the MySQL slow query log. Options are
-
- --verbose verbose
- --debug debug
- --help write this text to standard output
- -v verbose
- -d debug
- -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
- al: average lock time
- ar: average rows sent
- at: average query time
- c: count
- l: lock time
- r: rows sent
- t: query time
- -r reverse the sort order (largest last instead of first)
- -t NUM just show the top n queries
- -a don't abstract all numbers to N and strings to 'S'
- -n NUM abstract numbers with at least n digits within names
- -g PATTERN grep: only consider stmts that include this string
- -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
- default is '*', i.e. match all
- -i NAME name of server instance (if using mysql.server startup script)
- -l don't subtract lock time from total time
-
如果在线上操作,不需要
mysqldumpslow
去扫整个slow.log
, 可以去tail -n 10000 slow.log > last_10000_slow.log
(10000这个数字根据实际情况进行调整),然后进行mysqldumpslow last_10000_slow.log
- mysql> show variables like "log_output%";
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | FILE |
- +---------------+-------+
- 1 row in set (0.00 sec)
- mysql> set global log_output="table"; --设置为输出为表
- Query OK, 0 rows affected (0.00 sec)
- mysql> show variables like "log_output";
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | TABLE |
- +---------------+-------+
- 1 row in set (0.00 sec)
-
-
- mysql> show variables like "slow_query_log";
- +----------------+-------+
- | Variable_name | Value |
- +----------------+-------+
- | slow_query_log | ON |
- +----------------+-------+
- 1 row in set (0.00 sec)
-
- mysql> select sleep (11); --执行语句
- +------------+
- | sleep (11) |
- +------------+
- | 0 |
- +------------+
- 1 row in set (11.00 sec)
-
-
- mysql> select * from mysql.slow_log;
- +----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-------------------+-----------+
- | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text |thread_id |
- +----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-------------------+-----------+
- | 2017-11-23 16:58:23.873261 | root[root] @ localhost [] | 00:00:11.000196 | 00:00:00.000000 | 1 | 0 | | 0 | 0 | 168 | select sleep (11) | 314459 |
- +----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-------------------+-----------+
- 1 row in set (0.00 sec)
-
- --显示表结构
- mysql> show create table mysql.slow_log \G;
- *************************** 1. row ***************************
- Table: slow_log
- Create Table: CREATE TABLE `slow_log` (
- `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
- `user_host` mediumtext NOT NULL,
- `query_time` time(6) NOT NULL,
- `lock_time` time(6) NOT NULL,
- `rows_sent` int(11) NOT NULL,
- `rows_examined` int(11) NOT NULL,
- `db` varchar(512) NOT NULL,
- `last_insert_id` int(11) NOT NULL,
- `insert_id` int(11) NOT NULL,
- `server_id` int(10) unsigned NOT NULL,
- `sql_text` mediumblob NOT NULL,
- `thread_id` bigint(21) unsigned NOT NULL
- ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' --ENGINE=CSV 这里使用的是CSV的引擎,性能较差
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
-
-
- -- 建议将slow_log表的存储引擎改成MyISAM
- mysql> alter table mysql.slow_log engine = innodb;
- ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled --无法转化为innodb引擎
-
- mysql> alter table mysql.slow_log engine = myisam;
- ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled -- 提示我正在记录日志中,不能转换myisam引擎
-
- mysql> set global slow_query_log = 0; -- 先停止记录日志
- Query OK, 0 rows affected (0.01 sec)
-
- mysql> alter table mysql.slow_log engine = innodb;
- ERROR 1579 (HY000): This storage engine cannot be used for log tables --显示不支持innodb引擎
-
- mysql> alter table mysql.slow_log engine = myisam; -- 转换表的myisam引擎
- Query OK, 2 rows affected (5.05 sec)
- Records: 2 Duplicates: 0 Warnings: 0
-
- mysql> set global slow_query_log = 1; -- 再开启记录日志
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> show create table mysql.slow_log;
- --表结构
- ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' -- ENGINE转换成myisam引擎
使用
TABLE
的优势在于方便查询,但是记住当在备份的时候,不要备份慢查询日志的表,避免备份过大。
使用FILE
也可以,需要定时清除该文件,避免单文件过大。
当需要查找某条特定SQL语句,且该SQL语句执行较快,无法记录到slow_log中时,可以开启通用日志generic_log
,进行全面记录, 可用于审计Audit
A终端设置开启关闭通用日志
- mysql> set global general_log=on; --开启通用日志
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> show create table mysql.general_log\G --显示通用日志表结构
- *************************** 1. row ***************************
- Table: general_log
- Create Table: CREATE TABLE `general_log` (
- `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
- `user_host` mediumtext NOT NULL,
- `thread_id` bigint(21) unsigned NOT NULL,
- `server_id` int(10) unsigned NOT NULL,
- `command_type` varchar(64) NOT NULL,
- `argument` mediumblob NOT NULL
- ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
- 1 row in set (0.00 sec)
-
- mysql> show variables like '%general_log%';
- +------------------+------------------------------------------------------+
- | Variable_name | Value |
- +------------------+------------------------------------------------------+
- | general_log | ON |
- | general_log_file | /r2/soft/dbtest/mysql-5.7.18/mysqldata/localhost.log | --日志位置
- +------------------+------------------------------------------------------+
- 2 rows in set (0.00 sec)
-
- mysql> set global general_log=off; --关闭日志
- [root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18/mysqldata]# tail -f localhost.log
- SUM_TIMER_WRITE_LOW_PRIORITY,
- SUM_TIMER_WRITE_NORMAL,
- SUM_TIMER_WRITE_EXTERNAL
- FROM performance_schema.table_lock_waits_summary_by_table
- WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
- 2017-11-23T17:17:28.819250+08:00 315487 Quit
- 2017-11-23T17:17:29.304861+08:00 315489 Connect gcdb@192.168.48.168 on using TCP/IP
- 2017-11-23T17:17:29.305015+08:00 315489 Query select @@version_comment limit 1
- 2017-11-23T17:17:29.305307+08:00 315489 Query show global status where Variable_name in ("Com_select","Com_insert","Com_update","Com_delete","Innodb_buffer_pool_read_requests","Innodb_buffer_pool_reads","Innodb_rows_inserted","Innodb_rows_updated","Innodb_rows_deleted","Innodb_rows_read","Threads_running","Threads_connected","Threads_cached","Threads_created","Bytes_received","Bytes_sent","Innodb_buffer_pool_pages_data","Innodb_buffer_pool_pages_free","Innodb_buffer_pool_pages_dirty","Innodb_buffer_pool_pages_flushed","Innodb_data_reads","Innodb_data_writes","Innodb_data_read","Innodb_data_written","Innodb_os_log_fsyncs","Innodb_os_log_written")
Audit Plugin
- - MariaDB Audit 插件
- - MySQL社区版本目前没有提供Audit的功能,企业版本提供了该功能。MariaDB 提供了开源的Audit插件,且MySQL也能使用。
- - [官方注册下载插件](https://mariadb.com/my_portal/download/audit_plugin)
- - Mcaffer插件下载
- - https://bintray.com/mcafee/mysql-audit-plugin/release#files 点击Files文件
- - [audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip](https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip)
MySQL5.7.18 安装mcaffer audit-plugin-mysql-5.7-1.1.4-725 版本审计插件步骤如下:
- --显示插件目录
- root@gczheng 23:16: [(none)]> show global variables like 'plugin%';
- +---------------+------------------------------+
- | Variable_name | Value |
- +---------------+------------------------------+
- | plugin_dir | /usr/local/mysql/lib/plugin/ |
- +---------------+------------------------------+
- 1 row in set (0.01 sec)
-
- --解压plugin
- [root@rhel7 software]# unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
- Archive: audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
- creating: audit-plugin-mysql-5.7-1.1.4-725/
- creating: audit-plugin-mysql-5.7-1.1.4-725/lib/
- inflating: audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so
- inflating: audit-plugin-mysql-5.7-1.1.4-725/COPYING
- inflating: audit-plugin-mysql-5.7-1.1.4-725/THIRDPARTY.txt
- inflating: audit-plugin-mysql-5.7-1.1.4-725/README.txt
- inflating: audit-plugin-mysql-5.7-1.1.4-725/plugin-name.txt
- creating: audit-plugin-mysql-5.7-1.1.4-725/utils/
- inflating: audit-plugin-mysql-5.7-1.1.4-725/utils/offset-extract.sh
- --拷贝到插件目录
- [root@rhel7 software]# cp audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
-
- [root@rhel7 software]# cd /usr/local/mysql/lib/plugin/
- [root@rhel7 plugin]# ll libaudit_plugin.so
- -rw-r--r-- 1 root root 1542249 Nov 23 23:16 libaudit_plugin.so
-
- --mycnf添加plugin-load=AUDIT=libaudit_plugin.so
- [root@rhel7 plugin]# vim /etc/my.cnf
- [mysqld]
- ......
- plugin-load=AUDIT=libaudit_plugin.so --添加配置文件load模式,另外还有install模式:INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
- ......
-
- --重启MySQL
- [root@rhel7 plugin]# service mysqld restart
- Shutting down MySQL......... SUCCESS!
- Starting MySQL........ SUCCESS!
- --显示插件
- root@gczheng 23:25: [(none)]> show plugins;
- +----------------------------+----------+--------------------+--------------------+---------+
- | Name | Status | Type | Library | License |
- +----------------------------+----------+--------------------+--------------------+---------+
- | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
- .......
-
- | ngram | ACTIVE | FTPARSER | NULL | GPL |
- | AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
- +----------------------------+----------+--------------------+--------------------+---------+
- 45 rows in set (0.00 sec)
-
- root@gczheng 23:25: [(none)]> show global status like 'AUDIT_version'; --显示审计日志版本
- +---------------+-----------+
- | Variable_name | Value |
- +---------------+-----------+
- | Audit_version | 1.1.4-725 |
- +---------------+-----------+
- 1 row in set (0.00 sec)
-
-
- root@gczheng 00:00: [(none)]> show variables like "audit%"\G; -- 查看和audit相关的参数
- *************************** 1. row ***************************
- Variable_name: audit_before_after
- Value: after
- *************************** 2. row ***************************
- Variable_name: audit_checksum
- Value:
- *************************** 3. row ***************************
- Variable_name: audit_client_capabilities
- Value: OFF
- *************************** 4. row ***************************
- Variable_name: audit_delay_cmds
- Value:
- *************************** 5. row ***************************
- Variable_name: audit_delay_ms
- Value: 0
- *************************** 6. row ***************************
- Variable_name: audit_force_record_logins
- Value: OFF
- *************************** 7. row ***************************
- Variable_name: audit_header_msg
- Value: ON
- *************************** 8. row ***************************
- Variable_name: audit_json_file
- Value: OFF
- *************************** 9. row ***************************
- Variable_name: audit_json_file_bufsize
- Value: 1
- *************************** 10. row ***************************
- Variable_name: audit_json_file_flush
- Value: OFF
- *************************** 11. row ***************************
- Variable_name: audit_json_file_retry
- Value: 60
- *************************** 12. row ***************************
- Variable_name: audit_json_file_sync
- Value: 0
- *************************** 13. row ***************************
- Variable_name: audit_json_log_file
- Value: mysql-audit.json
- *************************** 14. row ***************************
- Variable_name: audit_json_socket
- Value: OFF
- *************************** 15. row ***************************
- Variable_name: audit_json_socket_name
- Value: /var/run/db-audit/mysql.audit__r2_mysqldata_3306
- *************************** 16. row ***************************
- Variable_name: audit_json_socket_retry
- Value: 10
- *************************** 17. row ***************************
- Variable_name: audit_json_socket_write_timeout
- Value: 1000
- *************************** 18. row ***************************
- Variable_name: audit_offsets
- Value:
- *************************** 19. row ***************************
- Variable_name: audit_offsets_by_version
- Value: ON
- *************************** 20. row ***************************
- Variable_name: audit_password_masking_cmds
- Value: CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE
- *************************** 21. row ***************************
- Variable_name: audit_password_masking_regex
- Value: identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]
- *************************** 22. row ***************************
- Variable_name: audit_record_cmds
- Value:
- *************************** 23. row ***************************
- Variable_name: audit_record_objs
- Value:
- *************************** 24. row ***************************
- Variable_name: audit_sess_connect_attrs
- Value: ON
- *************************** 25. row ***************************
- Variable_name: audit_socket_creds
- Value: ON
- *************************** 26. row ***************************
- Variable_name: audit_uninstall_plugin
- Value: OFF
- *************************** 27. row ***************************
- Variable_name: audit_validate_checksum
- Value: ON
- *************************** 28. row ***************************
- Variable_name: audit_validate_offsets_extended
- Value: ON
- *************************** 29. row ***************************
- Variable_name: audit_whitelist_cmds
- Value: BEGIN,COMMIT,PING
- *************************** 30. row ***************************
- Variable_name: audit_whitelist_users
- Value:
- 30 rows in set (0.00 sec)
- root@gczheng 00:05: [(none)]> set global audit_json_log_file='/r2/mysqldata/audit.log'; --配置审计日志路径
- Query OK, 0 rows affected (0.00 sec)
- root@gczheng 00:04: [(none)]> set global audit_json_file=ON; -- 打开审计功能
- Query OK, 0 rows affected (0.00 sec)
- root@gczheng 00:11: [(none)]> show variables like "audit_json%";
- +---------------------------------+--------------------------------------------------+
- | Variable_name | Value |
- +---------------------------------+--------------------------------------------------+
- | audit_json_file | ON |
- | audit_json_file_bufsize | 1 |
- | audit_json_file_flush | OFF |
- | audit_json_file_retry | 60 |
- | audit_json_file_sync | 0 |
- | audit_json_log_file | /r2/mysqldata/audit.log |
- | audit_json_socket | OFF |
- | audit_json_socket_name | /var/run/db-audit/mysql.audit__r2_mysqldata_3306 |
- | audit_json_socket_retry | 10 |
- | audit_json_socket_write_timeout | 1000 |
- +---------------------------------+--------------------------------------------------+
- [root@rhel7 ~]# tailf /r2/mysqldata/audit.log
- {"msg-type":"activity","date":"1511453256465","thread-id":"26","query-id":"1562","user":"gcdb","priv_user":"gcdb","ip":"192.168.1.10","host":"192.168.1.10","connect_attrs":{"_os":"Win64","_client_name":"libmysql","_pid":"12056","_thread":"5756","_platform":"x86_64","program_name":"MySQLWorkbench","_client_version":"5.6.23"},"client_port":"14462","cmd":"show_keys","objects":[{"db":"information_schema","name":"/r2/mysqldata/#sql_10da1_0","obj_type":"TABLE"}],"query":"SHOW INDEX FROM `information_schema`.`TABLES`"}
-
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。