Many websites and applications start off with their web server and database backend hosted on the same machine. By default, the MySQL server listens for connections only from localhost for that on same server no need to configure MySQL remote access.
However, if the database is hosted on different server than web application server, then you will need to enable a remote MySQL connection. with time, a distributed environment has become an essential requirement of many projects. A separate database server can improve security and allows you to quickly scale resources.
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:
Open the file with your text editor :
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Search for a line that begins with bind-address and set its value to the IP address on which a MySQL server should listen.
By default, this value is set to 127.0.0.1, meaning that the server will only look for local connections (listens only in localhost).
. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
. . .
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:
. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
. . .
After changing this line, save and close the file (CTRL + X, Y, then ENTER if you edited it with nano).
Then restart the MySQL service to put the changes you made to mysqld.cnf into effect.
sudo systemctl restart mysql
Now, verify the MySQL listening connection with the following command:
ss -antpl | grep 3306
If you have an existing MySQL user account which you plan to use to connect to the database from your remote host, you’ll need to reconfigure that account to connect from the remote server instead of localhost. To do so, open up the MySQL client as your root MySQL user or with another privileged user account:
sudo mysql
If you’ve enabled password authentication for root, you will need to use the following command to access the MySQL shell instead:
mysql -u root -p
To change a user’s host, you can use MySQL’s RENAME USER command. Run the following command, making sure to change sammy to the name of your MySQL user account and remote_server_ip to your remote server’s IP address:
RENAME USER 'david'@'localhost' TO 'david'@'remote_server_ip';
Alternatively, you can create a new user account that will only connect from the remote host with the following command:
CREATE USER 'david'@'remote_server_ip' IDENTIFIED BY 'password';
Then grant the new user the appropriate privileges for your particular needs. The following example grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users, as well as the power to INSERT, UPDATE, and DELETE data from any table on the server. It also grants the user the ability to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need, so feel free to adjust your own user’s privileges as necessary.
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'david'@'remote_server_ip' WITH GRANT OPTION;
Following this, it’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:
FLUSH PRIVILEGES;
Next, verify your granted privileges using the following command:
SHOW GRANTS FOR 'david'@'remote_ip_address';
Then you can exit the MySQL client:
exit
At this point, MySQL is configured to allow remote connection. Now, it’s time to verify the MySQL connection from the remote server.
On the remote server, run the following command to connect the MySQL server:
mysql -u david -p -h mysql_server_ip
You will be asked to provide a MySQL user password and by entering the password you will be able to connect MySQL remotely.