MySQL怎么去巡检

80 阅读 0 评论 53 点赞

我是靠谱客的博主笨笨小蘑菇，最近开发中收集的这篇文章主要介绍MySQL怎么去巡检，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、操作系统巡检
(CPU、内存、磁盘、I/O等)略
二、Mysql巡检
1.1 基础信息
登陆mysql已经可以看到版本信息
[root@cqpcfwq ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 73
Server version: 5.7.17-11 Percona Server (GPL), Release 11, Revision f60191c

也可以通过命令查看版本信息
mysql> show variables like '%version%';
+-------------------------+----------------------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------------------+
| innodb_version | 5.7.17-11 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.17-11 |
| version_comment | Percona Server (GPL), Release 11, Revision f60191c |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+----------------------------------------------------+
8 rows in set (0.00 sec)
查看基本目录信息
mysql> show variables like '%dir%';
+-----------------------------------------+-----------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------------+
| basedir | /mysql/Percona/Server/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /mysql/Percona/Server/share/charsets/ |
| datadir | /mysql/Percona/data/
查看默认存储引擎
mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

1.2 Mysql状态信息
查看所有状态
mysql> show global status
查看线程状态
mysql> show global status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 4 |
| Threads_connected | 1 |
| Threads_created | 6 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)

查看lock状态
mysql> show global status like '%_lock%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| Com_lock_tables | 0 |
| Com_lock_tables_for_backup | 0 |
| Com_lock_binlog_for_backup | 0 |
| Handler_external_lock | 14 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_metadata_lock_lost | 0 |
| Performance_schema_table_lock_stat_lost | 0 |
| Table_locks_immediate | 255 |
| Table_locks_waited | 0 |
+-----------------------------------------+-------+

查看wait状态
mysql> show global status like '%wait%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_log_waits | 0 |
查看临时表/文件状态
mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 7 |
+-------------------------+-------+
3 rows in set (0.01 sec)

查看索引使用状态
mysql> show global status like 'Handl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 14 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 7784 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 3885 |
+----------------------------+-------+
18 rows in set (0.00 sec)

等等……

1.3查看连接信息
mysql> show full processlist;
+----+-------------+-----------+------+---------+---------+--------------------------------------------------------+-----------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-------------+-----------+------+---------+---------+--------------------------------------------------------+-----------------------+-----------+---------------+
| 8 | system user | | NULL | Connect | 2946128 | Waiting for master to send event | NULL | 0 | 0 |
| 9 | system user | | NULL | Connect | 1122097 | Slave has read all relay log; waiting for more updates | NULL | 0 | 0 |
| 73 | root | localhost | NULL | Query | 0 | starting | show full processlist | 0 | 0 |
+----+-------------+-----------+------+---------+---------+--------------------------------------------------------+-----------------------+-----------+---------------+
3 rows in set (0.00 sec)

1.4查看innodb的状态信息
show engine innodb statusG (show innodb statusG)
mysql> show engine innodb statusG
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-02-07 16:48:20 0x7f3394dfe700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 14 srv_active, 0 srv_shutdown, 2946021 srv_idle
srv_master_thread log flush and writes: 2946035
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 14
OS WAIT ARRAY INFO: signal count 13
RW-shared spins 0, rounds 11, OS waits 5
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 11.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 4428
Purge done for trx's n:o < 4366 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421335037643104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421335037644288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
309 OS file reads, 109 OS file writes, 21 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2558334
Log flushed up to 2558334
Pages flushed up to 2558334
Last checkpoint at 2558325
Max checkpoint age 80826164
Checkpoint age target 78300347
Modified age 0
Checkpoint age 9
0 pending log flushes, 0 pending chkp writes
18 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 139722752
Dictionary memory allocated 365072
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2252736 (2219072 + 33664)
Page hash 139112 (buffer pool 0 only)
Dictionary cache 919840 (554768 + 365072)
File system 832208 (812272 + 19936)
Lock system 335144 (332872 + 2272)
Recovery system 0 (0 + 0)
Buffer pool size 8191
Buffer pool size, bytes 0
Free buffers 7885
Database pages 306
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 271, created 35, written 79
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 306, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
Process ID=18813, Main thread ID=139859689727744, state: sleeping
Number of rows inserted 202, updated 0, deleted 0, read 384
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

1.5查看Mysql错误日志
查看错误日志位置
mysql> show variables like 'log_error';
+---------------+--------------------------------+
| Variable_name | Value |
+---------------+--------------------------------+
| log_error | /mysql/Percona/data/mysqld.log |
+---------------+--------------------------------+
1 row in set (0.01 sec)
[root@cqpcfwq data]# tail -10f mysqld.log
2018-02-05T08:50:47.144885Z 61 [Note] Access denied for user 'hhj'@'localhost' (using password: YES)
2018-02-05T08:50:51.088905Z 62 [Note] Access denied for user 'hhj'@'localhost' (using password: YES)
2018-02-05T08:57:48.546781Z 65 [Warning] IP address '192.168.106.42' could not be resolved: Temporary failure in name resolution
2018-02-05T08:58:10.864132Z 66 [Warning] IP address '192.168.106.42' could not be resolved: Temporary failure in name resolution
2018-02-05T09:07:32.428302Z 67 [Warning] IP address '192.168.106.42' could not be resolved: Temporary failure in name resolution
2018-02-05T09:14:15.836269Z 68 [Warning] IP address '192.168.106.43' could not be resolved: Temporary failure in name resolution
2018-02-05T16:57:06.266684Z 64 [Note] Aborted connection 64 to db: 'unconnected' user: 'hhj' host: 'localhost' (Got timeout reading communication packets)
2018-02-06T02:06:17.165635Z 69 [Note] Access denied for user 'cqgds'@'localhost' (using password: YES)
2018-02-06T07:35:22.303101Z 70 [Warning] IP address '192.168.106.46' could not be resolved: Temporary failure in name resolution
2018-02-06T07:38:36.149218Z 71 [Warning] IP address '192.168.106.43' could not be resolved: Temporary failure in name resolution

1.6 查看是否有备份

1.7 如果有主从复制，到从数据库查看复制状态
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.106.91
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 578107368
Relay_Log_File: cqpcfwq-relay-bin.000031
Relay_Log_Pos: 578106926
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: test.t1
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 578107368
Relay_Log_Space: 578107582
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9aec1370-145b-11e7-9f3a-286ed488c681
Master_Info_File: /mysql/Percona/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)