DB-유

Installing MariaDB 10.3 on CentOS 7

· mariadb, centos, installation

The MariaDB 5.5 that CentOS 7 ships is ancient: no JSON functions, no window functions, no CTEs. I got tired of writing workarounds for it, so my newer boxes get MariaDB 10.3 from the official Yum repository, which adds all of that plus system-versioned tables. It's only slightly more work than the distro package. This is a fresh install on CentOS 7, including what happens to the stock mariadb-libs package that other software depends on. If you're upgrading from 5.5 instead, take a mysqldump --all-databases first and read the last note.

Add the MariaDB repository

Create /etc/yum.repos.d/MariaDB.repo:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1

I always pin 10.3 in the URL, so yum update brings minor releases but never jumps to a new major series behind my back.

Install

sudo yum install -y MariaDB-server MariaDB-client

Note the capitalization: the packages from the MariaDB repo are MariaDB-server and MariaDB-client, while the CentOS ones are mariadb-server and mariadb. Yum will report that MariaDB-compat obsoletes mariadb-libs; that is expected and keeps postfix and other dependents working.

mysqld --version
# mysqld  Ver 10.3.12-MariaDB for Linux on x86_64 (MariaDB Server)

Start it

sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb

The unit is mariadb.service; a mysql.service alias also exists. Unlike MySQL 5.7, the installation does not create a temporary root password. Instead root@localhost authenticates through the unix_socket plugin, so sudo mysql works immediately and mysql -u root -p from an ordinary user does not.

Secure it

sudo mysql_secure_installation

Press Enter for the current root password (there is none), then set one, remove anonymous users, disallow remote root login, drop the test database and reload privileges. Setting a root password on 10.3 turns off unix_socket for that account, which is fine on a server where scripts use /root/.my.cnf anyway.

Config

MariaDB on CentOS reads /etc/my.cnf, which includes everything under /etc/my.cnf.d/. Put server settings into /etc/my.cnf.d/server.cnf in the [mysqld] section:

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log_error = /var/log/mariadb/mariadb.log
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_file_per_table = 1
max_connections = 300
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 1

Create the log directory and restart:

sudo mkdir -p /var/log/mariadb
sudo chown mysql:mysql /var/log/mariadb
sudo systemctl restart mariadb

Changing innodb_log_file_size is safe on 10.3; InnoDB resizes the redo log on a clean shutdown and restart.

Firewall and remote access

Skip this if only local clients connect. Otherwise:

sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

And make sure bind-address is not set to 127.0.0.1 in any file under /etc/my.cnf.d/. Then create application users limited to their subnet rather than opening root.

Smoke test

mysql -u root -p -e "SELECT VERSION(); SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW PLUGINS;" | head -20

Try one of the features that motivated the upgrade:

WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 5)
SELECT n, SUM(n) OVER (ORDER BY n) AS running FROM seq;

Five rows back means you're on 10.2 or later.

Problems

  • Error: Package: MariaDB-server-10.3.12-1.el7.centos.x86_64 ... Requires: galera-4 – The 10.3 repo depends on the galera package from the same repository; make sure the repo file's baseurl is correct and run yum clean all before retrying.
  • ERROR 1698 (28000): Access denied for user 'root'@'localhost' when running mysql -u root -p as a regular user – Root still uses unix_socket. Either sudo mysql, or run mysql_secure_installation to set a password.
  • [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 5.5 – You installed 10.3 over an existing 5.5 data directory without a clean shutdown. Stop, restore the old binaries, shut down cleanly (innodb_fast_shutdown=0), then reinstall.
  • Job for mariadb.service failed and the log shows [ERROR] mysqld: File '/var/log/mariadb/slow.log' not found (Errcode: 13 "Permission denied") – Directory ownership or SELinux label. chown mysql:mysql and restorecon -Rv /var/log/mariadb.

Upgrading in place from the stock 5.5 is possible (yum remove mariadb-server, install from the repo, then mysql_upgrade), but a jump across that many major versions makes me nervous. For anything important I dump, install 10.3 fresh, and reload.