MySQLからMariaDBに移行

MySQL 8.0からMariaDB 11.4に移行したメモ。思ったよりいろいろと大変だった……。

環境
背景
移行作業手順
参考

環境

FreeBSD 14.1
nginx 1.26.1
MySQL 8.0.35
MariaDB 11.4.2

背景

MySQLを8.0から8.4にアップグレードしようと作業を始めたところ、認証プラグインのデフォルトが変更されていたりして、さくっとは動きそうもなかった。

どうせハマるなら、この機会にMariaDBに移行してしまえ！　というわけで、移行決定。

移行完了してみたら、はっきり体感できるほどWordPressの表示が速くなった。こんなに違うとは。移行してよかった。

移行作業手順

MySQLデータベースをバックアップ

mysqldumpで全データをバックアップ。

# mysqldump -u root -p --all-databases --set-gtid-purged=OFF --triggers --routines --events --source-data > allbackup.sql

--master-dataを指定するとobsoleteなオプションだと文句を言われたので、指示に従って--source-dataに変更してみた。

ユーザ情報や権限を新規登録し直すつもりなら、データベースのみバックアップしてもよい。

# mysqldump -u root -p --databases --set-gtid-purged=OFF DB1 DB2 DB3 > dbbackup.sql

MySQLをアンインストール

バックアップが完了したら、MariaDBをインストールする前に、まずMySQLをアンインストールする。

# sudo pkg delete mysql80-server

不要になったリポジトリも削除しておく。もちろん、ディスクの空き容量に余裕があるなら、念のためMariaDBが正常に動作するまで残しておくほうが安全。

# sudo rm /var/db/mysql

MariaDBをインストール

次に、portmasterでMariaDBをインストール。

# sudo portmaster databases/mariadb114-server

my.cnfを書き換える。/usr/local/etc/mysql/my.cnfには、MySQL用に作成したものがそのまま残されているので、念のため名前を変更してしばらく残しておく。

# cd /usr/local/etc/mysql
# sudo cp my.cnf my.cnf.mysql80
# sudo cp my.cnf.sample my.cnf

このmy.cnfは、conf.dの下にある拡張子が.cnfのファイルをすべて読み込むだけの内容となっている。conf.dの下のファイルを編集し、必要に応じて旧my.cnfの内容を反映させる。基本的にはserver.cnfを編集するだけでいい、はず。必要ならclient.cnf.sampleをclient.cnfにコピーして編集する。

設定ファイルの編集が終わったら、SQLサーバを起動。

# sudo service mysql-server start

データベースをインポート

バックアップしてあったデータベースをインストールする。

# sed -e '/@@GLOBAL.GTID_PURGED=/d' \
-e -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g' allbackup.sql | \
sudo mysql -u root -p

パスワードには何も入力せずにリターンキーを押す。初期状態では、root権限でmysqlを実行すると、パスワードなしで実行できる。MariaDBへのインポート時には、エラーが出るものを、sedを使って削除したり置換したりしておく。

インポートしたら、ユーザ情報を確認する。ユーザ情報まで含めてインポートしているので、mariadb-secure-installationを使った初期化は行わない。

MariaDB [(none)]> SELECT user,host,plugin FROM mysql.user;
+------------------+-----------------+-----------------------+
| user             | host            | plugin                |
+------------------+-----------------+-----------------------+
| root             | 127.0.0.1       | caching_sha2_password |
| root             | ::1             | caching_sha2_password |
| mysql.infoschema | localhost       | caching_sha2_password |
| mysql.session    | localhost       | caching_sha2_password |
| mysql.sys        | localhost       | caching_sha2_password |
| root             | localhost       | caching_sha2_password |
| user1            | localhost       | caching_sha2_password |
+------------------+-----------------+-----------------------+
7 rows in set (0.008 sec)

MySQL 8.0ではデフォルトの認証プラグインがcaching_sha2_passwordだったが、MariaDBのサーバではcaching_sha2_passwordがサポートされていない。caching_sha2_passwordの認証プラグインは存在するが、これはMariaDBクライアントがMySQLサーバに接続するためのものでしかない。

cf. Authentication Plugin – SHA-256 (MariaDB Server Documentation)

なので、MariaDBでデフォルトのmysql_native_passwordに変更しておく。なお、MariaDBでパスワード管理のセキュリティレベルを上げたい場合には、ed25519の利用が推奨されている。

cf. Authentication Plugin – ed25519 (MariaDB Server Documentation)

こちらは当然、MariaDBサーバでもサポートされている。設定ファイルにplugin_load_add = auth_ed25519の行を追加するか、mysqlで接続してINSTALL SONAME 'auth_ed25519';を発行してインストールして利用する。

認証プラグインを変更するにあたっては、まずはFLUSH PRIVILEGESを発行しておく。これをしないと、ALTER USERの実行時にエラーが出るため。

MariaDB [(none)]> FLUSH PRIVILEGES;

mysql.infoschema、mysql.session、mysql.sysは不要なので削除しておく。

MariaDB [(none)]> DROP USER mysql.infoschema;

ALTER USERを使用して、ひとつずつ認証プラグインを変更する。

MariaDB [(none)]> ALTER USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password USING PASSWORD('password');

すべて変更し終わったら、再度確認。

MariaDB [(none)]> SELECT user,host,plugin FROM mysql.user;
+------------------+-----------------+-----------------------+
| user             | host            | plugin                |
+------------------+-----------------+-----------------------+
| root             | 127.0.0.1       | mysql_native_password |
| root             | ::1             | mysql_native_password |
| root             | localhost       | mysql_native_password |
| user1            | localhost       | mysql_native_password |
+------------------+-----------------+-----------------------+
4 rows in set (0.005 sec)

rootにパスワードを設定したので、この後はスーパーユーザでなくてもmysqlを実行できるようになる。

PHPの設定

MySQLとはデフォルトのsocketの場所が異なるので、/usr/local/etc/php.iniを編集して、次のように位置を指定する。

mysqli.default_socket = /var/run/mysql/mysql.sock

php-fpmを再起動して、設定を反映させる。

Webページにアクセスして、WordPessがきちんと動いていることを確認する。

環境

背景