今回は、検証用に2台のノードにMySQL Clusterを導入してみました。
検証目的なので2台で設定してますが、本番ではデータノードは分けた方が良さそうです。

————————-
Node1: 192.168.56.114
Node2: 192.168.56.115
————————-

mysql> select * from ndbinfo.nodes;
+---------+--------+---------+-------------+-------------------+
| node_id | uptime | status  | start_phase | config_generation |
+---------+--------+---------+-------------+-------------------+
|       1 |   3994 | STARTED |           0 |                 1 |
|       2 |   3948 | STARTED |           0 |                 1 |
+---------+--------+---------+-------------+-------------------+
2 rows in set (0.05 sec)

mysql> 

mysql> select * from ndbinfo.arbitrator_validity_detail;
+---------+------------+------------------+---------------+-----------+
| node_id | arbitrator | arb_ticket       | arb_connected | arb_state |
+---------+------------+------------------+---------------+-----------+
|       1 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
|       2 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
+---------+------------+------------------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> 

既に、起動済みなのでNDBストレージエンジンでTABLEを作成してみる。
cluster74_02で作成

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_02 |
+--------------+
1 row in set (0.01 sec)

mysql> CREATE DATABASE NDB01 default character set utf8mb4;USE NDB01;
Query OK, 1 row affected (0.06 sec)

Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE T20150319(id int not null auto_increment, memo varchar(100), primary key(id) ) ENGINE=ndb;
Query OK, 0 rows affected (0.47 sec)

mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc T20150319;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> 

他のノードにてデータ確認
Node: cluster74_01

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_01 |
+--------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| NDB01              |
| mysql              |
| ndb_1_fs           |
| ndbinfo            |
| ndbmemcache        |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> show tables from NDB01;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

ここで一度バックアップを取得
※リストアすると上記の状態に戻る。

ndb_mgm> backup start

DBに追加でデータを追加すると同時にリソースアロケーションを確認。

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-4');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-5');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T20150319;
+----+--------------------------------------+
| id | memo                                 |
+----+--------------------------------------+
|  3 | This is MySQL Cluster 7.4.4 検証-3   |
|  5 | This is MySQL Cluster 7.4.4 検証-5   |
|  1 | This is MySQL Cluster 7.4.4 検証     |
|  2 | This is MySQL Cluster 7.4.4 検証-2   |
|  4 | This is MySQL Cluster 7.4.4 検証-4   |
+----+--------------------------------------+
5 rows in set (0.01 sec)

mysql> desc ndbinfo.memory_per_fragment;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| fq_name                | varchar(512)        | YES  |     | NULL    |       |
| parent_fq_name         | varchar(512)        | YES  |     | NULL    |       |
| type                   | varchar(512)        | YES  |     | NULL    |       |
| table_id               | int(10) unsigned    | YES  |     | NULL    |       |
| node_id                | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance         | int(10) unsigned    | YES  |     | NULL    |       |
| fragment_num           | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_bytes  | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_size_bytes  | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_count       | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_count  | decimal(16,0)       | YES  |     | NULL    |       |
| var_elem_alloc_bytes   | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_free_bytes    | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_count         | bigint(20) unsigned | YES  |     | NULL    |       |
| hash_index_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32608 |
|    1 |    1 |       32768 |      32512 |
|    2 |    0 |       32768 |      32608 |
|    2 |    1 |       32768 |      32512 |
+------+------+-------------+------------+
4 rows in set (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-6');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-7');
Query OK, 1 row affected (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-9');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-10');
Query OK, 1 row affected (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32480 |
|    1 |    1 |       32768 |      32480 |
|    2 |    0 |       32768 |      32480 |
|    2 |    1 |       32768 |      32480 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id,memory_type,used,used_pages,total,total_pages from ndbinfo.memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       1 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       2 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.01 sec)

mysql> 

ここからは、データのリストア
1. Clusterを停止(管理・データ)
2. 管理ノードを起動
3. データノードを–initial付きで起動
4.SQLノード停止
5. Restore作業1(1台目だけは-mオプション付き)
6. Restore作業2 (2台目は-mは不要)
7. データ確認して完了

[root@cluster74_01 BACKUP]# pwd
/usr/local/mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64/data/BACKUP
[root@cluster74_01 BACKUP]# ls -l
合計 0
drwxr-x---. 2 root root 72  3月 19 22:25 BACKUP-1
[root@cluster74_01 BACKUP]# 


[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
ndb_mgm> exit
[root@cluster74_01 cluster]# 

SQLノードも停止しました。

[root@cluster74_01 cluster]# /home/admin/cluster/start_management.sh 
Start MySQL Management Node
MySQL Cluster Management Server mysql-5.6.23 ndb-7.4.4
Management Prompt
/usr/local/mysql/bin/ndb_mgm
[root@cluster74_01 cluster]# 

[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:25:38 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:25:38 [ndbd] INFO     -- Angel allocated nodeid: 1
[root@cluster74_01 cluster]# 

[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:26:51 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:26:51 [ndbd] INFO     -- Angel allocated nodeid: 2
[root@cluster74_02 cluster]# 



[root@cluster74_01 BACKUP]# /usr/local/mysql/bin/ndb_restore -b 1 -n 1 -m -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 1
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Successfully restored table `NDB01/def/T20150319`
Successfully restored table event REPL$NDB01/T20150319
Successfully restored table `ndbmemcache/def/ndb_clusters`
Successfully restored table event REPL$ndbmemcache/ndb_clusters

省略....
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 0
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.log'
File size 52 bytes
Restored 15 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_01 BACKUP]# 



[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndb_restore -b 1 -n 2 -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 2
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1-0.2.Data'
File size 31068 bytes

省略...
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table(24) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.log'
File size 52 bytes
Restored 14 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_02 cluster]# 

SQLノードを起動してデータ確認

[root@cluster74_01 cluster]# ./start_mysql.sh 
Start MySQL Node
[root@cluster74_01 cluster]# 150319 23:45:00 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_01.err'.
150319 23:45:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_01 cluster]# 


[root@cluster74_02 cluster]# 150319 23:45:12 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_02.err'.
150319 23:45:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_02 cluster]# 


ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50 (not connected, accepting connect from 192.168.56.114)
id=51 (not connected, accepting connect from 192.168.56.115)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm> 



mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |           0 |          0 |
|    1 |    1 |       32768 |      32608 |
|    2 |    0 |           0 |          0 |
|    2 |    1 |       32768 |      32608 |
+------+------+-------------+------------+
4 rows in set (0.06 sec)

mysql> 

restore

参照:
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbinfo.html


MYSQLオンラインバックアップ

基本的にWeb系のサービスではオンラインバックアップが基本だと思うので、
改めてMYSQLのオンラインバックアップの選択肢を確認。

ハードウエアやOSの機能を利用
 LVMやストレージが提供しているSnapShot機能を利用する。
 自分で簡単なスクリプトを作成すれば瞬時にオンラインで
 MYSQLバックアップをする事が可能。

ソフトウエアの機能を利用したバックアップ
mysqldump
mMySQL Enterprise Backup
InnoDB Hot Backup
Xtra Backup

Xtra Backupの動作確認
32bit版はi686 64bit版はx86_64
http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/
http://www.percona.com/software/percona-xtrabackup

OS確認

[root@HOME001 mysql]# uname -m
i686
[root@HOME001 mysql]#

ダウンロード

[root@HOME001 mysql]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
--2013-01-24 13:31:20--  http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
www.percona.com をDNSに問いあわせています... 74.121.199.234
www.percona.com|74.121.199.234|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: /downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz [続く]
--2013-01-24 13:31:21--  http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
www.percona.com:80 への接続を再利用します。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 22328480 (21M) [application/x-gzip]
`percona-xtrabackup-2.0.5-499.tar.gz' に保存中

100%[==========================================================>] 22,328,480  1.40M/s 時間 15s

2013-01-24 13:31:36 (1.45 MB/s) - `percona-xtrabackup-2.0.5-499.tar.gz' へ保存完了 [22328480/22328480]

[root@HOME001 mysql]#

展開
MYSQLのホームディレクトリーのbinに展開したファイルをコピーしないとエラーが出たのでコピーしてます。

[root@HOME001 mysql]# tar zxvf percona-xtrabackup-2.0.5-499.tar.gz
percona-xtrabackup-2.0.5/
percona-xtrabackup-2.0.5/bin/
percona-xtrabackup-2.0.5/bin/xtrabackup_55
percona-xtrabackup-2.0.5/bin/xtrabackup_51
percona-xtrabackup-2.0.5/bin/innobackupex
percona-xtrabackup-2.0.5/bin/xtrabackup
省略…
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/bug723097.sql
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/ib_stream_common.sh
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/incremental_sample-schema.sql
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/common.sh
[root@HOME001 mysql]#


[root@HOME001 percona-xtrabackup-2.0.5]# pwd
/home/mysql/percona-xtrabackup-2.0.5
[root@HOME001 percona-xtrabackup-2.0.5]# ls -l
合計 8
drwxr-xr-x. 2 root root 4096  1月 18 05:37 2013 bin
drwxr-xr-x. 4 root root 4096  1月 18 05:37 2013 share
[root@HOME001 percona-xtrabackup-2.0.5]# ls -l bin/
合計 32272
-rwxr-xr-x. 1 root root   106390  1月 18 05:37 2013 innobackupex
lrwxrwxrwx. 1 root root       12  1月 24 13:34 2013 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x. 1 root root  2031819  1月 18 05:37 2013 xbstream
-rwxr-xr-x. 1 root root  9809963  1月 18 05:34 2013 xtrabackup
-rwxr-xr-x. 1 root root  8375751  1月 18 05:37 2013 xtrabackup_51
-rwxr-xr-x. 1 root root 12713916  1月 18 05:29 2013 xtrabackup_55
[root@HOME001 percona-xtrabackup-2.0.5]#cd bin
[root@HOME001 bin]# cp -rp * /usr/local/mysql/bin/
[root@HOME001 bin]# cd /usr/local/mysql/bin/
[root@HOME001 bin]# mkdir /home/mysql/backup

tar

InnoDBのオンラインバックアップ実行
パスワードは変数を入れてます。

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass /home/mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130124 14:44:51  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:44:51  innobackupex-1.5.1: Connected to database with mysql child process (pid=4736)
130124 14:44:57  innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex-1.5.1
           prints "completed OK!".

innobackupex-1.5.1: Using mysql  Ver 14.14 Distrib 5.5.29, for linux2.6 (i686) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

innobackupex-1.5.1: Created backup directory /home/mysql/backup/2013-01-24_14-44-57
130124 14:44:57  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:44:57  innobackupex-1.5.1: Connected to database with mysql child process (pid=4763)
130124 14:44:59  innobackupex-1.5.1: Connection to database server closed

130124 14:44:59  innobackupex-1.5.1: Starting ibbackup with command: xtrabackup_55  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/backup/2013-01-24_14-44-57
innobackupex-1.5.1: Waiting for ibbackup (pid=4771) to suspend
innobackupex-1.5.1: Suspend file '/home/mysql/backup/2013-01-24_14-44-57/xtrabackup_suspended'

xtrabackup_55 version 2.0.5 for Percona Server 5.5.16 Linux (i686) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 8388608
>> log scanned up to (1656933)
[01] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_14-44-57/ibdata1
[01]        ...done

130124 14:45:03  innobackupex-1.5.1: Continuing after ibbackup has suspended
130124 14:45:03  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:45:03  innobackupex-1.5.1: Connected to database with mysql child process (pid=4785)
>> log scanned up to (1656933)
130124 14:45:05  innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (1656933)
>> log scanned up to (1656933)
130124 14:45:15  innobackupex-1.5.1: All tables locked and flushed to disk

130124 14:45:15  innobackupex-1.5.1: Starting to backup non-InnoDB tables and files
innobackupex-1.5.1: in subdirectories of '/usr/local/mysql/data'
innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/performance_schema/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/DB_REPLICATION/db.opt'
innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/test/TABLE001.frm'
innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/mysql/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
130124 14:45:15  innobackupex-1.5.1: Finished backing up non-InnoDB tables and files

130124 14:45:15  innobackupex-1.5.1: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1656933'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1656933)

xtrabackup: Transaction log of lsn (1656933) to (1656933) was copied.
130124 14:45:18  innobackupex-1.5.1: All tables unlocked
130124 14:45:18  innobackupex-1.5.1: Connection to database server closed

innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_14-44-57'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107
130124 14:45:18  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#


[root@HOME001 bin]# ls -l /home/mysql/backup/
合計 4
drwxr-xr-x. 6 root root 4096  1月 24 14:45 2013 2013-01-24_14-44-57
[root@HOME001 bin]#

innobackup

並列処理を指定する事も可能

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --parallel=4 /home/mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130124 15:23:25  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 15:23:25  innobackupex-1.5.1: Connected to database with mysql child process (pid=5010)
130124 15:23:31  innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex-1.5.1
           prints "completed OK!".
省略・・・
xtrabackup: Starting 4 threads for parallel data files transfer
[03] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_15-23-31/ibdata1
[03]        ...done
省略・・・
innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107
130124 15:23:52  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#

リストアを試してみる
リストア時にフルバックアップで取得したデータをリストアすると、
データフォルダーが空では無いとエラーになる。
バイナリーログは差分リカバリーで必要になるので残しておく。
Oracleでいうテーブルスペースのオンライバックアップをリストアして、
ArchiveログやRedoを適用してロールフォワードするような感じ。

[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|       28 |
+----------+
[root@HOME001 bin]# mysql -u root -p$b_pass -e "truncate table test.TABLE001;"
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|        0 |
+----------+
[root@HOME001 bin]#
[root@HOME001 bin]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@HOME001 bin]# mv /usr/local/mysql/data  /home/mysql/backup/
[root@HOME001 bin]# mkdir /usr/local/mysql/data
[root@HOME001 bin]# chown -R mysql:mysql /usr/local/mysql/data

restore1

コピーバックしてリストア
注意:レプリケーション環境ではきちんと必要なファイル等を理解した上でリカバリーして下さい。

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --copy-back /home/mysql/backup/2013-01-24_15-23-31

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex-1.5.1
           prints "completed OK!".

innobackupex-1.5.1: Starting to copy files in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/performance_schema'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/file_summary_by_event_name.frm' to '/usr/l         ocal/mysql/data/performance_schema/file_summary_by_event_name.frm'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/rwlock_instances.frm' to '/usr/local/mysql         /data/performance_schema/rwlock_instances.frm'
....省略
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition.MYI' to '/usr/local/mysql/data/mys         ql/time_zone_transition.MYI'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition_type.frm' to '/usr/local/mysql/dat         a/mysql/time_zone_transition_type.frm'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/DB_REPLICATION'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/DB_REPLICATION/db.opt' to '/usr/local/mysql/data/DB_REPLICATI         ON/db.opt'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/test'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/test/TABLE001.frm' to '/usr/local/mysql/data/test/TABLE001.frm'

innobackupex-1.5.1: Starting to copy InnoDB system tablespace
innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original InnoDB data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/ibdata1' to '/usr/local/mysql/data/ibdata1'

innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex-1.5.1: Finished copying back files.
130124 15:38:03  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#

サービス再起動とデータの確認

[root@HOME001 bin]# /etc/init.d/mysql start
Starting MySQL....                                         [  OK  ]
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|       28 |
+----------+
[root@HOME001 bin]#

リカバリーログの内容(ログファイル再作成、ロールフォワードの履歴)

130124 15:40:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
130124 15:40:42 [Note] Plugin ‘FEDERATED’ is disabled.
130124 15:40:42 InnoDB: The InnoDB memory heap is disabled
130124 15:40:42 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
130124 15:40:42 InnoDB: Compressed tables use zlib 1.2.3
130124 15:40:42 InnoDB: Using Linux native AIO
130124 15:40:42 InnoDB: Initializing buffer pool, size = 32.0M
130124 15:40:42 InnoDB: Completed initialization of buffer pool
130124 15:40:42 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:42 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:43 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130124 15:40:43 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Last MySQL binlog file position 0 3917, file name ./mysql-bin.000035
130124 15:40:43 InnoDB: Waiting for the background threads to start
130124 15:40:44 InnoDB: 1.1.8 started; log sequence number 1657356
130124 15:40:44 [Note] Semi-sync replication initialized for transactions.
130124 15:40:44 [Note] Semi-sync replication enabled on the master.
130124 15:40:44 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
130124 15:40:44 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
130124 15:40:44 [Note] Server socket created on IP: ‘0.0.0.0’.
130124 15:40:44 [Note] Event Scheduler: Loaded 0 events
130124 15:40:44 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.29-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
130124 15:40:46 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000050, 194)
130124 15:40:46 [Note] Stop semi-sync binlog_dump to slave (server_id: 2)

restore3

その他LVM のスナップショット機能を利用したバックアップに関しては後日検証
LVMの現状確認

[root@HOME001 ~]# fdisk -l /dev/sda
ディスク /dev/sda: 60.0 GB, 60011642880 バイト
ヘッド 255, セクタ 63, シリンダ 7296
Units = シリンダ数 of 16065 * 512 = 8225280 バイト
セクタサイズ (論理 / 物理): 512 バイト / 512 バイト
I/O size (minimum/optimal): 512 bytes / 512 bytes
ディスク識別子: 0x1ecfb89b

デバイス ブート      始点        終点     ブロック   Id  システム
/dev/sda1   *           1          64      512000   83  Linux
パーティション 1 は、シリンダ境界で終わっていません。
/dev/sda2              64        7296    58091520   8e  Linux LVM
[root@HOME001 ~]#


[root@HOME001 ~]# pvscan
  PV /dev/sda2   VG vg_home001   lvm2 [55.40 GiB / 0    free]
  Total: 1 [55.40 GiB] / in use: 1 [55.40 GiB] / in no VG: 0 [0   ]
[root@HOME001 ~]# 

[root@HOME001 ~]# vgscan
  Reading all physical volumes.  This may take a while...
  Found volume group "vg_home001" using metadata type lvm2
[root@HOME001 ~]# 

[root@HOME001 ~]# lvscan
  ACTIVE            '/dev/vg_home001/lv_root' [46.86 GiB] inherit
  ACTIVE            '/dev/vg_home001/lv_home' [4.57 GiB] inherit
  ACTIVE            '/dev/vg_home001/lv_swap' [3.97 GiB] inherit
[root@HOME001 ~]# 

[root@HOME001 ~]# vgs
  VG         #PV #LV #SN Attr   VSize  VFree
  vg_home001   1   3   0 wz--n- 55.40g    0
[root@HOME001 ~]# 

[root@HOME001 ~]# lvs
  LV      VG         Attr     LSize  Pool Origin Data%  Move Log Copy%  Convert
  lv_home vg_home001 -wi-ao--  4.57g
  lv_root vg_home001 -wi-ao-- 46.86g
  lv_swap vg_home001 -wi-ao--  3.97g
[root@HOME001 ~]# 

[root@HOME001 ~]# df
Filesystem           1K-ブロック    使用   使用可 使用% マウント位置
/dev/mapper/vg_home001-lv_root      48360312   2618204  43285532   6% /
tmpfs                                 969472         0    969472   0% /dev/shm
/dev/sda1                             495844     36136    434108   8% /boot
/dev/mapper/vg_home001-lv_home       4721020    441796   4039404  10% /home
tmpfs                                  65536         0     65536   0% /usr/local/tmp
[root@HOME001 ~]# 

[root@HOME001 ~]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#


もしボリューム名を変更したら起動、fstabの変更も忘れずに
[root@HOME001 ~]# cat /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/mapper/vg_home001-lv_root
#          initrd /initrd-[generic-]version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title CentOS (2.6.32-279.el6.i686)
        root (hd0,0)
        kernel /vmlinuz-2.6.32-279.el6.i686 ro root=/dev/mapper/vg_home001-lv_root rd_NO_LUKS rd_LVM_LV=vg_home001/lv_swap rd_NO_MD rd_LVM_LV=vg_home001/lv_root crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=jp106 LANG=ja_JP.UTF-8 rd_NO_DM rhgb quiet
        initrd /initramfs-2.6.32-279.el6.i686.img
[root@HOME001 ~]#

参考

MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backup
Percona XtraBackup
Percona XtraBackupの基本的な使い方
XtraBackupを使ってMySQLをバックアップしよう
MySQLバックアップツール比較 XtraBackup / mysqldump / Mydumper
MySQL – XtraBackupを使ったバックアップ&リストアについて
レプリケーション作成を簡単にする mysql40dump という mysqldump の wrapper を作った話
keepalived + MySQL-MHA + xtrabackupで自動フェイルオーバーと手動フェイルバック(その1 keepalived編)
Percona XtraBackupの抽出と圧縮の並列処理
LVM を導入する
LVM のパーティションを管理する
LVM のスナップショット機能を使ってみる
How to setup a slave for replication in 6 simple steps with Xtrabackup


テーブルに対してALTER TABLE文でデータ型を変更したりする場合、
必ずバックアップを取得しておかないと後戻り出来ない場合があります。
作業の際は細心の注意をして対応しましょう。
もしくは、作業の際は以下のようなSQLモードをONにして対応すると安全かもしれません。

——————————————————————-
STRICT_TRANS_TABLES
STRICT_ALL_TABLES
——————————————————————-

SQLモードの確認は以下のようなQUERYにて確認出来ます。

mysql> SELECT @@global.sql_mode;
+---------------------+
| @@global.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES |
+--------------------+
1 row in set (0.00 sec)

mysql>

alter_table5


mysql> CREATE TABLE `T_ALTER` (
-> `number` int NULL DEFAULT NULL,
-> `comment` varchar(100) NULL DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> desc T_ALTER;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into T_ALTER values(1000000,'This is test table de su');
Query OK, 1 row affected (0.00 sec)

mysql> insert into T_ALTER values(NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql>

alter_table

データ型を変更するとどのような影響があるか。。。。


mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE T_ALTER
-> MODIFY number TINYINT UNSIGNED NOT NULL,
-> MODIFY comment TINYINT UNSIGNED NOT NULL;
Query OK, 2 rows affected, 4 warnings (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 3

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'number' at row 1 |
| Warning | 1366 | Incorrect integer value: 'This is test table de su' for column 'comment' at row 1 |
| Warning | 1265 | Data truncated for column 'number' at row 2 |
| Warning | 1265 | Data truncated for column 'comment' at row 2 |
+---------+------+-----------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from T_ALTER;
+--------+---------+
| number | comment |
+--------+---------+
| 255 | 0 | <----- TINYINT UNSIGNEDのMAX値に置き換えられてしまった。 | 0 | 0 | +--------+---------+ 2 rows in set (0.00 sec) mysql>

alter_table2

再度テーブルの定義を直して見てもデータは戻らない。。。。
本番環境のデータベースであればリストアが必要だ。。。


mysql> select * from T_ALTER;
+--------+---------+
| number | comment |
+--------+---------+
| 255 | 0 |
| 0 | 0 |
+--------+---------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE T_ALTER
-> MODIFY number int NULL DEFAULT NULL,
-> MODIFY comment varchar(100) NULL DEFAULT NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from T_ALTER;
+--------+---------+
| number | comment |
+--------+---------+
| 255 | 0 |
| 0 | 0 |
+--------+---------+
2 rows in set (0.00 sec)

mysql>

alter_table3

失敗したくないときは、自分の作業セッションでStrict Modeを有効にすると良いかもしれません。
4.2.6. SQL モード


mysql> SET @@session.sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `T_ALTER` (
-> `number` int NULL DEFAULT NULL,
-> `comment` varchar(100) NULL DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T_ALTER values(1000000,'This is test table de su');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_ALTER values(NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE T_ALTER
-> MODIFY number TINYINT UNSIGNED NOT NULL,
-> MODIFY comment TINYINT UNSIGNED NOT NULL;
ERROR 1264 (22003): Out of range value for column 'number' at row 1
mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql>

alter_table4


━ MYISAMのテーブルをコピーしてみる。 ━ 

mysql> use TEST
Database changed
mysql> LOCK TABLES MYSQLIMP READ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH TABLE MYSQLIMP;
Query OK, 0 rows affected (0.00 sec)
mysql>

——- ここは別コンソール ——–
[root@colinux TEST]# cp MYSQLIMP.* /tmp
——————————————

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

myisam_table_copy

DBを停止しても良い場合は、一度サービスを停止してファイル
コピーすればOK (COLD BACKUP)
STOP —> COPY FILES —–> START


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


MY ADMINを利用してMYSQLのFULL BACKUPから、
特定テーブルのリストアを行う方法。
普段は、mysqldumpでバックアップして、mysqlコマンドでリストア
しているので良い勉強になりました。

① まずはテストの為にBACKUPを作成してみます。
BACKUPプロジェクトを作成して「EXECUTE BACKUP NOW」で
バックアップを取得してみます。


BACKUP


② テストの為にBACKUPを取得した、DBにあるテーブルから
データを削除してみます。


DELETE


③ MYSQL AdministratorにてRESTOREを選択して、
①にて作成したBACKUPファイルを開き、「Analyze Backup File」を選択
しリストアしたいテーブルのみをチェックします。
※ FULLでリストアしたいときは、特に特定のテーブルのみ選択しないで
全てリストアしてください。


RESTORE


④ リストアがきちんと行われたかテーブルをselectして確認してみて下さい。
データが戻っていれば成功です。


CONFIRMATION


⑤ データが戻っているので成功ですね。