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:
yum install nano -y
apt-get install nano -y
Once your text editor is installed, you will need to edit the configuration file.
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
service mysqld restart
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 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';
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:
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.