Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

如何遠端連接虛擬主機上的 mySQL 資料庫 ? #33

Open
futianshen opened this issue Nov 14, 2018 · 0 comments
Open

如何遠端連接虛擬主機上的 mySQL 資料庫 ? #33

futianshen opened this issue Nov 14, 2018 · 0 comments

Comments

@futianshen
Copy link
Collaborator

futianshen commented Nov 14, 2018

緣起

在經歷上一次瘋狂的看文件、複製貼上、整理語法之後,已經許久沒有管虛擬主機的事情了,不過因為 Week11 的作業1 需要遠端連接 mySQL 資料庫才能進行,讓我必須再次設定主機,才能從遠端連接 mySQL 資料庫。

在解決這次的問題時發現,原來上次只是設定防火牆而已,根本就沒有開啟防火牆(真危險),語法已修正在上一次的筆記當中。一小時完成 VPS (Virtual Private Server) 部署

遇到問題:無法連接到遠端資料庫

錯誤訊息1 Error: connect ECONNREFUSED
錯誤訊息2 Error: ER_HOST_NOT_PRIVILEGED

解決問題的步驟

  1. 登入虛擬主機
  2. 防火牆
    sudo ufw enable 開啟防火牆
    sudo ufw status verbose 查看防火牆開了哪些 port
    sudo ufw allow mysql 開啟 port 3306
  3. mySQL 安全權限設定
    sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
    進入 Vim 編輯器將 bind-address = 127.0.0.1 那行的最前面加上 # 或 將 127.0.0.1 改成 0.0.0.0
    sudo service mysql restart
  4. mySQL 使用者權限
    上次使用 sudo mysql_secure_installation 之後需要登入才能改變 mySQL 權限設定
    mysql -u root -p 用 root mySQL 使用者登入 mySQL
    SELECT user,authentication_string,plugin,host FROM mysql.user 查看 mySQL 有那些使用者帳號
    GRANT ALL PRIVILEGES ON *.* TO 'mySQL 使用者帳號'@'%' IDENTIFIED BY 'mySQL 使用者密碼'; 選擇你想要遠端連接的使用者帳號,更改 host 欄位中的 localhost 為 %

參考資料

https://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts
https://stackoverflow.com/questions/2995054/access-denied-for-user-rootlocalhost-using-passwordno
#21
https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html#error_er_host_not_privileged
https://devanswers.co/cant-connect-mysql-server-remotely/
https://devanswers.co/installing-mysql-ubuntu-18-04/

延伸閱讀 防火牆安全設定

https://www.howtoing.com/how-to-set-up-a-firewall-with-ufw-on-ubuntu-16-04
https://blog.gtwang.org/web-hosting/linode-vps-ubuntu-linux-setup-for-security/2/
https://www.mxp.tw/2014/06/21/%E9%98%B2%E7%A6%A6%E4%BD%A0%E7%9A%84%E4%BC%BA%E6%9C%8D%E5%99%A8-linode-%E4%B8%BB%E6%A9%9F%E7%9A%84%E5%AE%89%E5%85%A8%E6%80%A7%E6%95%99%E6%88%B0%E6%89%8B%E5%86%8A/
https://www.linode.com/docs/security/securing-your-server/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant