MYSQL5.5から利用出来る以下のOptionを検証してみました。

--dump-slave --apply-slave-statements --include-master-host-port

前回はマスターで取得したデータはマスターのバイナリーログでリカバリー、
スレーブで取得したバックアップはスレーブのバイナリーログでリカバリーするように
–master-data=2オプションを付与してバックアップしてリカバリーを検証。
MYSQLDUMPを利用したオンラインバックアップと差分ログ適用

MYSQL5.5から追加されたdump-slaveオプションを利用すると、
追加のスレーブ作成が簡単に出来ます。(STOP SLAVE ~ START SLAVEコマンドも含まれます。)

先ずはSlaveデータベースにてレプリケーション対象のDBをバックアップします。

[root@HOME002 backup]# mysqldump --single-transaction --flush-logs --dump-slave --apply-slave-statements --include-master-host-port --databases test > DUMP_AT_SLAVE.sql -u root -p
Enter password:
[root@HOME002 backup]# ls -l
合計 12
-rw-r--r-- 1 root root 4357  1月 27 16:18 DUMP_AT_SLAVE.sql
[root@HOME002 backup]#


ダンプファイルは以下のようにスレーブのレプリケーションを停止して、
対象DBを再作成してマスターのログPOSITIONに合わせてからデータリストア後に
レプリケーションを再開しています。

[root@HOME002 backup]# cat DUMP_AT_SLAVE.sql
-- MySQL dump 10.13  Distrib 5.5.29, for linux2.6 (i686)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.5.29-log

   省略.....

--
-- stop slave statement to make a recovery dump)
--

STOP SLAVE;

--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--

CHANGE MASTER TO MASTER_HOST='HOME001', MASTER_PORT='3306', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=107;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

--
-- Table structure for table `TABLE001`
--


   省略.....


--
-- start slave statement to make a recovery dump)
--

START SLAVE;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- Dump completed on 2013-01-27 16:18:36
[root@HOME002 backup]#

上記のダンプファイルのCHANGE MASTERの部分に以下のスレーブユーザー設定を追記

MASTER_USER = 'slave_user',MASTER_PASSWORD = 'slave_password',

そして、新規Slaveの/etc/my.cnfにホストIDなどを追加しておけば、
直ぐにSlaveを追加する事が出来ます。

また、マスターのログとPOSITIONを参照しているのでマスターのDBが破損した場合も
リストアしてマスターのPOSITIONを確認した上でバイナリーログのリストアが可能です。

参考までにオプションを変更した場合のバックアップの違いは以下の通りです。

[root@HOME002 backup]# mysqldump --single-transaction --flush-logs --dump-slave --apply-slave-statements --include-master-host-port --databases test > DUMP_AT_SLAVE.sql -u root -p
Enter password:
[root@HOME002 backup]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DUMP_WITH_MASTER.sql -u root -p
Enter password:
[root@HOME002 backup]#

[root@HOME002 backup]# ls -l
合計 24
-rw-r--r-- 1 root root 4617  1月 27 17:37 DUMP_AT_SLAVE.sql
-rw-r--r-- 1 root root 4409  1月 27 17:39 DUMP_WITH_MASTER.sql
[root@HOME002 backup]# diff DUMP_AT_SLAVE.sql DUMP_WITH_MASTER.sql
19c19
< -- stop slave statement to make a recovery dump)
---
> -- Position to start replication or point-in-time recovery from
22,28c22
< STOP SLAVE;
<
< --
< -- Position to start replication or point-in-time recovery from (the master of this slave)
< --
<
< CHANGE MASTER TO MASTER_HOST='HOME001', MASTER_PORT='3306', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=126                          2;
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=107;
63,68d56
<
< --
< -- start slave statement to make a recovery dump)
< --
<
< START SLAVE;
79c67
< -- Dump completed on 2013-01-27 17:37:42
---
> -- Dump completed on 2013-01-27 17:39:13
[root@HOME002 backup]#

diff

参考サイト
MySQLコミュニティ騒然!MySQL 5.5.4が与えるインパクト。
MySQL 5.5新機能徹底解説
MYSQLDUMPを利用したオンラインバックアップと差分ログ適用



MYSQLDUMPを利用する時のOPTION確認とDUMPした後にバイナリーログを適用して、
最新のデータをリカバリーする方法の再確認。

以下のようなOPTIONでDB全体の整合性とバイナリーログのロールフォワードに必要なデータをダンプする。

–master-dataオプションと対象ロック

mysqldump --lock-all-tables --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p

–lock-all-tablesと–single-transactionは同時に利用する事が出来ません。HELPを読むと–lock-all-tablesを利用すると
自動的に–single-transactionはOFFにされるようです。–single-transactionはトランザクションをサポートする
InnoDBにのみ利用可能なのでMyISAMなどには有効では無いようです。同時にオプションを指定すると以下のようなエラーになる。
また–master-dataオプションを指定している場合は自動的に–lock-all-tablesがONになるようです。
暗黙的にONになるので運用者が明確にしておく為に意図的に指定しておいても良いかもしれません。
但し、–single-transactionが指定されていると–master-dataを指定していても–lock-all-tablesはONにならない様です。

[root@HOME002 data]# mysqldump --single-transaction --lock-all-tables --flush-logs --master-data=2 --databases test > /home/mysql/MYSQL_dump20130125.sql -u root -p
mysqldump: You can't use --single-transaction and --lock-all-tables at the same time.
[root@HOME002 data]#

こちらのバックアップオプションで特定データベースもしくはDB全体をバックアップ
InnoDBやMYISAMを含む全てのデータベースをDumpする場合
(–lock-all-tablesはあえて残してあります)
例)

 mysqldump --lock-all-tables --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
 mysqldump --lock-all-tables --flush-logs --master-data=2 --all-databases >  ALL_DB_DUMP.sql -u root -p

InnoDBのみ利用していて整合性の取れたデータをDumpする場合
例)

 mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
 mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >  ALL_DB_DUMP.sql -u root -p

—————————————————
以下のHELP抜粋
—————————————————
-x, –lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns –single-transaction and
–lock-tables off.

–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off –lock-tables.

–master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
–lock-all-tables on, unless –single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don’t
forget to read about –single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns –lock-tables
off.

-F, –flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
–databases= or –all-databases), the logs will be
flushed for each database dumped. The exception is when
using –lock-all-tables or –master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use –lock-all-tables or –master-data with
–flush-logs.

-A, –all-databases Dump all the databases. This will be same as –databases
with all databases selected.

–dump-date Put a dump date to the end of the output.
(Defaults to on; use –skip-dump-date to disable.)

-q, –quick Don’t buffer query, dump directly to stdout.
(Defaults to on; use –skip-quick to disable.)
—————————————————


MYISAMを含むデータもあるのでFULLをあえて–lock-all-tablesで取得して、
InnoDBしか含まないtestデータベースを–single-transactionで取得してます。

※本番環境では状況によって使い分けして下さい。

[root@HOME001 backup]# dumpdate=`date '+%F'`
[root@HOME001 backup]# dumpfile=MYSQL_DUMP_$dumpdate
[root@HOME001 backup]# dumpfile_dir=/home/mysql/backup/
[root@HOME001 backup]# mysqldump --lock-all-tables --flush-logs --master-data=2 --all-databases >  $dumpfile_dir$dumpfile\_ALL.sql -u root -p
Enter password:
[root@HOME001 backup]# 
[root@HOME001 backup]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > $dumpfile_dir$dumpfile\_test.sql -u root -p
Enter password:
[root@HOME001 backup]# ls -l /home/mysql/backup/MYSQL_DUMP_*                                                          
-rw-r--r--. 1 root root 516386  1月 26 11:00 2013 /home/mysql/backup/MYSQL_DUMP_2013-01-26_ALL.sql
-rw-r--r--. 1 root root   4000  1月 26 11:03 2013 /home/mysql/backup/MYSQL_DUMP_2013-01-26_test.sql
[root@HOME001 backup]#


FULLバックアップを取得したので先ずはデータの確認して、
データを追記した後に擬似的な操作ミスとしてTruncateしてデータを空にしてみる。

mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 25 | LOG20120113          | mysql-bin.000034 Take Full Backup             |
| 24 | LOG20120111--01      | after mysql-bin.000034                        |
| 23 | LOG20120111--01      | after mysql-bin.000034                        |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.62 sec)

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.13 sec)

mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 35 | Recreate Replication | after dump 20120126                           |
| 34 | Recreate Replication | after dump 20120126                           |
| 33 | Recreate Replication | after dump 20120126                           |
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>


mysql> truncate table TABLE001;
Query OK, 0 rows affected (0.40 sec)

mysql> select * from TABLE001 order by id desc limit 0,10;
Empty set (0.00 sec)

mysql>

confirmation1


上記でバックアップしたダンプファイルの中身を確認してLOGのポジションを確認。

[root@HOME001 backup]# cat MYSQL_DUMP_2013-01-26_test.sql | egrep -i master
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;
[root@HOME001 backup]#


擬似オペミス直前まで戻す為にどのバイナリーログまで戻したら良いか確認。

最後のバックアップから全てのデータを戻したい場合は、
バックアップのログポジションを確認した後に確認したポジション
以降の全てのログを戻せば良い。

[root@HOME001 data]# mysqlbinlog --no-defaults mysql-bin.000007 | egrep -i -B5 truncate
#130126 11:26:50 server id 1  end_log_pos 929   Xid = 366
COMMIT/*!*/;
# at 929
#130126 11:41:11 server id 1  end_log_pos 1015  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1359168071/*!*/;
truncate table TABLE001
[root@HOME001 data]#

ロールフォワードする為には以下のログの中身を適用する必要がある。
今回の検証で確認したケースでのStartとEndのポジション

mysql-bin.000007
 –start-position=107
 –stop-position=929

バイナリーログから戻すべきデータを再作成する為のスクリプトを作成する。
OracleでいうArchiveログを適用するRecoveryモード時に適用するログをマニュアル作成する感じ?
Positionは上記で確認したPositionを設定。

[root@HOME001 data]# mysqlbinlog --no-defaults --start-position=107 --stop-position=929 mysql-bin.000007 > /home/mysql/backup/recovery.sql
[root@HOME001 data]#

障害発生前まで戻す為の材料が揃ったのでリカバリー開始

[root@HOME001 backup]# ls -l
合計 524
drwxr-xr-x. 6 root root   4096  1月 24 14:45 2013 2013-01-24_14-44-57
drwxr-xr-x. 6 root root   4096  1月 24 15:23 2013 2013-01-24_15-23-31
-rw-r--r--. 1 root root 516386  1月 26 11:00 2013 MYSQL_DUMP_2013-01-26_ALL.sql
-rw-r--r--. 1 root root   4000  1月 26 11:03 2013 MYSQL_DUMP_2013-01-26_test.sql
-rw-r--r--. 1 root root   2713  1月 26 12:17 2013 recovery.sql
[root@HOME001 backup]#

[root@HOME001 backup]# mysql -u root -p test < MYSQL_DUMP_2013-01-26_test.sql
Enter password:
&#91;root@HOME001 backup&#93;# mysql -u root -p test < recovery.sql
Enter password:
&#91;root@HOME001 backup&#93;#
&#91;/SHELL&#93;

<strong>リカバリー完了したのでデータを確認して完了。</strong>
[SHELL]
mysql> use test
Database changed
mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 35 | Recreate Replication | after dump 20120126                           |
| 34 | Recreate Replication | after dump 20120126                           |
| 33 | Recreate Replication | after dump 20120126                           |
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>

after

スレーブでmysqldumpを取得時にバイナリーログPOSITIONを記録する場合の留意:

--master-data

オプションは、バックアップを取得したサーバーでのポジションになるので
スレーブでバックアップ取得していてマスターDBが破損した場合でも、
スレーブのバイナリーログから対象のデータをリカバリーする必要があります。
もしマスター側のバイナリーログから必要なDDL、DMLを追加で取得する場合は、
調査と対応に時間がかなりかかる事が想定されます。
MYSQL5.5以降であれば、スレーブで取得する場合は

--dump-slave

を使用する事で、
MasterデータベースのバイナリーログのPOSITIONを記録してくれるので、
マスターのログを利用してのリカバリーが可能です。
但し、マスターが全損する場合もあるので用途によって運用でカバーする必要があります。

検証結果

mysqldump

参考サイト
How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?
mysqldump –single-transaction に –flush-logs をつけてはいけない
mysqldump の option 復習
7.12. mysqldump — データベースバックアッププログラム
mysqlbinlog — バイナリログファイルを処理するためのユーティリティ


以前にもバックアップのレビューを不定期で行ってきましたが、
MYSQLのバージョンも上がっているので、改めてバックアップのレビューをしておく。

Defaultオプションの確認

[root@CentOS64VM data]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/var/lib/mysql/mysql.sock --quick --max_allowed_packet=16M --default-character-set=utf8
[root@CentOS64VM data]#

mysqldump default option

–master-data=2
If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

ここら辺は、”--dump-slave” スレーブにてダンプをとった場合、
CHANGE MASTER情報をダンプに含むに入れ替えられるか要検証。
合わせて、”--apply-slave-statements”: STOP SLAVEおよびSTART SLAVEコマンドを、
CHANGE MASTERの前後に追加してくれるオプションも検証。

ログのFlush
--flush-logs
Flush the MySQL server log files before starting the dump.
This option requires the RELOAD privilege. If you use this option in combination
with the –all-databases option, the logs are flushed for each database dumped.
The exception is when using –lock-all-tables or –master-data: In this case, the logs are flushed only once,

ロック関連確認
--lock-all-tables, -x
--single-transaction

Lock all tables across all databases. This is achieved by acquiring a global read lock
for the duration of the whole dump. This option automatically turns off --single-transaction
and --lock-tables. --single-transaction is a much better option than --lock-tables
because it does not need to lock the tables at all.

Because –lock-tables locks tables for each database separately,
this option does not guarantee that the tables in the dump file are
logically consistent between databases. Tables in different databases
may be dumped in completely different states.

詳細は沢山あるので、4.5.4. mysqldump — A Database Backup Programを確認。

関連リンク
MYSQLバックアップ見直し

参考サイト
4.5.4. mysqldump — A Database Backup Program
MySQL: mysqldumpのコマンドオプションメモ
MySQLのmysqldump で –skip-opt を使うときに気をつける
mysqldump –dump-slave についてAdd Star
MySQLコミュニティ騒然!MySQL 5.5.4が与えるインパクト。


MYSQLDUMPによるデータベースのバックアップ
何も指定しない場合は、以下のようになっている。

[root@colinux tmp]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --default-character-set=utf8 --quick --max_allowed_packet=16M --default-character-set=utf8

[root@colinux tmp]#

mysql_dump_review4

特定のデータベースから、全てのテーブルのデータのみをバックアップ
[root@colinux tmp]# mysqldump --no-create-info --tab=/tmp --lines-terminated-by="\r\n" STUDY -u root -p
Enter password:
[root@colinux tmp]#

mysql_dump_review

[root@colinux tmp]# cat MYSQLIMP.sql
[root@colinux tmp]# cat MYSQLIMP.txt
100 This is MYSQLIMPORT
101 STUDY AT HOME
102 HAVE A FUN
103 I WISH I CAN SLEEP..
[root@colinux tmp]#

特定のデータベースから、全てのオブジェクト作成DDLをバックアップ

[root@colinux tmp]# mysqldump --no-data --tab=/tmp --lines-terminated-by="\r\n" STUDY -u root -p
Enter password:
[root@colinux tmp]#

mysql_dump_review_3

[root@colinux tmp]# cat MYSQLIMP.sql
— MySQL dump 10.13 Distrib 5.1.39, for pc-linux-gnu (i686)

— Host: localhost Database: STUDY
— ——————————————————
— Server version 5.1.39-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′ */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=” */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


— Table structure for table `MYSQLIMP`

DROP TABLE IF EXISTS `MYSQLIMP`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `MYSQLIMP` (
`id` int(11) NOT NULL DEFAULT ‘0’,
`n` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2009-11-07 21:08:30
[root@colinux tmp]#

特定のデータベースから、全てのテーブルのデータとオブジェクト作成DDLをバックアップ

[root@colinux tmp]# mysqldump --tab=/tmp --lines-terminated-by="\r\n" STUDY -u root -p
Enter password:
[root@colinux tmp]#

mysql_dump_review_2

[root@colinux tmp]# cat MYSQLIMP.sql
— MySQL dump 10.13 Distrib 5.1.39, for pc-linux-gnu (i686)

— Host: localhost Database: STUDY
— ——————————————————
— Server version 5.1.39-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′ */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=” */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


— Table structure for table `MYSQLIMP`

DROP TABLE IF EXISTS `MYSQLIMP`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `MYSQLIMP` (
`id` int(11) NOT NULL DEFAULT ‘0’,
`n` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2009-11-07 20:59:38
[root@colinux tmp]# cat MYSQLIMP.txt
100 This is MYSQLIMPORT
101 STUDY AT HOME
102 HAVE A FUN
103 I WISH I CAN SLEEP..
[root@colinux tmp]#

※ --all-databasesなどで全部のDBをまとめて取得することも可能

——————————————————————————–
以下MYSQLDUMPのヘルプ
——————————————————————————–
[root@colinux tmp]# mysqldump

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

[root@colinux tmp]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.1.39, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
-a, --all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a 'DROP DATABASE' before each create.
--add-drop-table Add a 'drop table' before each create.
--add-locks Add locks around insert statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory where character sets are.
-i, --comments Write additional information.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
--create-options Include all MySQL specific create options.
-B, --databases To dump several databases. Note the difference in usage;
In this case no tables are given. All name arguments are
regarded as databasenames. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED;
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-x, --first-slave Deprecated, renamed to --lock-all-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an sql-error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.
--ignore-table=database.table
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
--log-error=name Append warnings and errors to given file.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.
--max_allowed_packet=#
--net_buffer_length=#
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
not be put in the output. The above line will be added
otherwise, if --databases or --all-databases option was
given.}.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name Socket file to use for connection.
-T, --tab=name Creates tab separated textfile for each table to given
path. (creates .sql and .txt files). NOTE: This only
works if mysqldump is run on the same machine as the
mysqld daemon.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records; QUOTES mandatory!
-X, --xml Dump a database as well formed XML.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 16777216
net_buffer_length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket /tmp/mysql.sock
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)

[root@colinux tmp]#


mysqldump Ver 10.13 Distrib 5.1.30, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] --all-databases[OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit

■ディフォルトのオプションを表示(オプションを指定しないと以下のように設定されている)
=======================================================================
[root@colinux mysql]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --default-character-set=utf8 --quick --max_
allowed_packet=16M --default-character-set=utf8

[root@colinux mysql]#
=======================================================================


--no-defaults Don't read default options from any options file

■ディフォルトオプションをオプションファイルから読み込まない

--defaults-file=# Only read default options from the given file #

■特定のオプションファイルからのみバックアップオプションを読み込む

--defaults-extra-file=# Read this file after the global files are read

■グローバルファイルを読み込んだ後にこのファイルを読み込む。

-a, --all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a 'DROP DATABASE' before each create.
--add-drop-table Add a 'drop table' before each create.
--add-locks Add locks around insert statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory where character sets are.
-i, --comments Write additional information.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --no-set-names
--skip-disable-keys --skip-add-locks
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
--create-options Include all MySQL specific create options.
-B, --databases To dump several databases. Note the difference in usage;
In this case no tables are given. All name arguments are
regarded as databasenames. 'USE db_name;' will be
included in the output.

-#, --debug[=#] This is a non-debug version. Catch this and exit
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.

■バックアップデータの文字コード。my.cnfで指定しているdefault-character-setと
同じコードを指定する。 省略した場合、UTF-8に変換されるので文字化けの原因となる。

--delayed-insert Insert rows with INSERT DELAYED;
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-x, --first-slave Deprecated, renamed to --lock-all-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs

■ダンプ前にバイナリログをスイッチさせる。差分ファイルを適用する際、
全体バックアップ直後のバイナリログから適用すればよいので、指定しておく。

--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an sql-error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.

■バイナリ型のデータをエスケープ処理を行わずに実際に格納された値の16進数表記で保存する。
これを指定しないとdefault-character-setがシフトJIS系の場合エスケープ処理に失敗し、
バイナリデータが破壊される場合がある。

-h, --host=name Connect to host.

■ mysqldumpは、サーバー上でもリモートクライアント側でもバックアップ出来るリモートにて
サーバーのバックアップを行う場合はこのオプションにてサーバー名を指定する。

--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.
--ignore-table=database.table
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.

■ダンプ前に全データベースを共有ロックする。"FLUSH TABLES WITH READ LOCK"
これは、すべてのセッションから一切の更新ができなくなる。"UNLOCK TABLES"コマンド
で共有ロックを解除できる。

-l, --lock-tables Lock all tables for read.

■ダンプ前に全テーブルをロックする。但し、データベース単位でロックする為、
DB間の一貫性は保障しない。 MyISAMバックアップ時はこのオプションでロックをかける。
InnoDBの場合は、--single-transactionを使用する。


--log-error=name Append warnings and errors to given file.

--master-data
[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.

■値が1の場合、CHANGE MASTER文でダンプ開始時のバイナリログの位置情報を記録する。
■値が2の場合、CHANGE MASTER文がコメントアウトされる。(デフォルト)

--max_allowed_packet=#
--net_buffer_length=#
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
not be put in the output. The above line will be added
otherwise, if --databases or --all-databases option was
given.}.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.

-P, --port
=# Port number to use for connection.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

■ダンプ前にBEGIN SQL文を発行する。--master-data=2と組み合わせて指定することで、
ロックをかけずに一貫性のあるバックアップを取得できる。但し、InnoDBの場合に限る。

--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name Socket file to use for connection.
-T, --tab=name Creates tab separated textfile for each table to given
path. (creates .sql and .txt files). NOTE: This only
works if mysqldump is run on the same machine as the
mysqld daemon.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records; QUOTES mandatory!
-X, --xml Dump a database as well formed XML.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 16777216
net_buffer_length 1047551
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket /tmp/mysql.sock
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)

■■■幾つかのオプションを利用してバックアップ検証■■■

mysql> show databases;
+——————————–+
| Database       |
+——————————–+
| information_schema   |
| DATABASE |
| DB01        | INNODB
| DB02        | INNODB
| TEST        | INNODB
| client_test_db     |
| mysql        |
+———————————+
7 rows in set (0.09 sec)


mysqldump --single-transaction --flush-logs --master-data=2 --databases DB01 DB02 TEST > /tmp/MYSQL_DUMP_3DB_WITHOPT.sql -u root -p

[root@colinux tmp]# ls -l
total 12
-rw-r–r– 1 root root 8843 2009-02-08 08:30 MYSQL_DUMP_3DB_WITHOPT.sql
srwxrwxrwx 1 mysql mysql 0 2009-02-08 08:30 mysql.sock
[root@colinux tmp]#

注意※ バイナリーログをONにしていないと –flush-logが指定出来ないのでONにする。

mysqldump

ログのスイッチが行われているので、メモリー内データもFLUSHされている。
オラクルのオンラインバックアップと同じですね。
mysqldump_log


MYSQLDUMPにてデータのみをテキストファイルに抽出する。

[root@colinux tmp]# mysqldump --no-create-info --tab=/tmp --lines-terminated-by=
"\r\n" TEST -u root -p

Enter password:
[root@colinux tmp]# ls -l

[root@colinux tmp]# ls -l
total 8
srwxrwxrwx 1 mysql mysql 0 2009-02-06 18:09 mysql.sock
-rw-rw-rw- 1 mysql mysql 320 2009-02-06 18:11 T1.txt
-rw-rw-rw- 1 mysql mysql 15 2009-02-06 18:11 T2.txt
[root@colinux tmp]#

data_only

mysql> LOAD DATA INFILE ‘/tmp/T1.txt’ INTO TABLE T1 LINES TERMINATED BY ‘\r\n’;
Query OK, 12 rows affected (0.02 sec)
Records: 12 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from T1;
+—-+————+—————+
| id | name | comment |
+—-+————+—————+
| 1 | SHOW WARIN | test update |
| 2 | SHOW WARIN | 22222222222 |
| 3 | select_lim | test 20090117 |
| 4 | select_lim | test 20090117 |
| 5 | select_lim | test 20090117 |
| 6 | select_lim | test 20090117 |
| 7 | select_lim | test 20090117 |
| 8 | select_lim | test 20090117 |
| 9 | select_lim | test 20090117 |
| 10 | FLUSHMAN | TEST DE SU |
| 11 | FLUSHMAN | TEST DE SU |
| 12 | FLUSHMAN | TEST DE SU |
+—-+————+—————+
12 rows in set (0.00 sec)

mysql>

load


REPLICATION環境にてSLAVE側でバックアップする為の手順。

STEP1
マスタの処理要求を停止する。または mysqladmin を使用して完全に
スレーブを停止する。

shell> mysqladmin stop-slave

or

別の方法としては、レプリケーション SQL スレッドを停止して
リレー ログ ファイルの処理を停止します。この方法は、
バイナリ ログのデータの転送を許可します。
この方法を活発なレプリケーション環境で使用すると、
スレーブ処理を再開をしたときにキャッチ アップ プロセスを
スピードアップする可能性があります。

shell> mysql -e ‘STOP SLAVE SQL_THREAD;’

STEP2
FLUSH TABLES
flush_tables

STEP3
データベースのバックアップ
shell> mysqldump --all-databases > fulldb.dump

STEP4
ダンプが完了したら、スレーブのオペレーションを再開する。
shell> mysqladmin start-slave
or
shell> mysql -e ‘START SLAVE SQL_THREAD;’


--single-transactionオプション

[root@colinux tmp]# mysqldump --databases --single-transaction TEST > /tmp/backu
p_single_tran_test.sql -u root -p
Enter password:
[root@colinux tmp]# ls -l /tmp/backup_single_tran_test.sql
-rw-r–r– 1 root root 2891 2009-02-06 12:38 /tmp/backup_single_tran_test.sql
[root@colinux tmp]#

このオプションはサーバからデータをダンプする前にBEGIN SQLステートメント
を発行します。InnoDBといったトランザクションテーブルに対してのみ便利です。
なぜなら、アプリケーションをブロックせずに、BEGINが発行された当時の
データベースの状態をダンプするからです。

このオプションを使用しているときは、一定の状態でダンプされるのは
InnoDBテーブルのみだということを留意してください。
例えば、このオプションを使用中にダンプされたMyISAMやMEMORYテーブル
は状態が変化する可能性があります。


障害発生時に既存のデーブルが残っていても、DROPしてからCREATE
テーブルを行いデータをリストアするため。(リストア先のDBはOver Writeされる)

[root@colinux ~]# mysqldump –add-drop-table –databases test > /tmp/backup_test.sql -u root -p
Enter password:
[root@colinux ~]# ls -l /tmp/backup_test.sql
-rw-r–r– 1 root root 3360 2009-02-06 11:19 /tmp/backup_test.sql
[root@colinux ~]#

db_restore

※ default設定なので、特にオプションは付けなくて良い。
   –skip-optしない限り、-optはdefaultで有効(add-drop-tableも有効)

※ 全てのDBをmysqldumpする場合は、
   「mysqldump –all-databases > ファイル.sql 」

※–single-transaction

[root@colinux tmp]# mysqldump –databases –single-transaction TEST > /tmp/backu
p_single_tran_test.sql -u root -p
Enter password:
[root@colinux tmp]# ls -l /tmp/backup_single_tran_test.sql
-rw-r–r– 1 root root 2891 2009-02-06 12:38 /tmp/backup_single_tran_test.sql
[root@colinux tmp]#

このオプションはサーバからデータをダンプする前にBEGIN SQLステートメント
を発行します。InnoDBといったトランザクションテーブルに対してのみ便利です。
なぜなら、アプリケーションをブロックせずに、BEGINが発行された当時の
データベースの状態をダンプするからです。

このオプションを使用しているときは、一定の状態でダンプされるのは
InnoDBテーブルのみだということを留意してください。
例えば、このオプションを使用中にダンプされたMyISAMやMEMORYテーブル
は状態が変化する可能性があります。


testデータベースのT1テーブルとT2テーブルをbackup_test_T1_T2.sql
ファイルにバックアップ。

[root@colinux ~]# mysqldump test T1 T2 > /tmp/backup_test_T1_T2.sql -u root -p
Enter password:
[root@colinux ~]# ls -l /tmp/backup_test_T1_T2.sql
-rw-r–r– 1 root root 2754 2009-02-06 11:07 /tmp/backup_test_T1_T2.sql
[root@colinux ~]#

backup_sp

backup_sp_result