热门城市
- 快召唤伙伴们来围观吧
- 微博 QQ QQ空间 贴吧
- 文档嵌入链接
- 复制
- 微信扫一扫分享
- 已成功复制到剪贴板
Basic Internal Troubleshooting Tools
MySQLServer有许多内置的故障诊断工具。它们总是可用的，可以提供许多关于内部正在发生的事情的见解。
许多图形工具，如Percona监控和管理（PMM），都使用内置的工具来获取他们漂亮图形的数据。即使你只打算使用图形工具，了解他们可以收集什么数据总是很好的，这样你就可以看到他们的局限性，并且在激烈的战斗中不会有不正确的期望。
可以通过SQL命令访问内置的故障排除工具。其中大多数是服务器上的标准配置，但详细信息是特定于组件的。
在本次网络研讨会中，我将讨论如何使用它们，如何解决特定于组件的问题，以及如何查找其他信息。
我将介绍show命令、信息模式、状态变量和一些特定于组件的语法。
我将不讨论性能模式（这将有一个单独的网络研讨会），并且我将尽可能使用PMM图来说明这些主题。
展开查看详情
1 .Basic Internal Troubleshooting Tools February, 15, 2018 Sveta Smirnova
2 .Table of Contents •Configuration •Metadata •Status •Engines •Percona Plugins 2
3 .Tools Available • SHOW • Available since very early versions • MySQL extension 3
4 .Tools Available • SHOW • Information Schema • Since version 5.0 • SQL Standard • SHOW commands mapped to I S tables • Plugin support • Performance issues before 8.0 For both SHOW and I S 3
5 .Tools Available • SHOW • Information Schema • Performance Schema 3
6 .Tools Available • SHOW • Information Schema • Performance Schema • EXPLAIN/DESCRIBE 3
7 .Configuration
8 .Character Sets and Collations • CHARACTER SETS mysql> select * from character_sets; -- SHOW CHARACTER SET; +--------------------+----------------------+-----------------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+-----------------------------+--------+ | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 | | dec8 | dec8_swedish_ci | DEC West European | 1 | | cp850 | cp850_general_ci | DOS West European | 1 | | hp8 | hp8_english_ci | HP West European | 1 | | koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 | | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 | | swe7 | swe7_swedish_ci | 7bit Swedish | 1 | | ascii | ascii_general_ci | US ASCII | 1 | | ujis | ujis_japanese_ci | EUC-JP Japanese | 3 | ... 5
9 .Character Sets and Collations • COLLATION CHARACTER SET APPLICABILITY mysql> select * from collation_character_set_applicability; +------------------+--------------------+ | COLLATION_NAME | CHARACTER_SET_NAME | +------------------+--------------------+ | big5_chinese_ci | big5 | | big5_bin | big5 | | dec8_swedish_ci | dec8 | | dec8_bin | dec8 | | cp850_general_ci | cp850 | | cp850_bin | cp850 | | hp8_english_ci | hp8 | | hp8_bin | hp8 | | koi8r_general_ci | koi8r | | koi8r_bin | koi8r | ... 5
10 .Character Sets and Collations • COLLATIONS mysql> select * from collations; -- SHOW COLLATIONS; +------------------+--------------------+----+------------+-------------+---------+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | +------------------+--------------------+----+------------+-------------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 | | dec8_bin | dec8 | 69 | | Yes | 1 | | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | | cp850_bin | cp850 | 80 | | Yes | 1 | | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | | hp8_bin | hp8 | 72 | | Yes | 1 | | koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | | koi8r_bin | koi8r | 74 | | Yes | 1 | ... 5
11 .What is Installed? • ENGINES mysql> SELECT * FROM ENGINES; -- SHOW ENGINES; +--------------------+---------+-------------------------+--------------+------+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +--------------------+---------+-------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MRG_MYISAM | YES | Collection of identi... | NO | NO | NO | | InnoDB | YES | Percona-XtraDB, Supp... | YES | YES | YES | | BLACKHOLE | YES | /dev/null storage en... | NO | NO | NO | | MEMORY | YES | Hash based, stored i... | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL stor... | NULL | NULL | NULL | +--------------------+---------+-------------------------+--------------+------+------------+ 9 rows in set (0.01 sec) 6
12 .What is Installed? • PLUGINS mysql> SHOW PLUGINS; +-----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +-----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | XTRADB_READ_VIEW | ACTIVE | INFORMATION SCHEMA | NULL | GPL | ... | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | ... | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | +-----------------------------+----------+--------------------+---------+---------+ 6 50 rows in set (0.01 sec)
13 .What is Installed? • PLUGINS mysql> SELECT * FROM PLUGINS WHERE PLUGIN_NAME=’binlog’\G *************************** 1. row *************************** PLUGIN_NAME: binlog PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 50721.0 PLUGIN_LIBRARY: NULL PLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: MySQL AB PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction PLUGIN_LICENSE: GPL LOAD_OPTION: FORCE 1 row in set (0.01 sec) 6
14 .Variables: Overview • GLOBAL VARIABLES mysql> SELECT * FROM GLOBAL_VARIABLES ORDER BY VARIABLE_NAME; -- SHOW GLOBAL VARIABLES; +--------------------------+---------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------+---------------------------+ | AUTOCOMMIT | ON | | AUTOMATIC_SP_PRIVILEGES | ON | | AUTO_INCREMENT_INCREMENT | 1 | | AUTO_INCREMENT_OFFSET | 1 | | AVOID_TEMPORAL_UPGRADE | OFF | | BACK_LOG | 80 | | BASEDIR | /home/sveta/build/ps-5.7/ | | BIG_TABLES | OFF | | BIND_ADDRESS | * | | BINLOG_CACHE_SIZE | 32768 | ... 7
15 .Variables: Overview • SESSION VARIABLES mysql> SELECT * FROM SESSION_VARIABLES ORDER BY VARIABLE_NAME; -- SHOW [SESSION] VARIABLES; +--------------------------+---------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------+---------------------------+ | AUTOCOMMIT | ON | | AUTOMATIC_SP_PRIVILEGES | ON | | AUTO_INCREMENT_INCREMENT | 1 | | AUTO_INCREMENT_OFFSET | 1 | | AVOID_TEMPORAL_UPGRADE | OFF | | BACK_LOG | 80 | | BASEDIR | /home/sveta/build/ps-5.7/ | | BIG_TABLES | OFF | | BIND_ADDRESS | * | | BINLOG_CACHE_SIZE | 32768 | ... 7
16 .Variables: Overview • Differences mysql> SELECT g.VARIABLE_NAME, -> g.VARIABLE_VALUE AS GLOBAL_VALUE, s.VARIABLE_VALUE AS SESSION_VALUE -> FROM GLOBAL_VARIABLES g JOIN SESSION_VARIABLES s USING(VARIABLE_NAME) -> WHERE g.VARIABLE_VALUE != s.VARIABLE_VALUE ORDER BY VARIABLE_NAME; +--------------------------+-------------------+-----------------+ | VARIABLE_NAME | GLOBAL_VALUE | SESSION_VALUE | +--------------------------+-------------------+-----------------+ | CHARACTER_SET_CLIENT | latin1 | utf8 | | CHARACTER_SET_CONNECTION | latin1 | utf8 | | CHARACTER_SET_DATABASE | latin1 | utf8 | | CHARACTER_SET_RESULTS | latin1 | utf8 | | COLLATION_CONNECTION | latin1_swedish_ci | utf8_general_ci | | COLLATION_DATABASE | latin1_swedish_ci | utf8_general_ci | +--------------------------+-------------------+-----------------+ 6 rows in set, 2 warnings (0.13 sec) 7
17 .Variables: Overview • Deprecated! mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: ’INFORMATION_SCHEMA.GLOBAL_VARIABLES’ is deprecated and will be removed in a future release. Please use performance_schema.global_variables instead *************************** 2. row *************************** Level: Warning Code: 1287 Message: ’INFORMATION_SCHEMA.SESSION_VARIABLES’ is deprecated and will be removed in a future release. Please use performance_schema.session_variables instead 2 rows in set (0.00 sec) 7
18 .Variables: Why they are Important? • MySQL uses multiple configuration files • You can easily miss one of them! 8
19 .Variables: Why they are Important? • MySQL uses multiple configuration files • You can easily miss one of them! • User session configuration adds complication 8
20 .Variables: Why they are Important? • MySQL uses multiple configuration files • You can easily miss one of them! • User session configuration adds complication • Variables could be changed dynamically 8
21 .Variables: Why they are Important? • MySQL uses multiple configuration files • You can easily miss one of them! • User session configuration adds complication • Variables could be changed dynamically • Runtime configuration may differ from what you expect 8
22 .Variables: Past and Future • Limitations • Session only for current user • Source of the variable is not known Configuration file Dynamic change • Performance issues 9
23 .Variables: Past and Future • Performance Schema • GLOBAL, SESSION variables 9
24 .Variables: Past and Future • Performance Schema • GLOBAL, SESSION variables • SESSION variables per user mysql> SELECT t.thread_id, t.variable_name, t.variable_value, -> g.variable_value AS global_value FROM variables_by_thread t JOIN global_variables g -> USING(variable_name) WHERE t.variable_value != g.variable_value; +-----------+------------------------+-----------------+-----------------------------------+ | thread_id | variable_name | variable_value | global_value | +-----------+------------------------+-----------------+-----------------------------------+ | 36 | character_set_database | utf8 | utf8mb4 | | 36 | collation_database | utf8_general_ci | utf8mb4_0900_ai_ci | | 36 | sql_mode | | ONLY_FULL_GROUP_BY,STRICT_TRAN... | +-----------+------------------------+-----------------+-----------------------------------+ 3 rows in set (0.03 sec) 9
25 .Variables: Past and Future • Performance Schema • GLOBAL, SESSION variables • SESSION variables per user • User-defined variables 9
26 .Variables: Past and Future • SET PERSIST (8.0+) • Dynamically set variables can be stored • Source of the variable is easily discovered mysql> SELECT VARIABLE_NAME, VARIABLE_SOURCE as SOURCE, VARIABLE_PATH, MIN_VALUE AS MIN, -> MAX_VALUE AS MAX, SET_TIME, SET_USER AS USER, SET_HOST -> FROM performance_schema.variables_info; +---------------+--------------+---------------+-----+-----+----------+------+-----------+ | VARIABLE_NAME | SOURCE | VARIABLE_PATH | MIN | MAX | SET_TIME | USER | SET_HOST | +---------------+--------------+---------------+-----+-----+----------+------+-----------+ | autocommit | COMPILED | | 0 | 0 | NULL | NULL | NULL | | datadir | EXPLICIT | /mysql/my.cnf | 0 | 0 | NULL | NULL | NULL | | log_output | COMMAND_LINE | | 0 | 0 | NULL | NULL | NULL | | sql_mode | DYNAMIC | | 0 | 0 | 2018-... | root | localhost | ... 9
27 .Privileges • SHOW PRIVILEGES mysql> show privileges; +-------------------------+--------------------------+------------------------------------------+ | Privilege | Context | Comment | +-------------------------+--------------------------+------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/pro... | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | ... 10
28 .Privileges • USER PRIVILEGES mysql> select * from user_privileges where privilege_type=’super’; +-----------------------------+---------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | +-----------------------------+---------------+----------------+--------------+ | ’mysql.session’@’localhost’ | def | SUPER | NO | | ’root’@’localhost’ | def | SUPER | YES | +-----------------------------+---------------+----------------+--------------+ 2 rows in set (0.00 sec) 10
29 .Privileges • SCHEMA PRIVILEGES mysql> select * from schema_privileges where table_schema=’website’; +-------------------+---------------+--------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE | +-------------------+---------------+--------------+----------------+--------------+ | ’web’@’localhost’ | def | website | SELECT | NO | | ’web’@’localhost’ | def | website | INSERT | NO | | ’web’@’localhost’ | def | website | UPDATE | NO | | ’web’@’localhost’ | def | website | DELETE | NO | +-------------------+---------------+--------------+----------------+--------------+ 4 rows in set (0.01 sec) 10
3秒后跳转登录页面
去登陆
