解析远程连接管理其他机器上的MYSQL数据库

数据库  /  管理员 发布于 7年前   141

在开发过程中，有时候需要远程连接并管理别的机器上的MYSQL数据库，在实现的过程中会遇到一系列的问题，现在以远程访问我自己安装在Ubuntu上的MYSQL数据为例（端口为默认端口3306），说明一下配置步骤及每一步中遇到的问题及相应解决方法：
远程连接管理MYSQL，总体上来说有三步：（A为主操作机器，B为远程机器（MYSQL安装在B上，由A访问B）
1，在被连接的MYSQL中创建专门的远程连接用户wow；

2，修改被连接的MYSQL的配置文件my.cnf，使此MYSQL不仅仅支持本地IP127.0.0.1的监听，也支持其他IP的监听，并重启Mysql服务，使配置生效。

3，验证A中MYSQL的配置端口是否与B中的一致（都是3306？），如果一致，通过 mysql -h B的ip -u wow --port=3306 -p，来远程连接。

下面，就每一步详细说明一下：
一，在被连接的MYSQL中创建专门的远程连接用户wow；
之所以要创建专门的用户，是因为MYSQL在最初安装的时候，默认的root帐号以及其他的帐号是仅限于在localhost连接使用的。在远程机器上即使你用同样的用户名及密码登录，都无法正常连接。比如你在远程机器192.168.83.56上用root帐号登录192.168.11.12机器上的Mysql：
 mysql -h 192.168.11.12 -u root  -p，是无法正常登录的。此时，需要登录192.168.11.12机器，进入mysql中的mysql数据库查看user表，确认具体的root权限，特别是它的host是localhost还是你对应的IP，还是%。

解决方法：
A，（在192.168.11.12机器中的MYSQL中创建专门的远程用户root或者wow），创建用户与赋予权限有两种方法：
1）改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，把"localhost"改为"%"。“%”的意思就是所有host都可以访问。
mysql -u root -p vmware mysql>use mysql; mysql>update user set host = '%' where user = 'root';mysql>select host, user from user;
2）授权法。例如，你想wow使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'wow'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
如果你想只允许用户wow从ip为192.168.83.56的主机连接到192.168.11.12的mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'wow'@'192.168.83.56' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES
第二句表示从mysql数据库的grant表中重新加载权限数据。因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。
B，在用GRANT授权法创建完用户wow并赋予权限以后，是不是就可以用了？不可以吗？我们可以简单验证一下。先在远程机器192.168.83.56上 mysql -h 192.168.11.12 -u root  -p，是无法正常登录。报2003的错：
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.12' (111)
什么原因？是账户没有建立成功？权限设置问题，还是网络配置防火墙等的问题？还是mysql的其他配置问题没有解决？
先排除帐号与权限的问题：在localhost(192.168.11.12)机器中，尝试用wow用户进入数据库。mysql -u wow -p mypassword，报1045的错：
ERROR 1045 (28000): Access denied for user 'wow'@'localhost' (using password: YES)，
为啥呢？怎么创建的新用户在本机上都无法登录MYSQL，难道真是用户wow没有创建成功？再查看user表，wow确实已经有了啊。一顿纠结，最后找到原因，原来是安装配置mysql的过程中没有删除匿名账户，删除匿名账户：
 mysql -u root -p
 mysql>use mysql
 mysql>delete from user where User=' '；
 mysql>quit;
再尝试    mysql -u wow -p mypassword，wow用户可以在本机正常登录了，看来帐号wow是没有问题的，那再试一下远程连接：
mysql -h 192.168.11.12 -u root  -p，是无法正常登录。还报2003的错：
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.12' (111)，
查看一下errorCode来排查问题：
[mysql@vvmvcs0 ~]$ perror 111
OS error code 111: Connection refused
那难道是网络的问题？ping xxx.xxx.xxx.12，可以PING通，那机器是没问题的。看来要看看mysql的配置文件my.cnf中关于远程连接的配置了，这就进入了我们的第二步。。

2，修改被连接的MYSQL的配置文件my.cnf，使此MYSQL不仅仅支持本地IP127.0.0.1的监听，也支持其他IP的监听，并重启Mysql服务，使配置生效。
主要是两个配置项：skip_networking或者bind_address，为了取消本地监听，需要在my.cnf中注释掉关于这两项的配置：
正常情况下，mysql占用的3306端口只是在IP 127.0.0.1上监听，拒绝了其他IP的访问（通过netstat可以查看到）。取消本地监听需要修改 my.cnf 文件：
            sudo vim /etc/mysql/my.cnf
            //找到如下内容，并注释
            bind-address = 127.0.0.1
            然后需要重启 mysql （可最后再重启）。sudo mysql stop
现在我们再试一试，在远程机器192.168.83.56上用如下命令登录一下192.168.11.12的数据库：mysql -h 192.168.11.12 -u root  -p，还是无法正常登录。还是报2003的错。又是什么原因呢？哦，是mysql端口配置是否统一的问题！

3，验证A中MYSQL的配置端口是否与B中的一致（都是3306？），如果一致，通过 mysql -h B的ip -u wow --port=3306 -p，来远程连接。
192.168.83.56上的mysql监听端口配置的不是3306，是3308，而192.168.11.12的端口是默认的3306，二者不匹配，如果仅仅在192.168.83.56上用mysql -h 192.168.11.12 -u root  -p来登录的话，就是去访问192.168.11.12的3308端口啦，当然无法访问了。我们还需要指定正确的被访问mysql的端口号：mysql -h 192.168.11.12 -u wow --port=3306 -p。