How to enable remote access to a MySQL database

The main reason why one has to access a MySQL database from another machine is that database server may be installed somewhere else than application server, thus we don’t connect to localhost, but to some IP or hostname instead. There are two things to do, first we have to create a database user (or grant privileges to an existing user) and secondly we have to allow network connection to a MySQL database. Let’s go through it below.

Prerequisites

Of course there should be a MySQL database installed on your machine. If you’re missing it, get one here. You should also have root OS privileges, or at least you should be able to perform all below-mentioned operations without getting any error.

Create a database user

#open a terminal and connect to the database server ssh 192.168.0.104 -l someuser #IP will be different of course, #you have to connect to your db server #now connect to the database and create our new user mysql -u root -p --enter root MySQL password and continue creating new user use mysql; CREATE USER 'user001'@'%' IDENTIFIED BY 'userpass_99'; --now grant privileges so our user can connect to the host GRANT USAGE ON *.* TO 'user001'@'%' IDENTIFIED BY 'userpass_99'; GRANT USAGE ON *.* TO 'user001'@'localhost' IDENTIFIED BY 'userpass_99'; -the first grant secures access to the database server from other computers --now exit and try telnet in terminal telnet 192.168.0.104 3306 #this should return success, otherwise you have to allow port 3306 #since the command to allow port 3306 may be different on Linux distributions, #I do not provide any particular command here. However you may also use #Firewall settings in yast2 if you’re using SuSE or openSuSE, #eventually check iptables command. Rules within iptables can be exported #easily if following command is issued as root (or privileged user) iptables-save > iptables_rules.list && cat iptables_rules.list #now it’s easy to find out how to compose your iptables command, look for ports #80, 21, 22 or 443 - allowing port 3306 is bascially same (execute #iptables -A ...replace 80 with 3306 for example...) #now try connection from database server: mysql -u user001 --password="userpass_99" #this works, let’s logout from the database server and try following command: mysql -u user001 --password="userpass_99" -h 192.168.0.104 --since our user doesn’t have any privileges except for usage, --we can connect to database test which usually exists in every MySQL installation USE test; --all finished, you can close connection by executing exit

Cleanup

If everything is clear, execute following commands in order to cleanup all above.

#connect to the database server from ssh terminal ssh 192.168.0.104 -l someuser #just like before #now connect to the database as root mysql -u root -p --enter root MySQL password and continue --drop the user DROP user 'user001'; exit; exit

Conclusion

The above-described principle can be used when accessing any MySQL server, however we have to think about security too. If IP address of the application server won’t change, it’s possible to specify which host may be allowed by the database server. ssh access to the database server should be granted to a database administrator too so any issues can be resolved even when database is experiencing timeouts or other issues.

ssh is a interface which allows one to securely access a remote host.

Further reading & help

If you need help with your MySQL database, drop me a line. You’re also welcome to visit other articles devoted to MySQL - they’re listed below.

↑