connect to MySQL server remotely

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘x.x.x.x’ (xxx)

原因為安全性問題，

測試方法:

1. 確認服務開啟

ps -e | grep mysql

netstat -n | grep mysql

2. 關閉selinux

3. 關閉firewalld

service firewalld stop

或者開啟port

# 永久將 mysql 服務從 public 區域中移除 
sudo firewall-cmd --zone=public --permanent --remove-port=3306/tcp
# 重新載入設定 sudo firewall-cmd --reload

# 永久關閉 tcp 的 3306 連接埠
sudo firewall-cmd --zone=public --permanent --remove-port=3306/tcp

4. 確認root grant option權限

mysql> grant all privileges on *.* to 'root'@'localhost' WITH GRANT OPTION;

mysql> grant all privileges on *.* to 'root'@'%' ;

ERROR 1410 (42000): You are not allowed to create a user with GRANT

(新的mysql: 不能對所有主機的帳號授予)

mysql> grant all privileges on *.* to 'root'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

5. 建立遠端連線帳號

mysql> CREATE USER 'sqmc'@'x.x.x.x' IDENTIFIED BY 'newpassword';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sqmc'@'x.x.x.x';

Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from user;

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

remote side:

#mysql -usqmc -p

password: newpassword

more info: https://www.tecmint.com/fix-error-2003-hy000-cant-connect-to-mysql-server-on-127-0-0-1-111/

留言