MySQL Cluster-備份恢復初步測試 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服務器。 [root@banggo 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 [root@banggo data]# [root@banggo data]# /etc/rc.d/init.d/mysqld stop Shutting down MySQL.... [確定] [root@banggo 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 [root@banggo 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操作 [root@test-db-20053 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 [root@test-db-20053 BACKUP-1]# 8.9 第二步驟 -r操作(如果有N個node，則需要執行N次) [root@test-db-20053 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節點，查到數據已經恢復了。
