上篇中我们进行了初步的安装配置,本篇继续进行问题解决。
7、Warning警告分析
在实验mysqld启动的时候,我们看到了一条Warning告警信息。
[mysql@bspdev mysql-5.5.25-linux2.6-i686]$ mysqld &
[1] 28398
[mysql@bspdev mysql-5.5.25-linux2.6-i686]$ 1207014:21:42 [Warning] Changed limits: max_open_files: 1024max_connections: 151table_cache: 431
1207014:21:42 [Note] Plugin 'FEDERATED' is disabled.
虽然告警信息不是什么重要问题,但是还是需要稍微关注一下。
从提示内容上看,似乎是关于用户连接数、同时打开文件数目和缓存的一些内容,存在限制。这让我们想起了Oracle在安装时,进行的操作系统用户限制开启过程。查阅资料后,只需要将mysql用户的限制开启就可以了。
[mysql@bspdev mysql-5.5.25-linux2.6-i686]$ ulimit -Sa | grep "open files"
open files(-n) 1024
[mysql@bspdev mysql-5.5.25-linux2.6-i686]$ ulimit -Ha | grep "open files"
open files(-n) 1024
[mysql@bspdev mysql-5.5.25-linux2.6-i686]$ su - root
Password:
--修改/etc/security/limits.conf文件内容;
[root@bspdev ~]# vi /etc/security/limits.conf
# /etc/security/limits.conf
#
#*softcore0
#*hardrss10000
#@studenthardnproc20
#@facultysoftnproc20
#@facultyhardnproc50
#ftphardnproc0
#@student-maxlogins4
#添加内容;
mysql hard nofile 8192
mysql soft nofile 1200
再尝试开启服务器,warning信息消失。
[root@bspdev ~]# su - mysql
[mysql@bspdev ~]$ mysqld &
[1] 16285
[mysql@bspdev ~]$ 1207014:26:21 [Note] Plugin 'FEDERATED' is disabled.
1207014:26:21 InnoDB: The InnoDB memory heap is disabled
1207014:26:21 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
1207014:26:21 InnoDB: Compressed tables use zlib 1.2.3
120701 4:26:21 InnoDB: Using Linux native AIO
(篇幅原因,有省略……)
[mysql@bspdev ~]$ ps -ef | grep mysqld
mysql16285 162571 04:26 pts/000:00:00 mysqld
mysql16304 162570 04:26 pts/000:00:00 grep mysqld
8、用户密码问题
本地连接成功之后,笔者尝试使用远程连接。结果发现linux版本的一些安全限制。
--从远程机器连接;
C:\Users\51ibm>mysql -h 192.168.137.89 -u root
ERROR 1130 (HY000): Host '192.168.137.1' is not allowed to connect to this MySQL
server
此时,MySQL服务器端日志记录错误信息。
[Warning] IP address '192.168.137.1' could not be resolved: Temporary failure in name resolution
从信息上看,应该是登录存在一些限制内容。
[mysql@bspdev ~]$ mysql -u root
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Database changed
mysql> select host, name, password from user;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
mysql> select host, user, password from user;
+--------------------+------+----------+
| host| user | password |
+--------------------+------+----------+
| localhost| root ||
| bspdev.localdomain | root ||
| 127.0.0.1| root ||
| ::1| root ||
| localhost|||
| bspdev.localdomain |||
+--------------------+------+----------+
6 rows in set (0.00 sec)
mysql>
注意,在权限表中,没有针对其他ip登陆的许可权限。说明Linux版本默认情况下,允许本地登陆,没有密码要求。但是不允许用户从其他客户端进行登陆。
我们需要人工的加以授权。
mysql> grant all on *.* to root@'%' identified by 'root' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, password from user;
+--------------------+------+-------------------------------------------+
| host| user | password|
+--------------------+------+-------------------------------------------+
| localhost| root ||
| bspdev.localdomain | root ||
| 127.0.0.1| root ||
| ::1| root ||
| localhost|||
| bspdev.localdomain |||
| %| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+--------------------+------+-------------------------------------------+
7 rows in set (0.00 sec)
此时,我们可以从非服务器进行登陆,而且必须输入密码。
--不输入用户密码登录,被拒绝;
C:\Users\51ibm>mysql -h 192.168.137.89 -u root
ERROR 1045 (28000): Access denied for user 'root'@'192.168.137.1' (using passwor
d: NO)
--输入root用户密码,通过;
C:\Users\51ibm>mysql -h 192.168.137.89 -u root -proot
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
额外说明,从那张user表中,我们可以构建这样的权限体系。相同的用户名,从不同的机器上登陆时使用不同的密码。
9、Linux服务化
在Windows中,我们是将MySQL作为一项服务,添加在服务列表中进行启动关闭管理。在Linux中,我们也可以使用相同的配置内容。
--首先关闭mysqld服务
[mysql@bspdev ~]$ ps -ef | grep mysqld
mysql1628510 04:26 ?00:00:00 mysqld
mysql16475 164420 04:37 pts/200:00:00 grep mysqld
[mysql@bspdev ~]$ mysqladmin -u root shutdown
[mysql@bspdev ~]$ ps -ef | grep mysqld
mysql16481 164420 04:38 pts/200:00:00 grep mysqld
[mysql@bspdev ~]$
将mysqld作为服务添加到列表中。
[root@bspdev ~]# cd /mysql/mysql-5.5.25-linux2.6-i686/support-files/
[root@bspdev support-files]# cp mysql.server /etc/rc.d/init.d/mysqld
[root@bspdev support-files]# chmod 700 /etc/init.d/mysqld
[root@bspdev support-files]# chkconfig --add mysqld
[root@bspdev support-files]# chkconfig --level 345 mysqld on
之后,我们就可以通过service mysqld start来启动MySQL数据库了。
[root@bspdev support-files]# service mysqld start
Starting MySQL...[OK]
[root@bspdev support-files]# mysql -u root
-bash: mysql: command not found
[root@bspdev support-files]# su - mysql
[mysql@bspdev ~]$ mysql -u root
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
对service命令,我们还可以使用如status、stop等命令。
[root@bspdev ~]# service mysqld status
MySQL running (16839)[OK]
[root@bspdev ~]# service mysqld stop
Shutting down MySQL.[OK]
[root@bspdev ~]# ps -ef | grep mysqld
root16988 169020 04:44 pts/200:00:00 grep mysqld
[root@bspdev ~]#
10、结论
作为目前最流行的开源数据库产品,MySQL是比较成功的。学习安装MySQL,只是学习的一个开始入手点。之后,还有很多的问题需要解决和学习,需要不断的坚持和努力才能完成。