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

• 履歴一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• DB/MySQL/Backup へ行く。
  • 1 (2019-01-24 (木) 10:20:56)
  • 2 (2019-01-24 (木) 10:26:58)
  • 3 (2019-02-22 (金) 10:19:23)
  • 4 (2019-08-12 (月) 15:33:57)
  • 5 (2020-08-14 (金) 14:15:30)
  • 6 (2020-08-15 (土) 19:09:47)
  • 7 (2020-08-16 (日) 10:02:19)
  • 8 (2020-08-16 (日) 14:43:56)
  • 9 (2020-08-27 (木) 20:14:38)
  • 10 (2020-09-05 (土) 08:50:23)
  • 11 (2020-09-05 (土) 09:49:05)
  • 12 (2020-09-05 (土) 20:18:43)
  • 13 (2020-09-07 (月) 19:53:41)
  • 14 (2020-09-09 (水) 08:11:39)
  • 15 (2020-09-09 (水) 20:52:06)
  • 16 (2020-09-12 (土) 19:39:39)
  • 17 (2020-09-14 (月) 20:50:59)
  • 18 (2022-10-14 (金) 13:16:53)

---

import†

pvを挟んで進捗を見る†

yum install pv
pv mysqldump.sql | mysql -uroot -phogehoge 

innodb_flush_log_at_trx_commitだけでは、binlog書き込みがボトルネックなので有意差なし。

MySQL Dump†

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

Aurora†

1. Replication停止はできない

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

1. 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が出るなどまだまだ不安定

コマンド例†

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のオプション指定が必要

オプション一覧†

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

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

スキーマのみ†

• 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の再起動で反映される。