mysql数据库多实例环境root用户登录
Mar242016
书接上文，本文主要演示root用户登录mysql数据库的问题，本实验环境还是之前搭建的mysql数据库，同一台主机上同时启动两个mysql实例，端口分别为3306和3307。
首先说一下在服务器上连接本地mysql数据库时，IP地址和端口号的关系，如果不 指定IP地址，只指定端口号，那么端口号将被忽略，默认连接3306也就是通过rpm包安装是初始化的数据库，无论指定的端口号是哪个是否有效。
[root@mysql-1 3307]# mysql -uroot -p -P3307 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 Server version: 5.6.21 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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>
虽然在连接数据库的时候指定了端口是3307，但是localhost方式访问数据库使用的是socket登录，在不指定IP地址也就是TCP的方式访问数据库时，端口参数将会被忽略，此时登录的是3306端口的数据库。
mysql> show variables like 'port'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | port | 3306 | +---------------------+-------+ 1 rows in set (0.00 sec)
接下来测试使用错误的端口来登录数据库，并不会报错，登录的数据库仍然是3306端口的数据库。
[root@mysql-1 3307]# mysql -uroot -p -P335345 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@mysql-1 3307]# mysql -uroot -p -P335345 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 44 Server version: 5.6.21 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> show variables like 'port'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | port | 3306 | +---------------------+-------+ 1 rows in set (0.00 sec)
以上部分的实验，验证了localhost方式登录数据库，指定端口参数是无效的。接下来测试使用主机名的方式登录数据库，这属于TCP的方式登录数据库。
[mysql@mysql-1 ~]$ mysql -uroot -p -P3307 -hmysql-1 Enter password: ERROR 1130 (HY000): Host '10.9.15.201' is not allowed to connect to this MySQL server
这个错误的意思是说，数据库的权限表中，没有10.9.15.201IP的记录，也就是在使用主机名的方式登录数据库时，会将主机名解析成IP，只有有这个IP的登录权限时，才可以登录数据库。再将主机名改为IP地址登录，将会遇到同样的错误，因为数据库的权限表里并没有这个IP对于的记录。
[mysql@mysql-1 ~]$ mysql -uroot -p -P3307 -h10.9.15.201 Enter password: ERROR 1130 (HY000): Host '10.9.15.201' is not allowed to connect to this MySQL server
下面通过127.0.0.1这个IP地址登录数据库，数据库中查看下权限表都有哪些记录，在初始化数据库时，默认是有127.0.0.1这个IP地址对于的权限记录的，可以通过这个IP访问数据库。
[mysql@mysql-1 ~]$ mysql -uroot -p -P3307 -h127.0.0.1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> select host,user,password from mysql.user; +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | mysql-1 | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | mysql-1 | | | +-----------+------+----------+ 6 rows in set (0.00 sec)
可见权限列表里并没有服务器IP的权限信息，添加这个IP地址的权限信息。
mysql> grant all on *.* to root@10.9.15.201; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password from mysql.user; +------+-------------+----------+ | user | host | password | +------+-------------+----------+ | root | localhost | | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | root | 10.9.15.201 | | +------+-------------+----------+ 5 rows in set (0.00 sec)
此时即可使用主机名或者IP地址的方式来访问数据库了。
[mysql@mysql-1 ~]$ mysql -uroot -p -P3307 -hmysql-1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.6.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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>
下面在看下使用socket的方式登录数据库，如果不在服务器本地不指定IP和端口，默认使用的就是这种方式，如果服务器上只有一个mysql数据库，而且my.cnf文件中也指定了socket的正确地址，那么不指定socket也可以正常登录数据库，但是如果一台服务器上存在多个mysql数据库，为了区分要登录的是哪个数据库，如果使用localhost的方式登录数据库，就必须指定socket的信息了。
在服务区本地通过localhost的方式登录mysql时，如果不指定IP和端口信息，会连接到默认的数据库，本案例中是3306端口的这个数据库。
[mysql@mysql-1 ~]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.6.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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>
通过\s命令可以看到当前的登录方式。
mysql> \s -------------- mysql Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper Connection id: 44 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.21 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 9 days 4 hours 20 min 46 sec Threads: 1 Questions: 155 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.000 --------------
可以看到，当前是使用socket登录的数据库，而这个socket正式3306这个端口的数据库的socket。
mysql> show variables like 'port'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | port | 3306 | +---------------------+-------+ 1 rows in set (0.00 sec)
通过show命令看到，登录的正是3306端口的这个数据库。那么如果想通过localhost的方式连接3307端口的数据库，就必须指定scoket啦。
[mysql@mysql-1 ~]$ mysql -uroot -p -S /mysql/data/3307/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.6.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> \s -------------- mysql Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper Connection id: 23 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.21-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/data/3307/mysql.sock Uptime: 6 days 16 min 0 sec Threads: 2 Questions: 130 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.000 --------------
由于在连接是明确指定使用3307的socket登录，所以连接的是3307端口的数据库。
mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3307 | +---------------+-------+ 1 row in set (0.01 sec)
至于默认连哪个数据库，和my.cnf文件有关系，因为不同的数据库使用不同的my.cnf文件，服务器先找到哪个数据库的my.cnf文件，就先连接哪个数据库。因为3306端口的数据库是在rpm包安装mysql时自动创建的数据库，在/usr目录下面自动创建了my.cnf文件，而3307端口的数据库，是通过mysql_install_db脚本创建，在创建时也需要在/usr目录下创建my.cnf文件，但是文件已存在，所以创建了my-new.cnf文件。而/etc的优先级比/usr要高，mysql会优先去/etc目录下去找my.cnf文件，下面把3307数据库的my.cnf文件拷贝到/etc目录再试试默认连哪个数据库。
[root@mysql-1 3307]# cp my.cnf /etc/ [root@mysql-1 3307]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.6.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> \s -------------- mysql Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper Connection id: 24 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.21-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/data/3307/mysql.sock Uptime: 6 days 1 hour 18 min 21 sec Threads: 2 Questions: 139 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.000 --------------
可见在把3307端口的数据库的my.cnf文件拷贝到/etc目录，默认登录的数据库就变成了3307端口的数据库了，因为/etc目录的优先级比/usr要高，所以mysql先找到了3307端口数据库的my.cnf文件，就连到了3307端口的数据库。
mysql数据库多实例环境root用户登录：等您坐沙发呢！
发表评论
