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
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;
MariaDB Server: User Management
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
MariaDB: Remote Client
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
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
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