mysql数据库巡检
1. 查看数据库版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.38-log |
+------------+
1 row in set (9.99 sec)
2. 数据库端口
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 11110 |
+---------------+-------+
1 row in set (0.63 sec)
3. 查询数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | |
| mdmdatafix |
| mdmquery |
| mysql |
| percona |
| performance_schema | |
| sys |
+--------------------+
9 rows in set (0.07 sec)
4. 数据库用户信息
mysql> use mysql;
Database changed
mysql> select user,host from user;
+-----------------+--------------+
| user | host |
+-----------------+--------------+
| mdmdatafixer | 10% |
| aiuap | 10.% |
+-----------------+--------------+
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query |
+---------------------------------------+
| User: 'mdmdatafixer'@'10%'; |
| User: 'aiuap'@'10.%'; |
+-----------------+--------------+
5. 数据库字符集
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.12 sec)
6. 数据库的安装目录
mysql> select @@basedir as basePath from dual ;
+-------------------+
| basePath |
+-------------------+
| /usr/local/mysql/ |
+-------------------+
1 row in set (0.10 sec)
mysql> show variables like '%basedir%';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| basedir | /usr/local/mysql/ |
+---------------+-------------------+
1 row in set (6.20 sec)
7. 数据文件目录
mysql> select @@datadir as dataPath from dual ;
+------------------------------+
| dataPath |
+------------------------------+
| /data/mysqldata11110/mydata/ |
+------------------------------+
1 row in set (0.72 sec)
mysql> show variables Like '%datadir%';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| datadir | /data/mysqldata11110/mydata/ |
+---------------+------------------------------+
1 row in set (0.19 sec)
8. 各个schema的总大小，表大小，索引大小，表个数
mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;
+--------------------+-----------------+-----------------+----------------+--------+------------+
| table_schema | total_mb | data_mb | index_mb | tables | today |
+--------------------+-----------------+-----------------+----------------+--------+------------+
| mdm_rector_db | 357219.06250000 | 287944.84375000 | 69274.21875000 | 507 | 2022-09-14 |
| mdmdatax | 1562.85937500 | 1413.70312500 | 149.15625000 | 374 | 2022-09-14 |
| mdmqry | 1153.15625000 | 441.60937500 | 711.54687500 | 17 | 2022-09-14 |
| percona | 27.35937500 | 16.73437500 | 10.62500000 | 3 | 2022-09-14 |
| mysql | 8.56507683 | 8.36195183 | 0.20312500 | 31 | 2022-09-14 |
| inforn_schema | 0.15625000 | 0.15625000 | 0.00000000 | 61 | 2022-09-14 |
| quewrite | 0.01562500 | 0.01562500 | 0.00000000 | 1 | 2022-09-14 |
| sys | 0.01562500 | 0.01562500 | 0.00000000 | 101 | 2022-09-14 |
| perfe_schema | 0.00000000 | 0.00000000 | 0.00000000 | 87 | 2022-09-14 |
+--------------------+-----------------+-----------------+----------------+--------+------------+
9 rows in set (0.59 sec)
9. 查询Innodb引擎的状态
show engine innodb status;
10. 查询当前活跃的进程状态
mysql> show processlist;
+--------+-------------+-------------------+-----------------+------------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+-------------------+-----------------+------------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 12 | tidb-repl | 10.24.84.64:60656 | NULL | Binlog Dump | 1853588 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 28 | tidb-repl | 10.24.84.54:52034 | NULL | Sleep | 15 | | NULL |
| 29 | tidb-repl | 10.24.84.54:52040 | NULL | Binlog Dump | 1853445 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 45 | system user | | NULL | Connect | 1853292 | Waiting for master to send event | NULL |
| 46 | system user | | NULL | Connect | 0 | Slave has read all relay log; waiting for more updates | NULL |
| 47 | system user | | NULL | Connect | 1853102 | Waiting for an event from Coordinator
+--------+-------------+-------------------+-----------------+------------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
212 rows in set (1.05 sec
11. 查看Threads使用情况
mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 459 |
| Threads_connected | 202 |
| Threads_created | 661 |
| Threads_running | 9 |
+-------------------+-------+
4 rows in set (0.44 sec)
12. Thread_cache命中率
1 - Threads_created / connections x 100%
mysql> show global status like 'connections';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Connections | 530595 |
+---------------+--------+
1 row in set (0.28 sec)
13. 查看QPS-TPS情况
QPS= Questions/Uptime
mysql> show global status like 'questions';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Questions | 669045249 |
+---------------+-----------+
1 row in set (0.14 sec)
mysql> show global status like 'Uptime';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Uptime | 1853843 |
+---------------+---------+
1 row in set (0.14 sec)
TPS=(com_commit+com_rollback) /Uptime
mysql> show status like 'com_commit'; #（不会记录隐式提交的事务）
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit | 0 |
+---------------+-------+
1 row in set (2.35 sec)
mysql> show status like 'com_rollback' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback | 0 |
+---------------+-------+
1 row in set (0.14 sec)
mysql> show global status like 'Uptime';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Uptime | 1853906 |
+---------------+---------+
1 row in set (0.13 sec)
14. 查看DML per second
记录每一次的语句，只记录隐式提交的数据，如 autocommit=1
mysql> SHOW GLOBAL STATUS WHERE variable_name IN
('Com_insert','Com_delete','Com_select','Com_update');
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_delete | 479687 |
| Com_insert | 48271869 |
| Com_select | 321116852 |
| Com_update | 48617442 |
+---------------+-----------+
4 rows in set (0.17 sec)
15. 流量监控
mysql> show status like 'bytes%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| Bytes_received | 2250 |
| Bytes_sent | 109191 |
+----------------+--------+
2 rows in set (0.28 sec)
16. 查看索引使用情况
mysql> show status like 'handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 36 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 2 |
| Handler_read_key | 1190 |
| Handler_read_last | 0 |
| Handler_read_next | 168 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9 |
| Handler_read_rnd_next | 9649 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1173 |
| Handler_write | 5465 |
+----------------------------+-------+
18 rows in set (0.14 sec)
17. 查看执行全表扫描的数量*
mysql> show status like 'select_scan';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Select_scan | 23 |
+---------------+-------+
1 row in set (0.71 sec)
18. 查看慢查询情况
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 row in set (0.22 sec)
19. 查看表缓存情况
mysql> show global status like 'open%_tables';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Open_tables | 2048 |
| Opened_tables | 8303605 |
+---------------+---------+
2 rows in set (0.21 sec)
20. 查看锁使用情况
+------------------------------------------+---------+
| Variable_name | Value |
+------------------------------------------+---------+
| Com_lock_tables | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 36 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 6739896 |
| Innodb_row_lock_time_avg | 359 |
| Innodb_row_lock_time_max | 21872 |
| Innodb_row_lock_waits | 18773 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 428684 |
| Key_blocks_used | 13 |
| Locked_connects | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_metadata_lock_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_lock_stat_lost | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Table_locks_immediate | 146157 |
| Table_locks_waited | 0 |
+------------------------------------------+---------+
21 rows in set (0.20 sec)
21. Binlog cache使用情况
mysql> show status like 'binlog_cache%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Binlog_cache_disk_use | 2414 |
| Binlog_cache_use | 11097113 |
+-----------------------+----------+
2 rows in set (0.68 sec)
22. 查看wait事件
mysql> show status like 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
+------------------------------+-------+
1 row in set (0.10 sec)
23. 检查错误日志
mysql> select @@global.log_error;
+------------------------------------------+
| @@global.log_error |
+------------------------------------------+
| /data/mysqldata11110/error_log/error.log |
+------------------------------------------+
1 row in set (0.88 sec)
24. 查看主从同步状态
show slave status \G;
如何判断主从完全同步
Master_Log_File和Relay_Master_Log_File所指向的文件必须一致
Relay_Log_Pos和Exec_Master_Log_Pos的为止也要一致才行
Slave_SQL_Running_State：显示为wait 意思是中继日志的sql语句已经全部执行完毕
