MariaDB Enable Remote Access

In this MariaDB tutorial, we will learn about the “MariaDB enable remote access“, here we will enable the MariaDB server for remote connection so that other machines at different servers can access it. Additionally, we will cover the following topics.

• MariaDB enable remote access on Linux
• MariaDB enable remote access on Windows

MariaDB enable remote access on Linux

MariaDB is an open-source and free relational database, that is forked from MySQL and very popular in the United States. Sometimes we need to access the database from another machine or place for that we allow the MariaDB to connect to the remote machine.

After enabling the MariaDB for remote connection, its database or information can be accessed from anywhere in the world from any database server.

By default, MariaDB is accessible only to the local system (localhost) or on the machine where the MariaDB server is installed. So here we will configure the setting of MariaDB for remote access to all IP addresses.

Here we will use the two different machines with different operating systems( Windows and CentOS) for the demonstration of remote access to the database. The MariaDB server installed on CentOS will be accessed by the client on the Windows machines.

Before beginning, make sure that the MariaDB install on both machines.

If you don’t know how to install MariaDB on different operating systems then refer to our tutorials “How to install MariaDB + Uninstallation steps” for installing the MariaDB.

First setting the MariaDB server on Ubuntu for remote connection, we need to configure the file mariadb-server.cnf that is a configuration file that exists at the location /etc/my.cnf.d/mariadb-server.cnf.

• Open the file using any text editor.

sudo nano /etc/my.cnf.d/mariadb-server.cnf

• After opening the file mariadb-server.cnf, change the bind-address from 127.0.0.1 to 0.0.0.0 otherwise leave it as default.

• Now save and close the file using the command CTRL+O followed by CTRL+X.

• Run the below command to restart the MariaDB server.

sudo systemctl restart mariadb

• After restarting the MariaDB server, it is ready to listen to all IP addresses.
• Allow access to a user from a remote machine, follow the below steps:
• Log in to the MariaDB prompt using the below code, if it asks for a password enter the password.

sudo mysql -u root -p     -- login into MariaDB prompt

• Create a database named ‘client’.

CREATE DATBASE client;

• Create a new user using the below query.

CREATE USER 'Jhon'@'%' IDENTIFIED BY '12345';

Where,

• CREATE USER ‘Jhon’@’%’: It is the command to create a new user in MariaDB, here we create the user 'Jhon' and it can be accessed from any IP address that is specified using the percentage symbol (%).
• IDENTIFIED BY ‘12345’: It is the command to specify the password for the user that we are creating. So we can access the user 'Jhon' with password '12345'.

Then grant permission for all kinds of privileges to user ‘Jason’ using the below code. If you don’t know about how permission is granted to a user, then follow our tutorial “How to Grant All Privileges in MariaDB”.

GRANT ALL PRIVILEGES ON *.* TO 'Jhon'@'%' IDENTIFIED BY '12345' WITH GRANT OPTION;

At last, flush the privileges using the below code.

FLUSH PRIVILEGES;

Let’s open the command line on windows and type the below code to connect the MariaDB running on CentOS.

mysql -u Jhon -p -h 192.168.253.130

Where,

• mysql -u Jhon -p: The mysql is the command to access the user 'Jhon' that can be specified using the option -u that represents the user and -p is for entering the password of that user 'Jhon'.
• -h 192.168.253.130 : -h is used to specify the host address where MariaDB serve is running. So here MariaDB server is running on a machine with an IP address 192.168.253.130.

From the above output, we have successfully logged into the MariaDB server.

Also, check: How to Create Table in MariaDB

MariaDB enable remote access on windows

While installing MariaDB on windows check mark for the option 'Enable access from the remote machine for root user'.

After enabling the option for remote access, proceed with installation.

Now we will be able to access the MariaDB server running on Windows from any machine. Use the below code on another machine to access the MariaDB server on windows as here for demonstration we will use the CentOS machine.

mysql -u root -p -h 192.168.50.135

Where,

• mysql -u root -p: The mysql is the command to access the user 'root' that can be specified using the option -u that represents the user and -p is for entering the password of that user 'root'.
• -h 192.168.50.135 : -h is used to specify the host address where MariaDB serve is running. So here MariaDB server is running on a machine with an IP address 192.168.50.135.

As from the output, we have logged into MariaDB on windows from the CentOS machine successfully.

Let’s access the database from the remote by creating a new user in the MariaDB on windows.

Log in to the MariaDB prompt using the below code, if it asks for a password enter the password.

mysql -u root -p     -- login into MariaDB prompt

Create a new user using the below query.

CREATE USER 'Jason'@'%' IDENTIFIED BY '12345';

Where,

• CREATE USER ‘Jason’@’%’: It is the command to create a new user in MariaDB, here we create the user 'Jason' and it can be accessed from any IP address that is specified using the percentage symbol (%).
• IDENTIFIED BY ‘12345’: It is the command to specify the password for the user that we are creating. So we can access the user 'Jason' with password '12345'.

Then grant permission for all kinds of privileges to user ‘Jason’ using the below code. If you don’t know about how permission is granted to a user, then follow our tutorial “How to Grant All Privileges in MariaDB”.

GRANT ALL PRIVILEGES ON *.* TO 'Jason'@'%' IDENTIFIED BY '12345' WITH GRANT OPTION;

At last, flush the privileges using the below code.

FLUSH PRIVILEGES;

Now restart the MariaDB on windows, press Windows key + R to open the RUN box and type the following command 'services.msc' in that box, a window appears that contains all the services running on windows.

In that window search for the MairaDB and click on the restart as shown in the below output.

After restarting the MariaDB on windows, open the terminal on the CentOS machine and type the below code to access the database running on a windows machine.

mysql -u Jason -p -h 192.123.50.135

Where,

• mysql -u Jason -p: The mysql is the command to access the user 'Jason' that can be specified using the option -u that represents the user and -p is for entering the password of that user 'Jason'.
• -h 192.123.50.135 : -h is used to specify the host address where MariaDB serve is running. So here MariaDB server is running on a machine with an IP address 192.123.50.135.

From the output, we have successfully logged into the MariaDB server on Windows from CentOS.

Also, take a look at some more MariaDB tutorials.

• MariaDB Backup Database
• MariaDB JSON Data Type
• MariaDB Foreign Key + Examples
• MariaDB Left Join – Helpful Guide
• Create Database in MariaDB
• Grant User Access to a MariaDB
• How to create a user in MariaDB
• How To Check MariaDB Version

So in this tutorial, we have learned about the “MariaDB enable remote access” and covered the following topics.

• MariaDB enable remote access on Linux
• MariaDB enable remote access on Windows