Linux:MYSQL(十一)LVM,mysqldumpバックアップ

バックアップとリカバリ

ディザスタリカバリをバックアップする理由:ハードウェア障害、ソフトウェア障害、自然災害、ハッカー攻撃、誤操作テストなどのデータ損失シーン

バックアップの注意点最大損失を許容できるデータ復旧データの所要時間数

リストア要点リストアテストを行い、バックアップの可用性リストアトレーニング

をテストします.
バックアップとリカバリ

バックアップ時に考慮すべき要因温備のロックがどのくらいかかっているかバックアップによって生じる負荷バックアッププロセスの時間長リカバリプロセスの時間長

は、どのようなデータバイナリ・ログ、InnoDBのトランザクション・ログ・プログラム・コード(ストアド・プロシージャ、関数、トリガ、イベント・スケジューラ)サーバのプロファイル

をバックアップするかを示す.

物理および論理バックアップ

物理バックアップ:データファイルを直接コピーしてバックアップを行い、ストレージエンジンと関係があり、多くの空間を占有し、速度が

速い.

論理バックアップ:データベースからデータを「エクスポート」して保存するバックアップは、ストレージエンジンに関係なく、占有スペースが少なく、速度が遅く、精度が失われる可能性があります

.

バックアップタイプ:フルバックアップ、インクリメンタルバックアップ、差分バックアップ

フル・バックアップ:データセット全体

インクリメンタル・バックアップ:インクリメンタルが存在する場合、最近のフル・バックアップまたはインクリメンタル・バックアップ以降に変化するデータのみをバックアップします.バックアップは高速で、複雑な

をリストアします.

差分バックアップ:最近の完全バックアップ以降に変化したデータのみをバックアップし、バックアップが遅く、簡単な

をリストアします.

注:バイナリ・ログ・ファイルは、データ・ファイルと同じディスクに置くべきではありません.

コールド、ホット・バックアップ

冷備:読み書き操作は

不可

温備:読み取り操作が実行可能;ただし、書き込み操作は実行できない

ホットスペア:読み書き操作はすべてMyISAM:ホットスペア、ホットスペアInnoDBをサポートしない:いずれも

をサポートする

バックアップツール

cp,tarなどのレプリケーションアーカイブツール:物理バックアップツール、すべてのストレージエンジンに適用;冷却のみをサポートします.完全および部分バックアップ

LVMのスナップショット:先にロックをかけて、スナップショットをしてからロックを解除して、ほとんどホットスペアです;ファイルシステムツールによるバックアップ

mysqldump:論理バックアップツール、すべてのストレージエンジンを適用し、温備;完全または一部のバックアップをサポートします.InnoDBストレージエンジンに対してホットスペアをサポートし、binlogのインクリメンタルバックアップ

を結合する.

xtrabackup:PerconaがInnoDBのホットスペア(物理バックアップ)をサポートするツールを提供し、フルバックアップ、インクリメンタルバックアップ

をサポートする.

MariaDB Backup:MariaDB 10.1.26から統合を開始し、Percona XtraBackup 2.3.8に基づいて

を実現

mysqlbackup:ホットバックアップ、MySQL Enterprise Editionコンポーネント

mysqlhotcopy:PERL言語で実装され、ほとんど冷却され、MyISAMストレージエンジンにのみ適用され、LOCK TABLES、FLUSH TABLES、cpまたはscpを使用してデータベース

を迅速にバックアップ
cp tarバックアップ
すべてコピー:

1.    
tar Jcvf /data/mysql.bak.tar.xz /var/lib/mysql/
2.   
systemctl stop mariadb
3.    
cd /var/lib/mysql/
rm -rf *
4.    
scp -rp /data/mysql.bak.tar.xz 192.168.32.17:/data
5.   
tar xf /data/mysql.bak.tar.xz
6.           (          ，      ，，，，，，，， ）
mv * /var/lib/mysql
7.    
systemctl start mariadb

##LVMバックアップLVMのバックアップ(1)要求に基づいてすべてのテーブルmysql>FLUSH TABLES WITH READ LOCKをロックする;(2)バイナリログファイル及びイベント位置mysql>FLUSH LOGSを記録する.mysql> SHOW MASTER STATUS; mysql-e‘SHOW MASTER STATUS’>/PATH/TO/SomeEFILE(3)スナップショットlvcreate-L#-s-pr-n NAME/DEP/VG_を作成NAME/LV_NAME(4)ロック解除mysql>UNLOCK TABLES;(5)スナップショットボリュームをマウントし、データバックアップを実行する(6)バックアップが完了した後、スナップショットボリュームを削除する(7)ポリシーを制定し、元のボリュームからバイナリログをバックアップする
一、準備

1.     
fdisk /dev/sda
p-n-t(8e)
partprobe
pvcreate /dev/sda6
vgcreate vg_data /dev/sda6
lvcreate -n mysqldata -L 2G vg_data
lvcreate -n binlog -L 4G vg_data
mkfs.xfs /dev/vg_data/mysqldata
mkfs.xfs /dev/vg_data/binlog
2.  
mkdir /vg_data/{binlong,mysqldata} -pv
mount /dev/vg_data/binlog /vg_data/binlog/
mount /dev/vg_data/mysqldata /vg_data/mysqldata/
3.  ,        
vim /etc/my.cnf
[mysqld]
datadir=/vg_data/mysqldata
log_bin=/vg_data/binlog/mysql-bin
chown -R mysql.mysql /vg_data/
4.     
systemctl restart mariadb


reset master;        
show master logs;  

二、データベースのロック、スナップショットの開始

mysql -e 'flush tables with read lock'
mysql -e 'show master logs' > /root/bin.log
cat bin.log     7697

lvcreate -s -n snap_mysql -L 1G /dev/vg_data/mysqldata -p r (    ）
mysql -e 'unlick tables'

三、スナップショットの内容をコピーする

lvs    ，    
mount /dev/vg_data/snap_mysql /mnt     ，  UUID  
mount -o nuuid,norecovery /dev/vg_data/snap_mysql /mnt
cp -av /mnt/* /data/test      

lvremove /dev/vg_data/snap_mysql     

四、バイナリログで、スナップショット後の修正操作を最新に復元する

mysql -e 'show binary logs'       
cat /root/bin.log        7697
mysqlbinlog --start-position=7696 mysql-bin.000001 > /root/binlog.sql
mysqlbinlog mysql-bin.000002 >> /root/binlog.sql
  mysql
>set sql_log_bin=off;
>source /root/binlog.sql;
>set sql_log_bin-on;

バックアップとリカバリ

論理バックアップツール:mysqldump,mydumper,phpMyAdmin

Schemaとデータは一緒に保存され、巨大なSQL文、単一の巨大なバックアップファイル

mysqldumpツール:クライアントコマンドmysqlプロトコルでmysqlサーバに接続してバックアップmysqldump[OPTIONS]database[tables]mysqldump[OPTIONS]–B DB 1[DB 2 DB 3...]mysqldump[OPTIONS]–A[OPTIONS]

mysqldumpリファレンス:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump hellodb > /data/hellodb_bak.sql
      sql    

mysql -e 'create database hi'
mysql hi < /data/hellodb_bak.sql
         
  ：        ：          ，     ，        ，         ,        

mysqldumpの一般的なオプション:
-A,--all-databases create databaseを含むすべてのデータベースをバックアップ

mysqldump -A > /data/all`data +%F`.sql
           

-B , --databases db_name...create database文を含むバックアップのデータベースを指定します.

mysqldump -uroot -pdushan -B hellodb > /data/bak_B.sql
  helloddb     ，         

mysqldump -uroot -pdushan -B hellodb |gzip > /data/bak_B.sql.gz
mysqldump -uroot -pdushan -B hellodb |xz > /data/bak_B.sql.xz
       

unxz bak_B.sql.xz
xz -d bak_B.sql.xz
   

-E,--events:関連するすべてのevent schedulerをバックアップ
-R,--routines:すべてのストレージ・プロシージャとカスタム関数をバックアップ
-triggers:バックアップテーブル関連トリガ、デフォルト有効、-skip-triggers、バックアップトリガなし
-default-character-set=utf 8指定文字セット
-master-data[=#]:このオプションはバイナリログ1を有効にする必要があります:バックアップされたデータの前にCHANGE MASTER TO文として記録する2:注釈として記録されたCHANGE MASTER TO文このオプションは自動的にオフになります–lock-tables機能は自動的にオンになります-x|--lock-all-tables機能は自動的にオンになります-single-transaction

mysqldum -A --master-data=2 >all.sql
     ，       2，          ，1   

-F,--flush-logs:バックアップ前にログをスクロールし、テーブルをロックした後、flush logsコマンドを実行し、新しいバイナリログファイルを生成し、-Aまたは-Bオプションに合わせると、複数のデータベースがリフレッシュされます.ダンプとログのリフレッシュは、-single-transactionまたは-x、-master-dataとともに使用することで実現することを推奨します.この場合、ログは1回のみリフレッシュされます.
-compactコメントを削除し、デバッグに適し、生産は使用しない
-d,--no-dataテーブル構造のみバックアップ
-t,--no-create-infoはデータのみをバックアップし、create tableをバックアップしない
-n,–no-create-dbはcreate databaseをバックアップせず、-Aまたは-Bで上書き可能
-flush-privileges mysqlまたは関連するバックアップに使用する必要があります
-f,--force SQLエラーを無視して実行を続行
-hex-blobは16進記号を使用してバイナリ列をダンプし、BINARY、VARRBINARY、BLOB、BITのデータ型を含む列がある場合に使用し、文字化けしないようにする
-q,--quickはクエリーをキャッシュせず、直接出力し、バックアップ速度を速める
MyISAMエンジンのバックアップオプション:
温備をサポートする.ホット・スペアはサポートされていません.バックアップするライブラリをロックしてから、バックアップ・オペレーションのロック方法を起動する必要があります.
-x,–lock-all-tables:グローバル・リード・ロックを追加し、すべてのライブラリのすべてのテーブルをロックします.また、-single-transactionまたは-lock-tablesオプションを追加すると、このオプション機能が閉じます.注意:データ量が多い場合、データベースに同時にアクセスできない場合があります.
-l,–lock-tables:バックアップが必要なデータベースごとに、バックアップを開始する前にすべてのテーブルをロックします.デフォルトはonです.-skip-lock-tablesオプションは無効になります.MyISAMをバックアップする複数のライブラリでは、データが一致しない可能性があります.
注意:上記のオプションはInnoDB表と同様に有効で、温備を実現しますが、推奨しません.
##InnoDBバックアップオプションはホットスペアをサポートし、ホットスペアは使用可能だが推奨しない
–single-transaction
このオプションInnodbでは推奨されています.MyISAMは適用されません.このオプションはバックアップを開始する前に、START TRANSACTIONコマンドを実行してトランザクションをオープンします.
このオプションは、すべてのテーブルを単一のトランザクションにダンプすることで、一貫したスナップショットを作成します.マルチバージョン制御をサポートするストレージエンジンに格納されているテーブルにのみ適用されます(現在はInnoDBのみ可能).ダンプは、他のストレージエンジンと一致することを保証しません.単一トランザクションのダンプを行う場合、有効なダンプ・ファイル(正しいテーブル・コンテンツとバイナリ・ログの場所)を確保するには、ALTER TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLEのオプションと–lock-tables(コミット保留中のトランザクションを含む)のオプションは互いに反発します.
大規模なテーブルをバックアップする場合は、–single-transactionオプションと–quickを組み合わせて使用することをお勧めします.
データベース・スクリプトのライブラリ・バックアップ

     ,       ：
mysql -e 'show databases' |cat 
      ：
mysql -e 'show databases'| grep -Ev '^Database|info|performanc

一、while...doバックアップデータベース

mysql -e 'show databases'| grep -Ev '^Database|info|performanc'|while read dbname;do mysqldump -B $dbname | gzip > /data/${dbname}_bak`date +%F` .gz;done

二、sedバックアップデータベース

  ：      ，   sed///     ， /  @

mysql -e 'show databases' | grep -Ev '^Database|info| performanc' |sed -r 's@(.*)@mysqldump -B \1| gzip > /data/\1_bak`date +F%`.gz@' |bash

三、for...inバックアップデータベース

for db in `mysql -e 'show databases'| grep -Ev '^Database|info|performanc'`;do mysqldump -B $db | gzip > /data/&{db}_bak`date +%F.gz;done

!!スクリプトをbashに直接実行するにはcat f1.sh |bash
本番環境の実戦バックアップポリシー

InnoDB推奨バックアップポリシー

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM推奨バックアップポリシー

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

実戦練習
一、初期状態を復元する

1.           ,     
[root@dushan7 ~]vim /etc/my.cnf
[mysqld]
log-bin=/data/binlog/mysql-bin 
datadir=/var/lib/mysql
2.     ，      
[root@dushan7 ~]#mkdir /data/binlog
[root@dushan7 ~]#chown mysql.mysql /data/binlog/

3.    
[root@dushan7 ~]#systemctl restart mariadb

4.  mysql         ，        
mysql> show master logs;
[root@dushan7 ~]#ll /var/lib/mysql/ 
[root@dushan7 ~]#cd /var/bin/mysql/
[root@dushan7 ~]#rm -f *bin*
5.    
[root@dushan7 ~]#systemctl restart mariadb

二、バックアップリストア1.バックアップの開始

mysqldump -A -F --single-transaction -- master-data=2 --hex-blob |gzip > /data/all_`date +%F`.sql.gz

2.データベース表の変更

mysql>insert teachers values (1,'a',30,'F');
mysql>insert teachers values (6,'b',49,'F');

3.アナログ破壊データベーステーブル

systemctl stop mariadb
rm -rf /var/lib/mysql/*

4.バックアップステータスのリストア(データベースへのリストア時進数ユーザーアクセス)iptables

   ，  mysql,        ，       
systemctl start mariadb
gzip -d all.sql.gz 
mysql>set sql_log_bin=off;
mysql>source /data/all.sql;

5.最新位置状態に戻す

           ，          ，          
grep "CHANGE MASTER" /data/all.sql    position mysql-bin.00002 245
cd /data/binlog/
mysqlbinlog --start-position=245 mysql-bin.000002 > incr.sql
mysqlbinlog --start-position=245 mysql-bin.000003 >> incr.sql 
     ！！！！

less incr.sql       

        ，       ，    ,    
mysql>set sql_log_bin=off
mysql>source /data/binlog/incr.sql
mysql>set sql_log_bin=on
mysql>select * from teachers;

6.オープンユーザーアクセスデータベース
シミュレーションシーン2:日曜日のバックアップ、月曜日の午前10時に削除され、10時10分に問題を発見し、修復を開始

1.     
mysqldum -A -F --single-transaction --smaster-data=2 |gzip >/data/all.sql.gz

2.     

4.   10:00
drop table testlog

5.      10:00-10:10

6.    ，      
mysql>flush tables with read lock;
（         ，      ）

7.         
   
systemctl stop mariadb
     
rm-rf /var/lib/mysql/*
   
systemctl start mariadb
gzip -d /data/all.sql.gz
mysql
>set sql_log_bin=off
>source /data/all.sql;

8.       ，  drop table  
grep -i "change master" /data/all.sql 245 mysql-bin.000002
mysqlbinlog --start-position=245 mysql-bin.000002 > /data/incr.sql

vim /data/incr.sql
  drop table  ，     

mysqlbinlog --start-position=245 mysql-bin.000003>> /data/incr.sql
mysqlbinlog --start-position=245 mysql-bin.000004>> /data/incr.sql

9.       
mysql
>set sql_log_bin=off;
>source /data/incr.sql;
>set sql_log_bin=on;

10.       

11.