MariaDBでレプリケーション
データベースバックアップのためにVPSサーバーを新規に構築し、MariaDBでレプリケーションを構成した時の覚書です。
2022/3追記
MariaDBでは、ポリコレでバージョン10.5.2から従来SLAVE(スレーブ)、MASTER(マスター)としていた表記の一部を、(REPLICA)レプリカと(PRIMARY)プライマリという表現に変更しています。
それに合わせてコマンドも、たとえば「SHOW SLAVE STATUS」は「SHOW REPLICA STATUS」に、「SHOW MASTER STATUS」は「SHOW BINLOG STATUS」とったように変わっています。
このページもそれに合わせて表記を変更しています。
設定ファイル
MariaDBのサーバー用の設定ファイルはデフォルトで/etc/mysql/mariadb.conf.d/50-server.cnfとなってます。レプリケーションを作成しようという話を読んでいただいているので、通常稼働用の設定はできていると思いますので、そのあたりは省略させていただきます。
レプリケーションにはバイナリログが使われるのでその出力設定を記述します。ちなみにmariaDB内の設定ファイル内では-(半角ハイフン)と_(半角アンダーバー)はどちらを使ってもいいそうです。この記述では_に統一しています。
親の50-server.cnf
[mysqld] server_id=1001 log_bin=/var/log/mysql/mysql-bin max_binlog_size =100M expire_logs_days=10 binlog_format=MIXED
server_idは0~4294967295までの値を指定できます。0や1番とかだとデフォルト値と重なる可能性があるので少し大きな値にします。
log_binはスレーブサーバーに配布するためのバイナリログの出力場所を指定します。上のように設定するとmysql-bin.123456という形で、連番のついたファイルが作成されます。
max_linlog_sizeで新しいファイルを作る基準のサイズを指定できますが、必ずこのサイズになるとは限りません。再起動で別のファイルになったり、処理の途中では切り替わらないので大きなファイルになったりします。
expire_logs_daysでバイナリログを保存しておく日数を指定します。
binlog_formatはSTATEMENT、ROW、MIXEDがあります。
STATMENTはSQL文を保存します。データ量を削減できるのですが、AUTO_INCREMENTを利用したテーブルにおいて、プライマリとレプリカで同じ値であるという保証ができません。AUTO_INCREMENTはそれぞれの環境に応じた値を取得するからです。これに対しては、行内容単位でバイナリログを取得するROWモードを利用することで回避できます。これはログが大きくなるので、MIXEDというSTATEMENTとROWを混在させるモードもあります。
次に子サーバーです。子サーバーでは親のIDとは別のIDを付けます。孫サーバーを持ちたい場合は親サーバーと同じ設定項目の他に「log_slave_update」を加えます。これを記述しないと親から伝わった更新は子のバイナリログには出力されません。
孫サーバーが不要なら子サーバーはserver_idを指定するだけでいいです。
子の50-server.cnf
[mysqld] server_id=1020 #以下はオプション log_bin=/var/log/mysql/mysql-bin max_binlog_size = 100M expire_logs_days=10 binlog_format=MIXED log_slave_update
レプリケーション用のユーザーの作成
親サーバーに、レプリケーションを取得するユーザーを登録します。root等、GRANT権限のあるユーザーで処理してください。
MariaDB[]> CREATE USER 'rep-user'@'192.168.1.0/255.255.255.0' identified by 'password'; MariaDB[]> GRANT REPLICATION REPLICA ON *.* TO 'rep-user'@'192.168.1.0/255.255.255.0'
記述が終わったら両方のサーバーを再起動します。
子サーバーの初期データセット(正攻法)
子サーバーに初期データをセットします。まずは親サーバーをロックして全データを取得する正攻法を紹介します。
- テーブルのロック
「FLUSH TABLES WITH READ LOCK;」を発行して、テーブルをロックします。これ以降すべてのテーブルがロックされますので注意してください。
MariaDB[]> FLUSH TABLES WITH READ LOCK; - バイナリログの位置を確認
「SHOW BINLOG STATUS;」でバイナリログの位置を確認します。
MariaDB[]> SHOW BINLOG STATUS;バイナリログの設定がされていないと、何も表示されませんので注意してください。
Fileで示されるファイル名と、Positionで示される場所(値)を記憶しておきます。この後もテーブルのロックは続けます。
- データ出力
別のウインドウから親サーバーにアクセスします。この時LOCKしているウインドウを閉じるとロックが解除されてしまいますのでそのままの状態にしておきます。
次のコマンドで全データを出力します。このコマンドはMariaDBコンソールからではないことに注意してください。
> mysqldump -u <ユーザー名> -p --all-databases --lock-all-tables>dump.dataDebianなら/home/user/dump.data、Windowsならc:¥dump.data等わかる場所に出力してください。
BLOBカラムを使っていたり文字化けが心配なら「--default-character-set=binary」をコマンドに追加しておくといいと思います。
- ロック解除
データ出力が終わったらロックを解除します。最初に実行した方の画面で「UNLOCK TABLES;」を実行します。
MariaDB[]> UNLOCK TABLES;ここまで終えたら親サーバーへの MariaDB接続は切断して構いません。
- データコピー
出力したdump.dataを子サーバーに移動させます。
手間なら省略できますが、大きなファイルでコピーミスはないかを確認するためにハッシュをとっておきます。
Windowsなら
> certutil -hashfile dump.data MD5Debianなら
$ md5sum dump.dataとし、出力された文字列が親と子で一致するかチェックします。
- 子サーバーでインポート
移動させたファイルを子サーバーでインポートします。dump時に--dafault-character-set=binaryを指定して出力したらここでも指定してください。
mysql -u root -p<dump.data - 同期開始
子サーバーのMariaDBコンソールから次のコマンドを入力して、親サーバーのどこのポイントから同期をとるかを指定します。
MariaDB[]> CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='rep-user',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin000001',MASTER_LOG_POS=1;MASTER_HOSTには親サーバーのIPアドレスを、MASTER_USER、MASTER_PASSWORDには先ほど親サーバーで作成したユーザーとそのパスワードを、MASTER_LOG_FILE、MASTER_LOG_POSには「SHOW BINLOG STATUS」実行時にメモした値をセットします。
ちなみにサーバーのIPアドレスや、ユーザーパスワードは一度設定すれば、以降はLOG_FILEとPOSだけの指定でかまいません。
「START REPLICA」で同期を開始させます。ちなみに同期を止めるときは「STOP REPLICA」となります。
MariaDB[]> START REPLICA;稼働状況を確認するには、SHOW REPLICA STATUSコマンドを子側のMariaDBコンソールで実行します。
MariaDB[]> SHOW REPLICA STATUS;長い結果が返ってきますが、「Slave_IO_Running」と「Slave_SQL_Running」の値がともに「Yes」になっていれば問題ないと思います。横長の出力が見づらい場合はコマンドの最後に ¥G;とすることで、項目別の出力に変わります。
もう少ししっかり確認するなら、親側で「SHOW BINLOG STATUS」で表示されたファイル名と位置、子側で「SHOW REPLICA STATUS」で表示された親ファイル名と位置が、一致するか確認します。
親のデータベースを止められない場合
ストレージエンジンがInnoDBの場合、テーブルロックしないでデータを取得する方法もあります。
ただし、ストレージエンジンがMyISAMである「mysql」データーベースは対象外となります。そのため、このデータベースは一貫性を持たせるのをあきらめて後からmysql_upgaradeコマンドを利用して修正します。
それでは方法です。まず、InnoDBのデータベースを抽出します。single-transactionオプションを利用することでテーブルにロックがかからなくなります。
Bオプションで対象となるデータベースをすべてを指定します。mysqlデータベース以外の対象のデータベースをすべて記述します。
「information_schema」と「performance_schema」データベースに関しては対象としません(これらのデータベースは--all-databasesを指定しても出力されません)。testデータベースは使っていないようなら削除するか、後述する方法で同期の対象から外してください。「mysql」データベースは別途dumpしますのでこれも除外します。
master-dataは同期を始めるファイル名と位置を出力するオプションです。これも併せて指定します。mastar-dataは値を与えないか1を設定すると、SQL文として出力され、データ取り込み時に実行されます。2とするとコメントとして書き込まれます。ここではmysql_upgradeを実行した後に同期を始めるので、2としています。
> mysqldump -u username -p --single-transaction -B dbname --master-data=2 --quick>dump.normal.data ... > mysqldump -u username -p mysql >dump.system.data ...
出力して子サーバーにコピーしたら、まずmysqlデータベースを取り込みます。その後、mysql_upgradeを実行してmysqlデータベースに異常がないかチェックします。滅多なことはないと思いますが、mysqlデータベースが壊れてしまったら、「mysql_install_db」コマンドで初期化することができます。この時設定ファイルも初期化されてしまいますので、バクアップをとっておいてください。
> mysql -u username -p mysql <dump.system.data ... > mysql_upgrade -u username ...
mysql_upgradeでエラーがないか、処理の過程で修正できていたら、通常のデータを取り込みます。終了したら念のためmysql_upgradeを再度実行します。
> mysql -u username -p mysql <dump.normal.data ... > mysql_upgrade -u username ...
問題なければ、dump.normal.dataのコメントからバイナリログのファイル名と位置を取得します。Debianなら、cat dump.data | grep 'CHANGE MASTER'とするとでてきます。1行出てきたら後はCtrl+Cで、breakして構いません。(この部分のMASTERは筆者の環境では変更されていませんでした、将来変更されるかもしれません)
Windowsの場合は次のような.vbsスクリプトを使って先頭の部分だけ抽出したあとメモ帳で読み込みます。元の出力ファイルが小さければそのままメモ帳で開いてもいいです。
extract.vbs
option explicit
Dim fs
Set fs =WScript.CreateObject("Scripting.FileSystemObject")
dim APP_PATH
APP_PATH =fs.getfile(WScript.ScriptFullName).ParentFolder.path
'終了行
const ENDROW = 30
'ファイル名
constDUMP_FILE_NAME="dbdump.data"
'出力ファイル名
const OUT_FILE_NAME="dbdump.head.txt"
'エントリー
callmain()
sub main()
dim lngCnt
dim objFileI
dim objFileO
dimstrLine
Set objFileI = fs.OpenTextFile(APP_PATH & "\" & DUMP_FILE_NAME,1,true)
SetobjFileO = fs.OpenTextFile(APP_PATH & "\" & OUT_FILE_NAME,2,true)
lngCnt = 0
do untilobjFileI.atEndOfStream
lngCnt = lngCnt+1
strLine =objFileI.readLine
objFileO.writeLine strLine
if lngCnt >= ENDROWthen
exit do
endif
loop
objFileO.close
objFileI.close
end sub
メモ帳を開いて上記内容をコピー&ペーストして、貼り付けます。名前を、extract.vbsにしてデスクトップに保存します。MariaDBから抽出したデータをデスクトップにコピーして名前をdbdump.dataにします。extract.vbsをダブルクリックすると、dbdump.head.txtができますのでこれをメモ帳で開いて中身を確認します。
同期するファイル名や位置がわかったら、あとは先ほどと同じように、子サーバーへコピーして、取り込み、CHANGE MASTERのコマンドを入力します。
一部のテーブルだけを同期させたい場合
一部のテーブルだけを同期させたい場合は、データベース単位でバイナリログの出力の有無の指定ができます。50-server.cnfのbinlog関連のオプションでbinlog-do-db(対象を指定)やbinlog-ignore-db(非対象を指定)を使います。複数データベースを記述する際は複数行にします。
同期対象としないテーブルのバイナリログを出力すると、子側で不整合を検知して同期が止まることもありますので気を付けてください。
# 対象を指定する場合 binlog-do-db=dbname1 binlog-do-db=dbname2 # 非対象を指定する場合 binlog-ignore-db=dbname3 binlog-ignore-db=dbname4
doの方を指定すると指定しないデータベースはすべて対象外となります。ignoreを指定した場合は指定したデータベース以外は対象となります。両方を指定すると、doだけ指定した時と同じ挙動でした。ただし、出力有無の判断はカレントデータベース単位で行うようで、dbname.tablenameといったようにデータベース名を付けた更新では対象外のデータベースでもログが出力されました。
Row size too largeの問題
子サーバーに取り込む際に、筆者の場合Row size too large (> 8126)から始まるエラーに遭遇しました。
この件については、公式ページのTroubleshooting Row Size Too Large Errors with InnoDBに載っていましたが、MariaDB 10.2.2からInnoDB Strict Modeというパラメータがデフォルト値がONで加わったの原因でした。
InnoDB Strict ModeがONの時は、新規作成や変更時にチェックを行い上限を超える可能性のあるテーブル設計の場合はエラーとなります。
ちなみにこの上限の目安はinnodb_page_sizeの約半分ぐらいだということです。
ということで、50-server-confの[mysqld]で、innodb_strict_modeをOFFにします。
[mysqld] ... innodb_strict_mode=0 ...
先の公式ページに詳細がありますが、これをONのままで対応したい場合は行フォーマットを変更するといいそうです。
レプリケーションでSSL
今度は、レプリケーション用の通信を暗号化してみます。暗号化するにはCHANGE MASTERの設定を加える必要があります。
また、レプリケーションデータを暗号化するには、事前にプライマリ側のMariaDBをSSL化しておく必要があります。
レプリケーションデータの暗号化を構成する際のパターンとしては、
- 証明書を検証しない方法
- プライマリ側の証明書を検証する方法
- プライマリとレプリカ側両方の証明書を検証する方法
の3つあります。それぞれの方法を紹介していきますが、前述したCHANGE MASTERコマンドでSSL以外の設定は済んでいる前提です。また、CHANGE MASTERコマンド前後のSTOP REPLICAとSTART REPLICAも省略しています。
まず、一番簡単などちらの証明書も検証をしない方法です。
次にプライマリ側のサーバーの証明書を検証する方法です。この場合、プライマリ側のサーバーの証明書を発行したCAの証明書が必要になるので、レプリカ側にコピーします。ここでは/etc/mysql/ca.crtにコピーしたと仮定します。
最後にプライマリとレプリカの両方向で証明書の検証をする方法です。この場合レプリカ側にも証明書を発行する必要があります。方法はあるのかもしれまんが筆者が試してみたところ、証明書を発行するCAはプライマリと同じでないと動きませんでした(CAPATHに複数のCAの証明書を入れる方法を試してみましたがうまく動きませんでした)。
caの証明書をca.crt、レプリカ用に作成した証明書をr.crt、秘密鍵をr.keyとして、すべてレプリカ側の/etc/mysqlディレクトリに存在しているものとします。
注意が必要なのはOpenSSLを使って作成した秘密鍵(r.key)は通常rootユーザーしか読み取り権限がありませんが、MariaDBを動かしている権限で読み込めるようにする必要があります。たとえば、r.keyのオーナーをmysqlにします。
処理中のセッションの切断対策
大きなデータベースのdumpやimportは時間がかかります。ssh経由でそのような処理をする場合、作業中にネットワークの切断があると処理は止まってしまいます。そこでsshセッションからそれらの処理を実行する場合は、nohupを使ってバックグラウンド実行する事で、セッションが切断されても処理を継続させることができます。
このnohupのかわりにtmuxを使う方法もあります。こちらはセッションを丸ごと保存しておけますので、処理中にメッセージがでて中断するといった事象にも対応できます。
tmuxはターミナルのマルチプレクサで、ひとつのウインドウで複数のターミナルを処理できるものです。この機能のひとつにデタッチがあり、途中で接続が失われてもセッションを維持できるようになっています。
この部分の操作だけを簡単に書いておきます。詳細は先の公式ページのリンクをを参考にしてください。
- インストール
aptを使ってtmuxをインストールします。
# apt install tmux - 起動
いろいろな起動の仕方がありますが、ここでは-sオプションでセッション名を付けて起動させます。起動すると既存のターミナルから、tmuxのターミナルに切り替わります。
$ tmux new -s セッション名
- デタッチ
ネットワーク障害なのでtmux起動中にターミナルが切断された場合そのセッションは残りますが、デタッチは意図的にセッションを残したままtmuxから抜ける方法です。
実行すると標準のターミナルに戻ります。
$ tmux detach
- tmuxセッションの確認
既存のtmuxセッションを確認するには次のようにします。ここで先ほど名前を付けたセッション名が出力されます。
$ tmux list-sessions セッション名 ...
- アタッチ
再びtmuxのセッションに接続します。
$ tmux attach -t セッション名
- tmuxセッションの終了
tmuxセッションから、exitやlogoutコマンドを実行するとそのtmuxセッションが終了します。
$ tmux exit
tmuxはユーザー権限で起動できますが、一度管理者権限で作成したセッション一覧をユーザー権限で見ようとするとエラーになるので注意してください。