MySQL-5.5へのMySQL-8.0(Aurora3.0)のレプリケーション

@aucfan-yotsuya です。
とある事情により表題の作業をするにあたって、ハマったポイントをば。

## 選定したAuroraバージョン

- mysql-5.5.25に対してレプリケーションを(結果的に)張れたAuroraは、以下の通り。

- Aurora (MySQL 5.7) 2.10.1
- Aurora MySQL 3.01.0 (compatible with MySQL 8.0.23

今回Aurora-3.01.0を選定。
公式にサポートされているわけではありません。腕力です。

## システム構成

- 現master/slave構成にもう一台、Aurora-3.01.0(新規)によるSlaveを増設します。

![db](https://imgur.com/xU4iw9W.jpg?1)

---

## ステートメントごとの動作と使用状況

MySQL-5.5のステートメント、特に更新系での影響について、DDL、DML、DCLに立てわけ調査しました。

参考:

https://docs.oracle.com/cd/E19957-01/mysql-refman-5.5/sql-syntax.html#flow-control-constructs

分類|Statement Syntax|使用
:--|:--|:--
SQL Statement Syntax|ALTER DATABASE|o
SQL Statement Syntax|ALTER EVENT|-
SQL Statement Syntax|ALTER FUNCTION|o
SQL Statement Syntax|ALTER PROCEDURE|o
SQL Statement Syntax|ALTER SERVER|-
SQL Statement Syntax|ALTER TABLE|o
SQL Statement Syntax|ALTER VIEW|o
SQL Statement Syntax|CREATE DATABASE|o
SQL Statement Syntax|CREATE EVENT|-
SQL Statement Syntax|CREATE FUNCTION|o
SQL Statement Syntax|CREATE INDEX|o
SQL Statement Syntax|CREATE PROCEDURE / CREATE FUNCTION|o
SQL Statement Syntax|CREATE SERVER|-
SQL Statement Syntax|CREATE TABLE|o
SQL Statement Syntax|CREATE TRIGGER|o
SQL Statement Syntax|CREATE VIEW|o
SQL Statement Syntax|DROP DATABASE|o
SQL Statement Syntax|DROP EVENT|o
SQL Statement Syntax|DROP FUNCTION|o
SQL Statement Syntax|DROP INDEX|o
SQL Statement Syntax|DROP PROCEDURE / DROP FUNCTION|o
SQL Statement Syntax|DROP SERVER|-
SQL Statement Syntax|DROP TABLE|o
SQL Statement Syntax|DROP TRIGGER|o
SQL Statement Syntax|DROP VIEW|o
SQL Statement Syntax|RENAME TABLE|-
Data Manipulation Statements|CALL|o
Data Manipulation Statements|DELETE|o
Data Manipulation Statements|DO|-
Data Manipulation Statements|HANDLER|o
Data Manipulation Statements|INSERT|o
Data Manipulation Statements|LOAD DATA INFILE|o
Data Manipulation Statements|LOAD XML|-
Data Manipulation Statements|REPLACE|o
Data Manipulation Statements|SELECT|o
Data Manipulation Statements|Subquery|o
Data Manipulation Statements|TRUNCATE TABLE|o
Data Manipulation Statements|UPDATE|o
MySQL Transactional and Locking Statements|START TRANSACTION, COMMIT, / ROLLBACK|o
MySQL Transactional and Locking Statements|Statements That Cannot Be Rolled Back|-
MySQL Transactional and Locking Statements|Statements That Cause an Implicit Commit|-
MySQL Transactional and Locking Statements|SAVEPOINT and ROLLBACK TO SAVEPOINT|o
MySQL Transactional and Locking Statements|LOCK TABLES and UNLOCK TABLES|o
MySQL Transactional and Locking Statements|SET TRANSACTION|o
MySQL Transactional and Locking Statements|XA Transactions|-
Database Administration Statements|Account Management Statements|o
Database Administration Statements|Table Maintenance Statements|o
Database Administration Statements|Plugin and User-Defined Function Statements|-
Database Administration Statements|SET|o
Database Administration Statements|SHOW|o
Database Administration Statements|Other Administrative Statements|o
Replication Statements|SQL Statements for Controlling Master Servers|o
Replication Statements|SQL Statements for Controlling Slave Servers|o
SQL Syntax for Prepared Statements|PREPARE|o
SQL Syntax for Prepared Statements|EXECUTE|o
SQL Syntax for Prepared Statements|DEALLOCATE PREPARE|-
SQL Syntax for Prepared Statements|Automatic Prepared Statement Repreparation|-
MySQL Compound-Statement Syntax|BEGIN ... END Compound Statement|o
MySQL Compound-Statement Syntax|DECLARE|o
MySQL Compound-Statement Syntax|Variables in Stored Programs|o
MySQL Compound-Statement Syntax|Conditions and Handlers|o
MySQL Compound-Statement Syntax|Cursors|o
MySQL Compound-Statement Syntax|Flow Control Constructs|o
MySQL Compound-Statement Syntax|RETURN|o
MySQL Compound-Statement Syntax|SIGNAL and RESIGNAL|-
MySQL Utility Statements|DESCRIBE|o
MySQL Utility Statements|EXPLAIN|o
MySQL Utility Statements|HELP|-
MySQL Utility Statements|USE|o

---

## レプリケーションで問題のあった構文

- 定義されている構文の中で、検証環境で試行して問題があった命令は以下のものでした。
	- DCL
		- Account Management Statements
			- GRANT / SET PASSWORD FOR
	- DDL
		- CREATE PROCEDURE / CREATE FUNCTION
		- CREATE TRIGGER
		- CREATE VIEW
	- DML
		- LOAD XML (未使用)
		- current_timestamp() / now()

- それらを動作不良別にまとめます。

---

### DCL周りの問題

- mysqlスキーマーの構造が異なることにも起因しますが、いわゆるmysql-5.5時代に通っていた以下のような構文、

```sql
grant all privileges on *.* to '新規アカウント'@'localhost' with grant option identified by 'パスワード';
```

とりわけアカウント作成/権限付与/パスワード設定の一括指定がmysql8系ではSQL Syntax Errorとなります。

#### 解決方法
- レプリケーション張る前にAuroraでcreate user/set password/grantを個別に実行。

### DDL周りの問題

- Auroraに対して `definer = @'localhost'` 権限でprocedure/trigger/viewの作成をどうするか？

#### 解決方法

Auroraへのdump流し込み用のmysql-5.5.25サーバー(疑似master)を建て、@'localhost'アカウントでdumpを流し込みます。
Aurora→疑似masterに対してレプリケーションを張ります。

![db](https://imgur.com/2uyNdzx.jpg?1)

その際、使用するmysqldumpコマンドオプションですが、 `--dump-slave` オプションを使用することで、slaveサーバーからmasterのbinlogポジションを得ることができるため、流し込んだ状態からmasterにレプリケーションを張り替えることが可能です。

```
mysqldump --single-transaction --flush-logs --hex-blob --default-character-set=binary --dump-slave -CER --databases {データベース名...}'
```

mysqldumpデータの22行目 change master to 構文。

```sql
-- Host: localhost    Database: schema
-- ------------------------------------------------------
-- Server version       5.5.25-log

(略)

--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000000', MASTER_LOG_POS=1234567;
```



注意点としては、mysqldumpを走らせている間slaveは、レプリケーションが停止状態になります。
(Slave_SQL_Running=No)

mysqldumpコマンドを途中で止めてしまった場合、レプリケーションが復旧しませんので、 `show slave status` でチェックし、止まっているときは、 `start slave` します。

```
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
(略)
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
```


疑似masterへの流し込みとレプリケーション同期が完了したら、Auroraのレプリケーションをmasterに貼り直すことで作業完了となります。

```sql
call mysql.rds_stop_replication;
call mysql.rds_reset_external_source;
call mysql.rds_set_external_source('masterのIP', 3306, 'レプリケーションアカウント', 'パスワード', 'mysql-bin.000000', 1234567, 0);
call mysql.rds_start_replication;
```

![db](https://imgur.com/JteHAsl.jpg?1)

---

### DML周りの問題

- current_timestamp() / now() でレプリケーションするとUTCで保存される問題。

masterのtime_zone変数

```
show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)
```

Auroraのtime_zone変数

```
MySQL [(none)]> show variables like '%time_zone%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | UTC        |
| time_zone        | Asia/Tokyo |
+------------------+------------+
2 rows in set (0.03 sec)
```

このときmasterのbinlogは、

```
SET @@session.time_zone='SYSTEM'/*!*/;
INSERT INTO xxxx.xxxxx(x,x,x,x) VALUES ( x,x,x ) ON DUPLICATE KEY UPDATE last_update = CURRENT_TIMESTAMP()
/*!*/;
```

各サーバーで `select current_timestamp()` した場合は、Asia/Tokyoの時刻で応答されますが、レプリケーション時 masterの time_zone = `SYSTEM` が利用されるため、AuroraではUTCで保存されます。

#### 解決方法

- masterのtime_zone変数を変更します。

```
mysql> SET GLOBAL time_zone = '+09:00';
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | +09:00 |
+------------------+--------+
2 rows in set (0.01 sec)
```

変更後のbinlog

```
SET @@session.time_zone='+09:00'/*!*/;
INSERT INTO xxxx.xxxxx(x,x,x,x) VALUES ( x,x,x ) ON DUPLICATE KEY UPDATE last_update = CURRENT_TIMESTAMP()
/*!*/;
```

---

### その他：パラメーターグループで解決した問題

今後レプリケーション時にエラーになる可能性のある構文は、Auroraクラスターのパラメーターグループ: replica-skip-errors 変数に設定しました。

`1045,1051,1064,1217,1396,1827,3730`

- 1045

```
Error number: 1045; Symbol: ER_ACCESS_DENIED_ERROR; SQLSTATE: 28000
Message: Access denied for user '%s'@'%s' (using password: %s)
```

- 1051

```
Error number: 1051; Symbol: ER_BAD_TABLE_ERROR; SQLSTATE: 42S02

Message: Unknown table '%s'
```

- 1064

```
Error number: 1064; Symbol: ER_PARSE_ERROR; SQLSTATE: 42000

Message: %s near '%s' at line %d
```

- 1217
    - foreign keyを張ったテーブルでの問題。

```
Error number: 1217; Symbol: ER_ROW_IS_REFERENCED; SQLSTATE: 23000

Message: Cannot delete or update a parent row: a foreign key constraint fails

InnoDB reports this error when you try to delete a parent row that has children, and a foreign key constraint fails. Delete the children first.
```

- 1396
    - DDL系の問題。

```
Error number: 1396; Symbol: ER_CANNOT_USER; SQLSTATE: HY000

Message: Operation %s failed for %s
```

- 1827
    - DCL系の問題。パスワード設定が同期できない問題。

```
Error number: 1827; Symbol: ER_PASSWORD_FORMAT; SQLSTATE: HY000

Message: The password hash doesn't have the expected format.
```


- 3730
    - foreign keyを張ったテーブルの削除順序がmysql-5.5.25と異なるため。

```
Error number: 3730; Symbol: ER_FK_CANNOT_DROP_PARENT; SQLSTATE: HY000

Message: Cannot drop table '%s' referenced by a foreign key constraint '%s' on table '%s'.

ER_FK_CANNOT_DROP_PARENT was added in 8.0.12.
```

---

他に未知の問題が発生するかもしれないですが...
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
そのときは運用でカバーですね()