如何在 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.