Setting up a MariaDB Server with SELinux on RHEL 7

We are going to install and configure a MariaDB server and create a simple database schema. MariaDB backup and restore procedures, as well as some common MariaDB errors, will be covered. 

We use a RHEL 7.0 server in this article with SELinux set to enforcing mode.

MariaDB Installation

Install MariaDB packages, enable the service and allow incoming connections to the standard MySQL TCP port 3306.

# yum install -y mariadb mariadb-server
# systemctl enable mariadb && systemctl start mariadb
# firewall-cmd --permanent --add-service=mysql
# firewall-cmd --reload

Secure mariadb by using mysql_secure_installation. Set a password for the database root user, disable remote root access and remove the test database and any anonymous users.

# mysql_secure_installation

Restart the service:

# systemctl restart mariadb

Test root login by executing a simple SQL query:

# mysql -uroot -ppassword -e "select @@version; show databases"
+----------------+
| @@version      |
+----------------+
| 5.5.35-MariaDB |
+----------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Configure MariaDB

For a small test system, there is a handy example config file available /usr/share/mysql/my-small.cnf which can be used as a basic template. The file /usr/share/mysql/my-innodb-heavy-4G.cnf is full of useful comments and can be used when complex setup is required.

Change MariaDB Port

We are going to change the default MariaDB port from 3306 to 5506.

Open the file /etc/my.cnf for editing, and add the following line:

[mysqld]
port = 5506

Allow firewall access for the new port:

# firewall-cmd --permanent --add-port=5506/tcp
# firewall-cmd --reload

Tell SELinux to allow MariaDB to bind to TCP port 5506:

# semanage port -a -t mysqld_port_t 5506 -p tcp

Finally, restart the service:

# systemctl restart mariadb

Verify:

# ss -nlp | grep 5506
tcp    LISTEN  0 50  *:5506 *:* users:(("mysqld",5665,13))

Change MariaDB Data Directory

Stop the MariaDB service first:

# systemctl stop mariadb

Create a new directory /mariadb_data to store data files and change ownership to mysql:

# mkdir /mariadb_data
# chown mysql:mysql /mariadb_data

Copy the existing content to the new location:

# cp -Rp /var/lib/mysql/* /mariadb_data/

Add SELinux file-context for everything under /mariadb_data:

# semanage fcontext -a -t mysqld_db_t "/mariadb_data(/.*)?"
# restorecon -Rv /mariadb_data

Open the file /etc/my.cnf for editing, and change the datadir to point to the new location:

#datadir=/var/lib/mysql
datadir = /mariadb_data

Start the service:

# systemctl start mariadb

Verify:

# mysql -uroot -ppassword -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| datadir       | /mariadb_data/ |
+---------------+----------------+

Change Listening Address

We want to enable access through all IP addresses (IPv4 and IPv6). Add the following the file /etc/my.cnf:

bind-address = ::

Note that leaving it blank would enable access over IPv4 only. A specific IP address can also be specified.

In case we wanted to disable all networking communications, we would add skip-networking=1 to the configuration file.

Restart the mariadb service and verify:

# ss -nlp | grep 5506
tcp LISTEN 0 50 :::5506 :::* users:(("mysqld",7064,13))

Store Tables as Separate Files

Configure MariaDB in a way so that each InnoDB table, including its indexes, is stored as a separate .ibd data file. This way ibdata1 will not grow as large.

Add the following to the file /etc/my.cnf and restart the service:

[mysqld]
innodb-file-per-table=1

Note that MariaDB 5.6 enables innodb-file-per-table by default.

Increase Max Allowed Packet Size

Set the server’s max-allowed-packet value to 16MB (default is 1M) and restart the service. We need to increase this if the server has to handle big queries.

[mysqld]
max-allowed-packet=16M

Working with MariaDB

Create a New Database and a New User

Login as a MariaDB root user. List existing users, create a new database test1, grant all privileges on the database test1 to the database user dbuser1, flush privileges and show grants for the newly created user.

# mysql -uroot -ppassword

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT user,host FROM mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+

MariaDB [(none)]> CREATE DATABASE test1;

MariaDB [(none)]> SET old_passwords = 0;

MariaDB [(none)]> CREATE USER 'dbuser1'@'localhost' IDENTIFIED BY "password";

MariaDB [(none)]> GRANT ALL PRIVILEGES ON test1.* TO 'dbuser1'@'localhost' IDENTIFIED BY "password";

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> SHOW GRANTS FOR 'dbuser1'@'localhost';
+-----------------------------------------------------------------------------+
| Grants for dbuser1@localhost                                                |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY PASSWORD 'secret' |
| GRANT ALL PRIVILEGES ON `test1`.* TO 'dbuser1'@'localhost'                  |
+-----------------------------------------------------------------------------+

MariaDB [(none)]> SELECT user,host,Grant_priv,Super_priv FROM mysql.user;
+-----------+-----------+------------+------------+
| user      | host      | Grant_priv | Super_priv |
+-----------+-----------+------------+------------+
| root      | localhost | Y          | Y          |
| root      | 127.0.0.1 | Y          | Y          |
| root      | ::1       | Y          | Y          |
| dbuser1   | localhost | N          | N          |
+-----------+-----------+------------+------------+

MariaDB [(none)]> exit

If we need to change the database user’s dbuser1 password, we can do so this way:

MariaDB [(none)]> SET PASSWORD FOR 'dbuser1'@'localhost' = PASSWORD('new_password');

Or if we want to delete the user dbuser1 from MariaDB:

MariaDB [(none)]> DELETE FROM mysql.user WHERE user='dbuser1';

We can also drop the user dbuser1 from MariaDB:

MariaDB [(none)]> DROP USER dbuser1@localhost;

If we were to have a WordPress database, the below would be a fair example for granting the database user dbuser1 privileges for the database test1:

MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE,DROP,INDEX ON test1.* TO 'dbuser1'@'localhost' IDENTIFIED BY "password";

Create a Simple Database Schema and Perform Simple SQL Queries Against a Database

# mysql -uroot -ppassword

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test1;
Database changed

MariaDB [test1]> create table services (id INT(10) unsigned, name VARCHAR(20), version INT(10));

MariaDB [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| services        |
+-----------------+

MariaDB [test1]> describe services;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | YES  |     | NULL    |       |
| name    | varchar(20)      | YES  |     | NULL    |       |
| version | int(10)          | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+

MariaDB [test1]> insert into services (id, name, version) values (1, "apache", "2");
MariaDB [test1]> insert into services (id, name, version) values (2, "samba", "4");

MariaDB [test1]> select * from services;
+------+--------+---------+
| id   | name   | version |
+------+--------+---------+
|    1 | apache |       2 |
|    2 | samba  |       4 |
+------+--------+---------+

MariaDB [test1]> delete from services where id=1;

MariaDB [test1]> select * from services;
+------+-------+---------+
| id   | name  | version |
+------+-------+---------+
|    2 | samba |       4 |
+------+-------+---------+

MariaDB [test1]> exit

Tables can be dropped with drop table.

Now if we were to have dozens of records inside a table, we may want to select a small fraction of them, say 5 rows:

MariaDB [test1]> select user,host from mysql.user limit 5;

See what queries are being processed at any given time:

MariaDB [test1]> SHOW FULL PROCESSLIST;

If required, you can kill the transaction thread directly, where 12345 is the process ID:

MariaDB [test1]> KILL 12345;

Backup and Restore MariaDB Database

Backup with Mysqldump

Mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used.

Mysqldump does not dump the INFORMATION_SCHEMA database by default. As of MySQL 5.1.38, mysqldump dumps INFORMATION_SCHEMA if we name it explicitly on the command line, although we must also use the –skip-lock-tables option.

Backup the database test1:

# mysqldump -uroot -ppassword test1 > test1.sql

Database backup can also be compressed on the fly:

# mysqldump -uroot -ppassword test1 | gzip > test1.sql.gz

Restore a Database

# mysql -uroot -ppassword test1 < test1.sql

Binary Log Backups

MariaDB binary logs aren’t covered by RHCE, but it’s good to know the way to back them up in case the need arises:

# mysqlbinlog -v -uroot -ppassword --read-from-remote-server \
  --host=localhost --to-last-log mysql-bin.000001 --result-file=binlog

Common MariaDB Errors

ERROR 1114 (HY000) at line : The table ‘KEY_COLUMN_USAGE’ is full

Increase max-heap-table-size to something bigger than it is now, for example:

max-heap-table-size=256M

ERROR 1153 (08S01) at line : Got a packet bigger than ‘max_allowed_packet’ bytes

Increase max-allowed-packet to something bigger than it is now, for example:

max-allowed-packet=16M

ERROR 1102 (42000): Incorrect database name ‘long_name_goes_here’

Maximum length of a MySQL 5 database name is 64, so if the name is longer than that, the error above is given. To resolve, make the database name shorter.

Test in Bash:

#!/bin/bash
db=123456789-123456789-123456789-123456789-123456789-1234567890;
db_restore=""$db"_restore";
echo "start: ""$db_restore";

# check if a database name is not longer than 64 characters
db_length=$(echo -n "$db_restore"|wc -c);
if [[ "$db_length" -gt "64"  ]]; then
  while [ "$db_length" -gt "64" ]
  do
    # delete the last character from the name until the length is 64
    db_restore=${db_restore%?};
    db_length=$(echo -n "$db_restore"|wc -c);
  done
fi
echo "end: ""$db_restore";