External MySQL connections

In some cases you may need to enable non-localhost connections to MySQL database. It’s easy to do on any Linux-based VPS or dedicated server.

Follow these steps:

1. Connect to your server via SSH as root:

2. Log into MySQL as root:

mysql -uroot -p

Enter the following command:

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

… substituting the following values with your own:

• database is replaced by the name of the database you’d like to allow access to. Using * will allow access to all databases
• user is replaced by the username you want to allow
• IP is replaced by the actual IP to connect from. Using % will allow access from all IPs
• password is replaced by the desired password

[alert]Allowing access from all IPs in NOT recommended unless you have a very good reason to do so[/alert]

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

4. Apply changes you’ve made with the following command:

FLUSH PRIVILEGES;

5. Quit MySQL:

quit

6. You might also need to allow connections to port 3306 (standard MySQL port) from a remote IP. Run the following command in shell:

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

Don’t forget to replace IP with the actual IP you want to allow MySQL connections from.