MySQL 特定のデータベースのバックアップとリカバリー

カキローウェブのTwitterはこちら

MySQLで特定のデータベースのバックアップを作成する方法と、リカバリーを行う方法について、以下に示します。

※ここではMySQLのバージョン5.6を使用して、動作確認を行っています。

※CentOSへのMySQLのインストールについては、当サイトのCentOSのyumリポジトリにMySQLを追加し、最新バージョンをインストールのページ、CentOSにMySQLをRPMからインストールのページもご覧ください。

※全てのデータベースのバックアップとリカバリーについては、当サイトのMySQL 全てのデータベースのバックアップとリカバリーのページをご覧ください。

※特定のテーブルのバックアップとリカバリーについては、当サイトのMySQL 特定のテーブルのバックアップとリカバリーのページをご覧ください。

動作確認用のデータベース「db01」の作成

ここでは、データベースのバックアップとリカバリーの動作確認用に、以下のようなデータベース「db01」を使用します。

データベース「db01」を作成

データベース「db01」を以下のように作成します。

mysql>, CREATE DATABASE db01 CHARACTER SET utf8 COLLATE utf8_bin,

以降のデータベース「db01」へのテーブルの作成、データの登録は、以下のように「db01」を選択した状態で行うものとします。

mysql>, USE db01,

データベース「db01」にテーブル「tbl11」を作成

データベース「db01」にテーブル「tbl11」を以下のように作成します。

mysql>, CREATE TABLE tbl11 (id CHAR(5) NOT NULL, num11 INT, txt11 VARCHAR(50), date11 DATETIME, PRIMARY KEY (id)) ENGINE InnoDB,

テーブル「tbl11」にデータを登録

テーブル「tbl11」に以下のようなデータを登録します。

mysql>, INSERT INTO tbl11 (id, num11, txt11, date11) VALUES ('00001', 1, '①あ〜あ001', '2015-01-02 03:04:05'),

mysql>, INSERT INTO tbl11 (id, num11, txt11, date11) VALUES ('00002', 2, '②い〜い002', '2015-11-12 13:14:15'),

データベース「db01」にテーブル「tbl12」を作成

データベース「db01」にテーブル「tbl12」を以下のように作成します。

mysql>, CREATE TABLE tbl12 (num12 INT UNSIGNED NOT NULL AUTO_INCREMENT, txt12 VARCHAR(30), date12 DATETIME, PRIMARY KEY (num12)) ENGINE InnoDB,

テーブル「tbl12」にデータを登録

テーブル「tbl12」に以下のようなデータを登録します。

mysql>, INSERT INTO tbl12 (txt12, date12) VALUES ('aaa', '2015-01-01 00:00:00'),

mysql>, INSERT INTO tbl12 (txt12, date12) VALUES ('bbb', '2015-01-01 01:00:00'),

特定のデータベースのバックアップを作成

特定のデータベースのバックアップを作成するには、以下のようなコマンドを実行します。

# mysqldump -u root -pxxxxxxxxxx --databases db01 >, db01.sql

バックアップの作成には、「mysqldump」コマンドを使用します。
「-u」オプションに続けて、MySQLに接続するユーザーを指定します。

ここでは、rootユーザーを指定しています。
「-p」オプションに続けて、rootユーザーのパスワードを指定します。（-pとパスワードの間にスペースは空けません。）

ただし、この場合「Warning: Using a password on the command line interface can be insecure.」とパスワードをコマンドラインに指定することは、安全でないことを示す警告メッセージが表示されます。

ここで「-p」オプションにパスワードを指定しなかった場合は、「Enter password:」と表示され、パスワードの入力を求められるようになります。
「–databases」オプションで、バックアップ対象のデータベースを指定します。ここでは、データベース「db01」を指定しています。

※「–databases db01 db02」のように、バックアップ対象のデータベースを半角スペースで区切って、複数指定することもできます。
リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

ここでは、ファイル「db01.sql」に出力するようにしています。

作成されたバックアップの内容は、以下のようになります。

-- MySQL dump 10.13 Distrib 5.6.25, for Linux (x86_64) -- -- Host: localhost Database: db01 -- ------------------------------------------------------ -- Server version 5.6.25-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */, /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */, /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */, /*!40101 SET NAMES utf8 */, /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */, /*!40103 SET TIME_ZONE='+00:00' */, /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */, /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */, /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */, /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */, -- -- Current Database: `db01` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db01` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */, USE `db01`, -- -- Table structure for table `tbl11` -- DROP TABLE IF EXISTS `tbl11`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl11` ( `id` char(5) COLLATE utf8_bin NOT NULL, `num11` int(11) DEFAULT NULL, `txt11` varchar(50) COLLATE utf8_bin DEFAULT NULL, `date11` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `tbl11` -- LOCK TABLES `tbl11` WRITE, /*!40000 ALTER TABLE `tbl11` DISABLE KEYS */, INSERT INTO `tbl11` VALUES ('00001',1,'①あ〜あ001','2015-01-02 03:04:05'),('00002',2,'②い〜い002','2015-11-12 13:14:15'), /*!40000 ALTER TABLE `tbl11` ENABLE KEYS */, UNLOCK TABLES, -- -- Table structure for table `tbl12` -- DROP TABLE IF EXISTS `tbl12`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl12` ( `num12` int(10) unsigned NOT NULL AUTO_INCREMENT, `txt12` varchar(30) COLLATE utf8_bin DEFAULT NULL, `date12` datetime DEFAULT NULL, PRIMARY KEY (`num12`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `tbl12` -- LOCK TABLES `tbl12` WRITE, /*!40000 ALTER TABLE `tbl12` DISABLE KEYS */, INSERT INTO `tbl12` VALUES (1,'aaa','2015-01-01 00:00:00'),(2,'bbb','2015-01-01 01:00:00'), /*!40000 ALTER TABLE `tbl12` ENABLE KEYS */, UNLOCK TABLES, /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */, /*!40101 SET SQL_MODE=@OLD_SQL_MODE */, /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */, /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */, /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */, /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */, /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */, /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */, -- Dump completed on 2015-11-15 23:10:26

全てのデータベースのバックアップとリカバリーのページで示した、全てのデータベースをバックアップした内容から「db02」と「mysql」を除いた形で、「db01」のみのバックアップが作成されます。

尚、特定のデータベースのバックアップの作成は、以下のように「–databases」オプションを指定せずに行うこともできます。

# mysqldump -u root -pxxxxxxxxxx db01 >, db01.sql

この場合、バックアップの内容にCREATE DATABASE文と、USE文は含まれなくなります。

また、「–databases」オプションを指定しない場合、バックアップ対象のデータベースに指定できるのは1つのみになります。

特定のデータベースのバックアップからのリカバリー

上記で取得した特定のデータベースのバックアップ「db01.sql」からリカバリーを行うには、以下のようなコマンドを実行します。

# mysql -u root -pxxxxxxxxxx <, db01.sql

バックアップからのリカバリーには、「mysql」コマンドを使用します。
「-u」オプションに続けて、MySQLに接続するユーザーを指定します。

ここでは、rootユーザーを指定しています。
「-p」オプションに続けて、rootユーザーのパスワードを指定します。（-pとパスワードの間にスペースは空けません。）

ただし、この場合「Warning: Using a password on the command line interface can be insecure.」とパスワードをコマンドラインに指定することは、安全でないことを示す警告メッセージが表示されます。

ここで「-p」オプションにパスワードを指定しなかった場合は、「Enter password:」と表示され、パスワードの入力を求められるようになります。
リダイレクトを使用して、バックアップの内容をファイルから入力するようにします。

ここでは、ファイル「db01.sql」から入力するようにしています。

尚、バックアップの作成時に「–databases」オプションを指定していない場合は、バックアップの内容にCREATE DATABASE文と、USE文が含まれていないため、リカバリーを行う前にデータベースの作成を行い、リカバリー時には以下のように使用するデータベースを指定する必要があります。

# mysql -u root -pxxxxxxxxxx -D db01 <, db01.sql

「-D」オプションに続けて、使用するデータベースを指定します。

ここでは、データベース「db01」を指定しています。

テーブルのデータのINSERT文を除いたバックアップを作成

テーブルのデータのINSERT文を除いたバックアップを作成するには、以下のようなコマンドを実行します。

※「–all-databases」オプションを指定して、全てのデータベースのバックアップを作成する際にデータを除くと、データベース「mysql」のテーブル「user」データも空になり、ログインユーザーがなくなってしまいますのでご注意ください。

# mysqldump -u root -pxxxxxxxxxx --databases db01 --no-data >, db01.sql

バックアップの作成には、「mysqldump」コマンドを使用します。
「-u」オプションに続けて、MySQLに接続するユーザーを指定します。

ここでは、rootユーザーを指定しています。
「-p」オプションに続けて、rootユーザーのパスワードを指定します。（-pとパスワードの間にスペースは空けません。）

ただし、この場合「Warning: Using a password on the command line interface can be insecure.」とパスワードをコマンドラインに指定することは、安全でないことを示す警告メッセージが表示されます。

ここで「-p」オプションにパスワードを指定しなかった場合は、「Enter password:」と表示され、パスワードの入力を求められるようになります。
「–databases」オプションで、バックアップ対象のデータベースを指定します。ここでは、データベース「db01」を指定しています。

※「–databases db01 db02」のように、バックアップ対象のデータベースを半角スペースで区切って、複数指定することもできます。
「–no-data」オプションを指定すると、テーブルのデータのINSERT文を除いたバックアップが作成されるようになります。
リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

ここでは、ファイル「db01.sql」に出力するようにしています。

作成されたバックアップの内容は、以下のようになります。

-- MySQL dump 10.13 Distrib 5.6.25, for Linux (x86_64) -- -- Host: localhost Database: db01 -- ------------------------------------------------------ -- Server version 5.6.25-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */, /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */, /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */, /*!40101 SET NAMES utf8 */, /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */, /*!40103 SET TIME_ZONE='+00:00' */, /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */, /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */, /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */, /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */, -- -- Current Database: `db01` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db01` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */, USE `db01`, -- -- Table structure for table `tbl11` -- DROP TABLE IF EXISTS `tbl11`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl11` ( `id` char(5) COLLATE utf8_bin NOT NULL, `num11` int(11) DEFAULT NULL, `txt11` varchar(50) COLLATE utf8_bin DEFAULT NULL, `date11` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Table structure for table `tbl12` -- DROP TABLE IF EXISTS `tbl12`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl12` ( `num12` int(10) unsigned NOT NULL AUTO_INCREMENT, `txt12` varchar(30) COLLATE utf8_bin DEFAULT NULL, `date12` datetime DEFAULT NULL, PRIMARY KEY (`num12`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */, /*!40101 SET SQL_MODE=@OLD_SQL_MODE */, /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */, /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */, /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */, /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */, /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */, /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */, -- Dump completed on 2015-11-16 0:36:58

バックアップからのリカバリー方法は、上記に記載しているものと同様です。

MySQL 特定のデータベースのバックアップとリカバリーについては、以上です。