By default when you install the MariaDB database server, it only accepts connections to 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 to databases set up 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 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 set up remote database access, please continue below.
Install MariaDB Database Server
If you haven’t installed the MariaDB server and you’re looking for a truly open-source database server, then MariaDB is a great place to start… To install MariaDB, simply run the commands below: After installing MariaDB, the commands below can be used to stop, start and enable the MariaDB service always to start up when the server boots… Run these on Ubuntu 18.04 LTS 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.
Enter current password for root (enter for none): Just press the Enter Set root password? [Y/n]: Y New password: Enter password Re-enter new password: Repeat password Remove anonymous users? [Y/n]: Y Disallow root login remotely? [Y/n]: Y Remove test database and access to it? [Y/n]: Y Reload privilege tables now? [Y/n]: Y
Now that MariaDB is installed, to test whether the database server was successfully installed, run the commands below… type the root password when prompted… If you see a similar screen as shown above, then the server was successfully installed…
Configure MariaDB 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 the MariaDB server accepts connections on all host IPv4 interfaces. If you have IPv6 configured on your system, use:: On Ubuntu systems with a MariaDB database server installed, its default configuration file is located at: /etc/mysql/mariadb.conf.d/50-server.cnf Simply run the commands below to open the MariaDB configuration file. Depending on your systems, you may find that same configuration file may be at the location below: When the file is opened, search for a line that begins with bind-address as shown below. Its default value should be 127.0.0.1. What you need to do is change the default value 127.0.0.1 to 0.0.0.0 as shown below: In the same file, you’ll want to comment out the line that begins with skip-networking by putting the # before it. or delete it 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. To verify that the change happens, run the commands below and you should find the result that looks like the one below Now the server is set up to listen to all IP addresses but individual IP needs to be explicitly configured to connect to a database. To enable a client to connect to a database, you must grant access to the remote server.
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.
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. 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 That’s it! You’ve successfully configured remote access to the MariaDB database server.
Ubuntu Firewall
If your Ubuntu server has a firewall enabled, then you will want to open a connection to the database server. Simply run the commands below to open the firewall to the client 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. To allow all IP addresses, (not secure), then run the commands below: That’s it! Congratulations! You have successfully installed and configured MariaDB to allow remote access.
