How to Enable External MySQL Connections

There will be certain instances wherein you will need to enable non-localhost connections to MySQL database, which is quite simple to do on Linux-based VPS or dedicated servers.

Steps for Enabling External MySQL Connections

1. Connect to your server via SSH as root.

2. Log into MySQL as root:

mysql -uroot -p

Then, enter this command:

GRANT ALL PRIVILEGES ON database.* TO user@'IP' IDENTIFIED BY 'password';

Replace these values with your own:

• Database – replace with the name of the database you are allowing access to. Use * if you wish to let access to all databases.
• User – replace with the username you wish to allow access to the database(s).
• IP – replace with the actual IP to connect from. Use % if you wish to allow access from all IPS.
• Password – replace with the desired password.

[su_note note_color=”#ffa6bd”]Allowing access from all IPs in NOT recommended unless you have a very good reason to do so.

A blank password value will result in no password (empty password) set for accessing databases. This is highly NOT recommended[/su_note]

3. Use this command to implement the changes you’ve made:

FLUSH PRIVILEGES;

4. Quit MySQL:

quit

5. If you need to allow connections to port 3306, which is a standard MySQL port, from a remote IP, run this command in shell:

iptables -I INPUT -s IP -p tcp --dport 3306 -j ACCEPT

Remember to replace ‘IP’ with the actual IP you wish to grant access to MySQL connections from.