Many websites and applications start with their web server and database backend hosted on the same machine. However, over time, a setup like this can become cumbersome and difficult to scale. A common solution is to separate these functions by creating a remote database, allowing the server and database to grow at their own pace on their own machines.
One of the most common issues users encounter when trying to set up a remote MySQL database is that their MySQL instance is only configured to listen for local connections. This is the default MySQL configuration, but it will not work for a remote database configuration as MySQL must be able to listen for an external IP address where the server can be accessed. To enable this, open your
mysqld.cnf file: sudo nano /etc/mysql/
- mysql.conf.d/mysqld.cnf
Navigate to the line that starts with the bind-address directive. It will look like this:
. . . 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 no less secure. bind-address = 127.0.0.1 . . .
By default, this value is set to 127.0.0.1, which means that the server will only look for local connections. You must change this policy to refer to an external IP address. For troubleshooting, you can set this policy 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 no 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 enforce the changes made to mysqld.cnf:
- sudo systemctl restart mysql
If you have an existing MySQL user account that you plan to use to connect to the database from your remote host, you will need to reconfigure that account to connect from the remote server instead of localhost. To do this, open the MySQL client as your MySQL user root or with another privileged user account
: sudo mysql
If you have 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 the MySQL 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 the IP address of your remote server
: RENAME USER ‘sammy’@’localhost’ TO ‘
- sammy’@’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 ‘sammy’@’remote_server_ip’ IDENTIFIED BY ‘password’; Then,
grant the new user the appropriate privileges for their particular needs. The following example grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users, as well as the ability to INSERT, UPDATE, and DELETE data from any table on the server. It also gives 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 needed.
GRANT
- CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD in *.* TO ‘sammy’@’remote_server_ip’ WITH GRANT OPTION;
After this, it is good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server has cached as a result of the previous CREATE USER and GRANT
- statements: FLUSH PRIVILEGES;
You can then exit the MySQL client:
- Sign Out
Finally, assuming you have 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 a specific computer, you can grant that computer exclusive permission to connect to the database remotely with the following command. Be sure to replace remote_IP_address with the actual IP address of the machine you plan to connect to:
- 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
:
- sudo ufw allow 3306
After this, try to access your database
remotely from another machine:
- mysql -u user-h database_server_ip-p
If you can access your database, confirm that the bind-address directive in your configuration file was the problem. Note, however, that the bind-address setting to 0.0.0.0 is insecure, as it allows connections to your server from any IP address. On the other hand, if you still can’t access the database remotely, then something else may be causing the problem. In any case, you may find it useful to follow our guide on How to Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 to Set Up a More Secure Remote Database Setup.
