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.
AnnouncementI have released my new course on Udemy, Kubernetes By Example. Sign up now to get free lifetime access!
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:
This will result in only socket based (i.e. local based) access being allowed. This setting also overrides the bind-address setting.
– 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
GRANT ALL on *.* TO root@’10.2.5.12′ IDENTIFIED BY ‘rootpassword’;
$ mysql -h 10.2.5.10 -u root -p
on the mysql server add the following line to /etc/my.cnf:
then restart the daemon: