Linux学习网!Linux爱好者的家园~凡事都是必须经过枯燥无味阶段才能做到较高层次的。
首页 新闻 Linux命令 学习入门 Ubuntu 服务配置 数据库 Linux发行版 视频教程 Linux下载 Linux论坛 留言本

TOP

了解mysql数据库的配置
2010-11-04 09:35:03 来源:Linux学习网 】 浏览:14次 评论:0

一个全新的

1、先要了解当前的Mysql数据库的版本和平台以及字符集等相关信息[ - ]
CODE: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的话顺便查下插件情况。

[ - ]
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、搞清楚这个环境是单机还是集群?

[ - ]
CODE:mysql> show variables like 'have_ndbcluster';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| have_ndbcluster | NO    |
+-----------------+-------+
1 row in set (0.00 sec)
4、是否配置了REPLICATION?

[ - ]
CODE:mysql> show slave status\G;
mysql> show master status\G;
5、查看Mysql的日志模式,查看近期的慢查询日志和ERR日志。

[ - ]
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当前有哪些触发器和存储过程

[ - ]
CODE:mysql> show triggers;
mysql> show procedure status;
7、是否支持分区,如果支持哪些使用了分区表

[ - ]
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密码默认为空),密码为空马上处理。

[ - ]
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。

分享到:
你可能感兴趣的文章:
Tags:mysql数据库 mysql数据库的配置
】 【打印繁体】【投稿】 【评论】 【关闭】 【返回顶部
上一篇centos下如何安装mysql数据库 下一篇linux下如何卸载oracle

推荐图文

赞助商

相关栏目

最新文章

热门文章

推荐文章