MySQL 特定のテーブルのバックアップとリカバリー

October 30, 2021 MySQL, MySQL 特定のテーブルのバックアップとリカバリー

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'),

特定のテーブルのバックアップを作成

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

# mysqldump -u root -pxxxxxxxxxx db01 tbl11 >, tbl11.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」のテーブル「tbl11」を指定しています。

    ※「db01 tbl11 tbl12」のように、バックアップ対象のテーブルを半角スペースで区切って、複数指定することもできます。

  • リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

    ここでは、ファイル「tbl11.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 */, -- -- 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, /*!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:56:13

  • バックアップの内容は、テーブルの作成、データの登録といった流れとなり、使用するデータベースを指定するUSE文は含まれません。

特定のテーブルのバックアップからのリカバリー

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

※テーブルをリカバリーするデータベース（ここでは「db01」）が、事前に作成されている必要があります。

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

  • バックアップからのリカバリーには、「mysql」コマンドを使用します。

  • 「-u」オプションに続けて、MySQLに接続するユーザーを指定します。

    ここでは、rootユーザーを指定しています。

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

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

    ここで「-p」オプションにパスワードを指定しなかった場合は、「Enter password:」と表示され、パスワードの入力を求められるようになります。

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

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

  • リダイレクトを使用して、バックアップの内容をファイルから入力するようにします。

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

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

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

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

# mysqldump -u root -pxxxxxxxxxx db01 tbl11 --no-data >, tbl11.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」のテーブル「tbl11」を指定しています。

    ※「db01 tbl11 tbl12」のように、バックアップ対象のテーブルを半角スペースで区切って、複数指定することもできます。

  • 「–no-data」オプションを指定すると、テーブルのデータのINSERT文を除いたバックアップが作成されるようになります。

  • リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

    ここでは、ファイル「tbl11.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 */, -- -- 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 */, /*!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 22:49:58

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

MySQL 特定のテーブルのバックアップとリカバリーについては、以上です。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

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