Enable Remote MySQL Access on a Linux Server
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.
- 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:
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 = x.x.x.x
- Save the changes and exit.
- Restart the MySQL service using following command:
- 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.
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.