一、操作系统巡检
(CPU、内存、磁盘、I/O等)略
二、Mysql巡检
1.1 基础信息
登陆mysql已经可以看到版本信息
[[email protected] ~]# 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)
[[email protected] 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)
![数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)