MyDumperとMyLoaderを利用したMysqlサーバのバックアップ＆リカバリが結構大変だったのでまとめ #MySQL

MyDumperとMyloaderは高速にバックアップの取得、復元が可能で非常に便利なのだが、先日利用した際に割と困ったことがたくさんあったのでまとめてみた。
プルリク出して直してもらえば本当はいいんだけど、とりあえずのワークアラウンドとして。現行仕様(Mydumper 0.95、Myloader 0.95)での利用について共有する。
実作業ログ的なものなので、間違ってたら指摘ください。

CPU利用率全コア約90%超えでThreadRipper冥利につきる、並列処理による高速バックアップ＆リストアしてくれるのは本当に素晴らしく、代替えが難しい。
でも割と不具合があり、ワークアラウンドで対応できるなら、Issueがすぐ直るような状況ではない為、理解して使う必要がある。

Mydumperの利用について

バックアップの際には--routines --triggers --eventsを付ける。

オプションなしではストアドプロシージャとストアドファンクション、トリガー、イベントが標準ではバックアップされません。

time mydumper --user root --ask-password --outputdir /opt/backup/export_full --threads 32 --verbose 3 --logfile /opt/backup/mydumper_buckup.log --routines --triggers --events

generated columnを含むテーブルは1行目のSET NAMES binaryをSET NAMES utf8mb4に変換する

1行目にSET NAMES binaryが残ったままだと、json型のデータをINSERTする際に、エラーが発生してリストア出来ないため、SET NAMES utf8mb4に変換する。

time mydumper --user root --ask-password --outputdir /opt/backup/export_json_table --threads 32 --verbose 3 --logfile /opt/backup/mysql/mydumper_buckup_json_table.log --no-data --regex '^(<DB名1>.<テーブル名1>|<DB名2>.<テーブル名2>)'

以下のSQLでjson型を含むテーブルを調べる

SELECT
    CONCAT( " --regex '^(", GROUP_CONCAT( CONCAT( TABLE_SCHEMA, "." , TABLE_NAME ) SEPARATOR  "|" ) , ")'") 
FROM
(SELECT
  DISTINCT
    TABLE_SCHEMA,
    TABLE_NAME
FROM
    `information_schema`.`COLUMNS`
WHERE
DATA_TYPE = "json") as v

日付型のデフォルトが'0000-00-00 00:00:00'のデータについて許容するように対応する。

4行目に以下のコメントを追加する。

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */

mysql5.7からstrict modeでは受け付けなくなった'0000-00-00 00:00:00'のテーブル定義だが、
実は、以下の2テーブルにはテーブル定義の日付のデフォルト値に'0000-00-00 00:00:00'が残っている。
mysql.proc
mysql.event

time mydumper --user root --ask-password --outputdir /opt/backup/export_zero_date --threads 32 --verbose 3 --logfile /opt/backup/mysql/mydumper_buckup_zero_date.log --no-data --regex '^(<DB名1>.<テーブル名1>|<DB名2>.<テーブル名>)'

以下のSQLで定義の日付のデフォルト値に'0000-00-00 00:00:00'が残っているテーブルを調べるSQL

SELECT
    CONCAT( " --regex '^(", GROUP_CONCAT( CONCAT( TABLE_SCHEMA, "." , TABLE_NAME ) SEPARATOR  "|" ) , ")'") 
FROM
    `information_schema`.`COLUMNS` 
WHERE
    `COLUMN_DEFAULT` = '0000-00-00 00:00:00' 
    AND `TABLE_SCHEMA` NOT IN ( "information_schema","performance_schema","sys")

federated tableも--no-dataオプションを付けて、テーブル定義だけバックアップする

別のサーバから延々テーブルをダンプしてくれるので、Federated Tableを抜き出して、--no-dataオプションを付けて、テーブル定義だけバックアップする
※Myloaderの動作は未確認だが下手するとデータが倍になるかも。

time mydumper --user root --ask-password --outputdir /opt/backup/export_federated --threads 32 --verbose 3 --logfile /opt/backup/mysql/mydumper_buckup_federated.log --no-data --regex '^(<DB名1>.<テーブル名1>|<DB名2>.<テーブル名2>)'

--regex以降の設定はこのSQLで抽出可能

SELECT
    CONCAT( " --regex '^(", GROUP_CONCAT( CONCAT( TABLE_SCHEMA, "." , TABLE_NAME ) SEPARATOR  "|" ) , ")'") 
FROM
    `information_schema`.`TABLES` 
WHERE
    `ENGINE`= 'FEDERATED' 
    AND TABLE_SCHEMA NOT IN ( "mysql","sys", "information_schema", "performance_schema" ) 
        AND TABLE_SCHEMA IS NOT NULL
        AND TABLE_NAME IS NOT NULL

またfederated tableデータは--ignore-engines 'FEDERATED'オプションを設定することで、mydumperからダンプしない設定を簡単に行えます。

time mydumper --user root --ask-password --outputdir /opt/backup/export_federated --threads 32 --verbose 3 --logfile /opt/backup/mysql/mydumper_full.log --ignore-engines 'FEDERATED'

巨大なテーブルは、--rowsオプションを利用して、10万件づつなどで処理する。

標準ではテーブル単位で並列処理を行うので、巨大なテーブルのバックアップが遅い。
10万行づつ並列処理を行う事で、高速にリストア可能。
今回のトラブルケースの場合ロック競合で問題が発生していたので、10万行づつ並列処理が行えなかった時の為、念のためrowsオプションがないデータも取得しておいた。

time mydumper --user root --ask-password --outputdir /opt/backup/export_full --threads 32 --verbose 3 --logfile /opt/backup/mysql/mydumper_full.log --rows 100000

Myloaderの利用について

バックアップディレクトリを指定する場合には、『metadata』ファイルが必要

こんな雑ロジックでMyDumperディレクトリかどうかを確認しているので、リストアする為のディレクトリにはmetadataファイルを保存しておくこと。

        } else {
                char *p= g_strdup_printf("%s/metadata", directory);
                if (!g_file_test(p, G_FILE_TEST_EXISTS)) {
                        g_critical("the specified directory is not a mydumper backup\n");
                        exit(EXIT_FAILURE);
                }
        }

mysqlデータベースのみまずリストアする。

mysqlデータベースを復元する際に、UDFやストアドプロシージャがリストアされる。Viewなどに含まれている場合、ファンクションがない等の理由で実行時にエラーが発生する。その為、まずはmysqlデータベースを復元する。

time myloader --user restore --ask-password --overwrite-tables --directory /opt/backup/export_restore --source-db mysql --threads 32 --verbose 3 2>&1 |tee /opt/backup/myloader_mysql.log

テーブルスペースを復元する

後でテーブルスペースを復元すると面倒なので、先にテーブルスペースを作っておく。

mysql -p
create tablespace scuel_common_compressed add datafile '/opt/mysql_external_tablespace/external_table_space.ibd' engine=InnoDB;

全データベースリストア

全データをリストアする。

time myloader --user restore --ask-password --overwrite-tables --directory /opt/backup/export_restore --threads 32 --verbose 3 2>&1 |tee /opt/backup/myloader_full.log

ポイント

全データがリストアできる状況を作ってから、リストア作業に取り掛かると作業しやすい。

参考資料

バックアップやリストア時に発生したMysqlサーバの未解決課題

以下はMysqlサーバの問題であり、MyDumperやMyLoader関係ないけど、参考までに記載。
今年最初からこのエラーが発生して困っているんだけど、対処法が分からない。誰か同じ悩みの人いないのかな。

bulk_insert_buffer_sizeを有効にすると、ロック競合が発生してサーバが落ちる

ちなみにbulk_insert_buffer_sizeを0にしないとINSERT途中でサーバが死んで復旧しなくなったため、
現在bulk_insert_buffer_sizeを0にして回避した。

innodb_purge_threadsが8の場合、purge thread競合が発生してサーバが落ちる？

innodb_purge_threadsを1にして回避した。もしかしたら関係ないかも
この関係で、History list lengthが多くなっていて、History list lengthが一杯になって落ちている可能性がある。

巨大なテーブルに高速にINSERT処理を行うと、ロック競合が発生してサーバが落ちる

History list lengthが2038になって落ちていたので、Undoログがあふれたか？History listを小さくする対応を行う予定。

InnoDB: ###### Diagnostic info printed to the standard error stream
2019-07-29T15:11:18.298291+09:00 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.

このあたりが問題か？

innodb_log_write_ahead_sizeが512だとロック競合が発生して落ちる？

innodb_buffer_pool_instancesが多すぎるとパージが間に合わない可能性があるのでパージスレッドを増やすか、分割数を減らす必要がある？