DB/MySQL/Backup の履歴(No.13)

import†

MySQL Dump†

# エラーは-fで無視する
mysql -u root -phogehoge -f < mysqldump.sql

↑

Aurora†

Replication停止はできない

https://blog.manabusakai.com/2017/03/difference-between-mysql-and-aurora/

Binlog停止はできる

https://helen.hatenablog.com/entry/2019/06/13/200022

↑

mysqlpump†

MySQL 5.7から導入されたmysqldumpよりも高機能なツール
MariaDBには使えない！　Server version is not compatible. Server version should be 5.6.46 or above.
MariaDBのデータインポートした状態で実施したらSegmentation Faultが出るなどまだまだ不安定

↑

コマンド例†

特定のDBかつ別ホストのDBかつ通信を圧縮する

mysqlpump -u root -p -h 192.168.11.157 --databases cakephp --compress

↑

Xtrabackup†

MySQL or PerconaServer対応。Mariadbもバージョンが古いのは対応しているが、新しいものはforkしたmariabackupを使うべし
物理ファイルを含むため、mysqldumpを圧縮したのに比べて二倍ほど(160M vs 340M)
ラッパーperlとしてinnobackupexもあるが、違いはなんだ（stream流して圧縮しながらバックアップできる？)

全体バックアップ(テーブルロックなし）

xtrabackup --backup -u root -pパスワード --target-dir /tmp/backup

↑

mariabackup†

全体バックアップ(テーブルロックあり）

mariabackup --backup -u root -pパスワード --target-dir /tmp/backup

バックアップの整合性チェック(取得中に更新があったものをロールフォワードする)

mariabackup --prepare --target-dir /tmp/backup

バックアップ時点でのbinlog情報

cat /tmp/backup/xtrabackup_binlog_info

リストア

systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir /tmp/backup
chown -R mysql:mysql /var/lib/mysql/*
systemctl start mariadb

↑

mysqldump†

指定されたDB/Tableについて、SQLを吐き出すだけ。単純だがSQLなので汎用性がたかいね。

↑

オプション†

↑

一貫性を保つために†

5.7より前（つまり2019年でも)一貫性を保つためにmysqldumpのオプション指定が必要

↑

オプション一覧†

mysqldump --all-databases --lock-all-tables	全テーブルロックで一番安全。ダンプ中にアプリケーションから更新処理が走る場合は下の選択肢。xでも同じことだがあとで見た時に意味不明なのでやめる
mysqldump --all-databases --single-transaction	ダンプ中にMyIsamテーブルを更新しない、innoDBスキーマを変更しない
--flush-logs	ダンプ時にバイナリログをリセットする
--master-data=2	バイナリログの位置をコメントで出力。レプリケーションやPITRに便利
--events	イベント関連のテーブルを明示的にバックアップする。 --all-databasesをつけていると除外されてワーニングがでるので、一緒につけることが多い

↑

インポート、エクスポート†

↑

CSVインポート†

デリミターを指定しない場合はタブ区切りとなる。
日本語関係のフィールドをロードする場合はクライアントの文字コードをセットしておいた方が良いかも。

LOAD DATA LOCAL INFILE 'car_import.csv' INTO TABLE テーブル名 FIELDS TERMINATED BY ',';

フィールドを指定する場合

LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

autoincrement列を除外する場合はその列を除外すれば自動的に連番がふられる（insert分でも同様)

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE test (col1);

↑

バックアップ&リカバリー†

↑

エクスポート†

標準出力にでるのでリダイレクトで保存すること。以下のコマンドを追加して、圧縮しつつバックアップを取るとよい。

| gzip > dump.sql.gz

単位	コマンド	備考
テーブル	mysqldump -u root -p --add-drop-table DB名テーブル名	drop tableを入れるオプション付き
テーブル where条件付き	mysqldump -u root -p --add-drop-table DB名テーブル名　--where 'is_delete = 0'	drop tableを入れるオプション付き
データベース	mysqldump -u root -p DB名	dropテーブルは自動で入るようなので、空じゃないDBに入れるときは既存データなくなる！

↑

スキーマのみ†

no-dataのオプションをつける

↑

テーブル単位†

主キーがかぶらないようにdrop tableして作り直す構文つきで出力

mysqldump -u root -p --add-drop-table DB名 テーブル名 > /var/tmp/rooms.sql

戻すときはデータベースの指定だけでOK

mysql -u root -p DB名 < /var/tmp/rooms.sql

↑

データベース単位†

↑

バックアップ†

バックアップ対象はデータベースmysqlとし、出力ファイル名はmysql.sqlとする。

mysqldump -u root -p データベース名 > mysql.sql

危険を承知でパスワードをコマンドに入れてしまう場合はこちらで

mysqldump -u root --password=パスワード データベース名 > mysql.sql

圧縮しつつバックアップは以下の通り

mysqldump -u root -p データベース名 | gzip > mysql.sql.gz

↑

リカバリー†

リカバリー対象データベースはcreateしておく。

mysql> create database mysql2;
mysql> exit
mysql -u root -p mysql2 < mysql.sql

圧縮したもののリカバリーは以下の通り

zcat mysql.sql.gz  | mysql -u root -p データベース名

↑

全部のデータベース†

↑

バックアップ†

全部のデータベースを出力

mysqldump -u root -x --all-databases > dump.sql

↑

リカバリ†

全部のデータベースをインポート(もとあるＤＢは名前がかぶらなければ残る）

mysql -u root -p < dump.sql

リカバリーした後はrootでログインして、flush privilegesすること。さもないと移行されたユーザでログインできない。もしくはMySQLの再起動で反映される。

Counter: 3114, today: 1, yesterday: 2