DB-유

MariaDB 10.5 Release Highlights for Administrators

· mariadb, mysql, upgrade

I waited for 10.5.8 before touching production. 10.5 has been GA since 10.5.4 in the summer, and the early bugs seem shaken out now. Most release write-ups list developer features; I care about what changes for whoever runs the server: renamed binaries, a privilege split that breaks scripts, InnoDB defaults that moved, and the upgrade steps on CentOS 7 and Ubuntu 18.04.

Before you begin: a server on 10.3 or 10.4 with a fresh backup (mariabackup or mysqldump --all-databases --single-transaction), the MariaDB Foundation repository rather than the distribution package so you can pick the exact version, and a maintenance window. The upgrade is a restart plus mysql_upgrade, usually a few minutes.

Renamed binaries

10.5 ships every client and server binary under a mariadb- name, with the old mysql names kept as symlinks:

mysqld           -> mariadbd
mysql            -> mariadb
mysqldump        -> mariadb-dump
mysqladmin       -> mariadb-admin
mysql_upgrade    -> mariadb-upgrade
mysqlbinlog      -> mariadb-binlog
mysql_install_db -> mariadb-install-db
mariabackup      (unchanged)

The symlinks mean existing scripts keep working. The visible difference is in process listings and error logs, where you now see mariadbd, and in systemctl status mariadb showing /usr/sbin/mariadbd. Monitoring checks that grep for mysqld need adjusting; I hit that on the first host.

Privilege changes

The SUPER privilege has been split into fine-grained ones: BINLOG ADMIN, BINLOG MONITOR, BINLOG REPLAY, CONNECTION ADMIN, FEDERATED ADMIN, READ_ONLY ADMIN, REPLICATION MASTER ADMIN, REPLICATION SLAVE ADMIN, SET USER. REPLICATION CLIENT is now an alias for BINLOG MONITOR, and SHOW MASTER STATUS requires it. mysql_upgrade maps existing SUPER grants so accounts keep working, but new accounts for tooling should get the specific privilege rather than SUPER:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'strong-password';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'backup'@'localhost';

CREATE USER 'repl'@'10.20.30.%' IDENTIFIED BY 'strong-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.20.30.%';

Also new: SHOW PRIVILEGES lists them all, and GRANT ... TO PUBLIC does not exist yet, so per-user grants remain.

InnoDB defaults that moved

Several InnoDB settings have new defaults or are deprecated. From SHOW VARIABLES on a 10.4 server compare:

  • innodb_adaptive_hash_index now defaults to OFF. Workloads with heavy point lookups on a hot index may see a small drop; enable it explicitly if you measured a benefit before.
  • innodb_checksum_algorithm defaults to full_crc32 for new tablespaces. Existing files stay on crc32 and are still read fine.
  • innodb_buffer_pool_instances, innodb_page_cleaners, innodb_log_files_in_group and several other variables are deprecated and ignored; a my.cnf still setting them logs a warning at startup, not an error.
  • innodb_flush_method gained O_DIRECT as the default on Linux in 10.5.
  • The innodb_log_files_in_group change means there is exactly one redo log file, ib_logfile0, sized by innodb_log_file_size.

A minimal [mysqld] block on a 10.5 server on CentOS 7 (/etc/my.cnf.d/server.cnf):

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
innodb_buffer_pool_size = 8G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
max_connections = 500
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
log_error = /var/log/mariadb/mariadb.log
slow_query_log = ON
long_query_time = 1

Smaller things

  • The INET6 data type stores IPv6 (and IPv4-mapped) addresses in 16 bytes with proper comparison, replacing VARBINARY(16) workarounds.
  • INSERT ... RETURNING and REPLACE ... RETURNING return the inserted rows, handy for applications that used LAST_INSERT_ID() round trips.
  • JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregate functions arrived.
  • The S3 storage engine can archive read-only tables to object storage; it is a plugin, not loaded by default.
  • ColumnStore is available as a storage engine plugin in the same server rather than a separate distribution.
  • Performance Schema was updated to be closer to MySQL 5.7's instrumentation.
  • Binary log: binlog_row_metadata and binlog_expire_logs_seconds were added; expire_logs_days still works.

Upgrading on CentOS 7

Point the repository at 10.5 (/etc/yum.repos.d/MariaDB.repo):

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

sudo systemctl stop mariadb
sudo yum remove -y MariaDB-server MariaDB-client
sudo yum install -y MariaDB-server MariaDB-client MariaDB-backup
sudo systemctl start mariadb
sudo mysql_upgrade
sudo systemctl restart mariadb

Removing the old packages first is what the MariaDB documentation recommends for major upgrades on RPM systems; the data directory is untouched. On Ubuntu 18.04, switch the apt source to 10.5 under http://mirror.../mariadb/repo/10.5/ubuntu bionic main, then apt update && apt install mariadb-server, which runs mysql_upgrade for you.

Replication is supported from a 10.4 primary to a 10.5 replica, so upgrade replicas first, then the primary.

After the restart

SELECT VERSION();
SHOW GLOBAL VARIABLES LIKE 'innodb_checksum_algorithm';
SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW ENGINES;
SELECT user, host, JSON_DETAILED(priv) FROM mysql.global_priv WHERE user='backup';

Expect 10.5.8-MariaDB, full_crc32, OFF, InnoDB as default, and the privileges stored as JSON in mysql.global_priv, which replaced mysql.user as the real table in 10.4. Check /var/log/mariadb/mariadb.log for [Warning] ... deprecated lines and clean your configuration accordingly.

If it goes sideways

  • mysql_upgrade prints Phase 1/7: Checking and upgrading mysql database ... Error: Table 'mysql.global_priv' doesn't exist when run against a server that is not actually 10.5. The old binary is still in PATH, or the service did not restart. Confirm with mariadb-admin version.
  • Startup fails with [ERROR] mariadbd: unknown variable 'innodb_log_files_in_group=2'. Only a handful of removed variables cause a hard failure; delete the line. Most deprecated ones only warn.
  • Replication from 10.5 to an older replica breaks with Unknown system variable errors. Downstream replicas must be at least the version of the primary. Upgrade in the right order.
  • Application monitoring shows SHOW MASTER STATUS failing with Access denied; you need (at least one of) the BINLOG MONITOR privilege(s). Grant BINLOG MONITOR to the monitoring user; REPLICATION CLIENT alone is no longer enough on accounts created after the upgrade.

The privilege split and the InnoDB defaults matter most to me; the renames are cosmetic thanks to the symlinks. Try your my.cnf against a 10.5 container before touching production.