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.data

Debianなら/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 MD5

Debianなら

$ 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も省略しています。

まず、一番簡単などちらの証明書も検証をしない方法です。

MariaDB []> CHANGE MASTER TO MASTER_SSL = 1;

次にプライマリ側のサーバーの証明書を検証する方法です。この場合、プライマリ側のサーバーの証明書を発行したCAの証明書が必要になるので、レプリカ側にコピーします。ここでは/etc/mysql/ca.crtにコピーしたと仮定します。

MariaDB []> CHANGE MASTER TO MASTER_SSL = 1,MASTER_SSL_CA = '/etc/mysql/ca.crt', MASTER_SSL_VERIFY_SERVER_CERT = 1;

最後にプライマリとレプリカの両方向で証明書の検証をする方法です。この場合レプリカ側にも証明書を発行する必要があります。方法はあるのかもしれまんが筆者が試してみたところ、証明書を発行するCAはプライマリと同じでないと動きませんでした(CAPATHに複数のCAの証明書を入れる方法を試してみましたがうまく動きませんでした)。

caの証明書をca.crt、レプリカ用に作成した証明書をr.crt、秘密鍵をr.keyとして、すべてレプリカ側の/etc/mysqlディレクトリに存在しているものとします。

注意が必要なのはOpenSSLを使って作成した秘密鍵(r.key)は通常rootユーザーしか読み取り権限がありませんが、MariaDBを動かしている権限で読み込めるようにする必要があります。たとえば、r.keyのオーナーをmysqlにします。

MariaDB []> CHANGE MASTER TO MASTER_SSL = 1, MASTER_SSL_CERT = '/etc/mysql/r.crt', MASTER_SSL_KEY = '/etc/mysql/r.key', MASTER_SSL_CA = '/etc/mysql/ca.crt', MASTER_SSL_VERIFY_SERVER_CERT=1;

処理中のセッションの切断対策

大きなデータベースの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はユーザー権限で起動できますが、一度管理者権限で作成したセッション一覧をユーザー権限で見ようとするとエラーになるので注意してください。

投稿:2021/12/11 - 更新: facebookで共有 twitterで共有 pocketに追加