Posted on 25 September, 2016

By default MySQL server only responds to requests generated from local server. If you like to access the MySQL service from an external server, you have to perform following steps:

Configure MySQL to allow remote connections

• Open MySQL configuration file in a file editor such as vi.

#vi /etc/my.cnf

• Locate the parameter skip-networking in the [mysqld] section and comment it out. Add a the following line below:  bind-address=x.x.x.x, where x.x.x.x is IP address of your server. Following is an example configuration:

[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

# skip-networking

bind-address    = x.x.x.x

 

• Save the changes and exit.
• Restart the MySQL service using following command:

#/etc/init.d/mysql restart

• Once the service is restarted, MySQL will listen for request on IP address x.x.x.x

 

Grant privileges to access the database.

• Once MySQL is configured to accept requests from external IP addresses, you have to grant the remote address permissions to access a specific database as follows.
• Login to MySQL server using following command:

# mysql -uroot –p<mysql_root_password> mysql

Where, mysql_root_password is the MySQL root password.

• If you want to allow access to database called ‘test_db’ for user ‘test_user’ with password ‘PASSWORD’ from a remote IP address y.y.y.y then type following command at mysql prompt:

mysql> GRANT ALL ON test_db.* TO test_user@’y.y.y.y’ IDENTIFIED BY ‘PASSWORD’;

• If you want to revoke the permissions from the IP address, use following command.

mysql> REVOKE GRANT OPTION ON test_db.* FROM test_user@’y.y.y.y’;

• Exit the MySQL console by typing exit command.

mysql> exit

 

Configure server firewall to allow MySQL connections

• Use following command to allow a remote IP address to have access to MySQL port (3306) on your server.

# iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT
# service iptables save

Note: If you are subscribed to Web24 Hardware Firewall, ensure that the MySQL port is allowed by contacting Web24 Service Desk.

Now, test accessing the MySQL service from the remote location.