Skip to main content
Version: v1

如何在 MySQL 中設定連結權限 ?

1. 選擇您的 MySQL 架設環境進行設定​

a. AWS MySQL/Aurora Setup​

Add MySQL to inbound rules.​

Go to security group of your ec2 instance -> edit inbound rules -> add new rule -> choose MySQL/Aurora and source to Anywhere.

2. 加入 bind-address = 0.0.0.0 到 my.cnf​

In instance console:

sudo vi /etc/mysql/my.cnf

this will open vi editor.in my.cnf file, after [mysqld] add new line and write this:

bind-address = 0.0.0.0

Save file by entering :wq(enter)

now restart MySQL:

sudo /etc/init.d/mysqld restart

3. 創立一個新的 User 以及給他相關權限​

login to MySQL:

mysql -u root -p mysql (enter password after this)

Now write following commands:

CREATE USER 'canner'@'%' IDENTIFIED BY 'canner_password!123';

GRANT ALL PRIVILEGES ON *.* to canner@'%' IDENTIFIED BY 'canner_password!123' WITH GRANT OPTION;

FLUSH PRIVILEGES;

EXIT;

After this, MySQL Database can be remotely accessed by entering public dns/ip of your instance as MySQL Host Address, username as canner and password as canner_password!123. (Port is set to default at 3306)

4. 確定 MySQL 的連線可以從遠端連線​

Use a MySQL Client to test the connection.

We recommend mycli (https://www.mycli.net/)

mycli -h <hostname> -u canner -D <database>

Or you can choose your preferred client.