Skip to main content
Version: v2

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.