====== Install And Configure MariaDB ======

**General Information**

Installing and configuring the MySQL drop in replacement, MariaDB. 

----

====== Lab Setup ======

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

----

====== MariaDB Server: Install and Initial Config ======

Install the server and client utilities
<code bash>
yum install mariadb mariadb-server
</code>

\\
Enable and start the service
<code bash>
systemctl enable mariadb
systemctl start mariadb
</code>

\\
Open up the firewall for MariaDB
<code bash>
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload
</code>

\\
Run the secure configuration script
<code bash>
mysql_secure_installation
</code>
  * 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
<code bash>
vim /etc/my.cnf

[mysqld]
# Listen on this IP address
bind-address=192.168.1.200
</code>
  * For all IPv4 and IPv6 address: bind-address=::
  * All IPv4 only (leave blank): bind-address=

\\
Restart the service
<code bash>
systemctl restart mariadb
</code>

\\
Connect to the database
<code bash>
mysql -u root -p
</code>
  * -u -> username
  * -p -> prompt for password

\\
Show the databases
<code bash>
MariaDB [(none)]> show databases;
</code>

\\
Use a specific database
<code bash>
MariaDB [(none)]> USE mysql;
</code>

\\
Show the tables in the current database
<code bash>
MariaDB [mysql]> SHOW TABLES;
</code>

----

====== MariaDB Server: User Management ======

Create a new user
<code bash>
MariaDB [(none)]> CREATE USER rjones@localhost IDENTIFIED by 'secret';
</code>
  * User login from any host: "rjones@'%' 
  * User login from specific host: "rjones@myserver1"
  * **HELP**<code bash>MariaDB [(none)]> help create user</code>

\\
Pre-Req: Create a quick database for the user to have permissions for<code bash>MariaDB [(none)]> CREATE DATABASE mydatabase;</code>

\\
Permissions for the new user
<code bash>
MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE ON mydatabase.* TO rjones@localhost;
</code>
  * Grants the commands on the database "mydatabase" and all tables (.*) in that database to rjones@localhost
  * **HELP**<code bash>MariaDB [(none)]> help grant</code>

\\
Flush privileges
<code bash>
MariaDB [(none)]> FLUSH PRIVILEGES;
</code>

\\
Show privileges
<code bash>
MariaDB [(none)]> SHOW GRANTS FOR rjones@localhost;
</code>

\\
Show all users
<code bash>
MariaDB [(none)]> SELECT user,host FROM mysql.user
</code>

----

====== MariaDB: Remote Client ======

Installing/configuring local and remote MariaDB clients.

\\
MariaDB Server: Verify Server is listening
<code bash>
ss -tulpn | grep mysql
</code>

\\
MariaDB Server: Connect to mariadb
<code bash>
mysql -u root -p
</code>

\\
MariaDB Server: Grant privileges to root from the client IP address and flush privileges
<code bash>
MariaDB [(none)]> GRANT ALL ON *.* TO root@'192.168.1.150' IDENTIFIED BY 'password-here';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
</code>
  * 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)
<code bash>
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload
</code>

\\
**MariaDB Remote Client**: Install components to remotely manage
<code bash>
yum install mariadb
</code>

\\
**MariaDB Remote Client**: Connect remotely
<code bash>
mysql -h 192.168.1.151 -u root -p
</code>

----

====== MariaDB Server: Local Client Only ======

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

Edit the MariaDB config file
<code bash>
vim /etc/my.cnf

# Don't allow network connections to the database
skip-networking=1
</code>
  * Cannot even connect to 'localhost', will need to connect to the socket

\\
Restart the mariadb service
<code bash>
systemctl restart mariadb
</code>

----