How Do I Enable Remote Access To MySQL Database Server?

By default remote access is disabled to your MySQL database server. However, sometimes you need to provide remote access to a database for some reason or another. This is a tutorial provides a quick overview how to easily enable remote access to your MySQL server on your ServerGrove VPS.

Step 1: Log Into Control Panel

Log into our control panel: https://control.servergrove.com

Step 2: Configure MySQL

Once in the control panel, go to the Applications section of the server you wish to configure and select MySQL.

Click on the Configure button. This will open the MySQL my.cnf configuration file for editing.

Step 3: Edit the my.cnf file

Search for the following line:

[mysqld]

Make sure line skip-networking is commented (or remove line) and add following line:

bind-address=YOUR-SERVER-IP

So if your IP is 69.195.199.51 the entire block should look like this:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
bind-address = 69.195.199.51
skip-networking

Where,

  • bind-address : IP address to bind to.
  • skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

Step 4: Save & Restart

Save your edits by clicking on the Save button and restart MySQL by clicking Restart

Step 5: Grant access to remote IP address

Go to the terminal in the control panel and log in (or connect via SSH) and connect to your MySQL database.

$ mysql -u root -p mysql

Grant access to a new database
If you want to add a new database called foo for user bar and remote IP 69.195.199.100 then you need to type the following commands at mysql> prompt:

mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'69.195.199.100' IDENTIFIED BY 'PASSWORD';

How Do I Grant Access To An Existing Database?
To grant access to an existng database called foo for user bar and remote IP 69.195.199.100 type the following command At mysql> prompt for existing database, enter:


mysql> GRANT ALL ON foo.* TO bar@'69.195.199.100' IDENTIFIED BY 'PASSWORD';

Logout of MySQL
Type exit command to logout mysql:
mysql> exit

Step 6: Test it!

From your remote system or your desktop type the following command:
$ mysql -u webadmin –h 69.195.199.51 –p

Where:
* -u webadmin: webadmin is MySQL username
* -h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
* -p : Prompt for password

You should see:
Connection to 69.195.199.51 3306 port [tcp/mysql] succeeded!

Troubleshooting

  1. Check that mysql is running on server YOUR-SERVER-IP
  2. Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
  3. Check the root has rights to connect to YOUR-SERVER-IP from your address (mysql rights define what clients can connect to the server and from which machines)
  4. Make sure you are both providing a password if needed and using the correct password for YOUR-SERVER-IP connecting from the host address you’re connecting from