Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
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?

More than 1 year has passed since last update.

オークファングループAdvent Calendar 2021

Day 19
@aucfan-yotsuya(四谷 豊)in株式会社オークファン

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

Last updated at Posted at 2021-12-18

@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

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

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

参考:

分類 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時代に通っていた以下のような構文、
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

その際、使用する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 構文。

-- 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に貼り直すことで作業完了となります。

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

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.

他に未知の問題が発生するかもしれないですが...














































そのときは運用でカバーですね()

3
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
3
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?