vertica数据库备份与恢复

107 阅读 0 评论 71 点赞

我是靠谱客的博主单身草莓，最近开发中收集的这篇文章主要介绍vertica数据库备份与恢复，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.veritica备份步骤（Full backup）：
1).进行备份配置(或者可以直接生成：vim full_test_cluster_01_20170215.ini,这里省略)
遇到的错误：
[vdbu@test_node1 bin]$ /opt/vertica/bin/vbr --setupconfig
Snapshot name (backup_snapshot): full_20170215
Destination Vertica DB bin directory (only required for object replication) (/opt/vertica/bin):
Number of restore points (1):
Specify objects (no default):
Object restore mode (coexist, createOrReplace or create) (createOrReplace):
Choose from these databases: test_cluster_01,test,test_vertica_01,test_cluster_02 (test_cluster_01):
Vertica user name (vdbu):
Save password to avoid runtime prompt? (n) [y/n]:
Node v_test_cluster_01_node0001
Backup host name (no default): 127.0.0.1
Backup directory (no default): /home/vdbu/backup
Node v_test_cluster_01_node0002
Backup host name (no default): 127.0.0.1
Backup directory (no default): /home/vdbu/backup
Change advanced settings? (n) [y/n]:
Config file name (full_20170215.ini):
Error: Can not save file full_20170215.ini, try again? (y) [y/n]: y
Config file name (full_20170215.ini):
Error: Can not save file full_20170215.ini, try again? (y) [y/n]:
Config file name (full_20170215.ini):
Error: Can not save file full_20170215.ini, try again? (y) [y/n]: n
Setup config failed: Can not save file full_20170215.ini

出错原因：在/opt/vertica/bin/目录下做/opt/vertica/bin/vbr --setupconfig配置，就会出现上面的错误。
解决方法：在其它文件中进行配置操作。
正常操作如下（最近本的操作）:
[vdbu@test_node1 ~]$ /opt/vertica/bin/vbr --setupconfig
Snapshot name (backup_snapshot): full_test_cluster_01_20170215
Destination Vertica DB bin directory (only required for object replication) (/opt/vertica/bin):
Number of restore points (1):
Specify objects (no default):
Object restore mode (coexist, createOrReplace or create) (createOrReplace):
Choose from these databases: test_cluster_01,test,test_vertica_01,test_cluster_02 (test_cluster_01):
Vertica user name (vdbu):
Save password to avoid runtime prompt? (n) [y/n]:
Node v_test_cluster_01_node0001
Backup host name (no default): 127.0.0.1
Backup directory (no default): /backup/vertica/backup_test_cluster_01/node1
Node v_test_cluster_01_node0002
Backup host name (no default): 127.0.0.1
Backup directory (no default): /backup/vertica/backup_test_cluster_01/node2
Change advanced settings? (n) [y/n]:
Config file name (full_test_cluster_01_20170215.ini):
Saved vbr config to full_test_cluster_01_20170215.ini.

查看full_test_cluster_01_20170215.ini文件如下：
[vdbu@test_node1 ~]$ more full_test_cluster_01_20170215.ini
[Misc]
snapshotName = full_test_cluster_01_20170215
dest_verticaBinDir = /opt/vertica/bin
restorePointLimit = 1
objectRestoreMode = createOrReplace

[Database]
dbName = test_cluster_01
dbUser = vdbu
dbPromptForPassword = True

[Transmission]

[Mapping]
v_test_cluster_01_node0001 = 127.0.0.1:/backup/vertica/backup_test_cluster_01/node1
v_test_cluster_01_node0002 = 127.0.0.1:/backup/vertica/backup_test_cluster_01/node2

2).执行备份命令
vbr --task backup --config-file full_test_cluster_01_20170215.ini
出现错误：
[vdbu@test_node1 ~]$ vbr --task backup --config-file full_test_cluster_01_20170215.ini
Error: Manifest not found at rsync://[127.0.0.1]:50000/backup/vertica/backup_test_cluster_01/node1 -- is the backup location initialized? Hint: Execute '--task init' first.
Backup FAILED.

解决方法: 按提示先初始化--

[vdbu@test_node1 ~]$ vbr --task init --config-file full_test_cluster_01_20170215.ini
Initializing backup locations.
Backup locations initialized.

再执行备份命令：
[vdbu@test_node1 ~]$ vbr --task backup --config-file full_test_cluster_01_20170215.ini
Starting backup of database test_cluster_01.
Participating nodes: v_test_cluster_01_node0001, v_test_cluster_01_node0002.
Enter vertica password:
Snapshotting database.
Snapshot complete.
Approximate bytes to copy: 436690712 of 436690712 total.
[=.................................................] 2%Error: Error accessing remote storage: failed to get remote files: rsync: failed to connect to 127.0.0.1: Connection refused (111)
rsync error: error in socket IO (code 10) at clientserver.c(122) [sender=3.0.7]
: returncode=10
Backup FAILED.

出现问题：（访问被拒绝）这个是因为我的是两个节点，其它节点访问不了备份节点的127.0.0.1：下面的文件夹

解决方法：（修改IP地址，把127.0.0.1改成192.168.1.6）
[vdbu@test_node1 ~]$ vim full_test_cluster_01_20170215.ini
[Misc]
snapshotName = full_test_cluster_01_20170215
dest_verticaBinDir = /opt/vertica/bin
restorePointLimit = 1
objectRestoreMode = createOrReplace

[Database]
dbName = test_cluster_01
dbUser = vdbu
dbPromptForPassword = True

[Transmission]

[Mapping]
v_test_cluster_01_node0001 = 192.168.1.6:/backup/vertica/backup_test_cluster_01/node1
v_test_cluster_01_node0002 = 192.168.1.6:/backup/vertica/backup_test_cluster_01/node2

再执行备份命令：
[vdbu@test_node1 ~]$ vbr --task backup --config-file full_test_cluster_01_20170215.ini
Starting backup of database test_cluster_01.
Participating nodes: v_test_cluster_01_node0001, v_test_cluster_01_node0002.
Enter vertica password:
Snapshotting database.
Snapshot complete.
Approximate bytes to copy: 436690712 of 436690712 total.
[==================================================] 100%
Copying backup metadata.
Finalizing backup.
Backup complete!

成功！

在备份路径可以看到：backup_manifest Objects Snapshots 三个文件夹

----------------------------------------------------------------
2.数据恢复(full backup)
1).随意删除一个表drop table XXX
2).进行恢复
[vdbu@test_node1 ~]$ vbr --task restore --config-file full_test_cluster_01_20170215.ini
Error: Full database restore requires the nodes be DOWN.
Restore FAILED.
出现错误：数据库关闭时才能恢复.

关闭完数据库
[vdbu@test_node1 ~]$ vbr --task restore --config-file full_test_cluster_01_20170215.ini
Starting full restore of database test_cluster_01.
Participating nodes: v_test_cluster_01_node0001, v_test_cluster_01_node0002.
Restoring from restore point: full_test_cluster_01_20170215_20170215_091252
Determining what data to restore from backup.
[==================================================] 100%
Approximate bytes to copy: 436688578 of 436690712 total.
Syncing data from backup to cluster nodes.
[==================================================] 100%
Restoring catalog.
Restore complete!

3)打开数据库：
d会看到刚才删除的表