Open4
mysql(ubuntu22.04) からneon(Postgres)への移行したときのメモ
yuna小さなプロジェクトでMySQLを利用していましたが、
随時DBが稼働している訳ではないため、コスト削減目的で、最近GA(Generaly Available: ユーザーに安定した形で提供される段階)となったserverless Postgresのneonに移行することにしました。
MysqlからPostgresの移行については、pgloaderというマイグレーションツールが便利です。
CSVなどのデータ出力を行う必要はなく直接Postgresにデータをimportします。
Mysql,Postgres間の異なるデータ型についての自動キャストなど、
マイグレーションに必要なステップを全て内包していて自動化できるとても便利なツールです。
neonのドキュメントに、このpgloaderを使ったマイグレーションの方法も詳しく載っているので、
基本的に、このドキュメントに沿って作業した内容を書きます。
yuna準備
①neonのアカウントを作成
②Mysqlで移行したいデータベース名と同じ名前で、neon上に空のデータベースを作成
※neonデータベース作成方法はこちら
※pgloaderの細かいオプション指定はこちら。
特に、Mysql独自機能に依存している場合は、確認必要かもしれません。
Mysqlに接続するための認証情報を集めます
Hostname or IP address
Database name
Username
Password
neonに接続するためのconnection文字列を確認、準備します。
例：
postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require
上記のconnection文字列だと、pgloaderで利用する一部のPostgres ドライバーでうまく動作しないため、回避策として、neonのconnectionの@より前の部分を、endpointキーワードを使ってを以下のように変更します（endpointとして、元の接続文字列のus-east-2の前の文字列を追記する）
postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require
※これについての詳細はこちら。
このようなエラーが出ることがあるようです
connectionエラーが発生するいくつかのパターン（例えば、GoのPosgresドライバで起こる事象などもあるようです）
pgloaderをインストール
私の環境はubuntuなので、aptでインストール
sudo apt install pgloader
pgloaderの設定ファイルを作成
pgloader設定ファイル（config.loadなど）を作成します。
MySQL データベースの認証情報を使用して、データベース・ソースの接続文字列を定義します。
前のステップで取得し変更したNeon データベースconnection文字列を宛先として使用します。
※intoの最後に; を付けるのを忘れずに
config.load
load database
from mysql://user:password@host/source_db?sslmode=require
into postgres://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require;
pgloaderコマンドを実行
コマンド実行して、以下のようなログが出ればマイグレーション成功です。
$ pgloader config.load
LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 2 0.727s
Create Schemas 0 0 0.346s
Create SQL Types 0 0 0.178s
Create tables 0 2 0.551s
Set Table OIDs 0 1 0.094s
----------------------- --------- --------- --------- --------------
"db-test".dbname 0 1 0.0 kB 0.900s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.905s
Index Build Completion 0 1 0.960s
Create Indexes 0 1 0.257s
Reset Sequences 0 0 1.083s
Primary Keys 0 1 0.263s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.169s
Set Search Path 0 1 0.427s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 1 0.0 kB 4.064s
ハマったポイント
pgloaderコマンド実行時のエラー
$pgloader config.load
2024-04-27T08:46:01.027999Z LOG pgloader version "3.6.3~devel"
2024-04-27T08:46:02.707928Z LOG Migrating from #<MYSQL-CONNECTION mysql://<username>@localhost:3306/<databasename> {1007FA4373}>
2024-04-27T08:46:02.707928Z LOG Migrating into #<PGSQL-CONNECTION pgsql://<username>@ep-tight-recipe-xxxxxxx.ap-southeast-1.aws.neon.tech:5432/<databasename> {1007FA6103}>
2024-04-27T08:46:02.763926Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "<username>": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
2024-04-27T08:46:02.763926Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch meta data 0 0 0.000s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
ハマったポイント
以下、解決までの試行錯誤メモ
pgloaderコマンド実行時のエラー（user名、db名などマスキングしてます）
$pgloader config.load
2024-04-27T08:46:01.027999Z LOG pgloader version "3.6.3~devel"
2024-04-27T08:46:02.707928Z LOG Migrating from #<MYSQL-CONNECTION mysql://<username>@localhost:3306/<databasename> {1007FA4373}>
2024-04-27T08:46:02.707928Z LOG Migrating into #<PGSQL-CONNECTION pgsql://<username>@ep-tight-recipe-xxxxxxx.ap-southeast-1.aws.neon.tech:5432/<databasename> {1007FA6103}>
2024-04-27T08:46:02.763926Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "<username>": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
2024-04-27T08:46:02.763926Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch meta data 0 0 0.000s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
移行元のDBは、ローカルにあるMysql ver8です。
config.loadのfromでローカルDBを指定していました。
load database
from mysql://<username>:<password>@localhost/<dbname>
エラーの内容をchatGPTで調べてみると
このエラーメッセージは、pgloaderがMySQLの認証方式をサポートしていないことを示しています。MySQL 8.0以降ではデフォルトの認証プラグインが「caching_sha2_password」に変更されたため、それ以前のバージョンのpgloaderと互換性がない可能性があります。
解決策としては、MySQLの認証方式を「mysql_native_password」に変更するか、pgloaderの新しいバージョンにアップデートすると良いでしょう。ただし、安全性の面から考えると後者のアップデートが推奨されます。
具体的な手順は以下の通りです:
- MySQLの認証方式を変更する場合:
- MySQLサーバーにログインします。
- 次のSQLコマンドを実行します:
ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';- MySQLサーバーを再起動します。
- pgloaderをアップデートする場合:
- pgloaderの公式ウェブサイトまたはGitHubリポジトリから最新バージョンをダウンロードします。
- ダウンロードしたファイルを解凍し、インストール手順に従ってアップデートを行います
とのこと。
まず、pgloaderをソースからビルドして最新を使って再挑戦した。
pgloaderをソースコードからビルドして最新で再実行
（公式を参考に進めました） （pgloaderはcommon lisp製とのこと)
# ビルドに必要なモジュールを入れる
$ sudo apt install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
# 最新コードをダウンロードして、ディレクトリに移動
$ git clone https://github.com/dimitri/pgloader.git
$ cd pgloader
# ビルド実行
$ make save
#確認(元のバージョン)
$ pgloader --version
pgloader version "3.6.3~devel"
compiled with SBCL 2.1.11.debian
#ビルドしたバージョン
$ ./build/bin/pgloader --version
pgloader version "3.6.2079646"
compiled with SBCL 2.1.11.debian
再度実行してみる
$ ./pgloader/build/bin/pgloader config.load
2024-04-27T09:39:08.707990Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "<username>": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
エラーは変わらず。
Mysqlの認証を変える方法で再実行
$ sudo mysql -u root
ALTER USER '<username>'@'%' identified with mysql_native_password by '<password>'';
#mysqlを再起動して再実行
$ sudo systemctl restart mysql
2024-04-27T09:49:18.220019Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "<username>": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
まだエラーが解消せず。
ローカルビルド版でもNG。
./pgloader/build/bin/pgloader config.load
RROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "<username>": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
my.cnfの設定に追加
pgloaderのissueを参考に、mysqlのconfigにも記述を入れて、再起動してもう一度挑戦した。
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
#設定を追加
default-authentication-plugin = mysql_native_password
$ sudo systemctl restart mysql
./pgloader/build/bin/pgloader config.load
2024-04-27T10:04:34.152854Z LOG report summary reset
table name errors rows bytes total time
--------------------------- --------- --------- --------- --------------
fetch meta data 0 16 0.172s
Create Schemas 0 0 0.576s
Create SQL Types 0 0 0.296s
Create tables 0 16 7.132s
Set Table OIDs 0 8 0.156s
--------------------------- --------- --------- --------- --------------
xxxx.xxxxx 0 145 41.5 kB 1.600s
xxxx.xxxxx 0 10 2.8 kB 1.492s
xxxx.xxxxx 0 0 2.904s
xxxx.xxxxx 0 1 0.2 kB 3.000s
xxxx.xxxxx 0 22 2.9 kB 0.296s
xxxx.xxxxx 0 0 0.224s
xxxx.xxxxx 0 0 1.612s
xxxx.xxxxx 0 1 0.1 kB 1.676s
--------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 3.288s
Create Indexes 0 8 3.384s
Index Build Completion 0 8 12.572s
Reset Sequences 0 0 2.028s
Primary Keys 0 8 3.288s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.276s
Set Search Path 0 1 0.684s
Install Comments 0 0 0.000s
--------------------------- --------- --------- --------- --------------
Total import time ✓ 179 47.5 kB 25.521s
無事成功しました！