他のサーバーなどにDBの側だけ作成するために、
データ無しのBACKUPを作成する方法。

[root@colinux tmp]# mysqldump --no-data TEST > /tmp/TEST_SCRIPT_ONLY.sql -u root
-p
Enter password:
[root@colinux tmp]#

テーブル作成ようのDDL文しかありません。

create_table_only


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テーブル
は状態が変化する可能性があります。


DBを誤って削除してしまった時の為に。。。
リストア方法は幾つかありますが、今回はあえてSOURCEコマンドにて
リストアしてみます。

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| T1 |
| T2 |
+—————-+
2 rows in set (0.01 sec)

mysql> exit

[root@colinux tmp]# pwd
/tmp
[root@colinux tmp]# ls -l
total 0
srwxrwxrwx 1 mysql mysql 0 2009-02-06 10:52 mysql.sock
[root@colinux tmp]# mysqldump test --tab=/tmp -u root -p
Enter password:

[root@colinux tmp]# ls -l
total 16
srwxrwxrwx 1 mysql mysql 0 2009-02-06 10:52 mysql.sock
-rw-r–r– 1 root root 1375 2009-02-06 11:46 T1.sql
-rw-rw-rw- 1 mysql mysql 239 2009-02-06 11:46 T1.txt
-rw-r–r– 1 root root 1374 2009-02-06 11:46 T2.sql
-rw-rw-rw- 1 mysql mysql 14 2009-02-06 11:46 T2.txt
[root@colinux tmp]#

drop_db

■削除してしまったDBの再作成
mysql> create database `TEST` /*!40100 default character set UTF8 */;
Query OK, 1 row affected (0.00 sec)

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

■テーブルのリストア
mysql>SOURCE /tmp/T1.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>SOURCE /tmp/T2.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>
■テーブルが戻っていることを確認。
mysql> show tables;
+—————-+
| Tables_in_TEST |
+—————-+
| T1 |
| T2 |
+—————-+
2 rows in set (0.00 sec)

mysql>

restore_table_only

■データのリスストア
mysql> select * from T1;
Empty set (0.00 sec)

mysql> select * from T2;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE ‘/tmp/T1.txt’ INTO TABLE T1;
Query OK, 9 rows affected (0.01 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0

mysql> LOAD DATA INFILE ‘/tmp/T2.txt’ INTO TABLE T2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

無事にリストアできていることを確認。
restore_confirm

mysqldumpコマンドでフルバックアップして、mysqlコマンドでいつもは
リストアしてますが、特定のテーブルの特定のデータをひとつひとつ
確認しながらリストア出来るので、特定のテーブルのリストアをしたり
するのに適しているかも知れません。


障害発生時に既存のデーブルが残っていても、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


mysql> select column_name from information_schema.columns where table_schema = ‘test’ and table_name=’T01′;
+————–+
| column_name |
+————–+
| company_id |
| company_name |
| create_date |
+————–+
3 rows in set (0.00 sec)

mysql>

mysql> desc test.T01;
+————–+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+————–+——+—–+———+—————-+
| company_id | int(4) | NO | PRI | NULL | auto_increment |
| company_name | varchar(255) | YES | | NULL | |
| create_date | timestamp | YES | | NULL | |
+————–+————–+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql>

mysql> SHOW COLUMNS FROM T01 FROM test;
+————–+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+————–+——+—–+———+—————-+
| company_id | int(4) | NO | PRI | NULL | auto_increment |
| company_name | varchar(255) | YES | | NULL | |
| create_date | timestamp | YES | | NULL | |
+————–+————–+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql>


■CHECK TABLE
http://dev.mysql.com/doc/refman/5.1/ja/check-table.html
CHECK TABLE はテーブルのエラーを確認します。CHECK TABLE は MyISAM、InnoDB、
そして ARCHIVE テーブルにのみ機能します。MySQL 5.1.9 から、CHECK は CSV
テーブルにも有効になりました。
MyISAM テーブルに対しては、キー統計もまた更新されます。

mysql> CHECK TABLE T01;
+———-+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+——-+———-+———-+
| test.T01 | check | status | OK |
+———-+——-+———-+———-+
1 row in set (0.00 sec)

mysql>

■REPAIR TABLES
http://dev.mysql.com/doc/refman/5.1/ja/repair-table.html
REPAIR TABLE は破損された可能性があるテーブルを修復します。これはデフォルトで、
myisamchk –recover tbl_name と同じ効果を持っています。REPAIR TABLE は MyISAM と ARCHIVE
テーブルに対して機能します。MySQL 5.1.9 から、REPAIR は CSV テーブルにも有効になりました。

■ANALYZE TABLES (Update table’s index statistics)
http://dev.mysql.com/doc/refman/5.1/ja/analyze-table.html
ANALYZE TABLE はテーブルのキーの分布を分析、格納します。
分析の最中に、テーブルは MyISAM のリード ロックを利用してロックされます。
InnoDB には、テーブルは書き込みロックでロックされます。
このステートメントは MyISAM と InnoDB テーブルと共に機能します。
MyISAM テーブルにとっては、このステートメントは myisamchk –analyze を利用する事と同じです。

mysql> analyze table T01;
+———-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+———+———-+———-+
| test.T01 | analyze | status | OK |
+———-+———+———-+———-+
1 row in set (0.00 sec)

■OPTIMAIZE TABLES
http://dev.mysql.com/doc/refman/5.1/ja/optimize-table.html
しテーブルの大部分を削除したり、変数長行で何箇所もテーブルを
変更した場合は(VARCHAR、VARBINARY、BLOB、または TEXT カラムを持つテーブル)、
OPTIMIZE TABLE を利用しなければいけません。
OPTIMIZE TABLE は MyISAM と InnoDB テーブルに対して のみ 機能します。
これは、NDB ディスク データ テーブルを含むその他のストレージ エンジンを
利用して作成されたテーブルには機能 しません。

—– MYISAM —–
mysql> OPTIMIZE TABLE T_TEST;
+—————-+———-+———-+—————————–+
| Table | Op | Msg_type | Msg_text |
+—————-+———-+———-+—————————–+
| WINDOWS.T_TEST | optimize | status | Table is already up to date |
+—————-+———-+———-+—————————–+
1 row in set (0.00 sec)

—– INNODB —–
mysql> OPTIMIZE TABLE T01;
+———-+———-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+———-+———-+———-+
| test.T01 | optimize | status | OK |
+———-+———-+———-+———-+
1 row in set (0.05 sec)

mysql>

■mysqlcheck
http://dev.mysql.com/doc/refman/5.1/ja/mysqlcheck.html
mysqlcheckのファンクションはmyisamchkと似ていますが、
作動方法が異なります。実質的な作動方法の違いは、mysqlcheckはmysqld
サーバが作動中の時に使用されなければいけません。
myisamchkはこのサーバが作動していない時に使用品kればいけません。
mysqlcheckを使用することの利点は、テーブルのチェックや修復時に
サーバを停止させなくてすむことです。
mysqlcheckはSQLステートメントCHECK TABLE、REPAIR TABLE、ANALYZE TABLE、
そしてOPTIMIZE TABLEをユーザにとって便利な方法で使用します。
実行したいオペレーションに対してどのステートメントを使用するか決定し、
実行のためサーバにステートメントを送信します。

[root@localhost ~]$ mysqlcheck –analyze test T01 -u root -ppassword
test.T01 OK
[root@localhost ~]$

—– MYISAM —–
[root@localhost WINDOWS]# mysqlcheck –optimize WINDOWS T_TEST -u root -ppassword
WINDOWS.T_TEST Table is already up to date
[root@localhost WINDOWS]#

[root@localhost WINDOWS]# mysqlcheck –repair WINDOWS T_TEST -u root -ppassword
WINDOWS.T_TEST OK
[root@localhost WINDOWS]#

[root@localhost WINDOWS]# mysqlcheck –repair WINDOWS -u root -ppassword
WINDOWS.Brokerage OK
WINDOWS.Channel OK
WINDOWS.ConfidenceTerm OK
WINDOWS.DetailCompany OK
WINDOWS.DetailForeignExchange OK

—– INNODB —–
[root@localhost WINDOWS]# mysqlcheck –optimize test T01 -u root -ppassword
test.T01 OK
[root@localhost WINDOWS]#

※ optimizeはmemory tableに利用出来ません。

[root@localhost WINDOWS]# mysqlcheck –repair test T01 -u root -ppassword
test.T01
note : The storage engine for the table doesn’t support repair

■myisamchk (MYISAM ONLY) テーブルにアクセスが無い事を確認
http://dev.mysql.com/doc/refman/5.1/ja/myisamchk.html
myisamchk ユーティリティはユーザのデータベース テーブルの情報を収集し、
チェック、修復、もしくは最適化します。myisamchk は MyISAM
テーブルとともに作動します(データやインデックスを記憶するための
.MYD や .MYI テーブル)。

[root@localhost WINDOWS]# myisamchk –analyze T_TEST.MYI
Checking MyISAM file: T_TEST.MYI
Data records: 1 Deleted blocks: 0
– check file-size
– check record delete-chain
– check key delete-chain
– check index reference
– check record links
[root@localhost WINDOWS]#

[root@localhost WINDOWS]# myisamchk –recover T_TEST
– recovering (with keycache) MyISAM-table ‘T_TEST’
Data records: 1
[root@localhost WINDOWS]#

※「誰も利用していない状態にしておく」
※「サービス停止をした方が良い」
※「オンラインの場合は、メンテナンス対象テーブルをロックする」

『起動時にMYISAMのテーブルを自動的にRecoverする為のオプション』
起動方法① mysqld –myisam-recover=QUICK
オプションファイル(my.cnf)に記入② –myisam-recover


==============================================================================
set autocommit=0;
==============================================================================

mysql> select * from T01;
+————+————–+———————+
| company_id | company_name | create_date |
+————+————–+———————+
| 1 | FRONTIER | 2009-02-04 00:00:00 |
| 2 | Frontier0 | 2009-02-04 00:00:00 |
| 3 | Frontier1 | 2009-02-04 00:00:00 |
+————+————–+———————+
3 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T01(company_name,create_date) values(‘transac’,current_date);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T01;
+————+————–+———————+
| company_id | company_name | create_date |
+————+————–+———————+
| 1 | FRONTIER | 2009-02-04 00:00:00 |
| 2 | Frontier0 | 2009-02-04 00:00:00 |
| 3 | Frontier1 | 2009-02-04 00:00:00 |
| 4 | transac | 2009-02-06 00:00:00 |
+————+————–+———————+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from T01;
+————+————–+———————+
| company_id | company_name | create_date |
+————+————–+———————+
| 1 | FRONTIER | 2009-02-04 00:00:00 |
| 2 | Frontier0 | 2009-02-04 00:00:00 |
| 3 | Frontier1 | 2009-02-04 00:00:00 |
+————+————–+———————+
3 rows in set (0.00 sec)

mysql>

==============================================================================
set autocommit=1;
==============================================================================

mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into T01(company_name,create_date) values(‘transac’,current_date);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T01;
+————+————–+———————+
| company_id | company_name | create_date |
+————+————–+———————+
| 1 | FRONTIER | 2009-02-04 00:00:00 |
| 2 | Frontier0 | 2009-02-04 00:00:00 |
| 3 | Frontier1 | 2009-02-04 00:00:00 |
| 5 | transac | 2009-02-06 00:00:00 |
+————+————–+———————+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T01;
+————+————–+———————+
| company_id | company_name | create_date |
+————+————–+———————+
| 1 | FRONTIER | 2009-02-04 00:00:00 |
| 2 | Frontier0 | 2009-02-04 00:00:00 |
| 3 | Frontier1 | 2009-02-04 00:00:00 |
| 5 | transac | 2009-02-06 00:00:00 |
+————+————–+———————+
4 rows in set (0.00 sec)

mysql>


mysql> show innodb status \g
+——–+——+————————————————-+
| Type | Name | Status
+——–+——+————————————————-+
| InnoDB | |
=====================================
090206 9:00:16 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 56 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 6, signal count 6
Mutex spin waits 0, rounds 340, OS waits 0
RW-shared spins 13, OS waits 6; RW-excl spins 1, OS waits 0
————
TRANSACTIONS
————
Trx id counter 0 1817
Purge done for trx’s n:o < 0 1814 undo n:o < 0 0 History list length 4 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 31575, OS thread id 8362912 MySQL thread id 1998, query id 98281 localhost root show innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 28 OS file reads, 37 OS file writes, 28 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 74422 Log flushed up to 0 74422 Last checkpoint at 0 74422 0 pending log writes, 0 pending chkp writes 23 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 17444756; in additional pool allocated 876160 Dictionary memory allocated 28616 Buffer pool size 512 Free buffers 488 Database pages 23 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 22, created 1, written 21 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 31575, id 113028000, state: waiting for server activity Number of rows inserted 3, updated 1, deleted 0, read 32 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | +--------+------+------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>

mysql> show engine innodb status \g
+——–+——+————————————————+
| Type | Name | Status
+——–+——+————————————————+
| InnoDB | |
=====================================
090206 9:01:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 6, signal count 6
Mutex spin waits 0, rounds 340, OS waits 0
RW-shared spins 13, OS waits 6; RW-excl spins 1, OS waits 0
————
TRANSACTIONS
————
Trx id counter 0 1817
Purge done for trx’s n:o < 0 1814 undo n:o < 0 0 History list length 4 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 31575, OS thread id 8362912 MySQL thread id 1998, query id 98284 localhost root show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 28 OS file reads, 37 OS file writes, 28 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 74422 Log flushed up to 0 74422 Last checkpoint at 0 74422 0 pending log writes, 0 pending chkp writes 23 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 17444756; in additional pool allocated 876160 Dictionary memory allocated 28616 Buffer pool size 512 Free buffers 488 Database pages 23 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 22, created 1, written 21 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 31575, id 113028000, state: waiting for server activity Number of rows inserted 3, updated 1, deleted 0, read 32 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | +--------+------+--------------------------------------------------+ 1 row in set (0.01 sec) mysql>