LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@mah_orz(mah ann)

Redmine 3.x w/ MySQL 5.7 のバージョン更新

Last updated at Posted at 2024-12-31

目的

プロジェクト管理用に redmine:3-passenger を使用していたが、あまりに古いのと、画像の貼り付けができないなど利便性が低いためバージョン更新する。

また redmine official image では MySQL:5.7 を使用してコンテナ起動する例が示されているが、MySQL 8.0: MySQL 5.7よりも最大2倍高速とあるので、この際 MySQL も更新する。

MySQL アップグレード

mysql 8.0 に アップグレード

Upgrade check

一旦 mysql:5.7 で起動し、コンテナ内で Upgrade Check する。

## 移行用データ作成
$ sudo cp -R mysql mysql8.0

## mysql:5.7 起動
$ docker run -d --rm --name mysql -e MYSQL_USER=redmine -e MYSQL_PASSWORD=secret -e MYSQL_DATABASE=redmine -e MYSQL_RANDOM_ROOT_PASSWORD=******** \
 -v ./mysql8.0:/var/lib/mysql \
 mysql:5.7

## コンテナの中に入る
$ docker exec -it mysql bash

## upgrade check
bash-4.2# mysqlsh localhost -p -e "util.checkForServerUpgrade();"
Please provide the password for 'root@localhost': ********
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): 
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.44 - MySQL
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.0.35...

1) Usage of old temporal type
  No issues found

2) MySQL 8.0 syntax check for routine-like objects
  No issues found

3) Usage of db objects with names conflicting with new reserved keywords
  Warning: The following objects have names that conflict with new reserved
    keywords. Ensure queries sent by your applications use `quotes` when
    referring to them or they will result in errors.
  More information:
    https://dev.mysql.com/doc/refman/en/keywords.html

  redmine.users.admin - Column name

4) Usage of utf8mb3 charset
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Partitioned tables using engines with non native partitioning
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  Notice: The following DB objects have obsolete options persisted for
    sql_mode, which will be cleared during upgrade to 8.0.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals

  global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
    option

10) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

11) Usage of partitioned tables in shared tablespaces
  No issues found

12) Circular directory references in tablespace data file paths
  No issues found

13) Usage of removed functions
  No issues found

14) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

15) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

16) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

17) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

18) Zero Date, Datetime, and Timestamp values
  No issues found

19) Schema inconsistencies resulting from file removal or corruption
  No issues found

20) Tables recognized by InnoDB that belong to a different engine
  No issues found

21) Issues reported by 'check table x for upgrade' command
  No issues found

22) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations. 
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:
    
    [mysqld]
    default_authentication_plugin=mysql_native_password
    
    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

23) Columns which cannot have default values
  No issues found

24) Check for invalid table names and schema names used in 5.7
  No issues found

25) Check for orphaned routines in 5.7
  No issues found

26) Check for deprecated usage of single dollar signs in object names
  No issues found

27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7
  No issues found

28) Check for deprecated '.<table>' syntax used in routines.
  No issues found

29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.
  No issues found

Errors:   0
Warnings: 2
Notices:  1

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

特にエラーは出ていない。

Inplace Upgrade

Redmine環境の更新に関する記事などを読むと多くの場合 MySQL データを一旦 mysqldump でエクスポートしてから新しい環境にインポートする手順が示されているが、Upgrade Paths にあるとおり 5.7 から 8.0 へのアップグレードがサポートされているため mysql:8.0 を指定して起動することで更新できる。

Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.

単にコンテナイメージの対象を mysql:8.0 に変更して起動するだけ。

$ docker run --name mysql --rm -e MYSQL_USER=redmine -e MYSQL_PASSWORD=secret -e MYSQL_DATABASE=redmine -e MYSQL_RANDOM_ROOT_PASSWORD=1 \
 -v ./mysql8.0:/var/lib/mysql \
 mysql:8.0

mysql:8.4 に アップグレード

mysql:8.0 にアップグレードした状態で mysql:8.4 を起動することでアップグレードできる。

$ docker run --name mysql --rm -e MYSQL_USER=redmine -e MYSQL_PASSWORD=secret -e MYSQL_DATABASE=redmine -e MYSQL_RANDOM_ROOT_PASSWORD=P@ssw0rd \
  -v ./mysql8.4:/var/lib/mysql \
  mysql:8.4

だが、これで redmine を動かすと以下の mysql_native_password エラーが出てしまう。

redmine         | ActiveRecord::ConnectionNotEstablished: Plugin 'mysql_native_password' is not loaded (ActiveRecord::ConnectionNotEstablished)

これに対処するには以下のいずれか。

ユーザパスワードを caching_sha2_password に更新

ALTER USER '<USERNAME>'@'<HOST>' IDENTIFIED WITH caching_sha2_password BY '<PASSWORD>';

mysql_native_password を有効にする

mysqlコンテナの起動オプションに --mysql_native_password=ON を追加する。

docker-compose.yml:

services:
  mysql:
    command: mysqld ... --mysql_native_password=ON

mysql:9 に アップグレード

mysql:8.4 にアップグレードした状態で mysql:9 を起動することでアップグレードできる。

mysql:9 では mysql_native_password が廃止されたため、ユーザパスワードの caching_sha2_password 更新が必須となる。
したがって mysql:9 にアップグレードする前に 8.4 の段階でユーザパスワードを更新しなければならない。

以下のようにして redmine 管理ユーザパスワードを sha2 にすることで redmine 起動できるようになる。

### mysql:8.4 データを作業用にコピー
$ sudo cp -R mysql8.4 mysql9

### 一旦mysql:8.4 で `--mysql_native_password=ON` 指定して起動
$ docker run --name mysql --rm -d -e MYSQL_USER=redmine -e MYSQL_PASSWORD=secret -e MYSQL_DATABASE=redmine -e MYSQL_RANDOM_ROOT_PASSWORD=P@ssw0rd \
 -v ./mysql9:/var/lib/mysql  mysql:8.4 --mysql_native_password=ON

### コンテナに入る
$ docker exec -it mysql bash

### database 接続
bash-5.1# mysql -p

### redmineユーザのパスワードをSHA2に更新
mysql> ALTER USER 'redmine' IDENTIFIED WITH caching_sha2_password BY 'redmine';

オープンなサービスならともかく、プライベートなサービスで MySQL 9以上に更新してセキュリティ向上するのは利便性と比較して割に合わないかもしれない。

Redmine アップグレード

Redmine:4 へのアップグレード

単に redmine のイメージバージョンを上げるだけで更新が可能。

docker-compose.yml:

services:
  redmine:
    #image: redmine:3-passenger
    image: redmine:4

Redmine:5 へのアップグレード

database adapter 修正

事前にdatabase adapter のconfig修正が必要。

redmine/config/database.yml:

production:
  adapter: mysql2   # "mysql2" のようにダブルクォートで囲われている箇所を外す

docker-compose.yml に適用したconfigを適用。

services:
  redmine:
    volumes:
      - ./redmine/config/database.yml:/usr/src/redmine/config/database.yml

plugin整理

動かないpluginについては削除したほうがよい。

以下は redmine:5 では動かない plugin。

  • redmine_datetime_custom_field

イメージ更新

後は redmine:4同様にredmine のイメージバージョンを上げるだけで更新が可能。

docker-compose.yml:

services:
  redmine:
    #image: redmine:3-passenger
    image: redmine:5

redmine:6 へのアップグレード

Redmine:5 へのアップグレードと同様の手順で更新できる。

docker-compose.yml:

services:
  redmine:
    #image: redmine:3-passenger
    image: redmine:6
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?