How to set connection permissions in MySQL?
1. Select your MySQL environment to set up
a. AWS MySQL/Aurora Setup
Add MySQL to inbound rules.
Go to the security group of your ec2 instance -> edit inbound rules -> add new rule -> choose MySQL/Aurora
and source to Anywhere
.
2. Add bind-address = 0.0.0.0
to my.cnf
For instance console:
sudo vi /etc/mysql/my.cnf
It will open the vi
editor. In my.cnf
file, after [mysqld]
add new line and write this:
bind-address = 0.0.0.0
Save the file by entering :wq
(enter)
Now restart mysql
:
sudo /etc/init.d/mysqld restart
3. Create a new User and give him relevant permissions
Login to MySQL:
mysql -u root -p mysql
(enter the password after this)
Now write the 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 your instance's public dns/ip as MySQL Host Address, username as canner
, and password as canner_password!123
. (The default port is at 3306)
4. Make sure the MySQL connection can connect remotely
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.