MySQL Cluster-备份恢复初步测试

标签: mysql cluster 备份 | 发表时间:2013-08-31 23:55 | 作者:mchdba
出处:http://blog.csdn.net
参考文档 
 http://blog.chinaunix.net/uid-20639775-id-1617795.html
 http://xxtianxiaxing.iteye.com/blog/563063
 http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-single-user-mode.html
 http://www.mysqlab.net/knowledge/kb/detail/topic/backup/id/8309

 

  http://blog.csdn.net/mchdba/article/details/10544585  的第八小节里面进行扩展测试


8.1 sql节点上面录入数据:
 建立测试的数据库和表


 CREATE DATABASE bg;

 CREATE TABLE bgt1 (id INT,`name` VARCHAR(20),PRIMARY KEY(`id`))ENGINE=NDBCLUSTER;

 INSERT INTO bgt1 VALUES(1,'zhang1'),(2,'zhang2'),(3,'zhang3'),(4,'zhang4');

  

 8.2 管理节点上面,开始备份
ndb_mgm> start backup
Waiting for completed, this may take several minutes
Node 4: Backup 1 started from node 1
Node 4: Backup 1 started from node 1 completed
StartGCP: 184725 StopGCP: 184772
#Records: 722078 #LogRecords: 0
Data: 287345616 bytes Log: 0 bytes
ndb_mgm> 


 8.3 再次插入几条数据(为了保持和正式环境尽可能接近,在插入数据中间穿插了flush logs操作!)

INSERT INTO bgt1 VALUES(5,'zhang5'),(6,'zhang6'); 
flush logs;
INSERT INTO bgt1 VALUES(7,'zhang7'),(8,'zhang8');
     
8.4 删掉SQL节点的数据。
mysql> drop database bg;

Query OK, 2 rows affected (6.16 sec)


8.5 关闭MYSQLD服务器。
[[email protected] data]# /etc/rc.d/init.d/mysqld stop
Shutting down MySQL......120718 18:58:11 mysqld_safe mysqld from pid file /usr/local/mysql/data/banggo.local.pid ended
  [确定]
[1]+  Done                    /usr/local/mysql/bin/mysqld_safe
[[email protected] data]# 
[[email protected] data]# /etc/rc.d/init.d/mysqld stop
Shutting down MySQL....                                    [确定]
[[email protected] data]# 

8.6 重新启动节点
ndb_mgm> shutdown
Node 4: Cluster shutdown initiated
Node 4: Node shutdown completed.
2 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
ndb_mgm> exit
[[email protected] mysql-cluster]# ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload
MySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4


8.7 重新启动数据节点
ndbd --initial   


8.8 在数据节点上面进行恢复。
/home/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_restore -e  -c 10.100.200.36 -n 4 -b 1 -m --backup_path=/var/lib/mysql-cluster/BACKUP/BACKUP-1/

其中backup_path 在默认的数据节点的根目录下面(找了很久,一开始以为在配置文件里面)


第一步骤 -m操作
   [[email protected] BACKUP-1]# /home/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_restore -e  -c 10.100.200.36 -n 4 -b 1 -m --backup_path=/var/lib/mysql-cluster/BACKUP/BACKUP-1/
Nodeid = 4
Backup Id = 1
backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-1/
Opening file '/var/lib/mysql-cluster/BACKUP/BACKUP-1/BACKUP-1.4.ctl'
File size 61160 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.5.19 ndb-7.2.4
Stop GCP of Backup: 184771
Connected to ndb!!
Successfully restored table `test/def/t2`
Successfully restored table event REPL$test/t2
Successfully restored table `bg/def/#sql-303d_2`
Successfully restored table event REPL$bg/#sql-303d_2
Successfully restored table `bg/def/#sql-51f0_3`
Successfully restored table event REPL$bg/#sql-51f0_3
Successfully restored table `test/def/t11`
Successfully restored table event REPL$test/t11
Successfully restored table `ndb/def/ndborder_goods`
Successfully restored table event REPL$ndb/ndborder_goods
Successfully restored table `bg/def/bgt1`
Successfully restored table event REPL$bg/bgt1
Successfully restored table `test/def/ndborder_info_history`
Successfully restored table event REPL$test/ndborder_info_history
Successfully restored table `mysql/def/ndb_schema`
Successfully restored table event REPL$mysql/ndb_schema
Successfully restored table `mysql/def/ndb_apply_status`
Successfully restored table event REPL$mysql/ndb_apply_status
Successfully restored table `ndb/def/ndbtest`
Successfully restored table event REPL$ndb/ndbtest
Successfully created index `PRIMARY` on `ndborder_info_history`
Successfully created index `uniq_order_os` on `ndborder_goods`
Successfully created index `is_update` on `ndborder_info_history`
Successfully created index `PRIMARY` on `#sql-51f0_3`
Successfully created index `sku_sn` on `ndborder_goods`
Successfully created index `PRIMARY` on `bgt1`
Successfully created index `exchange_from` on `ndborder_goods`
Successfully created index `addtime` on `ndborder_info_history`
Successfully created index `relating_return_sn` on `ndborder_info_history`
Successfully created index `PRIMARY` on `ndborder_goods`
Successfully created index `order_from` on `ndborder_info_history`
Successfully created index `order_out_sn` on `ndborder_info_history`
Successfully created index `PRIMARY` on `#sql-303d_2`
Successfully created index `order_status` on `ndborder_info_history`
Successfully created index `user_id` on `ndborder_info_history`
Successfully created index `uniq_order_os$unique` on `ndborder_goods`
Successfully created index `order_sn` on `ndborder_goods`


NDBT_ProgramExit: 0 - OK


[[email protected] BACKUP-1]# 
8.9 第二步骤 -r操作(如果有N个node,则需要执行N次)
[[email protected] BACKUP-1]# /home/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_restore -e  -c 10.100.200.36 -n 4 -b 1 -r --backup_path=/var/lib/mysql-cluster/BACKUP/BACKUP-1/
Nodeid = 4
Backup Id = 1
backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-1/
Opening file '/var/lib/mysql-cluster/BACKUP/BACKUP-1/BACKUP-1.4.ctl'
File size 61160 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.5.19 ndb-7.2.4
Stop GCP of Backup: 184771
Connected to ndb!!
Opening file '/var/lib/mysql-cluster/BACKUP/BACKUP-1/BACKUP-1-0.4.Data'
File size 287834112 bytes
_____________________________________________________
Processing data in table: test/def/t2(20) fragment 0
_____________________________________________________
Processing data in table: bg/def/#sql-303d_2(34) fragment 0
_____________________________________________________
Processing data in table: bg/def/#sql-51f0_3(32) fragment 0
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 0
_____________________________________________________
Processing data in table: test/def/t11(11) fragment 0
_____________________________________________________
Processing data in table: ndb/def/ndborder_goods(12) fragment 0
_____________________________________________________
Processing data in table: ndb/def/NDB$BLOB_12_13(13) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 0
_____________________________________________________
Processing data in table: bg/def/bgt1(36) fragment 0
_____________________________________________________
Processing data in table: test/def/ndborder_info_history(21) fragment 0
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize)
在执行 -r操作的时候 报错

【ok】飞鸿大哥说是由于REDO log 文件太小了要加大,不影响恢复效果。参考了http://bugs.mysql.com/bug.php?id=19651 这上面的人也这么讲。



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndb                |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
-- 没有库?看来要重建了


mysql> create database bg; -- 重建
Query OK, 1 row affected (0.06 sec)


mysql> use bg
Database changed
mysql> show tables; -- ok,看下表
+--------------+
| Tables_in_bg |
+--------------+
| bgt1         |
+--------------+
1 row in set (0.00 sec)


mysql> select * from bgt1; --数据恢复过来了
+----+--------+
| id | name   |
+----+--------+
|  3 | zhang3 |
|  1 | zhang1 |
|  2 | zhang2 |
|  4 | zhang4 |
+----+--------+
4 rows in set (0.01 sec)


8.10 找一个mysqld节点,在管理节点进入单用户模式,然后启动sql节点,启动该mysqld节点,并登陆找到最大的epoch的值
ndb_mgm> ENTER SINGLE USER MODE 10;
Single user mode entered
Access is granted for API node 10 only.
ndb_mgm> 

mysql> SELECT @LASTEPOCH:=MAX(epoch) FROM mysql.ndb_apply_status;
+------------------------+
| @LASTEPOCH:=MAX(epoch) |
+------------------------+
|        793593992183807 |
+------------------------+
1 row in set (0.04 sec)

根据epoch的值,找到二进制日志的位置以及文件名


mysql> SELECT POSITION, @FIRSTFILE:=FILE
-> 
-> FROM mysql.ndb_binlog_index
-> 
-> WHERE epoch > @LASTEPOCH
-> 
-> ORDER BY epoch ASC
-> 
-> LIMIT 1;
Empty set (0.03 sec)

8.11 根据时间点恢复
找出恢复的时候需要用到的除第一个日志文件以外的其他的二进制日志文件
SELECT DISTINCT File 
FROM mysql.ndb_binlog_index
WHERE epoch > @LASTEPOCH 
AND File <> @FIRSTFILE 
ORDER BY File;


然后进行二进制日志的恢复:
mysqlbinlog -H --set-charset="utf8" -D --start-position=829 ./mysql-bin.000012 | grep -v "RELOAD DATABASE" |mysql bg
mysqlbinlog -H --set-charset="utf8" -D --stop-datetime="2012-07-18 13:30:00" ./mysql-bin.0000013 | grep -v "RELOAD DATABASE" |mysql  bg


恢复完成后,退出单用户模式,并启动另外一个sql节点
ndb_mgm> EXIT SINGLE USER MODE;
Exiting single user mode in progress.
Use ALL STATUS or SHOW to see when single user mode has been exited.
ndb_mgm>


进入mysql节点,查到数据已经恢复了。
作者:mchdba 发表于2013-8-31 18:05:23 原文链接
阅读:60 评论:0 查看评论

相关 [mysql cluster 备份] 推荐:

MySQL Cluster-备份恢复初步测试

- - CSDN博客数据库推荐文章
在  http://blog.csdn.net/mchdba/article/details/10544585  的第八小节里面进行扩展测试. 8.1 sql节点上面录入数据:.  8.2 管理节点上面,开始备份.  8.3 再次插入几条数据(为了保持和正式环境尽可能接近,在插入数据中间穿插了flush logs操作!).

MySQL HA 高可用性,MySQL Cluster 叢集

- - SSORC.tw
而 SQL Node (mysqld程序) 只是讓我們建立資料庫、表的地方 (看得到/var/lib/mysql/XXX),只是 SQL Node 這邊是看不到實際空間用量的. manager node 及所有的 node 都要裝 mysql-cluster (到 mysql 官網下載). manager node 設定,它只要 ndb_mgm 與 ndb_mgmd 而已.

MySQL Cluster 与 MongoDB 复制集分片设计及原理

- - MySQLOPS 数据库与运维自动化技术分享
分布式数据库计算涉及到分布式事务、数据分布、数据收敛计算等等要求. 分布式数据库能实现高安全、高性能、高可用等特征,当然也带来了高成本(固定成本及运营成本),我们通过MongoDB及MySQL Cluster从实现上来分析其中的设计思路,用以抽象我们在设计数据库时,可以引用的内部方法. 首先说说关系及非关系数据库的特征.

又有一家大的 MySQL distribution 支援 Galera Cluster…

- - Gea-Suan Lin's BLOG
Galera Cluster 是 Codership 所提供的 MySQL master-master 方案,與其他 master-master 方案比起來,最大的好處就在於比較不需要擔心資料同步的問題…. 剛剛看到,除了 Percona 外,又有一家 MySQL distribution 支援 Galera Cluster:「 MariaDB Galera cluster released」.

一次惊心动魄的Percona XTRADB Cluster数据修复过程【MySQL】

- - CSDN博客推荐文章
一次惊心动魄的Percona XTRA Cluster DB数据修复过程.         2014.12.27日中午约12:30,电话响起,是同事YI的电话,告之说库中出现大量死锁,用“service mysql restart”无法重启. 这里我先说明下:我们在移动音乐项目中使用的是. Percona XTRA Cluster DB,在生成环境中,建议最低是3个节点.

Linux 安装 MySQL / MySQL 主从备份

- - BlogJava-首页技术区
假定所有安装包均在 /share目录,安装目录为 /opt !!. ========================= 安装 MySQL =========================. $ vi /opt/mysql/my.cnf (参考下面配置). $ vi /etc/rc.d/init.d/mysql (参考下面配置).

使用Percona XtraBackup备份MySQL

- - searchdatabase
  Percona XtraBackup是一款开放源码,免费的MySQL热备份软件,以非阻塞方式执行备份InnoDB和XtraDB为存储引擎的数据库,是商业备份工具InnoDB Hotbackup的一个很好的替代品. 作者的前一家公司就花了不菲美元买了Hotbackup产品.     下载地址: http://www.searchdatabase.com.cn/softwaredownload_10241.htm.

mysql实现增量备份

- - CSDN博客数据库推荐文章
有点要注意 如果你误删了表 想通过这个恢复 必须恢复日志里面有创建表的日志 不然的话是无法回复的  就是必须是从你开始创建表的时候就已经记录日志了 . 恢复到哪个位置 就按照哪个位置来计算. mysql  5.0不支持增量备份.     mysql数据库会以二进制形式 自动把用户对mysql数据库的操作 记录到文件 当用户希望恢复的时候可以使用备份文件 进行回复.

mysql数据备份3种方案

- - 开心平淡对待每一天。热爱生活
   mysql按照备份恢复方式分为逻辑备份和物理备份 逻辑备份是备份sql语句,在恢复的时候执行备份的sql语句实现数据库数据的重现. 物理备份就是备份数据文件了,比较形象点就是cp下数据文件,但真正备份的时候自然不是的cp这么简单. 这2种备份各有优劣,一般来说,物理备份恢复速度比较快,占用空间比较大,逻辑备份速度比较慢,占用空间比较小.

有效的MySQL备份与恢复

- - haohtml's blog
【TechTarget中国原创】如果您接手了一个 MySQL生产系统,但不确定它是否运行了MySQL备份策略,这时需要做哪些保障措施呢. 在实施备份策略之前,一定要明确数据规模和存储引擎使用等先决条件. 这会对系统在备份过程中的可用性产生直接影响. 在本文中,我们将介绍用于确定最小备份功能所需要的方法,其中包括:.