AmazonLinuxのMariaDBを10.1から10.2にバージョンアップ

# 概要
MariaDBを10.1から10.2にアップデートした際のメモです。

## yumでやってみる

- 現在の設定を確認

```bash
$ sudo vi /etc/yum.repos.d/MariaDB.repo
```

- アップデートしたいバージョンにURLを変更
- 今回は`10.1`の部分を`10.2`に書き換えました。

```/etc/yum.repos.d/MariaDB.repo
# MariaDB 10.1 CentOS repository list - created 2016-03-13 07:22 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64  # ここを変える
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
```

- 現在インストールされているパッケージを確認

```bash
$ rpm -qa | grep -i mariadb
```

- アップデート

```bash
$ sudo yum install -y MariaDB-server MariaDB-client
```

__実行結果__

```bash
Loaded plugins: priorities, update-motd, upgrade-helper
amzn-main                                                          | 2.1 kB  00:00:00
amzn-updates                                                       | 2.3 kB  00:00:00
mariadb                                                            | 2.9 kB  00:00:00
mariadb/primary_db                                                 |  42 kB  00:00:00
89 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.1.43-1.el6 will be updated
---> Package MariaDB-client.x86_64 0:10.2.30-1.el6 will be an update
---> Package MariaDB-server.x86_64 0:10.1.43-1.el6 will be updated
---> Package MariaDB-server.x86_64 0:10.2.30-1.el6 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================
 Package                 Arch            Version                   Repository        Size
==========================================================================================
Updating:
 MariaDB-client          x86_64          10.2.30-1.el6             mariadb           49 M
 MariaDB-server          x86_64          10.2.30-1.el6             mariadb          112 M

Transaction Summary
==========================================================================================
Upgrade  2 Packages

Total download size: 161 M
Downloading packages:
(1/2): MariaDB-10.2.30-centos6-x86_64-client.rpm                   |  49 MB  00:00:09
(2/2): MariaDB-10.2.30-centos6-x86_64-server.rpm                   | 112 MB  00:00:20
------------------------------------------------------------------------------------------
Total                                                     8.0 MB/s | 161 MB  00:00:20
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : MariaDB-client-10.2.30-1.el6.x86_64                                    1/4

******************************************************************
A MySQL or MariaDB server package (MariaDB-server-10.1.43-1.el6.x86_64) is installed.

Upgrading directly from MySQL 10.1 to MariaDB 10.2 may not
be safe in all cases.  A manual dump and restore using mysqldump is
recommended.  It is important to review the MariaDB manual's Upgrading
section for version-specific incompatibilities.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf
  files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages.  Usually this command will
  list the packages you should remove:
  rpm -qa | grep -i '^mysql-'

  You may choose to use 'rpm --nodeps -ev <package-name>' to remove
  the package which contains the mysqlclient shared library.  The
  library will be reinstalled by the MariaDB-shared package.
- Install the new MariaDB packages supplied by MariaDB Foundation
- Ensure that the MariaDB server is started
- Run the 'mysql_upgrade' program

This is a brief description of the upgrade process.  Important details
can be found in the MariaDB manual, in the Upgrading section.
******************************************************************
error: %pre(MariaDB-server-10.2.30-1.el6.x86_64) scriptlet failed, exit status 1
Error in PREIN scriptlet in rpm package MariaDB-server-10.2.30-1.el6.x86_64
error: MariaDB-server-10.2.30-1.el6.x86_64: install failed
error: MariaDB-server-10.1.43-1.el6.x86_64: erase skipped
  Cleanup    : MariaDB-client-10.1.43-1.el6.x86_64                                    3/4
  Verifying  : MariaDB-client-10.2.30-1.el6.x86_64                                    1/4
  Verifying  : MariaDB-client-10.1.43-1.el6.x86_64                                    2/4
  Verifying  : MariaDB-server-10.2.30-1.el6.x86_64                                    3/4
MariaDB-server-10.1.43-1.el6.x86_64 was supposed to be removed but is not!
  Verifying  : MariaDB-server-10.1.43-1.el6.x86_64                                    4/4

Updated:
  MariaDB-client.x86_64 0:10.2.30-1.el6

Failed:
  MariaDB-server.x86_64 0:10.1.43-1.el6       MariaDB-server.x86_64 0:10.2.30-1.el6

Complete
```

手動でアップデートしろと言われた

## 手動アップデート

### 1. バックアップ
- 一応データベースとmy.confのバックアップをとります

```bash
$ sudo cp /etc/my.cnf /etc/my.cnf.org
```

### 2. MariaDB停止

```bash
$ sudo service mysql stop
Shutting down MariaDB... SUCCESS!
```

### 3. 古いMariaDBを削除

- インストールされているMariaDBを確認(上でupdateを試行しているので、clientだけ10.2になっています。)

```bash
$ yum list installed | grep maria
MariaDB-client.x86_64                10.2.30-1.el6                 @mariadb
MariaDB-common.x86_64                10.1.43-1.el6                 @mariadb
MariaDB-compat.x86_64                10.1.43-1.el6                 @mariadb
MariaDB-server.x86_64                10.1.43-1.el6                 @mariadb
galera.x86_64                        25.3.28-1.rhel6.el6           @mariadb
```

- 今回は10.1から10.2にするので、`MariaDB-common.x86_64`を消す

```bash
$ sudo yum remove MariaDB-common.x86_64
```

- 消えたか確認

```bash
$ yum list installed | grep maria
galera.x86_64                        25.3.28-1.rhel6.el6           @mariadb
```

依存してる物は全部消えるので、MariaDB周りが全部消えました。
結果的に、ver.10.1関連が消えました。

### 4. 新しいMariaDBをインストール

```bash

$ sudo yum install -y MariaDB-server MariaDB-client
```

__実行結果__

```bash
Loaded plugins: priorities, update-motd, upgrade-helper
89 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.2.30-1.el6 will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.2.30-1.el6.x86_64
---> Package MariaDB-server.x86_64 0:10.2.30-1.el6 will be installed
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.2.30-1.el6 will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.2.30-1.el6.x86_64
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:10.2.30-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================
 Package                 Arch            Version                   Repository        Size
==========================================================================================
Installing:
 MariaDB-client          x86_64          10.2.30-1.el6             mariadb           49 M
 MariaDB-server          x86_64          10.2.30-1.el6             mariadb          112 M
Installing for dependencies:
 MariaDB-common          x86_64          10.2.30-1.el6             mariadb          173 k
 MariaDB-compat          x86_64          10.2.30-1.el6             mariadb          4.0 M

Transaction Summary
==========================================================================================
Install  2 Packages (+2 Dependent packages)

Total download size: 166 M
Installed size: 699 M
Downloading packages:
(1/4): MariaDB-10.2.30-centos6-x86_64-common.rpm                   | 173 kB  00:00:01
(2/4): MariaDB-10.2.30-centos6-x86_64-compat.rpm                   | 4.0 MB  00:00:01
(3/4): MariaDB-10.2.30-centos6-x86_64-client.rpm                   |  49 MB  00:00:10
(4/4): MariaDB-10.2.30-centos6-x86_64-server.rpm                   | 112 MB  00:00:13
------------------------------------------------------------------------------------------
Total                                                      10 MB/s | 166 MB  00:00:16
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-common-10.2.30-1.el6.x86_64                                    1/4
  Installing : MariaDB-compat-10.2.30-1.el6.x86_64                                    2/4
  Installing : MariaDB-client-10.2.30-1.el6.x86_64                                    3/4
  Installing : MariaDB-server-10.2.30-1.el6.x86_64                                    4/4
/usr/sbin/semodule: SELinux policy is not managed or store cannot be accessed.
  Verifying  : MariaDB-compat-10.2.30-1.el6.x86_64                                    1/4
  Verifying  : MariaDB-common-10.2.30-1.el6.x86_64                                    2/4
  Verifying  : MariaDB-server-10.2.30-1.el6.x86_64                                    3/4
  Verifying  : MariaDB-client-10.2.30-1.el6.x86_64                                    4/4

Installed:
  MariaDB-client.x86_64 0:10.2.30-1.el6       MariaDB-server.x86_64 0:10.2.30-1.el6

Dependency Installed:
  MariaDB-common.x86_64 0:10.2.30-1.el6       MariaDB-compat.x86_64 0:10.2.30-1.el6

Complete!
```

- インストールされたか確認

```bash
$ yum list installed | grep maria
MariaDB-client.x86_64                10.2.30-1.el6                 @mariadb
MariaDB-common.x86_64                10.2.30-1.el6                 @mariadb
MariaDB-compat.x86_64                10.2.30-1.el6                 @mariadb
MariaDB-server.x86_64                10.2.30-1.el6                 @mariadb
galera.x86_64                        25.3.28-1.rhel6.el6           @mariadb
```

10.1から10.2にバージョンが変わっていることが確認できました。

### 5. 起動

- バックアップしておいたconfを再設定

```bash
$ sudo mv /etc/my.cnf.org /etc/my.cnf
```

- MariaDB起動

```bash
$ sudo service mysql start
```

## ログインできなくなった場合

- アップデート後にログインはおろか、`mysql --version`もできなくなった
- `mysql.user`テーブルが初期化されたっぽい(アプリケーションのデータベースは残ってるのに、、)

```bash
ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: NO)
```

- セーフモードで起動


```bash
$ sudo mysqld_safe --skip-grant-tables &
$ mysql -uroot
```
ログイン後は以下の方法で良しなにユーザー権限を与える。

  __既にあるユーザのパスワードを変更__
  
```sql
  > UPDATE mysql.user SET authentication_string = PASSWORD('your_password') WHERE User = 'your_username' AND Host = 'localhost';
  ```

  __パスワード付きでユーザーを作成__

  ```sql
  > grant all privileges on *.* to your_username@localhost identified by 'your_password' with grant option;
  ```

  __パスワードなしでユーザーを作成__
  
```sql
  > flush privileges;
  > CREATE USER your_username@localhost
  ```

- MariaDB再起動

```
$ sudo service mysql restart
```

# 参考
https://yassu.jp/pukiwiki/index.php?MySQL%20767byte%CC%E4%C2%EA

https://qiita.com/rururu_kenken/items/5ec05d50eae8796ca96a

https://teratail.com/questions/145770

https://hacknote.jp/archives/37540/