SQL

Allow Remote Connections To MySQL

By default, MySQL allows other services on the computer you’re running the daemon to connect to the database and denies any connections from hosts outside that computer. However, it’s pretty easy to provide access to the database from another host (for example, if you’re splitting up the back-end and front-end of a site, clustering, etc. To get started, you’ll edit your my.cnf file and find the [mysqld] section of the file.

Then, locate the bind-address, which you will need to set as the IP of your server and comment out the line for skip-networking. Let’s say we’re going to open access for 192.168.2.2. The section would look similar to the following:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 192.168.2.2
# skip-networking

Then restart MySQL and a listener should be running on the system. You can connect using the mysql command, with the -u option to define a user (root) and then the -h to define an IP (in this case 192.168.2.2):

mysql -u root –h 192.168.2.2 –p

You can also use the telnet command to attempt a connection into a given port, which in the MySQL case would be 3306:

telnet 192.168.2.2 3306

Now, just because you can connect remotely doesnt necessarily mean that another computer can actually get into any databases. Next, we’ll GRANT access to ALL resources for a user called krypted for all tables on a new database, coming from an IP of 192.168.2.3:

GRANT ALL ON *.* TO krypted@'192.168.2.3' IDENTIFIED BY 'mysupersecretpassword';

Or for an existing database called mydatabase, using the same IP and account as before:

UPDATE DB set Host='192.168.2.3' where Db='mydatabase';
UPDATE USER set Host='192.168.2.3' where user='krypted';

You’ll also need to open up port 3306 coming in, whether that’s using a firewall or opening traffic in from the internets. Once that’s done, you should be able to connect and use the database as needed.