centos中mysql远程连接失败解决方案

来源：互联网发布：最优化方法孙文瑜答案编辑：程序博客网时间：2024/05/17 06:39

#######-------说明：查看所有linux版本的信息

[root@homename etc]# lsb_release -a
LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS release 6.5 (Final)
Release: 6.5
Codename: Final

#######-------说明：查看服务器是否安装了mysql

[root@homename etc]# rpm -qa | grep mysql
qt-mysql-4.6.2-26.el6_4.x86_64
mysql-libs-5.1.71-1.el6.x86_64
mysql-5.1.71-1.el6.x86_64
mysql-server-5.1.71-1.el6.x86_64

#######-------说明：查看mysql服务是否启动正常
[root@homename etc]# service mysqld status
mysqld (pid 10053) 正在运行...

#######-------说明：进入mysql
[root@homename etc]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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> grant all privileges on *.* to 'root'@'192.168.3.114' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@homename etc]# vi /etc/sysconfig/iptables
# Generated by iptables-save v1.4.7 on Sat Jun 6 13:48:25 2015
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [6400198:1533000000]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 9080 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 9000 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT
# mysql start
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
# mysql stop
# add cluster start
-A INPUT -p tcp -m state --state NEW -m tcp --dport 4000 -j ACCEPT
-A OUTPUT -p tcp -m state --state NEW -m tcp --sport 4000 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 4001 -j ACCEPT
-A OUTPUT -p tcp -m state --state NEW -m tcp --sport 4001 -j ACCEPT
-A INPUT -p udp -m state --state NEW -m udp --dport 45564 -j ACCEPT
-A OUTPUT -p udp -m state --state NEW -m udp --sport 45564 -j ACCEPT
# add cluster end
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Sat Jun 6 13:48:25 2015
~
~
~
~
~

[root@homename etc]# service iptables restart
iptables：将链设置为政策 ACCEPT：filter [确定]
iptables：清除防火墙规则： [确定]
iptables：正在卸载模块： [确定]
iptables：应用防火墙规则： [确定]

----------------------------------------------------------------------------------------------------------------------------
在mysql控制台下修改权限

[html] view plaincopy
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;  

[html] view plaincopy
//root 是用户名，% 表示任意主机，'123456' 指定的登录密码（这个和本地的root密码可以设置不同，互不影响）  

[html] view plaincopy
flush privileges; //重载系统权限  

[html] view plaincopy
exit;//退出mysql控制台  

2.CentOS环境开放3306端口

添加规则，打开3306端口

[html] view plaincopy
iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT  

查看规则是否生效

[html] view plaincopy
iptables -L -n  //或者 service iptables status  

-------------------------------------------------------------------------------------------------

参考文档：

《在CentOS环境下mysql如何远程连接》

《Linux学习之CentOS(十三)--CentOS6.4下Mysql数据库的安装与配置》

0 0