MariaDB – Connect to a MariaDB server from a remote client on CentOS/RHEL 7

In the previous article we set up MariaDB server, now we are going to access it from a mysql client. In our example we’ll try to connect using the root mysql account (which has the password, ‘rootpassword’) from a mysql client server that has the ip address of 10.2.5.12.

You can follow along this article using our MariaDB vagrant project on Github.

To do this, we first install the following on the mysql client:

$ yum groupinstall -y mariadb
$ yum groupinstall -y mariadb-client

We installed the MariaDB server software too. That’s to ensure we install all the necessary library files.

Next on the MariaDB server, we need to whitelist our root user to be allowed to have access from the remote client, this is done by running the following command:

[root@mariadb-server ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT ALL on *.* TO root@'10.2.5.12' IDENTIFIED BY 'rootpassword';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

Note, we have specify the password to remove possibility of ambiguity because in mysql it’s possible to have multiple users with the same username.

On the client we can then create a new mysql session like this:

[root@mariadb-client ~]# mysql -h 10.2.5.10 -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Disabling remote access

If you want to lock down access, so that you can only create mysql sessions if you are logged in locally, then you can do this by inserting the following line in the /etc/my.cnf file, in the ‘mysqld’ stanza:

skip-networking=1

This will result in only socket based (i.e. local based) access being allowed. This setting also overrides the bind-address setting.

Take the RHCSA Quiz

This article is part of our RHCSA Study guide (click on the yellow tab on the far left). By the end of this article you should be able to answer the following questions:


What are the steps to give the mysql user 'root' msyql access from mysql client (which has ip of 10.2.5.12) ?

– install mariadb software on the mysql client
– locally start mysql session as root user, so to add root user access rule for given client’s ip address
– flush privileges
– attempt connection from the client

What is the mysql query to give full access to the mysql user account 'root' (which has the password 'rootpassword') when this account creates new mysql session from a mysql client that has the ip address of 10.2.5.12?

GRANT ALL on *.* TO root@’10.2.5.12′ IDENTIFIED BY ‘rootpassword’;
FLUSH PRIVILEGES;

What is the command to run on the client to attempt access?

$ mysql -h 10.2.5.10 -u root -p

What do you need to do to disable all remote access?

on the mysql server add the following line to /etc/my.cnf:

skip-networking=1

then restart the daemon: