Mysql Enterprise Backup异机恢复文档.docx
《Mysql Enterprise Backup异机恢复文档.docx》由会员分享,可在线阅读,更多相关《Mysql Enterprise Backup异机恢复文档.docx(59页珍藏版)》请在冰豆网上搜索。
![Mysql Enterprise Backup异机恢复文档.docx](https://file1.bdocx.com/fileroot1/2023-1/31/82d1e25b-1631-4084-aa9c-fe32f996ff22/82d1e25b-1631-4084-aa9c-fe32f996ff221.gif)
MysqlEnterpriseBackup异机恢复文档
MysqlEnterpriseBackup异机恢复文档
一、备份与恢复验证;这部分测试由于环境因素,在两个B200的物理节点;下面描述详细验证过程;1ndb物理备份测试恢复drop的表;1.1准备环境;主备份窗口:
;[mysql@sicloud-b200-3my_;--NDBCluster--Management;ndb_mgm>show;ConnectedtoManagementSer;ClusterCo
一、备份与恢复验证
这部分测试由于环境因素,在两个B200的物理节点上创建的7节点ndb集群下做的测试,其中每个物理节点启动两个数据节点,一个mysqld节点,一个管理节点。
数据量受内存现在只做了几十个g的备份、恢复操作。
下面描述详细验证过程。
1ndb物理备份测试恢复drop的表
1.1准备环境
主备份窗口:
[mysql@sicloud-b200-3my_cluster]$ndb_mgm
--NDBCluster--ManagementClient--
ndb_mgm>show
ConnectedtoManagementServerat:
localhost:
1186
ClusterConfiguration
---------------------
[ndbd(NDB)]4node(s)
id=21@192.168.180.202(mysql-5.5.29ndb-7.2.10,Nodegroup:
0,Master)id=22@192.168.180.203(mysql-5.5.29ndb-7.2.10,Nodegroup:
1)
id=23@192.168.180.209(mysql-5.5.29ndb-7.2.10,Nodegroup:
2)id=24@192.168.180.210(mysql-5.5.29ndb-7.2.10,Nodegroup:
3)
[ndb_mgmd(MGM)]1node(s)
id=11@192.168.180.202(mysql-5.5.29ndb-7.2.10)
[mysqld(API)]6node(s)
id=31@192.168.180.202(mysql-5.5.29ndb-7.2.10)
id=32(notconnected,acceptingconnectfrom192.168.180.203)id=33(notconnected,acceptingconnectfrom192.168.180.209)id=34(notconnected,acceptingconnectfrom192.168.180.210)id=35(notconnected,acceptingconnectfromanyhost)
id=36(notconnected,acceptingconnectfromanyhost)
进入单用户方式:
db_mgm>ENTERSINGLEUSERMODE31
Singleusermodeentered
AccessisgrantedforAPInode31only.
ndb_mgm>startbackup
Waitingforcompleted,thismaytakeseveralminutes
Node21:
Backup1startedfromnode11
ndb_mgm>Node21:
Backup1startedfromnode11completedStartGCP:
257415StopGCP:
257443
#Records:
8102118#LogRecords:
0
Data:
2365678260bytesLog:
0bytes
耗时45秒
退出单用户方式:
Mgm》exitsingleusermode
查看各节点备份文件:
[mysql@sicloud-b200-3BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-3BACKUP-1]$ls-l
total595672
-rw-rw-r--.1mysqlmysql609828624Apr2209:
13BACKUP-1-0.21.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.21.ctl-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.21.log
[mysql@sicloud-b200-4BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-4BACKUP-1]$ls-l
total577448
-rw-rw-r--.1mysqlmysql591166636Apr2209:
13BACKUP-1-0.22.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.22.ctl-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.22.log
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-1BACKUP-1]$ls-l
total560444
-rw-rw-r--.1mysqlmysql573753576Apr2209:
13BACKUP-1-0.23.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.23.ctl-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.23.log
[mysql@sicloud-b200-2BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-2BACKUP-1]$ls-l
total577220
-rw-rw-r--.1mysqlmysql590933368Apr2201:
11BACKUP-1-0.24.Data-rw-rw-r--.1mysqlmysql126996Apr2201:
11BACKUP-1.24.ctl-rw-rw-r--.1mysqlmysql52Apr2201:
11BACKUP-1.24.log
登录数据库
mysql-h192.168.180.202-uroot-proot123-P3136
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|clu_db|
|mysql|
|ndbinfo|
|performance_schema|
|test|
+--------------------+
6rowsinset(0.00sec)
mysql>useclu_db
ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>showtables;
+------------------+
|Tables_in_clu_db|
+------------------+
|lf_t1|
|lf_t2|
|members1|
|test_part|
+------------------+
4rowsinset(0.02sec)
mysql>selectcount(*)fromlf_t1;
+----------+
|count(*)|
+----------+
|5999999|
+----------+
1rowinset(0.01sec)
mysql>showtables;
+------------------+
|Tables_in_clu_db|
+------------------+
|lf_t1|
|lf_t2|
|members1|
|test_part|
+------------------+
4rowsinset(0.02sec)
mysql>droptablelf_t2;
QueryOK,0rowsaffected(0.54sec)
mysql>showtables
->;
+------------------+
|Tables_in_clu_db|
+------------------+
|lf_t1|
|members1|
|test_part|
+------------------+
3rowsinset(0.00sec)
1.2执行恢复drop的表lf_t2进入每个数据节点,执行恢复,因为数据存放在所有的数据节点上,所以需要到每个节点上执行ndb_restore命令,简单介绍restore命令的几个选项
-n恢复的数据节点id
-b使用的备份id
-r备份数据的目录
登陆数据节点1,执行恢复命令,
[mysql@sicloud-b200-3BACKUP-1]$ndb_restore-nsicloud-b200-3-b21-r/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/
Unknownsuffix's'usedforvariable'nodeid'(value'sicloud-b200-3')ndb_restore:
Errorwhilesettingvalue'sicloud-b200-3'to'nodeid'
NDBT_ProgramExit:
2-Wrongarguments
[mysql@sicloud-b200-3BACKUP-1]$ndb_restore-n21-b1-r/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/
Nodeid=21
BackupId=1
backuppath=/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/Openingfile
'/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/BACKUP-1.21.ctl'
Filesize126996bytes
Backupversioninfiles:
ndb-6.3.11ndbversion:
mysql-5.5.29ndb-7.2.10StopGCPofBackup:
257442
Connectedtondb!
!
Unabletofindtable:
`lf_t2`
Restore:
Failedtorestoretable:
`clu_db/def/lf_t2`...Exiting
NDBT_ProgramExit:
1-Failed
由上说明这种方式恢复,是在有表的前提下,删除数据才能恢复。
尝试先用恢复表结构来恢复表数据。
。
。
[mysql@sicloud-b200-3BACKUP-1]$ndb_restore-n21-m-b1-r/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/
Nodeid=21
BackupId=1
backuppath=/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/
Opening
'/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1/BACKUP-1.21.ctl'Filesize126996bytes
Backupversioninfiles:
ndb-6.3.11ndbversion:
mysql-5.5.29ndb-7.2.10
StopGCPofBackup:
257442
Connectedtondb!
!
Createtable`mysql/def/ndb_db_backup`failed:
721:
Schemaobjectwithgivennamealreadyexists
Restore:
Failedtorestoretable:
`mysql/def/ndb_db_backup`...Exiting
NDBT_ProgramExit:
1–Failed
这样不行,它先从最基本的系统表开始。
。
。
。
,这样会报错。
。
。
。
file
1.3关闭数据库,重启管理和数据节点
mysql>showtables;
+------------------+
ndb_mgm>Node21:
Backup;#Records:
8102118#LogReco;Data:
2365678260bytesLog:
;耗时45秒;退出单用户方式:
;Mgm》exitsingleusermode;查看各节点备份文件:
;[mysql@sicloud-b200-3BAC;/home/mysql/my_cluster/n;[mysql
ndb_mgm>Node21:
Backup1startedfromnode11completedStartGCP:
257415StopGCP:
257443
#Records:
8102118#LogRecords:
0
Data:
2365678260bytesLog:
0bytes
耗时45秒
退出单用户方式:
Mgm》exitsingleusermode
查看各节点备份文件:
[mysql@sicloud-b200-3BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-3BACKUP-1]$ls-l
total595672
-rw-rw-r--.1mysqlmysql609828624Apr2209:
13BACKUP-1-0.21.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.21.ctl-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.21.log
[mysql@sicloud-b200-4BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-4BACKUP-1]$ls-l
total577448
-rw-rw-r--.1mysqlmysql591166636Apr2209:
13BACKUP-1-0.22.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.22.ctl-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.22.log
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-1BACKUP-1]$ls-l
total560444
-rw-rw-r--.1mysqlmysql573753576Apr2209:
13BACKUP-1-0.23.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.23.ctl-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.23.log
[mysql@sicloud-b200-2BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-2BACKUP-1]$ls-l
total577220
-rw-rw-r--.1mysqlmysql590933368Apr2201:
11BACKUP-1-0.24.Data-rw-rw-r--.1mysqlmysql126996Apr2201:
11BACKUP-1.24.ctl-rw-rw-r--.1mysqlmysql52Apr2201:
11BACKUP-1.24.log
登录数据库
mysql-h192.168.180.202-uroot-proot123-P3136
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|clu_db|
|mysql|
|ndbinfo|
|performance_schema|
|test|
+--------------------+
6rowsinset(0.00sec)
mysql>useclu_db
ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>showtables;
+------------------+
|Tables_in_clu_db|
+------------------+
|lf_t1|
|lf_t2|
|members1|
|test_part|
+------------------+
4rowsinset(0.02sec)
mysql>selectcount(*)fromlf_t1;+----------+
|count(*)|
+----------+
|5999999|
+----------+
1rowinset(0.01sec)
mysql>showtables;
+------------------+
|Tables_in_clu_db|
+------------------+
|lf_t1|
|lf_t2|
|members1|
|test_part|
+------------------+
4rowsinset(0.02sec)
mysql>droptablelf_t2;
QueryOK,0rowsaffected(0.54sec)
mysql>showtables
->;
+------------------+
|Tables_in_clu_db|
+------------------+
|lf_t1|
|members1|
|test_part|
+------------------+
3rowsinset(0.00sec)
total577448;-rw-rw-r--.1mysqlmysql59;-rw-rw-r--.1mysqlmysql52;/home/mysql/my_cluster/n;[mysql@sicloud-b200-1BAC;total560444;-rw-rw-r--.1mysqlmysql57;-rw-rw-r--.1mysqlmysql52;[mysql
total577448
-rw-rw-r--.1mysqlmysql591166636Apr2209:
13BACKUP-1-0.22.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.22.ctl
-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.22.log
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-1BACKUP-1]$ls-l
total560444
-rw-rw-r--.1mysqlmysql573753576Apr2209:
13BACKUP-1-0.23.Data-rw-rw-r--.1mysqlmysql126996Apr2209:
13BACKUP-1.23.ctl
-rw-rw-r--.1mysqlmysql52Apr2209:
13BACKUP-1.23.log
[mysql@sicloud-b200-2BACKUP-1]$pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@sicloud-b200-2BACKUP-1]$ls-l
total577220
-rw-rw-r--.1mysqlmysql590933368Apr2201:
11BACKUP-1-0.24.Data-rw-rw-r--.1mysqlmysql126996Apr2201:
11BACKUP-1.24.ctl
-rw-rw-r--.1mysqlmysql52Apr2201:
11BACKUP-1.24.log
登录数据库
mysql-h192.168.180.202-uroot-proot123-P31