Vertica的這些事(十二)——-vertica備份與恢復

最近在使用vertica,上網找了很多資料都沒有,只有自己看官方文檔動手搞一下了,今天搞了vertica的備份與恢復 以下是整理的過程,分享給大家,如有問題歡迎大家指正~ 可加QQ群交流:412191494

1、vertica備份

1.1 vertica備份配置:

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py  --setupconfig  Snapshot name (backup_snapshot): full_backup20160505  Number of restore points (1): 1  Specify objects (no default):  Object restore mode (coexist, createOrReplace or create) (createOrReplace):  Vertica user name (dbadmin): dbadmin  Save password to avoid runtime prompt? (n) [y/n]: y  Database user password to save in vbr password config file (no default):  Node v_windy1990_node0001  Backup host name (no default): 192.168.231.110  Backup directory (no default): /home/dbadmin/backup  Change advanced settings? (n) [y/n]: y  Temp directory (/tmp/vbr):  Number of times to retry (2):  Seconds between retry attempts (1):  Encrypt data during transmission? (n) [y/n]:  Use checksum for data integrity (not file data and size)? (n) [y/n]:  Port number for rsync daemon (50000):  User name to access rsync daemon (no default):  Password of the user who accesses rsync daemon:  Backup transfer bandwidth limit in KBps or 0 for unlimited (0):  Number of concurrency for backup (1):  Restore transfer bandwidth limit in KBps or 0 for unlimited (0):  Number of concurrency for restore (1):  Password file name (no default): password  Saved vbr password to password.  Config file name (full_backup20160505.ini):  Saved vbr config to full_backup20160505.ini.  

備份成功後查看備份的文件內容如下:

[dbadmin@localhost ~]$ more full_backup20160505.ini  [Misc]  snapshotName = full_backup20160505  restorePointLimit = 1  objectRestoreMode = createOrReplace  tempDir = /tmp/vbr  retryCount = 2  retryDelay = 1  passwordFile = password    [Database]  dbName = Windy1990  dbUser = dbadmin    [Transmission]  encrypt = False  checksum = False  port_rsync = 50000  serviceAccessUser = None  total_bwlimit_backup = 0  concurrency_backup = 1  total_bwlimit_restore = 0  concurrency_restore = 1    [Mapping]  v_windy1990_node0001 = 192.168.231.110:/home/dbadmin/backup  

備份中我選了一個文件保存密碼:

/home/dbadmin/password  

裡面存有數據路的密碼:

[dbadmin@localhost ~]$ more password  [Passwords]  dbPassword = dbadmin  1.2 vertica 全備份  官方文檔:  A full backup is a complete copy of the database catalog, its schemas, tables, and other objects. It is a consistent image of the database at the time the backup occurred. You can use a full backup for disaster recovery to restore a damaged or incomplete database.  

執行備份命令:

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini  Error: Manifest not found at rsync://[192.168.231.110]:50000/home/dbadmin/backup/ -- is the backup location initialized? Hint: Execute '--task init' first.  Backup FAILED.  

發現報錯了,根據提示,必須先初始化路徑(個人理解的就是先聲明路徑)

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task init --config-file full_backup20160505.ini  Initializing backup locations.  Backup locations initialized.  

路徑聲明成功! 查看該備份路徑下多了一個文件。

[dbadmin@localhost backup]$ ls -l  total 4  -rw-r--r--. 1 dbadmin verticadba 22 May  5 13:47 backup_manifest  

然後再次執行備份命令:

/opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini  

可以看到這次可以備份成功了:

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini  Starting backup of database Windy1990.  Participating nodes: v_windy1990_node0001.  Snapshotting database.  Snapshot complete.  Approximate bytes to copy: 60269241 of 60269241 total.  [==================================================] 100%  Copying backup metadata.  Finalizing backup.  Backup complete!  

我們可以看到,已經完全備份了vertica資料庫。 此時我們應該可以猜到,備份的數據存在我們剛剛聲明的路徑/home/dbamin/backup下:

[dbadmin@localhost backup]$ ll  total 12  -rw-r--r--. 1 dbadmin verticadba 1044 May  5 14:08 backup_manifest  drwxr-xr-x. 8 dbadmin verticadba 4096 May  5 14:07 Objects  drwxr-xr-x. 3 dbadmin verticadba 4096 May  5 14:08 Snapshots  

可以看到多了2個文件夾 /Objects / Snapshots 我們打開Objects文件夾可以看到,我們的vertica資料庫文件就在該目錄下。 /home/dbadmin/backup/Snapshots/full_backup20160505_20160505_060600/v_windy1990_node0001目錄下存放著我們備份的原vertica資料庫的一下資訊。

檢查每個節點的數據流:

select node_name,sum(used_bytes) as size_in_bytes from v_monitor.storage_containers group by node_name;      

1.3 vertica的增量備份 官方文檔上是把全量和增量一起介紹的,稱為Full and Incremental Backups,在這裡我把增量單獨列了出來。 restorePointLimit控制著增量備份集的數量 我這隻有一個節點,剛在配置備份文件的時候設置restorePointLimit = 1 再次執行1.2中的全備份命令即可實現增量備份~

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini  Starting backup of database Windy1990.  Participating nodes: v_windy1990_node0001.  Snapshotting database.  Snapshot complete.  Approximate bytes to copy: 0 of 60269241 total.  [==================================================] 100%  Copying backup metadata.  Finalizing backup.  Backup complete!  

有人會問,我怎麼知道這樣就是增量備份而不是全量備份的呢?? 這個問題很好,我備份的時候也在想,備份完成後,我再次打開了backup文件夾,看到:

[dbadmin@localhost ~]$ cd backup/  [dbadmin@localhost backup]$ ll  total 12  -rw-r--r--. 1 dbadmin verticadba 1103 May  5 14:36 backup_manifest  drwxr-xr-x. 8 dbadmin verticadba 4096 May  5 14:07 Objects  drwxr-xr-x. 4 dbadmin verticadba 4096 May  5 14:36 Snapshots  

大家看Objects目錄的時間,因為在此過程中我沒有對vertica資料庫進行操作,所以vertica資料庫文件還是原來的就沒有更新。 所以想要知道上面我們操作的是增量的其實很簡單,我在vertica資料庫中的任意一張表中插入一條數據,然後我再執行上面的備份腳本。 我插入一條手機號:

dbadmin=> insert into tb_serv_num values('13488888888','qin**');   OUTPUT  --------        1  (1 row)    dbadmin=> commit;  COMMIT  dbadmin=> q  

然後重新執行備份語句,執行後查看backup文件下的內容:

[dbadmin@localhost ~]$ cd backup/  [dbadmin@localhost backup]$ ll  total 12  -rw-r--r--. 1 dbadmin verticadba 1171 May  5 14:46 backup_manifest  drwxr-xr-x. 9 dbadmin verticadba 4096 May  5 14:46 Objects  drwxr-xr-x. 4 dbadmin verticadba 4096 May  5 14:46 Snapshots  

看文件時間,是不是說明我們上面的推理是正確的呢~~~ 所以,不要懷疑了,我們的增量備份是沒有問題的! 1.4 vertica對象級備份 官網文檔: The database administrator user can create object-level backups consisting of one or more schemas and tables. Object-level backups are especially useful for multi-tenanted database sites. 其實對象級備份和全量備份差不多,知識在生成備份配置文件時有所不同(注意不同之處):

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --setupconfigSnapshot name (backup_snapshot): backup_object_tb_serv  Number of restore points (1):  Specify objects (no default): tb_serv_num  Object restore mode (coexist, createOrReplace or create) (createOrReplace):  Vertica user name (dbadmin):  Save password to avoid runtime prompt? (n) [y/n]: y  Database user password to save in vbr password config file (no default):  Node v_windy1990_node0001  Backup host name (no default): 192.168.231.110  Backup directory (no default): /home/dbadmin/backup  Change advanced settings? (n) [y/n]:  Password file name (no default): password  Saved vbr password to password.  Config file name (backup_object_tb_serv.ini):  Saved vbr config to backup_object_tb_serv.ini.  

然後在執行備份命令:

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file backup_object_tb_serv.ini  Starting backup of database Windy1990.  Objects: ['tb_serv_num']  Participating nodes: v_windy1990_node0001.  Snapshotting database.  Snapshot complete.  Approximate bytes to copy: 0 of 244 total.  [==================================================] 100%  Copying backup metadata.  Finalizing backup.  Backup complete!  
[Transmission]  encrypt = False  checksum = False  port_rsync = 50000  serviceAccessUser = None  total_bwlimit_backup = 0  concurrency_backup = 1  total_bwlimit_restore = 0  concurrency_restore = 1  hardLinkLocal = True  

然後還是執行備份命令:

/opt/vertica/bin/vbr.py --task backup --config-file HardLinkLocal_backup20160505.ini  [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file HardLinkLocal_backup20160505.ini  Starting backup of database Windy1990.  Participating nodes: v_windy1990_node0001.  Snapshotting database.  Snapshot complete.  Approximate bytes to copy: 0 of 60269355 total.  [==================================================] 100%  Copying backup metadata.  Finalizing backup.  Backup complete!  

1.6 查看備份 查看備份的內容:

/opt/vertica/bin/vbr.py --task listbackup --config-file HardLinkLocal_backup20160505.ini  [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task listbackup --config-file HardLinkLocal_backup20160505.ini  backup                                epoch   objects   hosts(nodes)                            file_system_type  full_backup20160505_20160505_074850   16                v_windy1990_node0001(192.168.231.110)   [Linux]  full_backup20160505_20160505_064640   16                v_windy1990_node0001(192.168.231.110)   [Linux]  

在vertica資料庫中可以查詢到備份的資訊:

dbadmin=> select * from database_backups;         backup_timestamp        |      node_name       |     snapshot_name     | backup_epoch | node_count | file_system_type |   objects  -------------------------------+----------------------+-----------------------+--------------+------------+------------------+-------------   2016-05-05 14:08:03.369797+08 | v_windy1990_node0001 | full_backup20160505   |           15 |          1 | [Linux]          |   2016-05-05 14:36:39.496688+08 | v_windy1990_node0001 | full_backup20160505   |           15 |          1 | [Linux]          |   2016-05-05 14:42:35.517465+08 | v_windy1990_node0001 | full_backup20160505   |           15 |          1 | [Linux]          |   2016-05-05 14:44:43.043536+08 | v_windy1990_node0001 | full_backup20160505   |           15 |          1 | [Linux]          |   2016-05-05 14:46:57.958863+08 | v_windy1990_node0001 | full_backup20160505   |           16 |          1 | [Linux]          |   2016-05-05 15:03:19.580159+08 | v_windy1990_node0001 | backup_object_tb_serv |           16 |          1 | [Linux]          | tb_serv_num   2016-05-05 15:50:53.143446+08 | v_windy1990_node0001 | full_backup20160505   |           16 |          1 | [Linux]          |  (7 rows)  

2、vertica恢復

2.1 vertica恢復

為了看到是恢復是有用的,再次我破壞一下數據,drop一個表,刪除一條數據

dbadmin=> select * from tb_rp_st_user_day ;   serv_number | user_name  -------------+-----------  (0 rows)    dbadmin=> drop table tb_rp_st_user_day;  DROP TABLE    dbadmin=> select * from tb_serv_num where name='ss';    serv_num   | name  -------------+------   186371***** | ss  (1 row)    dbadmin=> delete from tb_serv_num where name='ss';   OUTPUT  --------        1  (1 row)    dbadmin=> commit;  COMMIT  

開始執行恢復命令:

/opt/vertica/bin/vbr.py --task restore --config-file full_backup20160505.ini  [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file full_backup20160505.ini Error: Full database restore requires the nodes be DOWN.  Restore FAILED.  

報錯啦~ 提示只有在vertica資料庫down的狀態下才能恢復……

查看官方文檔:

To restore a full database backup, you must ensure that:  •	The database is down. You cannot restore a full backup when the database is running.  •	All of the backup hosts are up and available.  •	The backup directory exists and contains the backups from which to restore.  •	The cluster to which you are restoring the backup has the same number of hosts as the one used to create the backup. The node names and the IP addresses must also be identical.  •	The database you are restoring must already exist on the cluster to which you are restoring data. The database can be completely empty without any data or schema. As long as the database name matches the name in the backup, and all of the node names match the names of the nodes in the configuration file, you can restore to it.  •	The user performing the restore is the database administrator.  

所以此處,我們先嘗試第二種恢復方式(對象級恢復)來恢複數據,剛剛我們同時對tb_serv_num表做了對象級備份:

執行對象級恢復:

/opt/vertica/bin/vbr.py --task restore --config-file backup_object_tb_serv.ini  [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file backup_object_tb_serv.ini  Starting object restore of database Windy1990.  Participating nodes: v_windy1990_node0001.  Objects to restore: tb_serv_num.  Restoring from restore point: backup_object_tb_serv_20160505_070249  Loading snapshot catalog from backup.  Syncing data from backup to cluster nodes.  [==================================================] 100%  Finalizing restore.  Restore complete!  

OK~看到上面我們已經恢復成功了,那就登上vertica資料庫驗證一下數據是否恢復了。

dbadmin=> select * from tb_serv_num ;    serv_num   | name  -------------+-------   138***** | windy   186***** | ss   134***** | qin**  (3 rows)  

可以看到,數據已恢復了~,等等,剛才我們還drop了一個 表,這個表是不是恢復了呢?

dbadmin=> d tb_rp_st_user_day;  Did not find any relation.  
/opt/vertica/bin/vbr.py --task restore --config-file HardLinkLocal_backup20160505.ini  [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file HardLinkLocal_backup20160505.ini  Error: Full database restore requires the nodes be DOWN.  Restore FAILED.  

同樣看到,也需要vertica資料庫為DOWN

看來要停掉vertica資料庫了~(停掉資料庫,此處略)
停掉vertica資料庫後,再試全量恢復:

[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file full_backup20160505.ini  Starting full restore of database Windy1990.  Participating nodes: v_windy1990_node0001.  Restoring from restore point: full_backup20160505_20160505_074850  Computing the size of data to be synced from backup to cluster nodes.  Syncing data from backup to cluster nodes.  [==================================================] 100%  Restoring catalog.  Restore complete!  

可以看到,恢復的還不錯。
再重新啟動vertica,登上vertica後查看錶tb_rp_st_user_day是否恢復了:

dbadmin=> d tb_rp_st_user_day;                                              List of Fields by Tables   Schema |       Table       |   Column    |    Type     | Size | Default | Not Null | Primary Key | Foreign Key  --------+-------------------+-------------+-------------+------+---------+----------+-------------+-------------   public | tb_rp_st_user_day | serv_number | varchar(13) |   13 |         | f        | f           |   public | tb_rp_st_user_day | user_name   | varchar(50) |   50 |         | f        | f           |  (2 rows)  

可以看到,恢復成功了。

以上就是vertica備份以及簡單的恢復。

通過此次vertica恢復可以看到,很多問題可以在官方文檔中找到解決方法,在停掉vertica和啟動時,遇到了一點問題,不過根據錯誤提示可以看出解決方法的。