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 status\G  (show innodb status\G)
mysql> show engine innodb status\G
*************************** 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)