General Information

Installing and configuring the MySQL drop in replacement, MariaDB.

The following virtual machines will be used:

• server1.example.com (192.168.1.150) → Perform any client testing here
• server2.example.com (192.168.1.151) → Install the database here

Install the server and client utilities

yum install mariadb mariadb-server

Enable and start the service

systemctl enable mariadb
systemctl start mariadb

Open up the firewall for MariaDB

firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload

Run the secure configuration script

mysql_secure_installation

• Prompted for current root password (should be blank because of fresh install, just press ENTER)
• Prompted to set a root password
• Other options
  • Remove anonymous users
  • Disallow root login remotely
  • Remove test database
  • Reload privileges

Configure MariaDB to listen on an IP address

vim /etc/my.cnf
 
[mysqld]
# Listen on this IP address
bind-address=192.168.1.200

• For all IPv4 and IPv6 address: bind-address=::
• All IPv4 only (leave blank): bind-address=

Restart the service

systemctl restart mariadb

Connect to the database

mysql -u root -p

• -u → username
• -p → prompt for password

Show the databases

MariaDB [(none)]> show databases;

Use a specific database

MariaDB [(none)]> USE mysql;

Show the tables in the current database

MariaDB [mysql]> SHOW TABLES;

Create a new user

MariaDB [(none)]> CREATE USER rjones@localhost IDENTIFIED by 'secret';

• User login from any host: “rjones@'%'
• User login from specific host: “rjones@myserver1”
• HELP

  MariaDB [(none)]> help create user

Pre-Req: Create a quick database for the user to have permissions for

MariaDB [(none)]> CREATE DATABASE mydatabase;

Permissions for the new user

MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE ON mydatabase.* TO rjones@localhost;

• Grants the commands on the database “mydatabase” and all tables (.*) in that database to rjones@localhost
• HELP

  MariaDB [(none)]> help grant

Flush privileges

MariaDB [(none)]> FLUSH PRIVILEGES;

Show privileges

MariaDB [(none)]> SHOW GRANTS FOR rjones@localhost;

Show all users

MariaDB [(none)]> SELECT user,host FROM mysql.user

Installing/configuring local and remote MariaDB clients.

MariaDB Server: Verify Server is listening

ss -tulpn | grep mysql

MariaDB Server: Connect to mariadb

mysql -u root -p

MariaDB Server: Grant privileges to root from the client IP address and flush privileges

MariaDB [(none)]> GRANT ALL ON *.* TO root@'192.168.1.150' IDENTIFIED BY 'password-here';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

• If you don't flush privileges, you could get inconsistent behaviour.
• The password for root and be different for this remote grant. This allows you to have different root password's depending upon the grant or if its local/remote.
  • FYI: 'root'@'localhost' and 'root'@'192.168.1.150' are two different user accounts.

MariaDB Server: Open up the firewall to allow mysql connections (if you have not already)

firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload

MariaDB Remote Client: Install components to remotely manage

yum install mariadb

MariaDB Remote Client: Connect remotely

mysql -h 192.168.1.151 -u root -p

To make it so the MariaDB server will only allow local client (socket) connections (and NOT from IP addresses):

Edit the MariaDB config file

vim /etc/my.cnf
 
# Don't allow network connections to the database
skip-networking=1

• Cannot even connect to 'localhost', will need to connect to the socket

Restart the mariadb service

systemctl restart mariadb