Upgrading from MariaDB 10.3.23 to a later release
mysqladmin version -p
SELECT host, user FROM mysql.user;
show grants for 'root'@'localhost';
A new installation of MariaDB 10.3.24
A new installation of MariaDB 10.5.5
The Upgrade Journey
MariaDB 10.4 uses an improved authentication model. It uses operating system credentials for the root account rather than requiring a separate root db user.
It’s recommended that you backup the MariaDB databases before proceeding. You can use phpMyAdmin to accomplish this. Once you’ve completed the backup, proceed with the following steps.
Upgrade from MariaDB 10.3.23
- Stop MariaDB
service mysql-server stop
2. Remove MariaDB 10.3
pkg delete mariadb103-server and its dependencies
3. Install MariaDB 10.4
pkg update and
pkg install mariadb104-server
service mysql-server start
So far, so good. However, attempting to connect to the site
barry.udance.com.au results in this error:
This FreeBSD forum post Can’t connect to mysql database after upgrading to mariadb 10.4.13_3 provided the clue. After extensive experimentation, to get past the error, I needed to add the line
mysqli.default_socket = /var/run/mysql/mysql.sock to
/usr/local/etc/php.ini and the restart PHP services
service php-fpm restart.
It’s now possible to connect to the site. However, there’s still the requirement to do a database upgrade between MariaDB revisions. Here’s the second stumbling block.
To get around this, I need to supply the root db password. It’s clear for this that the original authentication scheme is still in play.
Observed Upgrade Behaviour
In summary, this is what I’ve observed:
- When installing MariaDB 10.4 (WP installation script 2.x), the new passwordless authentication scheme is the default.
- When installing MariaDB 10.3 (WP installation script 1.x), the legacy password authentication scheme is used.
- When upgrading from MariaDB 10.3 to a newer release, the legacy password authentication scheme is retained.
To enable a password for the db root account:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abracadbra');
To enable passwordless access to MariaDB:
mysql> ALTER USER [email protected] IDENTIFIED VIA unix_socket;
- Upgrading from MariaDB 10.3 to MariaDB 10.4
- Authentication from MariaDB 10.4
- User Accounts Created by Default
- Changes and Improvements in MariaDB 10.4
- mysql.global_priv Table
- Fix MariaDB Plugin ‘unix_socket’ is not loaded Error on Ubuntu 17.04 | 17.10
- How to Install MariaDB on FreeBSD
- How to see/get a list of MySQL/MariaDB users accounts
- Can’t connect to mysql database after upgrading to mariadb 10.4.13_3