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";

27 thoughts on “Setting up a MariaDB Server with SELinux on RHEL 7

  1. There is typo. It should be underscore
    innodb_file_per_table
    max_allowed_packet
    max_heap_table_size

    • I’m sure it works both ways, with hyphens as well as underscores.

      I’m on MySQL 5.7, and if I put max-allowed-packet=64M into the file my.cnf and restart the daemon, the change works.

  2. This can be good tips. I am sure you will like if you have hard time remembering syntax on creating user or granting privileges — It has good examples as well.
    —————————————————————————————–

    [root@csrv175 ~]# mysql -u root -p

    MariaDB [(none)]> help contents help Account Management <—————————
    You asked for help about help category: "Account Management"
    For more information, type 'help ‘, where is one of the following
    topics:
    CREATE USER
    DROP USER
    GRANT
    RENAME USER
    REVOKE
    SET PASSWORD

    MariaDB [(none)]> help CREATE USER help grant <————————— this may be just enough to remeber
    Name: 'GRAN

    Normally, a database administrator first uses CREATE USER to create an
    account, then GRANT to define its privileges and characteristics. For
    example:

    CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
    GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
    GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

    • Agreed, and it’s not just MySQL the logic applies to, but pretty much any tool or command that you use during the exam. Help pages are time savers.

  3. MariaDB [(none)]> help contents
    MariaDB [(none)] help Account Management
    MariaDB [(none)] help create USER
    MariaDB [(none)] help grant

  4. I just came across a scenario while preparing for exam.

    The Database must be accessible locally only.

    How to achieve this task

    • It’s mentioned in the article: skip-networking=1. This option disables TCP/IP. You can also use 127.0.0.1 as a bind-address which implies TCP/IP. In both cases the database will be accessible locally only.

  5. I installed mariadb with following options

    Set a password for the database root user,
    enable remote root access
    and remove the test database and any anonymous users.

    But even with this configration i am not able to login remotely on this db.
    firewalld is ok

    Error:

    #mysql -uroot -p -h 192.168.1.245
    Enter password: ****
    ERROR 1130 (HY000): Host ‘192.168.1.221’ is not allowed to connect to this MariaDB server

    This is with default settings. I have not changed any thing in configuration files.

    Server Logs:
    # tail -f /var/log/mariadb/mariadb.log

    171026 23:15:29 [Note] /usr/libexec/mysqld: ready for connections.
    Version: ‘5.5.56-MariaDB’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MariaDB Server
    171026 23:16:01 [Warning] IP address ‘192.168.1.221’ could not be resolved: Name or service not known

    any clues please

    • Please post the output of the following MySQL query:

      SELECT host FROM mysql.user WHERE User = 'root';

      If you only see results with localhost and 127.0.0.1, then it means that you cannot connect from an external source.

    • To fix this you need to :

      1.systemctl stop mariadb
      2.mkdir /mariadb_data
      3.cp /var/lib/mysql/* /mariadb_data
      4.chown -R mysql:mysql /mariadb_data
      5.update /etc/my.cnf to reflect port=5555 and datadir=/srv/mariadb
      6.semanage port -a -t mysqld_port_t -p tcp 5555
      7.semanage fcontext -a -t mysqld_db_t “/srv/mariadb(/.*)?”
      8.rm /var/lib/mysql/mysql.sock
      9.systemctl start mariadb
      10.ln -s /srv/mariadb/mysql.sock /var/lib/mysql/mysql.sock

      /mariadb_data

  6. Btw i solved this issue from this post.
    https://mariadb.com/kb/en/library/configuring-mariadb-for-remote-client-access/

    output for the command

    MariaDB [(none)]> SELECT host FROM mysql.user WHERE User = 'root';
    +-----------------------+
    | host                  |
    +-----------------------+
    | 127.0.0.1             |
    | 192.168.1.%           |  ---- New line added after problem solved
    | ::1                   |
    | client1.example.local |
    | localhost             |
    +-----------------------+
    5 rows in set (0.00 sec)

    thanks for your instant reply

  7. Hi ,
    thanks a lot for a fantastic job. I want to recommend the site to all my friends.
    Just one questions on the mariadb:
    I know that for the rhce one question on mariadb is to write a query.
    I don’t understand where and how i must write th query ? in a file ? in a script?
    or i must just exec a query on db ? how Red hat check the query ?
    thanks

    • Hi, thanks for your kind words, I’m glad that you found the website useful.

      To answer your question, you need to be able to write simple SQL queries. For example, imagine a scenario where you have to create a new database called “test”. How would you do that? You have to know some basic SQL, e.g.:

      mysql> create database test;

      Now, you can run the query in a MySQL shell (as shown above), or you can store it in a file query.sql, and call it from bash:

      $ mysql -u root -p < query.sql

      You should get the idea. If that was the exam question, RedHat would simply check for the presence of the database. It the database exists, then you get points, because it is evident that in order to create a database you must know some SQL.

  8. This information was incredibly helpful and completely accurate. Thank you so much for making it available.

  9. Great post! Thanks alot Mr Tomas. If I may ask a question, lets say for example that we have imported a .sql file to a db and we have to find how many users live in the same city.
    How would that query be? It probably needs a count(*) I think, any ideas?

    Best regards

    • You’re welcome!

      To answer your question, it depends on your database schema (or tables in particular). Without this info, it’s hard to tell.

  10. how to achieve this task ?
    Firewall should allow access to port 5555 from srv1.rhce.local only.

  11. Any opinions to this?
    When the task says “database should only be accessible from localhost” – What would you say:
    – skip-networking=0 AND bind-address=127.0.0.1
    – skip-networking=1

    I think the first one. localhost means over network and skip-networking=1 forces through socket.

Leave a Reply

Your email address will not be published. Required fields are marked *