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

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

1. Firstly we need to edit the MySQL Configuration file to tell MySQL to listen on all available IP Addresses. We recommend using the ‘nano’ text editor if you’re unfamiliar with more advanced text editors. Edit the configuration file with the below command,

nano /etc/mysql/my.cnf

2. Once you’ve loaded into the ‘nano’ text editor, press CTL + W (which will start a ‘find’ prompt), 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 cannot specify multiple IP addresses. 

3. Once your configuration file has been updated, press CTL + O, then ENTER which will save the file.
4. Press CTL + X when you’re ready to exit the ‘nano’ text editor.
5. Next we will need to restart the MySQL Service for the edits to take effect. There are multiple listings below depending on your version of MySQL.

service mysql restart

OR

service mysqld restart

OR

service mariadb restart

6. Now that your MySQL Service is listening for external connections, we need to create a Remote MySQL user. This needs to be completed before MySQL will allow any external connections to your databases. To do this, open the MySQL client with the following command dependant on your version, followed by ENTER:

mysql

OR

mysql -u root -p

7. To create a new user, you can use the following command and replace the example name and IP address with your own details. (Remember to use your own credentials in the below command):

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

9. Press ENTER to execute the command.

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

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' appended to the end, for 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.

10. Once the new user is created you will need to update their privileges with the following command and then pressing ENTER:

FLUSH PRIVILEGES;

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

Note: Make sure your firewall is allowing connections to port 3306 from your IP address. This will vary depending on individual firewall setups etc. Please check your relevant firewall documentation on steps to achieve this. Ensure that you only open the port to the relevant IP and do not allow access from any outside source.