How To Connect To A Mysql Database Remotely
This article explains how to setup a remote connection to an external mysql server from your application, my example makes use of a laravel app to connect to a remote mysql server. Regardless, this method works for all kinds of applications. So, lets dive in!
Make sure you have the following ready somewhere:
The root user password for your mysql server.
You can use the following command to get it.
sudo vim /root/.my.cnf
Step 1:
Log in to your MySQL server locally as the root
user by using the following command:# mysql -u root -p
You’ll be prompted to input the password for the root user.
Step 2:
Use a GRANT
command in the following format to enable access for the remote user. Ensure that you change remoteDatabase
to the name of the remote database you will be connecting to and remote_user_password
to the password that you want remoteUserName
to use:
GRANT ALL ON remoteDatabase.* TO remoteUserName IDENTIFIED BY 'remote_user_password';
This statement grants ALL
permissions to the new user when the user connects by using the specified password.
Step 3:
One of the more common problems that users run into when trying to set up a remote MySQL database is that their MySQL instance is only configured to listen for local connections. This is MySQL’s default setting, but it won’t work for a remote database setup since MySQL must be able to listen for an external IP address where the server can be reached. To enable this, open up your mysqld.cnf
file:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Navigate to the line that begins with the bind-address
directive. It will look like this:
By default, this value is set to 127.0.0.1
, meaning that the server will only look for local connections. You will need to change this directive to reference an external IP address. For the purposes of troubleshooting, you could set this directive to a wildcard IP address, either *
, ::
, or 0.0.0.0
:
bind-address = 0.0.0.0
After changing this line, save and close the file (esc
, :wq
if you edited it with vim
).
Then restart the MySQL service to put the changes you made to mysqld.cnf
into effect:
sudo systemctl restart mysql
Step 4:
Lastly, assuming you’ve configured a firewall on your database server, you will also need to open port 3306
— MySQL’s default port — to allow traffic to MySQL.
If you only plan to access the database server from one specific machine, you can grant that machine exclusive permission to connect to the database remotely with the following command. Make sure to replace remote_IP_address
with the actual IP address of the machine you plan to connect with:
sudo ufw allow from remote_IP_address to any port 3306
If you need to access the database from other machines in the future, you can grant them access on an ad hoc basis with this command. Just remember to include their respective IP addresses.
Alternatively, you can allow connections to your MySQL database from any IP address with the following command:
Warning: This command will enable anyone to access your MySQL database. Do not run it if your database holds any sensitive data.
sudo ufw allow 3306
That’s all!
You should be able to connect remotely to a mysql server!
Article reference:
https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql
https://forums.cpanel.net/threads/the-system-failed-to-connect-to-the-mysql-database-issue.637229/