|
前段时间有高人写了一篇《面对一个全新的环境,作为一个Oracle DBA,首先应该了解什么》,本文借花献佛,总结了一些思路,如何面对
一个全新的Mysql环境。
1、先要了解当前的Mysql数据库的版本和平台以及字符集等相关信息
- mysql> status
- --------------
- mysql Ver 14.14 Distrib 5.1.34, for unknown-linux-gnu (x86_64) using EditLine 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: 7 Questions: 190708290 Slow queries: 19 Opens: 57835 Flush tables: 1 Open tables: 84 Queries per second avg:
- 162.344
- --------------
复制代码
2、其次要了解你的数据库中支持哪些存储引擎,5.1的话顺便查下插件情况。
- 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、搞清楚这个环境是单机还是集群?
- mysql> show variables like 'have_ndbcluster';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | have_ndbcluster | NO |
- +-----------------+-------+
- 1 row in set (0.00 sec)
复制代码
4、是否配置了REPLICATION?
- mysql> show slave status\G;
- mysql> show master status\G;
复制代码
5、查看Mysql的日志模式,查看近期的慢查询日志和ERR日志。
- 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当前有哪些触发器和存储过程
- mysql> show triggers;
- mysql> show procedure status;
复制代码
7、是否支持分区,如果支持哪些使用了分区表
- 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密码默认为空),密码为空马上处理。
- 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,以上信息基本上对你新接触的这个系统有了一个大概的了解,接下来你再慢慢的深入分析,然后制订出一套符合实际情况的运维规范来。
当然,这只是个人的一些心得和体会,每个人的认识的角度是不一样的,欢迎大家继续补充完善。
[ 本帖最后由 shengang34 于 2009-7-8 14:56 编辑 ] |
|