CentOS Mysql远程连接

Mysql设置

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant all privileges on . to ‘root’@’%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)

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

重启mysql服务
service mysqld restart;

开放防火墙端口：

sudo vim /etc/sysconfig/iptables

# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

添加上
-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT

重新加载规则
sudo service iptables restart

存在问题

• 问题
  Win下Navicat Mysql连接，出现ERROR 2013 (HY000)。

• 解决方案
  /etc/ my.cnf 里面的 [mysqld] 段增加一个启动参数
  skip-name-resolve
  以下为修改完成的 my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

这样就连接上了

解决问题参考：

http://blog.csdn.net/jallin2001/article/details/6776943

所谓反向解析是这样的：
mysql接收到连接请求后，获得的是客户端的ip，为了更好的匹配mysql.user里的权限记录（某些是用hostname定义的）。
如果mysql服务器设置了dns服务器，并且客户端ip在dns上并没有相应的hostname，那么这个过程很慢，导致连接等待。

添加skip-name-resolve以后就跳过着一个过程了。