This brief tutorial shows students and new users how to configure remote access connection to MySQL database servers on Ubuntu 18.04 systems.
Our previous tutorial showed you how to enable remote access to MariaDB database server. Since these are two separate (but also the same in some ways) database servers, some configurations might be different.
By default when you install MySQL database server, it only accepts connections its local host. The same host computer it is installed on.
If want to connect from a remote client computer from a remote location, you will not be able to connect databases setup on the server. This brief guide shows you how to enable that.
When configured correctly, you will be able to connect to the database servers from a remote systems and applications not connected to the same subnet or host computer.
If the server is connected directory to the Internet, you may able able to access it from anywhere around the world where Internet access is available. however, opening up your database servers directly to the internet is not recommended, especially in a production environment.
When you’re ready to setup remote database access, please continue below.
Step 1: Install MySQL Database Server
If you haven’t installed MySQL server and you’re looking for a truly open source database server, then MySQL is a great place to start… To install MySQL, simply run the commands below:
sudo apt update sudo apt install mysql-server mysql-client
After installing MySQL, the commands below can be used to stop, start and enable MySQL service to always start up when the server boots…
Run these on Ubuntu 18.04 LTS
sudo systemctl stop mysql.service sudo systemctl start mysql.service sudo systemctl enable mysql.service
Next, run the commands below to secure the database server with a root password if you were not prompted to do so during the installation…
When prompted, answer the questions below by following the guide.
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
- Press y|Y for Yes, any other key for No: N
- New password: Create New Password
- Re-enter new password: Repeat New Password
- Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
- Disallow root login remotely? (Press y|Y for Yes, any other key for No): Y
- Remove test database and access to it? (Press y|Y for Yes, any other key for No): Y
- Reload privilege tables now? (Press y|Y for Yes, any other key for No): Y
Now that MySQL is installed, to test whether the database server was successfully installed, run the commands below…
sudo mysql -u root -p
type the root password when prompted…
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
If you see a similar lines as shown above, then the server was successfully installed…
Step 2: Configure MySQL Remote Access
As we mentioned above, all remote access to the server is denied by default. To enable remote access, you’ll need to set the bind-address to allow for remote access.
For example, to allow all IPv4 addresses, set the bind-address to: 0.0.0.0 This will allow MySQL server accepts connections on all host IPv4 interfaces. If you have IPv6 configured on your system, use ::
On Ubuntu systems with MySQL database server installed, its default configuration file is located at: /etc/mysql/mysql.conf.d/mysqld.cnf
Simply run the commands below to open MySQL configuration file.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Depending on your systems, you may find that same configuration file maybe at the location below:
sudo nano /etc/my.cnf
When the file is opened, search for a line that begins with bind-address as shown below. It default value should be 127.0.0.1.
# this is read by the standalone daemon and embedded servers # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp 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 # # * Fine Tuning
What you need to do is change the default value 127.0.0.1 to 0.0.0.0 as shown below:
# this is read by the standalone daemon and embedded servers # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp 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 # # * Fine Tuning
In the same file, you’ll want to comment out the line that begin with skip-networking by putting the # before it. or delete it all together. then save your changes.
Please make sure to add the changes above under the [mysqld] section.
After making the change above, save the file and run the commands below to restart the server.
sudo systemctl restart mysql.service
To verify that the change happens, run the commands below
sudo apt install net-tools sudo netstat -anp | grep 3306
and you should find the result that looks like the one below
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3213/mysqld
Now the server is setup to listen to all IP addresses but individual IP needs to be explicitly configure to connect to a database.
To enable a client to connect to a database, you must grant access to the remote server.
Step 3: Access from Remote Clients
Now that the server is configured. use the steps below to allow remote clients to access the database.
For example, if you wish for a client computer with IP address 192.168.1.2 to connect to a database called database_name as user database_user, then run the commands below after logging onto the database server.
GRANT ALL ON database_name.* TO 'firstname.lastname@example.org' IDENTIFIED BY 'database_user_password';
- database_name is the name of the database that the user will connect to.
- database_user is the name of the database user.
- 192.168.1.2 is the IP from which the client is connecting from.
- database_user_password is the password of the database_user account
After running the commands above, you should be able to access the server from the client computer with that assigned IP.
To connect to the server from the approved IP address, run the commands below
mysql -u database_user -p database_user_password -h database_server
That’s it! You’ve successfully configured a remote access to MySQL database server.
If your Ubuntu server has firewall enabled, then you will want to open connection to the database server. Simply run the commands below to open the firewall to client from from the IP address to the port only.
For example, to open Ubuntu Firewall to allow IP address 192.168.1.2 to connect on port 3306.
sudo ufw allow from 192.168.1.2 to any port 3306
To allow all IP addresses, (not secure), then run the commands below:
sudo ufw allow 3306/tcp
Congratulations! You have successfully installed and configure MySQL to allow remote access.
You may also like the post below: