Allow Remote Access to MySQL database

Many websites and applications begin with the web server and database backend running on the same machine. A setup like this, on the other hand, can become complicated and challenging to scale over time. The solution is to separate these functions and establishing a remote database. Permitting the server and database to grow independently on their respective machines.

To enable this, open up your mysqld.cnf file:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

or

$ sudo nano /etc/mysql/my.cnf

or

$ sudo nano /etc/my.cnf

Find the line that starts with the bind-address directive. It will appear as follows:

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# 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
mysqlx-bind-address     = 127.0.0.1

This value is set to 127.0.0.1 by default, which means that the server will only look for local connections. This directive must be modified to refer to an external IP address. For troubleshooting purposes, you could set this directive to a wildcard IP address, such as * , : : , or 0.0.0.0:

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# 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

Save and close the file after changing this parameter.

Then restart the MySQL service to apply the changes made to mysqld.cnf:

$ sudo systemctl restart mysql

If you already have a MySQL user account that you intend to use to connect to the database from a remote host, you must reconfigure that account to connect from the remote server rather than localhost. Open the MySQL client as the root MySQL user or another privileged user account:

$ sudo mysql

if User root has a password use:

$ mysql -u root -p

With the following command, we can create a new user account that will only connect from the remote host:

mysql> CREATE USER 'bob'@'r_server_ip' IDENTIFIED BY 'password';

Or To change an existing user’s host, you can use MySQL’s RENAME USER command. Run the following command:

mysql> RENAME USER 'bob'@'localhost' TO 'bob'@'r_server_ip';

Then, for your specific needs, assign the new user the relevant rights. The following example allows a user to CREATE, ALTER, and DROP databases, tables, and users, as well as INSERT, UPDATE, and DELETE data from any table on the server. It also allows the user to query data using SELECT, construct foreign keys using the REFERENCES keyword, and conduct FLUSH operations using the RELOAD permission. You should, however, only provide people the access they require, so feel free to change your own user’s privileges as required.

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'bob'@'r_server_ip' WITH GRANT OPTION;

After that, it’s a good idea to execute the FLUSH PRIVILEGES command. This will release any memory cached by the server as a result of the previous CREATE USER and GRANT statements:

mysql> FLUSH PRIVILEGES;

The MySQL client can then be closed:

mysql> exit;

Finally, if you have a firewall installed on your database server, you must open port 3306 — MySQL’s default port — to enable communication to MySQL.

If you only want to connect to the database server from one machine, use the following command to grant that machine exclusive authorization to connect to the database remotely. Replace r_server_ip with the real IP address of the computer to which you want to connect:

Or to enable anyone to access your MySQL database in use:

UFW

$ sudo ufw allow from r_server_ip to any port 3306

Iptables

$ sudo iptables -A INPUT -p tcp -s r_server_ip --dport 3306 -j ACCEPT
$ sudo iptables-save

firewalld

$ sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp

After that, try remotely accessing your database from another machine:

Note: If you configured your firewall to only accept connections from a single IP address, you must attempt to access the database using the machine associated with that address.

$ mysql -u user -h db_server_ip -p

If you can access your database, it demonstrates that the bind-address directive in your configuration file was the source of the problem. However, setting bind-address to 0.0.0.0 is unsafe since it permits connections to your server from any IP address.