mysql-cluster的备份与还原

2019独角兽企业重金招聘Python工程师标准>>>

测试start backup

#############################################

//备份概念

1.备份时当前数据库的一个快照，备份包含下面三部分

A.metadata:数据库的定义和结构

B.table records:存储在表中到数据

C.transaction log: 记录数据存储的时间

2.每个备份存储在所有的节点上，备份文件由三个文件组成：

BACKUP-backup_id.node_id.ctl

BACKUP-backup_id-0.node_id.data

BACKUP-backup_id.node_id.log

3.backup_id标识每一次备份，node_id表示创建备份文件的节点

//start backup语法

START BACKUP [backup_id] [wait_option] [snapshot_option]

backup_id:大于等于1的整数，不能重复

wait_option:WAIT {STARTED | COMPLETED}

wait started:等待直到备份启动  默认

wait completed ：等待备份完成

snapshot_option:

SNAPSHOTSTART | SNAPSHOTEND

SNAPSHOTSTART:匹配备份开始时集群的状态

SNAPSHOTEND: 备份反映备份完成时集群到状态 默认

//备份操作 

1.在管理节点上进行

ndb_mgm> start backup 7 wait completed snapshotstart

Waiting for completed, this may take several minutes

Node 2: Backup 7 started from node 1

Node 2: Backup 7 started from node 1 completed

 StartGCP: 4696 StopGCP: 4699

 #Records: 2073 #LogRecords: 0

 Data: 54164 bytes Log: 0 bytes

2.在node=3上查看数据节点备份文件

/data/backup/BACKUP/BACKUP-6

shell> ll

-rw-r--r-- 1 root root 14056 2月  28 09:54 BACKUP-6-0.3.Data

-rw-r--r-- 1 root root 25616 2月  28 09:54 BACKUP-6.3.ctl

-rw-r--r-- 1 root root    52 2月  28 09:54 BACKUP-6.3.log

-----------------------------------------------------------

测试ndb_restore

###################################################

1.环境介绍

管理节点:154

四台 data/sql节点:154/155/156/157

2.cluster当前状态

[ndbd(NDB)] 4 node(s)

id=2 @10.10.54.154  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0, *)

id=3 @10.10.54.155  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0)

id=4 @10.10.54.156  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 1)

id=5 @10.10.54.157  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @10.10.54.154  (mysql-5.5.35 ndb-7.2.15)

[mysqld(API)] 8 node(s)

id=7 @10.10.54.154  (mysql-5.5.35 ndb-7.2.15)

id=9 @10.10.54.155  (mysql-5.5.35 ndb-7.2.15)

id=10 @10.10.54.156  (mysql-5.5.35 ndb-7.2.15)

id=11 @10.10.54.157  (mysql-5.5.35 ndb-7.2.15)

id=12 (not connected, accepting connect from any host)

id=13 (not connected, accepting connect from any host)

id=14 (not connected, accepting connect from any host)

id=15 (not connected, accepting connect from any host)

3.所有数据节点上数据库状态

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| employees          |

| mysql              |

| ndb_2_fs           |

| ndbinfo            |

| performance_schema |

| test               |

+--------------------+

//管理机:先创建新数据库和表，然后执行备份命令

1.创建测试数据

mysql> create database d1;

mysql> use d1;

mysql> create table d1_t1(id int NOT NULL) engine=ndb;

mysql> create table d1_t2(id int NOT NULL) engine=ndb;

mysql> insert into d1_t1 values(1),(2);

mysql> insert into d1_t2 values(1),(2);

mysql> show tables;

+--------------+

| Tables_in_d1 |

+--------------+

| d1_t1        |

| d1_t2        |

+--------------+

2.执行备份命令

ndb_mgm> start backup 4

Waiting for completed, this may take several minutes

Node 2: Backup 4 started from node 1

Node 2: Backup 4 started from node 1 completed

 StartGCP: 540 StopGCP: 543

 #Records: 2070 #LogRecords: 0

 Data: 53152 bytes Log: 0 bytes

//关闭并重启cluster

关闭顺序 

sql节点---data节点---管理节点

启动cluster

1.启动管理节点

shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini

2.开启data节点

shell> ndbd --initial #--initial操作会清空所有表和数据

2014-02-28 13:19:25 [ndbd] INFO     -- Angel connected to '10.10.54.154:1186'

2014-02-28 13:19:25 [ndbd] INFO     -- Angel allocated nodeid: 2

3.开启sql节点

/etc/init.d/mysqld start

//再次创建新数据库和表，验证增量备份

1.创建新数据库

mysql> create database d2;

mysql> use d2;

mysql> create table d2_t1(id int NOT NULL) engine=ndb;

mysql> insert into d2_t1 values(1),(2);

mysql> show tables;

+--------------+

| Tables_in_d2 |

+--------------+

| d2_t1        |

+--------------+

//恢复数据，并查看数据库中数据

1.说明

A.之前的操作流程为:

创建d1----备份----创建d2&&删除d1----恢复数据

B.恢复数据后验证的是:

d1数据库被恢复，d2数据库仍保留

2.在四个data节点上都执行恢复操作

shell> ndb_restore -c 10.10.54.154 -n 2 -m -b 4 -r /data/backup/BACKUP/BACKUP-4/

shell> ndb_restore -c 10.10.54.154 -n 3 -b 4 -r /data/backup/BACKUP/BACKUP-4/

shell> ndb_restore -c 10.10.54.154 -n 4 -b 4 -r /data/backup/BACKUP/BACKUP-4/

shell> ndb_restore -c 10.10.54.154 -n 5 -b 4 -r /data/backup/BACKUP/BACKUP-4/

 #-c:管理节点

 #-n:当前恢复节点，变化

 #-m:第一次恢复时需要加

 #-b:执行备份时的备份id

 #-r:备份文件位置

3.部分结果

shell> ndb_restore -c 10.10.54.154 -n 2 -m -b 4 -r /data/backup/BACKUP/BACKUP-4/

Nodeid = 2

Backup Id = 4

backup path = /data/backup/BACKUP/BACKUP-4/

Opening file '/data/backup/BACKUP/BACKUP-4/BACKUP-4.2.ctl'

File size 23040 bytes

Backup version in files: ndb-6.3.11 ndb version: mysql-5.5.35 ndb-7.2.15

Stop GCP of Backup: 542

Connected to ndb!!

Successfully restored table `d1/def/d1_t1`

Successfully restored table event REPL$d1/d1_t1

...

NDBT_ProgramExit: 0 - OK

4.验证d1数据库恢复，d2保留

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| d1                 |

| d2                 |

...

mysql> use d1;

mysql> show tables;

+--------------+

| Tables_in_d1 |

+--------------+

| d1_t2        |

| d1_t1        |

+--------------+

###d1数据库中的表成功恢复

mysql> use d2;

mysql> show tables;

+--------------+

| Tables_in_d2 |

+--------------+

| d2_t1        |

+--------------+

mysql> select * from d2_t1;

+----+

| id |

+----+

|  2 |

|  1 |

+----+

###d2数据库中的数据被保留

转载于:https://my.oschina.net/zijian1315/blog/204601