本文主要是介绍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语句已经全部执行完毕

这篇关于mysql数据库巡检的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！