How To Allow Remote Access to MySQ

由於資安的需求, 資料庫不能與運行的主程式放同一台, 所以要把mysql 搬出來, 就需要設定允許 mysql 可以遠端連進去.

open up your mysqld.cnf file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

修改

bind-address = 127.0.0.1

bind-address = 0.0.0.0

重啟 server

sudo systemctl restart mysql

照下面的格式, 建立使用者:

CREATE USER 'your-account'@'remote_server_ip' IDENTIFIED BY 'account-password';

給權限:

GRANT CREATE, INDEX, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES on database_name.* TO 'your-account'@'remote_server_ip' WITH GRANT OPTION;

FLUSH PRIVILEGES;

附註:如果這個帳號,沒有權限可以使用create index 的話,請把 INDEX 這個項目刪除。

檢查用的 script:

SELECT user,host FROM mysql.user;

如果有必要的話, 要啟用防火牆規則:

If you only plan to access the database server from one specific machine, you can grant that machine exclusive permission to connect to the database remotely with the following command. Make sure to replace remote_IP_address with the actual IP address of the machine you plan to connect with:

sudo ufw allow from remote_IP_address to any port 3306

如果要允許, 所有的電腦都可以連到 3306 port: (被攻擊的風險較高)

sudo ufw allow 3306

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *