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:
4. Quit MySQL:
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.