This is an old revision of the document!
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 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: 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”
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
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.50' IDENTIFIED BY 'password-here'; MariaDB [(none)]> FLUSH PRIVILEGES;
- If you don't flush privileges, you could get inconsistent behaviour.
MariaDB Remote Client: Install components to remotely manage
yum install mariadb
MariaDB Remote Client: Connect remotely
mysql -h 192.168.1.200 -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