作为系统管理人员，当我们接手之前是其他人在维护的MySQL服务器时我们需要做什么？我个人的经验是要多看、多记、多思考，尽量避免在不熟悉服务
的情况下进行操作。那么，我们应该看哪些东西，记哪些东西呢？结合网上一些文章与自己的切身体会，总结为以下几点：
1、首先我们要进入数据库
/usr/local/mysql/bin/mysql -uroot -p
2、查看MySQL数据库的状态信息，包括平台、版本及字符集等
mysql> status
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.5.34, for Linux (x86_64) using EditLine wrapper
Connection id: 3861
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.34-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 19 min 45 sec
Threads: 48 Questions: 224662 Slow queries: 3 Opens: 4778 Flush tables: 3 Open tables: 64 Queries per second avg: 2.565
--------------
3、查看数据库的默认存储引擎以及支持哪些存储引擎
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)
4、查看数据库插件
mysql> show plugins;
+--------------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------+--------+--------------------+---------+---------+
20 rows in set (0.00 sec)
5、查看此数据库是单机环境还是集群环境
mysql> show variables like 'have_ndbcluster';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| have_ndbcluster | NO |
+-----------------+-------+
1 row in set (0.00 sec)
6、查看是否配置了主从同步
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000289
Position: 103723785
Binlog_Do_DB: ht,htgw
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.12
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000278
Read_Master_Log_Pos: 103912568
Relay_Log_File: mysql-bin.000291
Relay_Log_Pos: 3603802
Relay_Master_Log_File: mysql-bin.000278
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 103912568
Relay_Log_Space: 3603952
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: 2
1 row in set (0.00 sec)
7、查看当前Mysql的日志模式，分析慢查询日志及错误日志(分析错误日志很重要)
mysql> show variables like 'log%';
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /usr/local/mysql/error.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+---------------------------------+----------------------------+
9 rows in set (0.00 sec)
8、查看是否支持分区，如果支持哪些使用了分区表
mysql> show variables like 'have_part%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> select TABLE_NAME from information_schema.PARTITIONS where PARTITION_NAME is not null;
Empty set (0.07 sec)
9、查看拥有超级权限的用户，以及密码为空的用户
mysql> select * from information_schema.USER_PRIVILEGES where PRIVILEGE_TYPE='SUPER';
+--------------------------------+---------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------------------+---------------+----------------+--------------+
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost.localdomain' | def | SUPER | YES |
| 'root'@'%' | def | SUPER | YES |
+--------------------------------+---------------+----------------+--------------+
3 rows in set (0.00 sec)
mysql> select host,User,Password from mysql.user where Password='';
Empty set (0.00 sec)
10、查看当前MySQL有哪些触发器及存储过程
mysql> show triggers;
Empty set (0.00 sec)
mysql> show procedure status;
Empty set (0.00 sec)
11、查看MySQL并发情况
mysql> show processlist;
+------+------+---------------------+------------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------+------------+---------+-------+-------+------------------+
| 2467 | root | 192.168.1.129:50096 | NULL | Sleep | 27209 | | NULL |
| 2468 | root | 192.168.1.129:50097 | ht | Sleep | 27208 | | NULL |
| 2469 | root | 192.168.1.129:50098 | ht | Sleep | 5338 | | NULL |
| 2900 | root | 192.168.1.129:52426 | ht | Sleep | 6401 | | NULL |
| 2902 | root | 192.168.1.129:52428 | ht | Sleep | 14314 | | NULL |
| 2904 | root | 192.168.1.129:52433 | ht | Sleep | 6402 | | NULL |
| 3027 | root | 192.168.1.129:53318 | ht | Sleep | 14314 | | NULL |
| 3036 | root | 192.168.1.129:53378 | ht | Sleep | 6401 | | NULL |
| 3392 | root | 192.168.1.15:49531 | NULL | Sleep | 7614 | | NULL |
| 3393 | root | 192.168.1.15:49532 | htreal | Sleep | 7606 | | NULL |
| 3394 | root | 192.168.1.38:23152 | NULL | Sleep | 7389 | | NULL |
| 3395 | root | 192.168.1.38:23153 | ht | Sleep | 5923 | | NULL |
| 3413 | root | 192.168.1.15:49536 | ht | Sleep | 7080 | | NULL |
| 3428 | root | 192.168.1.253:57362 | NULL | Sleep | 7053 | | NULL |
| 3429 | root | 192.168.1.253:57364 | ht | Sleep | 4831 | | NULL |
| 3430 | root | 192.168.1.253:57370 | bugtracker | Sleep | 7083 | | NULL |
| 3431 | root | 192.168.1.253:57371 | art | Sleep | 7335 | | NULL |
| 3432 | root | 192.168.1.253:57376 | htgw | Sleep | 7309 | | NULL |
| 3503 | root | 192.168.1.129:54886 | ht | Sleep | 3846 | | NULL |
| 3536 | root | 192.168.1.38:24060 | test | Sleep | 6291 | | NULL |
| 3644 | root | 192.168.1.21:14029 | NULL | Sleep | 5413 | | NULL |
| 3646 | root | 192.168.1.21:14030 | ht | Sleep | 5395 | | NULL |
| 3703 | root | node136:44333 | ht | Sleep | 341 | | NULL |
| 3704 | root | node136:44334 | ht | Sleep | 2497 | | NULL |
| 3706 | root | node136:44340 | ht | Sleep | 341 | | NULL |
| 3708 | root | node136:44348 | ht | Sleep | 341 | | NULL |
| 3710 | root | node136:44351 | ht | Sleep | 44 | | NULL |
| 3712 | root | node136:44354 | ht | Sleep | 775 | | NULL |
| 3714 | root | node136:44358 | ht | Sleep | 27 | | NULL |
| 3716 | root | node136:44372 | ht | Sleep | 2661 | | NULL |
| 3718 | root | 192.168.1.253:60491 | htgw | Sleep | 4103 | | NULL |
| 3720 | root | node136:44435 | ht | Sleep | 950 | | NULL |
| 3723 | root | 192.168.1.253:60505 | bugtracker | Sleep | 4076 | | NULL |
| 3733 | root | node136:44992 | ht | Sleep | 3941 | | NULL |
| 3784 | root | node136:45286 | ht | Sleep | 3647 | | NULL |
| 3793 | root | node136:45386 | ht | Sleep | 348 | | NULL |
| 3818 | root | node136:45442 | ht | Sleep | 1790 | | NULL |
| 3838 | root | node136:45652 | ht | Sleep | 2680 | | NULL |
| 3839 | root | node136:45655 | ht | Sleep | 1758 | | NULL |
| 3859 | root | node136:45872 | ht | Sleep | 2465 | | NULL |
| 3871 | root | node136:46026 | ht | Sleep | 2348 | | NULL |
| 3874 | root | node136:46033 | ht | Sleep | 2324 | | NULL |
| 3876 | root | node136:46058 | ht | Sleep | 343 | | NULL |
| 3948 | root | node136:46523 | ht | Sleep | 249 | | NULL |
| 3952 | root | node136:46624 | ht | Sleep | 224 | | NULL |
| 3954 | root | node136:46655 | ht | Sleep | 27 | | NULL |
| 3957 | root | node136:46762 | ht | Sleep | 333 | | NULL |
| 3958 | root | node136:46764 | ht | Sleep | 332 | | NULL |
| 3959 | root | node136:46766 | ht | Sleep | 332 | | NULL |
| 3960 | root | node136:46768 | ht | Sleep | 331 | | NULL |
| 3961 | root | node136:46770 | ht | Sleep | 330 | | NULL |
| 3969 | root | node136:46773 | ht | Sleep | 285 | | NULL |
| 3970 | root | node136:46778 | ht | Sleep | 265 | | NULL |
| 3971 | root | node136:46781 | ht | Sleep | 263 | | NULL |
| 3972 | root | node136:46795 | ht | Sleep | 249 | | NULL |
| 3973 | root | node136:46800 | ht | Sleep | 224 | | NULL |
| 3986 | root | localhost | ht | Query | 0 | NULL | show processlist |
+------+------+---------------------+------------+---------+-------+-------+------------------+
57 rows in set (0.00 sec)
735
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
