MySQLデータベース入門：データベースバックアップ操作の詳細な説明

データとなると、誰もがとても緊張します。データにはさまざまな種類がありますが、一般的に、データは非常に重要です。そのため、日々のデータバックアップ作業は、運用保守作業の焦点の中心となっています..................

まず、データベースのデータを見てみましょう

mysql> テストから * を選択します。
+-----+------+
| ID | 名前 |
+-----+------+
| 1 | 1 |
| 11 | テキスト |
| 21 | アルファベット |
| 9 | bcd |
| 111 | 1 |
| 441 | テキスト |
| 41 | アルファベット |
| 999 | bcd |
+-----+------+
セット内の行数は 8 です (0.00 秒)

1. 単一データベースのバックアップ

[root@centos6 ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sql
パスワードを入力してください: 

[root@centos6 ~]# ll /download/
合計 2
-rw-r--r--。1 ルート ルート 1888 12月 12 20:34 testbak_2016-12-12.sql

このバックアップファイルに何が含まれているか見てみましょう。

[root@centos6 ~]# egrep -v "^--|\*|^$" 

/ダウンロード/testbak_2016-12-12.sql

`test` が存在する場合はテーブルを削除します。

テーブル「test」を作成します（

 `id` int(4) NULLではない、

 `name` char(20) NULLではない

)ENGINE=MyISAM デフォルト文字セット=latin1;

LOCK TABLES `test` WRITE;

`test` に値 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd') を挿入します。

テーブルのロックを解除します。

上記のファイルの内容から、このバックアップの実際のプロセスは、データベースの作成、テーブルの構築、およびデータの挿入のための SQL ステートメントをバックアップすることであることがわかります。SQL ステートメントがエクスポートされているとも言えます。

-B パラメータ

[root@centos6 ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql

パスワードを入力してください: 

[root@centos6 ~]# egrep -v "^--|^$" /download/testbak_2016-12-12_b.sql  

/*!40101 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT を設定します */;

/*!40101 @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS を設定します */;

/*!40101 @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION を設定します */;

/*!40101 名前をutf8に設定 */;

/*!40103 @OLD_TIME_ZONE=@@TIME_ZONE を設定します */;

/*!40103 TIME_ZONE='+00:00' を設定します */;

/*!40014 @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS、UNIQUE_CHECKS=0 に設定 */;

/*!40014 @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS、FOREIGN_KEY_CHECKS=0 に設定 */;

/*!40101 @OLD_SQL_MODE=@@SQL_MODE、SQL_MODE='NO_AUTO_VALUE_ON_ZERO' を設定します */;

/*!40111 @OLD_SQL_NOTES=@@SQL_NOTES、SQL_NOTES=0 に設定 */;

CREATE DATABASE /*!32312 存在しない場合は*/ `test` /*!40100 デフォルト文字セット latin1 */;

`test` を使用します。

`test` が存在する場合はテーブルを削除します。

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

テーブル「test」を作成します（

 `id` int(4) NULLではない、

 `name` char(20) NULLではない

)ENGINE=MyISAM デフォルト文字セット=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `test` WRITE;

/*!40000 ALTER TABLE `test` でキーを無効にする */;

`test` に値 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd') を挿入します。

/*!40000 ALTER TABLE `test` ENABLE KEYS */;

テーブルのロックを解除します。

/*!40103 TIME_ZONE=@OLD_TIME_ZONE を設定します */;

/*!40101 SQL_MODE を @OLD_SQL_MODE に設定します */;

/*!40014 FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS を設定します */;

/*!40014 UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS を設定します */;

/*!40101 CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT を設定します */;

/*!40101 CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS を設定します */;

/*!40101 COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION を設定します */;

/*!40111 SQL_NOTES=@OLD_SQL_NOTES を設定します */;

-B パラメータの機能は一目瞭然です。データベースが失われた場合、データベースとテーブルを再構築してデータ復旧操作を実行することなく、このバックアップファイルを直接使用してデータベースを復元できます。

2. 圧縮バックアップ

場合によっては、データベースデータが大きいため、バックアップ時間とディスク領域を節約するために、バックアップ用に圧縮する必要がある場合があります。

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
パスワードを入力してください: 

[root@centos6 ~]# ll /download/testbak_2016-12-12.sql.gz
-rw-r--r--。1 ルート ルート 753 12月12日 20:49 /download/testbak_2016-12-12.sql.gz

[root@centos6 ~]# ll /download/
合計 14
-rw-r--r--。1 ルート ルート 2027 12月 12 20:41 testbak_2016-12-12_b.sql
-rw-r--r--。1 ルート ルート 1888 12月 12 20:34 testbak_2016-12-12.sql
-rw-r--r--。1 ルート ルート 753 12月12日 20:49 testbak_2016-12-12.sql.gz

圧縮効果も確認できます

3. マルチデータベースバックアップ

[root@centos6 ~]# mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz
パスワードを入力してください: 
-- 警告: テーブル mysql.event のデータをスキップします。--events オプションを明示的に指定してください。

[root@centos6 ~]# ll /download/testbak_2016-12-12.sql01.gz 
-rw-r--r--。1 ルート ルート 152696 12月12日 20:52 /download/testbak_2016-12-12.sql01.gz

ここには警告メッセージがありますが、無視するか、バックアップ中にパラメータを追加することができます。バックアップステートメントは次のとおりです。

[root@centos6 ~]# mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz
パスワードを入力してください: 
[root@centos6 ~]# ll /download/testbak_2016-12-12.sql02.gz                 
-rw-r--r--。1 ルート ルート 152749 12月12日 20:54 /download/testbak_2016-12-12.sql02.gz

こうすれば警告メッセージは表示されなくなります。

ただし、複数のデータベースをまとめてバックアップするこの方法では問題が発生します。データベースの 1 つだけに問題がある場合、単一のデータベースを復元することは困難です。したがって、このバックアップ方法は一般的に使用されておらず、実際のニーズを満たしていません。したがって、複数のデータベースをバックアップする場合は、複数の単一データベースバックアップ操作が必要です。

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbackup_$(date +%F).sql.gz         
パスワードを入力してください: 

[root@centos6 ~]# mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz      
パスワードを入力してください: 

[root@centos6 ~]# ll /download/
合計80
-rw-r--r--。1 ルート ルート 152608 12月12日 20:58 mysqlbak_2016-12-12.sql.gz
-rw-r--r--。1 ルート ルート 754 12月12日 20:58 testbackup_2016-12-12.sql.gz
-rw-r--r--。1 ルート ルート 2027 12月 12 20:41 testbak_2016-12-12_b.sql
-rw-r--r--。1 ルート ルート 1888 12月 12 20:34 testbak_2016-12-12.sql
-rw-r--r--。1 ルート ルート 152696 12月12日 20:52 testbak_2016-12-12.sql01.gz
-rw-r--r--。1 ルート ルート 152749 12月12日 20:54 testbak_2016-12-12.sql02.gz
-rw-r--r--。1 ルート ルート 753 12月12日 20:49 testbak_2016-12-12.sql.gz

4. 単一テーブルバックアップ

別々のデータベースにバックアップする目的は、データベースを復元する際の操作を容易にすることですが、データベース内のテーブルが破損した場合、データベース全体を復元できないという問題にも直面するため、実際の運用では、別々のデータベースとテーブルのバックアップがよく使用されます。このようにすると、データもバックアップされ、リカバリ時に操作しやすくなります。

[root@centos6 ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql   

パスワードを入力してください: 

[root@centos6 ~]# egrep -v "#|^$|\*" /download/test_testbak_2016-12-12.sql

-- MySQL ダンプ 10.13 Distrib 5.5.52、linux2.6 (x86_64) 用
--
-- ホスト: localhost データベース: test
-- ------------------------------------------------------
--サーバーバージョン 5.5.53-ログ
--
-- 現在のデータベース: `test`
--
`test` を使用します。
--
-- テーブル `test` のテーブル構造
--
`test` が存在する場合はテーブルを削除します。
テーブル「test」を作成します（
 `id` int(4) NULLではない、
 `name` char(20) NULLではない
)ENGINE=MyISAM デフォルト文字セット=latin1;
--
-- テーブル `test` のデータをダンプしています
--
LOCK TABLES `test` WRITE;
`test` に値 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd') を挿入します。
テーブルのロックを解除します。
--
-- 現在のデータベース: `test`
--
`test` を使用します。
--
-- テーブル `test` のテーブル構造
--
`test` が存在する場合はテーブルを削除します。
テーブル「test」を作成します（
 `id` int(4) NULLではない、
 `name` char(20) NULLではない
)ENGINE=MyISAM デフォルト文字セット=latin1;
--
-- テーブル `test` のデータをダンプしています
--
LOCK TABLES `test` WRITE;
`test` に値 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd') を挿入します。
テーブルのロックを解除します。
-- ダンプは 2016-12-12 21:13:16 に完了しました

したがって、シャードテーブルのバックアップは、シャードライブラリのバックアップと同じです。単一テーブルのバックアップ操作を複数回実行するだけで済みます。ただし、一部の友人は間違いなく質問します。ライブラリに数千または数万のテーブルがある場合、そのようなバックアップを準備するにはどのくらいの時間がかかりますか? ? ? ?大量のデータのバックアップには、専門的なバックアップツールを使用できます。少量のデータまたはテーブルのバックアップの場合は、バックアップ操作をスクリプトに記述し、スケジュールされたタスクに含めることができます。バックアップが成功したかどうかを確認するだけで済みます。

実際の運用環境での簡単なバックアップスクリプトを参考用に共有します。

[root@centos6 スクリプト]# vi bak.sh 
#!/bin/sh
##########################################
#このスクリプトはmingonggeのルートによって作成されました
#2016-11-11 作成
#######################################

ip=`grep 'IPADDR' /etc/sysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}'`
#サーバIP変数BAKDIR=/backupを定義する 
#バックアップパスを定義する [ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
 #パスが存在しない場合は作成します。これは、サーバーが多数ある場合に便利です。 DB_PWD="mingongge"
DB_USER="ルート"
MYSQL="/application/mysql/bin/mysql"
MYSQL_DUMP="/application/mysql/bin/mysqldump"
DATA=`日付 +%F`

####テストのデータベースのバックデータ####

DB_NAME=`$MYSQL -u$DB_USER -p$DB_PWD -e "データベースを表示;"|sed '1,5d'`
 #$DB_NAME で名前のデータベース変数を定義します
#forループ文はライブラリ名を取り、
 $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz 
 #データベースの完全バックアップ [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name}
#`$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'` のテーブル名のデータベースのバックアップ ファイルを区別するには、このパスを判断します。
#forループ文はテーブル名を取り、
  $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${名前} ${テーブル名} |gzip >$BAKDIR/${ip}/${名前}/${テーブル名}_$DATA.sql.gz
#テーブルのバックアップが完了しました
終わり

実行結果は次のとおりです。

[root@ranzhioa ~]# ツリー /backup/
/バックアップ/
10.1xx.1xx.1xx #サーバーIP
 xxxxxxx #実際のデータベース名はcash_balance_2016-12-15.sql.gzです
キャッシュデポジター_2016-12-15.sql.gz
キャッシュトレード_2016-12-15.sql.gz
crm_customer_2016-12-15.sql.gz
crm_delivery_2016-12-15.sql.gz
crm_order_2016-12-15.sql.gz
crm_orderAction_2016-12-15.sql.gz
crm_orderField_2016-12-15.sql.gz
crm_plan_2016-12-15.sql.gz

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: