DB-유

Upgrading MariaDB 10.3 to 10.4

· mariadb, upgrade, linux, mysql

10.4 isn't a huge release feature-wise, but it changed how root@localhost authenticates, and that broke a couple of my provisioning scripts. Before 10.4, mysql.user held one authentication method per account. From 10.4 it's a view over the new mysql.global_priv table, an account can have several authentication methods, and the packaged root account uses unix_socket and a password at the same time. Scripts that UPDATE mysql.user SET plugin = ... stop working, and whether password logins as root survive depends on how the account was set up before.

This is an in-place upgrade on CentOS 7 from the MariaDB yum repository (not a distribution package). On Ubuntu 18.04 it's the same with apt instead of yum. Before you start, have a backup you have actually restored (mariabackup --backup or mysqldump --all-databases --single-transaction --routines --events) and a maintenance window. The server restarts once, and mysql_upgrade can take a while on schemas with thousands of tables.

Read the changes first

Skim the "Upgrading from MariaDB 10.3 to MariaDB 10.4" page in the Knowledge Base. These are the items that mattered on my servers:

  • mysql.user becomes a view; direct writes to it fail.
  • Root uses unix_socket by default on new installs; on upgrades the existing method is kept but mysql_upgrade may add unix_socket as an alternative.
  • innodb_stats_sample_pages, innodb_idle_flush_pct and the innodb_locks_unsafe_for_binlog variable were removed or deprecated.
  • The default character_set_client handshake behaviour for utf8 is unchanged, but utf8 still means utf8mb3; nothing to do here, just do not expect a change.

Check your config for removed variables:

grep -E 'innodb_locks_unsafe_for_binlog|innodb_stats_sample_pages|innodb_idle_flush_pct' /etc/my.cnf /etc/my.cnf.d/*.cnf

Remove or comment them, or the server will refuse to start after the upgrade with unknown variable.

Prepare the server

I always do a slow shutdown so the redo log is fully applied and no change buffer entries are outstanding. A fast shutdown followed by a version change is a classic source of InnoDB: Upgrade after a crash is not supported errors.

SET GLOBAL innodb_fast_shutdown = 0;

If this is a replica, stop replication cleanly and note the position:

STOP SLAVE;
SHOW SLAVE STATUS\G

Then stop the server:

sudo systemctl stop mariadb
sudo tail -5 /var/log/mariadb/mariadb.log

The log should end with [Note] /usr/sbin/mysqld: Shutdown complete.

Swap the repository, upgrade the packages

Edit /etc/yum.repos.d/MariaDB.repo and change 10.3 to 10.4 in the baseurl:

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

MariaDB recommends removing and reinstalling the server packages rather than a straight yum update, because the package set changed between series:

sudo yum clean all
sudo yum remove MariaDB-server MariaDB-client MariaDB-common
sudo yum install MariaDB-server MariaDB-client MariaDB-backup

Removing the packages does not touch /var/lib/mysql or /etc/my.cnf.d. Your data and configuration remain. On Ubuntu the equivalent is to update the sources list entry to 10.4/ubuntu bionic main, then apt-get update && apt-get install mariadb-server.

Start it and run mysql_upgrade

sudo systemctl start mariadb
sudo mysql_upgrade

mysql_upgrade checks every table for incompatibilities and, critically for 10.4, migrates mysql.user into mysql.global_priv and replaces mysql.user with the view. Its output ends with OK per table and a final line Phase 7/7: Running 'FLUSH PRIVILEGES'. It writes /var/lib/mysql/mysql_upgrade_info so that a second run is a no-op unless you pass --force.

Because root on a MariaDB 10.3 package install already used unix_socket, sudo mysql_upgrade needs no password. If your root account used a password, pass -p.

If this was a replica, restart replication:

START SLAVE;

What happened to root?

See what the upgrade did:

SELECT User, Host, JSON_DETAILED(Priv) FROM mysql.global_priv WHERE User = 'root'\G

The plugin and authentication_string fields in the JSON tell you the primary method. To give root both socket authentication for admin scripts and a password for remote tools, 10.4 lets you set both in one statement:

ALTER USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('new-root-password');

Existing application accounts are unaffected; they keep their plugin and password.

After the upgrade

SELECT VERSION();
SHOW GLOBAL STATUS LIKE 'Uptime';
SELECT * FROM mysql.user LIMIT 1;  -- works, it is a view now
SHOW CREATE TABLE mysql.user\G      -- confirms VIEW
SHOW ENGINE INNODB STATUS\G

Check the error log for warnings introduced by the new version:

sudo grep -iE 'warning|error' /var/log/mariadb/mariadb.log | tail -20

Run the application's smoke tests, and confirm any monitoring, backup or replication scripts that connect as root still authenticate. On a replica, confirm Slave_IO_Running and Slave_SQL_Running are both Yes and Seconds_Behind_Master is shrinking.

Things that broke

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them typically appears when mysql_upgrade has not been run yet, or was interrupted. Run mysql_upgrade --force.

ERROR 1348 (HY000): Column 'plugin' is not updatable from an old provisioning script doing UPDATE mysql.user SET plugin=.... Replace it with ALTER USER ... IDENTIFIED VIA .... The view is read-only.

[ERROR] mysqld: unknown variable 'innodb_locks_unsafe_for_binlog=1' on start. Removed variable left in a config file; see the grep near the top.

ERROR 1698 (28000): Access denied for user 'root'@'localhost' from a tool that used to log in with a password over TCP. Root is now unix_socket only. Use the ALTER USER ... OR mysql_native_password statement above, or create a dedicated administrative account for the tool.

InnoDB: Upgrade after a crash is not supported and the server exits. The 10.3 shutdown was not clean. Reinstall the 10.3 packages, start the server, set innodb_fast_shutdown = 0, stop cleanly, and repeat the upgrade.

The upgrade itself takes a few minutes. The prep is what makes it boring, and boring is what I want from an upgrade.