Server 允許遠端存取

1. mysql 啟動後會監聽在 3306 port。
   

   [root@kvm6 ~]# netstat -tlunp | grep mysql
   tcp        0      0 0.0.0.0:3306         0.0.0.0:*    LISTEN      2712/mysqld
   

2. 允許遠端存取，防火牆必須開啟 3306 port，CentOS 6 防火牆使用 iptables，查看目前的防火牆，3306 port 沒開啟。
   

   [root@kvm6 ~]# iptables-save
   # Generated by iptables-save v1.4.7 on Fri Mar 15 19:27:03 2019
   *filter
   :INPUT ACCEPT [0:0]
   :FORWARD ACCEPT [0:0]
   :OUTPUT ACCEPT [12762:1492182]
   -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 -j REJECT --reject-with icmp-host-prohibited 
   -A FORWARD -j REJECT --reject-with icmp-host-prohibited 
   COMMIT
   # Completed on Fri Mar 15 19:27:03 2019
   

3. vim 編輯 iptables 設定檔，增加防火牆開啟 3306 port。
   

   [root@kvm6 ~]# vim /etc/sysconfig/iptables
   [root@kvm6 ~]# cat /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 -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
   -A INPUT -j REJECT --reject-with icmp-host-prohibited
   -A FORWARD -j REJECT --reject-with icmp-host-prohibited
   COMMIT
   

4. 重新啟動 iptables。
   

   [root@kvm6 ~]# /etc/init.d/iptables restart
   iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
   iptables: Flushing firewall rules:                         [  OK  ]
   iptables: Unloading modules:                               [  OK  ]
   iptables: Applying firewall rules:                         [  OK  ]
   

5. 登入 mysql 增加一只能從 192.168.122.1 存取的帳號 hosttest。
   

   [root@kvm6 ~]# mycli -uroot -S /var/lib/mysql/mysql.sock -p123qwe mysql
   mysql 5.5.49
   mycli 1.19.0
   Chat: https://gitter.im/dbcli/mycli
   Mail: https://groups.google.com/forum/#!forum/mycli-users
   Home: http://mycli.net
   Thanks to the contributor - jweiland.net
   mysql root@localhost:mysql> INSERT INTO user (host,user,password,select_priv,inser
                               t_priv,update_priv) VALUES ('192.168.122.1','hosttest'
                               ,password('123qwe'),'Y','Y','Y');                     
   Query OK, 1 row affected
   Time: 0.009s
   mysql root@localhost:mysql> Flush privileges;                                     
   Query OK, 0 rows affected
   Time: 0.001s
   mysql root@localhost:mysql> exit                                                  
   Goodbye!
   

6. 從本機以 hosttest 帳號無法登入 mysql。
   

   [root@kvm6 ~]# mysql -uhosttest -p123qwe
   ERROR 1045 (28000): Access denied for user 'hosttest'@'localhost' (using password: YES)