面对全新的环境,一个Mysql DBA应该首先了解什么?

amwdnuff

1、先要了解当前的Mysql数据库的版本和平台以及字符集等相关信息


[Copy to clipboard] [ - ]
CODE:
mysql> status
--------------
mysqlVer 14.14 Distrib 5.1.34, for unknown-linux-gnu (x86_64) usingEditLine wrapper

Connection id:25874330
Current database:
Current user:
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.1.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:13 days 14 hours 18 min 36 sec

Threads: 7Questions: 190708290Slow queries: 19Opens: 57835Flush tables: 1Open tables: 84Queries per second avg:

162.344
--------------

2、其次要了解你的数据库中支持哪些存储引擎，5.1的话顺便查下插件情况。


[Copy to clipboard] [ - ]
CODE:
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment| Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES| YES| YES|
| MRG_MYISAM | YES | Collection of identical MyISAM tables| NO | NO | NO |
| BLACKHOLE| YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV| YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables| NO | NO | NO |
| FEDERATED| NO| Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE| YES | Archive storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL| GPL |
| partition| ACTIVE | STORAGE ENGINE | NULL| GPL |
| ARCHIVE| ACTIVE | STORAGE ENGINE | NULL| GPL |
| BLACKHOLE| ACTIVE | STORAGE ENGINE | NULL| GPL |
| CSV| ACTIVE | STORAGE ENGINE | NULL| GPL |
| FEDERATED| DISABLED | STORAGE ENGINE | NULL| GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL| GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL| GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL| GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL| GPL |
+------------+----------+----------------+---------+---------+

　

3、搞清楚这个环境是单机还是集群？


[Copy to clipboard] [ - ]
CODE:
mysql> show variables like 'have_ndbcluster';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| have_ndbcluster | NO|
+-----------------+-------+
1 row in set (0.00 sec)

4、是否配置了REPLICATION？


[Copy to clipboard] [ - ]
CODE:
mysql> show slave status\\G;
mysql> show master status\\G;

5、查看Mysql的日志模式，查看近期的慢查询日志和ERR日志。


[Copy to clipboard] [ - ]
CODE:
mysql> show variables like 'log%';
+---------------------------------+----------------------+
| Variable_name | Value|
+---------------------------------+----------------------+
| log | OFF|
| log_bin | ON |
| log_bin_trust_function_creators | OFF|
| log_bin_trust_routine_creators| OFF|
| log_error | /dir/hostname.err|
| log_output| FILE |
| log_queries_not_using_indexes | OFF|
| log_slave_updates | OFF|
| log_slow_queries| ON |
| log_warnings| 1|
+---------------------------------+----------------------+

　

6、查看Mysql当前有哪些触发器和存储过程


[Copy to clipboard] [ - ]
CODE:
mysql> show triggers;
mysql> show procedure status;

7、是否支持分区，如果支持哪些使用了分区表


[Copy to clipboard] [ - ]
CODE:
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;

8、有多少用户拥有超级权限,是否有密码为空（ROOT密码默认为空），密码为空马上处理。


[Copy to clipboard] [ - ]
CODE:
mysql> select * from information_schema.USER_PRIVILEGES where PRIVILEGE_TYPE='SUPER';
mysql> select host,User,Password from mysql.user where Password='';
+-------------+------+----------+
| host| User | Password |
+-------------+------+----------+
| localhost | root ||
| 127.0.0.1 | root ||
+-------------+------+----------+
mysql> delete from mysql.user where Password='';flush PRIVILEGES;

9.show processlist
执行一会show processlist，看看 Mysql 能有多少并发，一般都是什么sql。

10、更进一步,Mysql的备份方法和策略是什么？网络环境的配置是如何的？

11、跑几个性能分析报告，看看最近系统的运行状态如何，例如用mysqlreport。

OK，以上信息基本上对你新接触的这个系统有了一个大概的了解，接下来你再慢慢的深入分析，然后制订出一套符合实际情况的运维规范来。

当然，这只是个人的一些心得和体会,每个人的认识的角度是不一样的，欢迎大家继续补充完善。

amwdnuff

1、先要了解当前的Mysql数据库的版本和平台以及字符集等相关信息


[Copy to clipboard] [ - ]
CODE:
mysql> status
--------------
mysqlVer 14.14 Distrib 5.1.34, for unknown-linux-gnu (x86_64) usingEditLine wrapper

Connection id:25874330
Current database:
Current user:
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.1.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:13 days 14 hours 18 min 36 sec

Threads: 7Questions: 190708290Slow queries: 19Opens: 57835Flush tables: 1Open tables: 84Queries per second avg:

162.344
--------------

2、其次要了解你的数据库中支持哪些存储引擎，5.1的话顺便查下插件情况。


[Copy to clipboard] [ - ]
CODE:
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment| Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES| YES| YES|
| MRG_MYISAM | YES | Collection of identical MyISAM tables| NO | NO | NO |
| BLACKHOLE| YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV| YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables| NO | NO | NO |
| FEDERATED| NO| Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE| YES | Archive storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL| GPL |
| partition| ACTIVE | STORAGE ENGINE | NULL| GPL |
| ARCHIVE| ACTIVE | STORAGE ENGINE | NULL| GPL |
| BLACKHOLE| ACTIVE | STORAGE ENGINE | NULL| GPL |
| CSV| ACTIVE | STORAGE ENGINE | NULL| GPL |
| FEDERATED| DISABLED | STORAGE ENGINE | NULL| GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL| GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL| GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL| GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL| GPL |
+------------+----------+----------------+---------+---------+

　

3、搞清楚这个环境是单机还是集群？


[Copy to clipboard] [ - ]
CODE:
mysql> show variables like 'have_ndbcluster';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| have_ndbcluster | NO|
+-----------------+-------+
1 row in set (0.00 sec)

4、是否配置了REPLICATION？


[Copy to clipboard] [ - ]
CODE:
mysql> show slave status\\G;
mysql> show master status\\G;

5、查看Mysql的日志模式，查看近期的慢查询日志和ERR日志。


[Copy to clipboard] [ - ]
CODE:
mysql> show variables like 'log%';
+---------------------------------+----------------------+
| Variable_name | Value|
+---------------------------------+----------------------+
| log | OFF|
| log_bin | ON |
| log_bin_trust_function_creators | OFF|
| log_bin_trust_routine_creators| OFF|
| log_error | /dir/hostname.err|
| log_output| FILE |
| log_queries_not_using_indexes | OFF|
| log_slave_updates | OFF|
| log_slow_queries| ON |
| log_warnings| 1|
+---------------------------------+----------------------+

　

6、查看Mysql当前有哪些触发器和存储过程


[Copy to clipboard] [ - ]
CODE:
mysql> show triggers;
mysql> show procedure status;

7、是否支持分区，如果支持哪些使用了分区表


[Copy to clipboard] [ - ]
CODE:
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;

8、有多少用户拥有超级权限,是否有密码为空（ROOT密码默认为空），密码为空马上处理。


[Copy to clipboard] [ - ]
CODE:
mysql> select * from information_schema.USER_PRIVILEGES where PRIVILEGE_TYPE='SUPER';
mysql> select host,User,Password from mysql.user where Password='';
+-------------+------+----------+
| host| User | Password |
+-------------+------+----------+
| localhost | root ||
| 127.0.0.1 | root ||
+-------------+------+----------+
mysql> delete from mysql.user where Password='';flush PRIVILEGES;

9.show processlist
执行一会show processlist，看看 Mysql 能有多少并发，一般都是什么sql。

10、更进一步,Mysql的备份方法和策略是什么？网络环境的配置是如何的？

11、跑几个性能分析报告，看看最近系统的运行状态如何，例如用mysqlreport。

OK，以上信息基本上对你新接触的这个系统有了一个大概的了解，接下来你再慢慢的深入分析，然后制订出一套符合实际情况的运维规范来。

当然，这只是个人的一些心得和体会,每个人的认识的角度是不一样的，欢迎大家继续补充完善。

amwdnuff

1、先要了解当前的Mysql数据库的版本和平台以及字符集等相关信息


[Copy to clipboard] [ - ]
CODE:
mysql> status
--------------
mysqlVer 14.14 Distrib 5.1.34, for unknown-linux-gnu (x86_64) usingEditLine wrapper

Connection id:25874330
Current database:
Current user:
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.1.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:13 days 14 hours 18 min 36 sec

Threads: 7Questions: 190708290Slow queries: 19Opens: 57835Flush tables: 1Open tables: 84Queries per second avg:

162.344
--------------

2、其次要了解你的数据库中支持哪些存储引擎，5.1的话顺便查下插件情况。


[Copy to clipboard] [ - ]
CODE:
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment| Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES| YES| YES|
| MRG_MYISAM | YES | Collection of identical MyISAM tables| NO | NO | NO |
| BLACKHOLE| YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV| YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables| NO | NO | NO |
| FEDERATED| NO| Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE| YES | Archive storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL| GPL |
| partition| ACTIVE | STORAGE ENGINE | NULL| GPL |
| ARCHIVE| ACTIVE | STORAGE ENGINE | NULL| GPL |
| BLACKHOLE| ACTIVE | STORAGE ENGINE | NULL| GPL |
| CSV| ACTIVE | STORAGE ENGINE | NULL| GPL |
| FEDERATED| DISABLED | STORAGE ENGINE | NULL| GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL| GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL| GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL| GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL| GPL |
+------------+----------+----------------+---------+---------+

　

3、搞清楚这个环境是单机还是集群？


[Copy to clipboard] [ - ]
CODE:
mysql> show variables like 'have_ndbcluster';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| have_ndbcluster | NO|
+-----------------+-------+
1 row in set (0.00 sec)

4、是否配置了REPLICATION？


[Copy to clipboard] [ - ]
CODE:
mysql> show slave status\\G;
mysql> show master status\\G;

5、查看Mysql的日志模式，查看近期的慢查询日志和ERR日志。


[Copy to clipboard] [ - ]
CODE:
mysql> show variables like 'log%';
+---------------------------------+----------------------+
| Variable_name | Value|
+---------------------------------+----------------------+
| log | OFF|
| log_bin | ON |
| log_bin_trust_function_creators | OFF|
| log_bin_trust_routine_creators| OFF|
| log_error | /dir/hostname.err|
| log_output| FILE |
| log_queries_not_using_indexes | OFF|
| log_slave_updates | OFF|
| log_slow_queries| ON |
| log_warnings| 1|
+---------------------------------+----------------------+

　

6、查看Mysql当前有哪些触发器和存储过程


[Copy to clipboard] [ - ]
CODE:
mysql> show triggers;
mysql> show procedure status;

7、是否支持分区，如果支持哪些使用了分区表


[Copy to clipboard] [ - ]
CODE:
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;

8、有多少用户拥有超级权限,是否有密码为空（ROOT密码默认为空），密码为空马上处理。


[Copy to clipboard] [ - ]
CODE:
mysql> select * from information_schema.USER_PRIVILEGES where PRIVILEGE_TYPE='SUPER';
mysql> select host,User,Password from mysql.user where Password='';
+-------------+------+----------+
| host| User | Password |
+-------------+------+----------+
| localhost | root ||
| 127.0.0.1 | root ||
+-------------+------+----------+
mysql> delete from mysql.user where Password='';flush PRIVILEGES;

9.show processlist
执行一会show processlist，看看 Mysql 能有多少并发，一般都是什么sql。

10、更进一步,Mysql的备份方法和策略是什么？网络环境的配置是如何的？

11、跑几个性能分析报告，看看最近系统的运行状态如何，例如用mysqlreport。

OK，以上信息基本上对你新接触的这个系统有了一个大概的了解，接下来你再慢慢的深入分析，然后制订出一套符合实际情况的运维规范来。

当然，这只是个人的一些心得和体会,每个人的认识的角度是不一样的，欢迎大家继续补充完善。