MySQL シリーズ 12 バックアップとリカバリ

使用法：
           シェル> mysqldump [オプション] db_name [tbl_name ...]
           シェル> mysqldump [オプション] --databases db_name ...
           シェル> mysqldump [オプション] --all-databases

オプション:
	-A: すべてのライブラリをバックアップします -B db_name1,[db_name2,...]: 指定されたライブラリをバックアップします -E: 関連するすべてのイベント スケジューラをバックアップします
	-R: すべてのストアド プロシージャとストアド関数をバックアップします --triggers: テーブル関連のトリガーをバックアップします。デフォルトで有効になっています。トリガーをバックアップしない場合は --skip-triggers を使用します --master-data={1|2}:
		 1: バックアップされたデータの前に、CHANGE MASTER TO ステートメントとしてレコードを追加します。コメントなし。指定されていない場合はデフォルト値は 1 です。
		 2: CHANGE MASTER TO ステートメントがコメントとして記録されます。注: このオプションは、--lock-tables 機能を自動的にオフにし、--lock-all-tables 機能を自動的にオンにします (--single-transaction がオンになっていない限り)
	-F: バックアップの前にログをロールします。テーブルをロックした後、flush logs コマンドを実行して新しいバイナリ ログ ファイルを生成します。-A と一緒に使用すると、データベースが複数回更新されます。ダンプとログの更新を同時に実行する場合は、--flush-logs と -x、--master-data、または --single-transaction を同時に使用する必要があります。この場合、更新は 1 回だけ行われます。推奨事項: -x、--master-data、または --single-transaction と一緒に使用します。--compact コメントを削除します。デバッグに適していますが、本番環境では使用されません。-d: テーブル構造のみをバックアップします。-t: データのみをバックアップし、テーブルの作成はバックアップしません。
	-n: 作成データベースをバックアップしません。-A または -B で上書きできます。--flush-privileges: バックアップ前に認証テーブルを更新します。MySQL データベースまたは関連するデータベースのバックアップに必要です。-f: SQL エラーを無視して実行を続行します。--hex-blob: バイナリ列をダンプするために 16 進表記を使用します (たとえば、「abc」は 0x616263 になります)。影響を受けるデータ型には、BINARY、VARBINARY、BLOB、BIT が含まれます。
	-q: クエリをキャッシュせず、直接出力し、バックアップを高速化します

InnoDB では、次のバックアップ戦略が推奨されています。
	mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM 推奨バックアップ戦略:
	mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

使用法：
	innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

オプション:
    --user: このオプションは、バックアップ アカウントを示します --password: このオプションは、バックアップ パスワードを示します --host: このオプションは、バックアップ データベースのアドレスを示します --databases: このオプションで受け入れられるパラメーターは、データ名です。複数のデータベースを指定する場合は、スペースで区切る必要があります。例: "xtra_test dba_test"。同時に、データベースを指定するときに、その中のテーブルのみを指定することもできます。たとえば、「mydatabase.mytable」などです。このオプションは InnoDB エンジン テーブルでは無効ですが、すべての InnoDB テーブルがバックアップされます。--defaults-file: このオプションは、MySQL 構成を読み取るファイルを指定します。コマンドラインの最初のオプション位置に配置する必要があります。--incremental: このオプションは増分バックアップを作成することを意味します。--incremental-basedir を指定する必要があります。
    --incremental-basedir: このオプションは、以前の完全バックアップまたは増分バックアップのディレクトリを指定し、--incremental と一緒に使用されます。 --incremental-dir: このオプションは、復元中の増分バックアップのディレクトリを示します。 --include=name: テーブル名を指定します。形式: databasename.tablename
    --apply-log: 通常、バックアップが完了した後、バックアップされたデータにはまだコミットされていないトランザクションや、コミットされたがデータ ファイルにまだ同期されていないトランザクションが含まれている可能性があるため、そのデータをリカバリ操作に使用することはできません。したがって、現時点ではデータ ファイルはまだ不整合な状態にあります。このオプションは、コミットされていないトランザクションをロールバックし、コミットされたトランザクションをデータ ファイルに同期することで、データ ファイルの一貫性を保つために使用されます。--use-memory: このオプションは、--apply-log オプションと一緒に使用されます。バックアップを準備するときに、クラッシュ リカバリ用に xtrabackup によって割り当てられるメモリ サイズはバイト単位です。 1MB、1M、1G、1GBも利用可能、1Gが推奨
	--export: 別のテーブルをエクスポートして、他の MySQL にインポートできることを示します。 --redo-only: このオプションは、基本の完全バックアップを準備し、それに増分バックアップをマージするときに使用します。 --copy-back: データを復元するときに、バックアップ データ ファイルを MySQL サーバーのデータ ディレクトリにコピーします。
	--move-back: このオプションは --copy-back に似ていますが、唯一の違いは、ファイルをコピーするのではなく、ファイルを宛先に移動する点です。このオプションはバックアップ ファイルを削除するため、注意して使用する必要があります。使用シナリオ: データファイルとバックアップコピーの両方を保存するのに十分なディスク容量がありません

~]# mkdir /backup
~]# systemctl stop mariadb #サービスを停止~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #バックアップをパッケージ化して圧縮]# systemctl start mariadb

~]# systemctl を停止 mariadb
~]# rm /var/lib/mysql/ -rf #破損したライブラリを削除します~]# cd /backup/
バックアップ]# tar xf mariadb_all.tar.xz #パッケージ化されたデータベースファイルを解凍します。バックアップ]# cp -av var/lib/mysql/ /var/lib/ #バックアップを復元します。バックアップ]# systemctl start mariadb #サービスを開始して正常に復元します。

~]# systemctl を停止 mariadb
~]# rm /var/lib/mysql/ -rf #破損したライブラリを削除します~]# cd /backup/
バックアップ]# tar xf mariadb_all.tar.xz #パッケージ化されたデータベースファイルを解凍します。バックアップ]# cp -av var/lib/mysql/ /var/lib/ #バックアップを復元します。バックアップ]# systemctl start mariadb #サービスを開始して正常に復元します。

lvm 環境を準備します。
~]# pvcreate /dev/sda5
~]# vgcreate vg0 /dev/sda5
~]# lvcreate -n lv_data -L 10G vg0
~]# lvcreate -n lv_binlog -L 10G vg0
~]# mkfs.xfs /dev/vg0/lv_data
~]# mkfs.xfs /dev/vg0/lv_binlog
~]# mkdir -pv /data/{mysqldb,binlog} #データディレクトリとバイナリログ保存ディレクトリを作成~]# chown -R mysql:mysql /data/
~]# vim /etc/fstab
	UUID=4e3d726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs デフォルト 0 0
	UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs デフォルト 0 0

データベースを構成し、大量のデータの生成をシミュレートします。
~]# yum インストール mariadb-server -y
~]# vim /etc/my.cnf
    [mysqld]
    datadir = /data/mysqldb #データベースの保存パスを指定します log_bin = /data/binlog/mariadb-bin #バイナリログを有効にして、指定したパスに保存します innodb_file_per_table = ON #テーブルごとに個別のテーブルスペースを有効にします~]# systemctl start mariadb
~]# mysql #データベースに接続します。ユーザー名とパスワードはここでは省略します。以下は同じです。MariaDB [(none)]> CREATE DATABASE school; #テストライブラリを作成します。MariaDB [(none)]> use school
MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20); #データテーブルを作成する MariaDB [school]> DELIMITER // #ステートメントのターミネータを「//」に変更する
MariaDB [school]> CREATE PROCEDURE pro_testtb() #テスト用に100,000件のレコードを生成するストアドプロシージャを作成します-> BEGIN
    -> i int を宣言します。
    -> i = 1 を設定します。
    -> i < 100000 の場合
    -> INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i); を実行します。
    -> i = i + 1 を設定します。
    -> END while;
    -> 終了//
MariaDB [school]> DELIMITER ; #ステートメントのターミネータを元に戻すことを忘れないでください MariaDB [school]> CALL pro_testtb; #ストアドプロシージャを呼び出します MariaDB [school]> SELECT COUNT(*) FROM testtb; #テーブルに100,000件のレコードがあることを確認します +----------+
| カウント(*) |
+----------+
| 99999 |
+----------+

バックアップを開始します:
MariaDB [school]> FLUSH TABLES WITH READ LOCK; #ユーザーが書き込みを続行できないように、バックアップする前にテーブルをロックすることを忘れないでください MariaDB [school]> FLUSH LOGS; #バイナリログをスクロールします MariaDB [school]> SHOW MASTER LOGS; #バイナリログの場所を確認します +--------------------+-----------+
| ログ名 | ファイルサイズ |
+--------------------+------------+
| mariadb-bin.000001 | 30334 |
| mariadb-bin.000002 | 1038814 |
| mariadb-bin.000003 | 29178309 |
| mariadb-bin.000004 | 528 |
| mariadb-bin.000005 | 245 | #この出力を記録しておきます。後で必要になります+--------------------+-----------+
~]# lvcreate -L 5G -n lv_mysql_snap -s -pr /dev/vg0/lv_data #スナップショットを作成するには、別のターミナルを開く必要があります。mysql ターミナルを終了しないでくださいMariaDB [school]> UNLOCK TABLES; #スナップショットを作成したら、できるだけ早くロックを解除してください。ユーザーからの苦情に注意してください~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/ #スナップショットを /mnt にマウントします
~]# cp -av /mnt/ /backup #データをバックアップディレクトリにコピーします~]# umount /mnt/
~]# lvremove /dev/vg0/lv_mysql_snap #コピーが完了したら、サーバーのパフォーマンスに影響を与えるため、すぐにスナップショットを削除してください。これで完全バックアップが完了しました~

その他のデータ:
MariaDB [school]> CALL pro_testtb; #100,000件のレコードの挿入をシミュレートしてみましょう MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| カウント(*) |
+----------+
| 199998 | #現在レコード数は 200,000 件です+----------+

データベースの破損をシミュレートする:
~]# rm -rf /data/mysqldb/* #サーバーがクラッシュし、BB がなくなったので、ライブラリをクリアするだけです~]# systemctl stop mariadb #サービスを停止します

復元を開始します:
~]# cp -av /backup/* /data/mysqldb/ # バックアップしたファイルを対応するライブラリ ディレクトリに cp します。/etc/my.cnf の [mysqld] の下に skip_networking を追加して、ユーザーによるデータベースの使用を禁止し、リカバリ プロセス中にデータが書き込まれるのを防ぎます。 ~]# systemctl start mariadb # サービスを開始します ~]# ls -1 /data/binlog/ # バイナリ ログ ファイルの数を表示します mariadb-bin.000001
    mariadb-bin.000002
    mariadb-bin.000003
    mariadb-bin.000004
    mariadb-bin.000005
    mariadb-bin.000006
    mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000005 > binlog.sql #完全バックアップ時点以降のデータ~]# mysqlbinlog /data/binlog/mariadb-bin.000006 >> binlog.sql #後続のすべてのデータを同じ sql ファイルに追加します~]# mysql < binlog.sql #バイナリ ログを使用して、前回の完全バックアップの時点から増分的に復元します~]# mysql -e 'SELECT COUNT(*) FROM school.testtb' #確認してください。200,000 件のレコードがすべて揃っています。すばらしい
+----------+
| カウント(*) |
+----------+
| 199998 |
+----------+
/etc/my.cnf に移動し、[mysqld] の下の skip_networking を削除し、サービスを再起動すると復元が完了します。

~]# mysqldump -A -F -E -R --single-transaction --master-data=2 --flush-privileges > /backup/full-`date +%F-%T`.sql #データベース全体のバックアップ

MariaDB [(なし)]> CREATE DATABASE db1; #データベースを作成します MariaDB [(なし)]> CREATE DATABASE db2; #別のデータベースを作成します MariaDB [school]> use school;
MariaDB [school]> DROP TABLE testtb; #エラーのため、200,000件のレコードを含むテーブルが削除されましたMariaDB [school]> CREATE TABLE students (id INT(4) AUTO_INCREMENT PRIMARY KEY,name CHAR(30),age TINYINT); #その後、他のユーザーが他のテーブルを作成しましたMariaDB [school]> INSERT INTO students(name,age) VALUES ('user1',20); #データも追加されました

この時点で、テーブルが欠落しており、緊急に復元する必要があることがわかりました。では、始めましょう。 MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #テーブルをロック MariaDB [(none)]> FLUSH LOGS; #バイナリ ログ ファイルをフラッシュしてロール MariaDB [(none)]> SHOW MASTER LOGS; #現在のログ ステータスを表示 +--------------------+-----------+
| ログ名 | ファイルサイズ |
+--------------------+------------+
| mariadb-bin.000001 | 30334 |
| mariadb-bin.000002 | 1038814 |
| mariadb-bin.000003 | 29178309 |
| mariadb-bin.000004 | 528 |
| mariadb-bin.000005 | 29177760 |
| mariadb-bin.000006 | 29177786 |
| mariadb-bin.000007 | 953 |
| mariadb-bin.000008 | 245 |
+--------------------+------------+
~]# systemctl stop mariadb #サービスを停止し、修復の準備をします~]# head -30 /backup/full-2018-06-14-05\:33\:47.sql |grep "CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000007', MASTER_LOG_POS=245; # mariadb-bin.000007 の 245 にあるフルバックアップのログポイントを見つけます
~]# ls -1 /data/binlog/
mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000007 > /backup/binlog.sql #完全バックアップ後にバイナリログをエクスポートします~]# mysqlbinlog /data/binlog/mariadb-bin.000008 >> /backup/binlog.sql
~]# vim /backup/binlog.sql #エクスポートされたSQLファイルを変更し、誤ったSQL文を削除します。「DROP TABLE `testtb` /* generated by server */」の行を削除します

バックアップをインポートします。
~]# rm -rf /data/mysqldb/* #まず障害データベースをクリアします~]# vim /etc/my.cnf #設定ファイルを編集します。ユーザーがデータを書き込むのを防ぐために、skip_networkingを[mysqld]に追加します~]# systemctl start mariadb #サービスを開始します~]# mysql < /backup/full-2018-06-14-05\:33\:47.sql #完全バックアップをインポートします~]# mysql < /backup/binlog.sql #増分バックアップをインポートしますMariaDB [(none)]> show databases; #データが正常に復元されたかどうかを確認します+--------------------+
| データベース |
+--------------------+
| 情報スキーマ |
| db1 | #回復済み | db2 | #回復済み | mysql |
| パフォーマンススキーマ |
| 学校 |
| テスト |
+--------------------+
MariaDB [(なし)]> SELECT COUNT(*) FROM school.testtb;
+----------+
| カウント(*) |
+----------+
| 199999 | #回復+----------+
MariaDB [(なし)]> SELECT * FROM school.students;
+----+-------+------+
| ID | 名前 | 年齢 |
+----+-------+------+
| 1 | user1 | 20 | #回復済み+----+-------+------+
ここまでで復旧は完了です。設定ファイル内の skip_networking を削除し、サービスを再起動すれば完了です～

~]# innobackupex --user=root /backup/ #ここではパスワードは省略します

MariaDB [school]> CALL pro_testtb; #データを追加しますMariaDB [school]> SELECT COUNT(*) FROM testtb; #現在、レコードは 300,000 件あります+----------+
| カウント(*) |
+----------+
| 299998 |
+----------+
MariaDB [school]> INSERT INTO students VALUES (2,'user2',21);
MariaDB [school]> UPDATE students SET age=19 WHERE id=1;
MariaDB [学校]> SELECT * FROM students;
+----+-------+------+
| ID | 名前 | 年齢 |
+----+-------+------+
| 1 | ユーザー1 | 19 |
| 2 | ユーザー2 | 21 |
+----+-------+------+

~]# mkdir /backup/inc{1,2} #増分バックアップディレクトリを作成~]# innobackupex --incremental /backup/inc1/ --incremental-basedir=/backup/2018-06-14_10-44-57/ #完全バックアップに基づいて増分バックアップを指定

MariaDB [(なし)]> CREATE DATABASE db3; 
MariaDB [(なし)]> DROP TABLE school.students; #テーブルが誤って削除されました MariaDB [(なし)]> use school
MariaDB [school]> CALL pro_testtb; #その後、さらにデータが生成されますMariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| カウント(*) |
+----------+
| 399997 |
+----------+
MariaDB [school]> SELECT * FROM students; #この時点で、students テーブルが消えていることがわかりました。どうすればよいでしょうか?
エラー 1146 (42S02): テーブル 'school.students' が存在しません

~]# rm -rf /data/mysqldb/* #MariaDB を復元する前にデータ ディレクトリをクリアします [(なし)]> show databases; #データベースは削除されました+--------------------+
| データベース |
+--------------------+
| 情報スキーマ |
+--------------------+

完全バックアップと増分バックアップを復元するには:
: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :

バイナリ ログを使用して、最新の増分バックアップを現在まで復元します。
~]# cat /backup/2018-06-14_10-44-57/xtrabackup_binlog_info #バックアップ中のバイナリログレコードポイントを確認する mariadb-bin.000011 35740416
~]# ls -1 /data/binlog/ #バイナリログファイルが記録されている場所を確認します mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.000009
mariadb-bin.000010
mariadb-bin.000011
mariadb-bin.000012
mariadb-bin.000013
mariadb-bin.index
~]# mysqlbinlog --start-position=35740416 /data/binlog/mariadb-bin.000011 > /backup/binlog.sql #最新の増分バックアップ後のバイナリログデータをエクスポートします~]# mysqlbinlog /data/binlog/mariadb-bin.000012 >> /backup/binlog.sql
~]# mysqlbinlog /data/binlog/mariadb-bin.000013 >> /backup/binlog.sql
/backup/binlog.sql ファイルを編集し、「DROP TABLE `school`.`students` /* generated by server */」を削除して、誤って削除した内容を元に戻します。MariaDB [(none)]> SET sql_log_bin=0; #バイナリ ログ機能を一時的にオフにします。MariaDB [(none)]> source /backup/binlog.sql #増分バックアップ後の最新データをインポートして、データが完全に復元されたかどうかを確認します。my.cnf の skip_networking を削除し、サービスを再起動します。これで、データが最新の状態に復元されました。

~]# innobackupex --include="testdb.testlog" /backup #テーブルデータをバックアップ~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql #テーブルスペースをバックアップ~]# mysql -e 'DROP TABLE testdb.testlog' #失敗をシミュレートし、testlog テーブルを削除します

~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/ #テーブルデータを整理する~]# vim /backup/desc_testdb_testlog.sql #ステートメントを編集してテーブルスペースを作成し、次のフィールドを削除します テーブル テーブルの作成
    テストログ
~]# mysql testdb < /backup/desc_testdb_testlog.sql #テーブルスペースをインポートします~]# mysql testdb -e 'DESC testlog' #インポートが成功したかどうかを確認します+-------+---------+-----+-----+---------+----------------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | 自動増分 |
| 名前 | char(30) | はい | | NULL | |
| 年齢 | int(11) | はい | | 20 | |
+-------+----------+------+-----+---------+----------------+
~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE' # テーブルスペースをクリア~]# cd /backup/2018-06-14_17-47-02/testdb/
testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/ #テーブルデータをライブラリディレクトリにコピーします~]# chown -R mysql:mysql /var/lib/mysql/testdb/ #所有者とグループを変更します~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE' #テーブルスペースをインポートします