bitsCN.com
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节点，查到数据已经恢复了。
bitsCN.com
解释酸的特性（原子，一致性，隔离，耐用性）。Apr 16, 2025 am 12:20 AMACID属性包括原子性、一致性、隔离性和持久性，是数据库设计的基石。1.原子性确保事务要么完全成功，要么完全失败。2.一致性保证数据库在事务前后保持一致状态。3.隔离性确保事务之间互不干扰。4.持久性确保事务提交后数据永久保存。
MySQL：数据库管理系统与编程语言Apr 16, 2025 am 12:19 AMMySQL既是数据库管理系统（DBMS），也与编程语言紧密相关。1）作为DBMS，MySQL用于存储、组织和检索数据，优化索引可提高查询性能。2）通过SQL与编程语言结合，嵌入在如Python中，使用ORM工具如SQLAlchemy可简化操作。3）性能优化包括索引、查询、缓存、分库分表和事务管理。
mySQL：使用SQL命令管理数据Apr 16, 2025 am 12:19 AMMySQL使用SQL命令管理数据。1.基本命令包括SELECT、INSERT、UPDATE和DELETE。2.高级用法涉及JOIN、子查询和聚合函数。3.常见错误有语法、逻辑和性能问题。4.优化技巧包括使用索引、避免SELECT*和使用LIMIT。
MySQL的目的：有效存储和管理数据Apr 16, 2025 am 12:16 AMMySQL是一种高效的关系型数据库管理系统，适用于存储和管理数据。其优势包括高性能查询、灵活的事务处理和丰富的数据类型。实际应用中，MySQL常用于电商平台、社交网络和内容管理系统，但需注意性能优化、数据安全和扩展性。
SQL和MySQL：了解关系Apr 16, 2025 am 12:14 AMSQL和MySQL的关系是标准语言与具体实现的关系。1.SQL是用于管理和操作关系数据库的标准语言，允许进行数据的增、删、改、查。2.MySQL是一个具体的数据库管理系统，使用SQL作为其操作语言，并提供高效的数据存储和管理。
说明InnoDB重做日志和撤消日志的作用。Apr 15, 2025 am 12:16 AMInnoDB使用redologs和undologs确保数据一致性和可靠性。1.redologs记录数据页修改，确保崩溃恢复和事务持久性。2.undologs记录数据原始值，支持事务回滚和MVCC。
在解释输出（类型，键，行，额外）中要查找的关键指标是什么？Apr 15, 2025 am 12:15 AMEXPLAIN命令的关键指标包括type、key、rows和Extra。1）type反映查询的访问类型，值越高效率越高，如const优于ALL。2）key显示使用的索引，NULL表示无索引。3）rows预估扫描行数，影响查询性能。4）Extra提供额外信息，如Usingfilesort提示需要优化。
在解释中使用临时状态以及如何避免它是什么？Apr 15, 2025 am 12:14 AMUsingtemporary在MySQL查询中表示需要创建临时表，常见于使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通过优化索引和重写查询避免其出现，提升查询性能。具体来说，Usingtemporary出现在EXPLAIN输出中时，意味着MySQL需要创建临时表来处理查询。这通常发生在以下情况：1)使用DISTINCT或GROUPBY时进行去重或分组；2)ORDERBY包含非索引列时进行排序；3)使用复杂的子查询或联接操作。优化方法包括：1)为ORDERBY和GROUPB
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
EditPlus 中文破解版
体积小，语法高亮，不支持代码提示功能
螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具，用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。
DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中
适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。
Atom编辑器mac版下载
最流行的的开源编辑器
