MySQL建立使用者與授權(CentOS6.5)

標籤：flush   any   修改   state   init.d   roo   local   ice   centos6   

 

1.相關SQL語句

#建立使用者與授權方法##本地訪問create user ‘zend‘@‘localhost‘ IDENTIFIED BY ‘123456‘;grant ALL  privileges ON zenddb.* TO ‘zend‘@‘localhost‘;##本地訪問create user ‘zend‘@‘127.0.0.1‘ IDENTIFIED BY ‘123456‘;grant ALL privileges   ON zenddb.* TO ‘zend‘@‘127.0.0.1‘;##針對遠端存取create user ‘zend‘@‘%‘ IDENTIFIED BY ‘123456‘;grant ALL privileges   ON zenddb.* TO ‘zend‘@‘%‘;#重新整理許可權 flush privileges;#撤銷使用者權限 刪除使用者REVOKE ALL ON  zenddb.* FROM ‘zend‘@‘localhost‘;DROP USER ‘zend‘@‘localhost‘;

2.退出MySQL,重啟服務

/etc/init.d/mysqld restart

3.問題處理
3.1  串連MySQL資料庫時出現The dirver has not received any packets from the server
解決辦法：查看iptables後發現是連接埠3306被防火牆阻止

[[email protected] support-files]# vi /etc/sysconfig/iptables# Generated by iptables-save v1.4.7 on Tue Nov  8 16:07:49 2016*filter:INPUT ACCEPT [0:0]:FORWARD ACCEPT [0:0]:OUTPUT ACCEPT [1:184]-A INPUT -i lo -j ACCEPT-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT-A INPUT -p tcp -m tcp --dport 22 -j ACCEPT-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT-A INPUT -p tcp -m tcp --dport 3306 -j DROP-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT-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-A INPUT -m state --state NEW -m tcp -p tcp --dport 15672 -j ACCEPTCOMMIT# Completed on Tue Nov  8 16:07:49 2016

-A INPUT -p tcp -m tcp --dport 3306 -j DROP 修改  -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

接下來，重啟服務即可service iptables restart

MySQL建立使用者與授權(CentOS6.5)