当前位置: 首页 > Mysql > 正文

mysql数据库多实例环境root用户登录

书接上文,本文主要演示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端口的数据库。

本文固定链接: http://www.dbdream.com.cn/2016/03/mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%a4%9a%e5%ae%9e%e4%be%8b%e7%8e%af%e5%a2%83root%e7%94%a8%e6%88%b7%e7%99%bb%e5%bd%95/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2016年03月24日发表在 Mysql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: mysql数据库多实例环境root用户登录 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

mysql数据库多实例环境root用户登录:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter