RDS (Aurora MySQL) に引っ越す

そろそろDBをVPSからクラウドへ移行しようと思う。移行先は最近頻繁に証明書期限が切れるから更新しろとメールの来るRDS。ずいぶんご無沙汰だから思い出しがてらさわることにする。

AWSマネジメントコンソールのRDSでデータベースを作成するが以下に注意する

• DBにアクセスするためのパスワードは作成時に1度表示されるらしい（見逃した）ので控えておく

• パスワードがわからなくなったらSecrets Managerの「シークレットの値を取得する」で確認できる（AIからインスタンスのアクションからのパスワード変更やAWS CLIによる方法を提案されたがどれもうまくいかず最終的にこの方法で確認した）

• データベース作成時に全てデフォルトの設定で進むとインスタンスタイプが恐ろしく高額なものになってしまうので慎重に選択する。証明書も期限切れが近いものになるので選び直す。

• デフォルトでパブリックアクセスはなしになっているがEC2を用意してなければパブリックアクセスを有効にしてIPで制限する

• データベース作成後に最初にすることはセキュリティグループのルールにアクセスするIPの追加。例えばMySQLWorkbenchでアクセスしたければ自分のグローバルIPを許可し、EC2を用意してなければVPSなどのIPからのアクセスを許可する必要がある。

• SSHでAuroraに直接接続することはできないのでVPSのMySQLクライアントからAuroraのエンドポイントにアクセスする

$ mysql -h <Auroraのエンドポイント> -u admin -p

データベースを作る

mysql> create database iwan;

確認

mysql> show databases;

終わる

mysql> exit

おそらく次にするのはDBのコピーと思う。まずはスキーマを作ってみる。

$ mysql -h <Auroraのエンドポイント> -u admin -p iwan < schema_20240627.sql

こんなエラーが出るだろう

ERROR 1118 (42000) at line 498: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

これで対応してみる

mysql> SET GLOBAL innodb_strict_mode = 0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

直接変更できないようだ。Auroraではパラメータグループで設定するらしいがinnodb_strict_modeが見当たらない。特定のテーブルのフィールド※が多すぎることが原因なのはわかっているので最終的にテーブル分割で対応した。
※FileMakerでは作り過ぎる傾向があるが他システムへの移行を考えると1テーブル（計算フィールドなどのワークフィールドを除いて）200以下に抑えた方がいいだろう。

スキーマでエラーが出ないことを確認したらVPSのDBをバックアップしてAuroraにアップロードする。

$ mysqldump --routines --events --triggers --max_allowed_packet=1G -u root -p iwan > dump_20240627_1455.sql
$ mysql -h <Auroraのエンドポイント> -u admin -p iwan < dump_20240627_1455.sql

アプケーションの接続先を変更して動作を確認。概ね動くがビューでエラーが発生している。

CREATE 
   ALGORITHM = UNDEFINED 
   DEFINER = `root`@`localhost` 
   SQL SECURITY DEFINER
VIEW `v_exams` AS
   SELECT 
       `m`.`fid` AS `fid`,
...
       `s`.`d20l` AS `d20l`
   FROM
       (`examm` `m`
       LEFT JOIN `exams` `s` ON ((`m`.`no` = `s`.`item_no`)))

localhostのrootでアクセスできないことが原因。以下に変更。

CREATE OR REPLACE
   ALGORITHM = UNDEFINED
   SQL SECURITY INVOKER
VIEW `v_exams` AS
   SELECT 
       `m`.`fid` AS `fid`,
...
       `s`.`d20l` AS `d20l`
   FROM
       (`examm` `m`
       LEFT JOIN `exams` `s` ON ((`m`.`no` = `s`.`item_no`)))

権限関係

mysql> GRANT SELECT ON v_exams TO 'admin'@'%';
mysql> GRANT SELECT ON examm TO 'admin'@'%';
mysql> GRANT SELECT ON exams TO 'admin'@'%';

改善した。ストアドファンクションでも同様の問題が発生。

CREATE DEFINER=`ymaki`@`%` FUNCTION `f_exams`(inPetID VARCHAR(50), inDate date) RETURNS int
BEGIN
 
 DECLARE n int;
 DECLARE hid VARCHAR(50);
 DECLARE f VARCHAR(50);
 DECLARE d date;
 DECLARE t time;
 SELECT COUNT(*) INTO n FROM examh WHERE pet_uid = inPetID AND impl_date <= inDate AND is_del <> 1;
...
 INSERT INTO exams (fid, item_no, pet_uid, d10r, d10h, d10l) VALUES (f, 0, inPetID, CONCAT(d, " ", LEFT(t, 5)), "", "") ON DUPLICATE KEY UPDATE d10r = CONCAT(d, " ", LEFT(t, 5)), d10h = "", d10l = "";
 RETURN 10;
END

こっちは再作成が必要。削除して、

DROP FUNCTION IF EXISTS f_exams;

作成する
※sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1267, "Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='") エラーが発生したのでその問題も対処している

CREATE FUNCTION `f_exams`(inPetID VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, inDate date)
   RETURNS int
   SQL SECURITY INVOKER
BEGIN
 
 DECLARE n int;
 DECLARE hid VARCHAR(50);
 DECLARE f VARCHAR(50);
 DECLARE d date;
 DECLARE t time;
 SELECT COUNT(*) INTO n FROM examh WHERE pet_uid = inPetID AND impl_date <= inDate AND is_del <> 1;
...
 INSERT INTO exams (fid, item_no, pet_uid, d10r, d10h, d10l) VALUES (f, 0, inPetID, CONCAT(d, " ", LEFT(t, 5)), "", "") ON DUPLICATE KEY UPDATE d10r = CONCAT(d, " ", LEFT(t, 5)), d10h = "", d10l = "";
 RETURN 10;
END

権限を付与

mysql> GRANT EXECUTE ON FUNCTION f_exams TO 'admin'@'%';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON examh TO 'admin'@'%';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON examd TO 'admin'@'%';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON exams TO 'admin'@'%';

タイムゾーンが問題だがAuroraの設定箇所が見つからない。DBはUTCのままでアプリケーション層で対応しろということらしい。今まで当たり前のように設定していたタイムゾーン。クラウド時代はユーザが世界中にいるからそりゃそうだ。あとでじっくり考えることにする。

CSVアップロードのシェルスクリプトが機能してないのでLOAD DATAにLOCALをつけて対応

use iwan;
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/accountd.csv' REPLACE INTO TABLE accountd FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r';
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/accounth.csv' REPLACE INTO TABLE accounth FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r';
...

シェルスクリプトが参照している~/.my.cnfに以下の行を追加

[mysql]
local-infile=1

正常に動作してFileMakerからデータ更新がかかることを確認した。

以上