How To Enable Remote Access to MySQL via SSH / Command Line In Linux

Follow

For servers that don't run a control panel, you will need to manually enable remote access to your MySQL server via SSH (you can do this for servers that use a control panel too, if you prefer).

Once you log into your server via SSH, the steps are as follows:

 

1. Edit the MySQL configuration file, to tell the MySQL server to listen on all available IP addresses.

 

We recommend using nano if you are unfamiliar with more advanced text editors such as vim.

 

To install nano, you can run the following command:

 

On CentOS/RedHat:

yum install nano -y

 

On Ubuntu/Debian

apt-get install nano -y

 

Once your text editor is installed, you will need to edit the configuration file.

nano /etc/mysql/my.cnf

 

Once in nano, press CTL + W (find), then type bind-address and press ENTER.

It will move the cursor to the variable bind-address in the configuration file.

Make sure the bind-address is set to 0.0.0.0, this means the server will listen on ALL available IP addresses.

Alternately, you can specify a single public IP, or private IP address here, but you can't specify multiple IP addresses.

Once your configuration file has been updated, press CTL + O, then ENTER to save the file.

Then press CTL + X to exit nano.

 

2. Restart MySQL Server

Now that the configuration file is updated, you will need to restart MySQL for this to take effect.

service mysql restart

OR

service mysqld restart

OR 

service mariadb restart

Depending on your version of MySQL.

 

Now your MySQL server will be listening for external connections.

 

3. Create a remote MySQL user.

You will need to create a remote MySQL user before MySQL will allow any external connections to your database server.

To do this, open the MySQL client with the following command:

mysql -u root -p

then ENTER.

Then type the root mysql password, then press ENTER.

You should see a command prompt if your password was correct:

To create a new user, you can use the following command, replacing the example name and IP address with your own details. 

 

Here is the full command, replace all the BOLD parts with your relevant information.

GRANT ALL PRIVILEGES ON databasename.tablename TO 'username'@'123.456.789.123' IDENTIFIED BY 'password';

then ENTER.

 

If you want the remote user to have access to all databases and tables, replace databasename.tablename with *.*

If you want the user to be able to create new databases and modify permissions (i.e. root user), the command needs 'WITH GRANT OPTION' added to the end, example:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'123.456.789.123' IDENTIFIED BY 'password' WITH GRANT OPTION;

 

* is a wildcard, so if you want the user to have access to all tables in the database 'wordpress', you would use wordpress.* here instead.

Once the new user is created, you will need to update privileges, with the following command:

 

FLUSH PRIVILEGES;

then ENTER.

 

Now you can exit the MySQL program by typing exit and pressing ENTER.

 

4. Make sure your firewall is allowing connection to port 3306 from your IP address.

Depending on your set-up this step may vary quite a lot. Please check your firewall documentation for instructions on how to complete this step.

Please note - do NOT open the firewall unconditionally on port 3306, this will allow attacks on your MySQL server. Please only ever allow 3306 to specific IP addresses.

 

Your MySQL server is now set up to listen for external connections and accept them only from the account you just created and the IP address you specified.

Have more questions? Submit a request

Comments

Powered by Zendesk