本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger的Oracle&MySQL技术博客
本文链接地址: how_to_use_roach_backup_gaussdb
对于备份工具来讲，gauss官方是推荐使用roach工具来进行备份；该工具本质上来讲是python脚本;大家有兴趣可以进一步去研究。我这里主要是分享一下如何使用该工具对数据库进行备份和恢复。
+++需要增加相关环境变量
PATH=$PATH:/opt/gauss/gauss100/bin:$HOME/.local/bin:$HOME/bin LD_LIBRARY_PATH=/opt/gauss/gauss100/lib:/opt/gauss/gauss100/add-ons:/opt/gauss/GaussDB_100_1.0.1-TOOLS/GaussDB_100_1.0.1-RESTORE/add-ons:/opt/gauss/GaussDB_100_1.0.1-TOOLS/GaussDB_100_1.0.1-RESTORE/lib export INSTANCE_NMAE=enmotech export ROACH_HOME=/opt/gauss/GaussDB_100_1.0.1-ROACH-REDHAT-64bit export GAUSSLOG=/tmp/gaussdb_backup export PATH
+++调用roach工具
[roger@mysqldb bin]$ ls -ltr
total 9376
-r-x------ 1 roger roger 32056 Feb 13 15:58 zencrypt
-r-x------ 1 roger roger 2922488 Feb 13 15:58 zsql
-r-x------ 1 roger roger 13145 Feb 13 15:58 GaussLog.py
-r-x------ 1 roger roger 4430 Feb 13 15:58 Common.py
-r-x------ 1 roger roger 43710 Feb 13 15:58 zctl.py
-r-x------ 1 roger roger 30101 Feb 13 15:58 uninstall.py
-r-x------ 1 roger roger 79727 Feb 13 15:58 sql_process.py
-r-x------ 1 roger roger 6227 Feb 13 15:58 shutdowndb.sh
-r-x------ 1 roger roger 6449000 Feb 13 15:58 zengine
lrwxrwxrwx 1 roger roger 61 Feb 14 17:38 Gauss_rman.py -> /opt/gauss/GaussDB_100_1.0.1-ROACH-REDHAT-64bit/GaussRoach.py
lrwxrwxrwx 1 roger roger 70 Feb 14 21:13 ztrst -> /opt/gauss/GaussDB_100_1.0.1-TOOLS/GaussDB_100_1.0.1-RESTORE/bin/ztrst
[roger@mysqldb bin]$ python Gauss_rman.py
GaussRoach.py is a utility to backup GaussDB100 to NBU/disk and restore GaussDB100 from NBU/disk.
Usage:
python GaussRoach.py -h | --help
python GaussRoach.py -v | --version
python GaussRoach.py --mode [cluster|single] -t backup [options]
python GaussRoach.py --mode [cluster|single] -t restore [options]
python GaussRoach.py --mode [cluster|single] -t delete [options]
python GaussRoach.py --mode [cluster|single] -t show [options]
python GaussRoach.py -t validate [options]
python GaussRoach.py -t start [options]
python GaussRoach.py -t stop
Common options:
-t The step of GaussRoach, include config, clean and start,backup,restore,delete,stop
-h --help Show this help, then exit
-v --version Display the current roach version.
--mode <option mode> The mode be used to for every action options, the mode value is 'single' or 'cluster'.
--master-port <master port> The port be only used for backup and restore in cluster mode, port values: [1024-65535]
--media-type <media type> Mention type of media either disk or NBU
--media-destination <media destination> The path or policy that should be used for backup/restore as per media-type,
and path length is less than 127 byte.
--metadata-destination <path-name> The path where the metadata file is to be kept in backup
--nbu-policy <nbu policy> The NBU policy that should be used for backup/restore.
--nbu-schedule <nbu schedule> The NBU schedule that should be used for backup/restore.
--obs-ak <obs ak> Access obs server credentials.
--obs-sk <obs ak> Access osb server credentials.
--obs-bucket <obs bucket name> The obs bucket name
--obs-server <obs server> The obs access server address.
--cluster-id <cluster unique id> Cluster or local node unique id.
Options for backup
--compression-level The compression level that should be used for backup.
--compression-mode The compression mode that should be used for backup.
--prior-backup-key Previous Full Backup key to be considered as the base for Incremental Backup
--validation-type To validate disk backup set integrity,the value is sha256.
--enable-encrypt To be used for encrypt backupset.
--parallel-process Backup process threads, the values:[1-8]
--incremental-type The type of incremental backup, the values:[all, newest]
Options for Restore
--backup-key <backup key> The key for restoring.
--clean For cluster restore,performs clean up activity internally.
--restore-new-cluster Restore data to new cluster
--restore-target-time Time(<=) to be used for restore, format '<YYYY-MM-DD HH:MM:SS>'
--validation-type To validate disk backup set integrity,the value is 'sha256'.
--nbu-backup-host A hostname, restore to new cluster from NBU.
Options for Show
--all-backups Show complete backup catalogue information. Default action if no option given.
--backup-key <backup_key> Show restore path upto a given backup key.
--last-full-backup Show last backup catalogue information.
--nbu-backup-host A hostname, restore to new cluster from NBU.
Options for Validate
--validation-type To validate disk backup set integrity,the value is sha256.
--backup-key <backup key> The key for validate.
Options for Delete
--cascade To delete all descendant backups of the provided backup key.
--backup-key <backup key> The key to delete.
Options for Start
--media-type <media type> Mention type of media either disk,nbu,abu.
--restore-new-cluster Restore data to new cluster
### Refer user manual for more details ###我这里是自己做了一个软连接，实际上还是调用的roach。
+++数据库全备
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t backup --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ start run backup. Successfully backup data, backup key: 20200215_190737, 00:00:05 Performing backup completed. [roger@mysqldb bin]$ [roger@mysqldb bin]$ [roger@mysqldb bin]$ [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t show --all-backups --metadata-destination /tmp/gaussdb_backup/ start run show. | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | SUCCESS | Performing show completed. [roger@mysqldb bin]$ ls -ltr /tmp/gaussdb_backup/ total 16 drwx------ 2 roger roger 4096 Feb 14 17:58 roger_level0_01.bak drwx------ 2 roger roger 4096 Feb 14 18:06 roger_level1_01.bak drwx------ 2 roger roger 4096 Feb 15 18:59 roger_level0_0215.bak drwxrwxr-x 3 roger roger 78 Feb 15 19:07 roach -rw------- 1 roger roger 710 Feb 15 19:07 backupset_disk.ini [roger@mysqldb bin]$ ls -ltr /tmp/gaussdb_backup/roach/ total 8 drwx------ 4 roger roger 28 Feb 15 19:07 20200215_190737 -rw------- 1 roger roger 1551 Feb 15 19:07 roach-single.log -rw------- 1 roger roger 1533 Feb 15 19:08 roach-controller.log [roger@mysqldb bin]$ ls -ltr /tmp/gaussdb_backup/roach/20200215_190737/ total 4 drwx------ 2 roger roger 4096 Feb 15 19:07 data drwx------ 2 roger roger 21 Feb 15 19:07 path [roger@mysqldb bin]$ ls -ltr /tmp/gaussdb_backup/roach/20200215_190737/data/ total 48880 -rw------- 1 roger roger 9497 Feb 15 19:07 ctrl_0_0.bak -rw------- 1 roger roger 52 Feb 15 19:07 data_TEMP_1_0.bak -rw------- 1 roger roger 415826 Feb 15 19:07 data_SYSTEM_0_0.bak -rw------- 1 roger roger 52 Feb 15 19:07 data_TEMP2_9_0.bak -rw------- 1 roger roger 52 Feb 15 19:07 data_TEMP2_UNDO_11_0.bak -rw------- 1 roger roger 1376534 Feb 15 19:07 data_SYSAUX_12_0.bak -rw------- 1 roger roger 16546671 Feb 15 19:07 data_UNDO_3_1.bak -rw------- 1 roger roger 16566163 Feb 15 19:07 data_USERS_4_0.bak -rw------- 1 roger roger 15085066 Feb 15 19:07 data_UNDO_3_0.bak -rw------- 1 roger roger 17268 Feb 15 19:07 arch_14_0.bak -rw------- 1 roger roger 4424 Feb 15 19:07 backupset
+++增量备份
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t backup --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ --incremental-type all start run backup. Successfully backup data, backup key: 20200215_191153, 00:00:02 Performing backup completed. [roger@mysqldb bin]$
此时的日志如下：
[roger@mysqldb roach]$ cat roach-controller.log [2020-02-15 19:07:37,175] [DEBUG]: checking backupset info [GSroachCommon.py:141] [2020-02-15 19:07:37,175] [DEBUG]: start restore backupset info file. [GSroachCommon.py:948] [2020-02-15 19:07:37,176] [INFO]: start run backup. [Gauss_rman.py:577] [2020-02-15 19:07:37,176] [DEBUG]: start backup. [GSroachBackup.py:230] [2020-02-15 19:07:37,176] [DEBUG]: start create backup directory. [GSroachBackup.py:363] [2020-02-15 19:07:37,176] [DEBUG]: Successfully to create backup directory. [GSroachBackup.py:387] [2020-02-15 19:07:42,793] [DEBUG]: end backup. [GSroachBackup.py:257] [2020-02-15 19:07:42,793] [INFO]: Performing backup completed. [Gauss_rman.py:586] [2020-02-15 19:08:07,650] [DEBUG]: checking backupset info [GSroachCommon.py:141] [2020-02-15 19:08:07,651] [DEBUG]: start restore backupset info file. [GSroachCommon.py:948] [2020-02-15 19:08:07,651] [INFO]: start run show. [Gauss_rman.py:577] [2020-02-15 19:08:07,651] [DEBUG]: start show backupset information. [GSroachCommon.py:505] [2020-02-15 19:08:07,651] [INFO]: | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | SUCCESS | [GSroachCommon.py:550] [2020-02-15 19:08:07,651] [DEBUG]: Successfully show backupset information. [GSroachCommon.py:556] [2020-02-15 19:08:07,652] [INFO]: Performing show completed. [Gauss_rman.py:586] [2020-02-15 19:11:53,041] [DEBUG]: checking backupset info [GSroachCommon.py:141] [2020-02-15 19:11:53,041] [DEBUG]: start restore backupset info file. [GSroachCommon.py:948] [2020-02-15 19:11:53,042] [INFO]: start run backup. [Gauss_rman.py:577] [2020-02-15 19:11:53,042] [DEBUG]: start backup. [GSroachBackup.py:230] [2020-02-15 19:11:53,042] [DEBUG]: Check prior key for single backup. [GSroachBackup.py:118] [2020-02-15 19:11:53,042] [DEBUG]: Get prior key for single backup. [GSroachBackup.py:79] [2020-02-15 19:11:53,042] [DEBUG]: Get prior key of '/opt/gauss/gaussdata', cmd: $GSDB_HOME/bin/zsql / as sysdba -D /opt/gauss/gaussdata -q -c "SELECT TAG FROM SYS.BACKUP_SET$ WHERE STAGE=4 AND INCREMENTAL_LEVEL=0 ORDER BY COMPLETION_TIME DESC LIMIT 1" [GSroachBackup.py:103] [2020-02-15 19:11:53,091] [DEBUG]: The backup key to use is 20200215_190737. [GSroachBackup.py:213] [2020-02-15 19:11:53,096] [DEBUG]: start create backup directory. [GSroachBackup.py:363] [2020-02-15 19:11:53,096] [DEBUG]: Successfully to create backup directory. [GSroachBackup.py:387] [2020-02-15 19:11:55,293] [DEBUG]: end backup. [GSroachBackup.py:257] [2020-02-15 19:11:55,293] [INFO]: Performing backup completed. [Gauss_rman.py:586]
其实蓝色部分日志是增量备份信息。可见会自动去查询备份集信息，并在最新的全备基础上做增量备份。
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t show --all-backups --metadata-destination /tmp/gaussdb_backup/ start run show. | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | SUCCESS | |20200215_191153| INCR | DISK |2020-02-15 19:11:53|2020-02-15 19:11:55| 120 KB | SUCCESS | Performing show completed.
++++删除备份集
-删除单独备份key [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t delete --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup --backup-key 20200215_191153 start run delete. start delete backup data. Successfully delete backup data. Performing delete completed. [roger@mysqldb bin]$ [roger@mysqldb bin]$ [roger@mysqldb bin]$ p bash: [roger@mysqldb: command not found... [roger@mysqldb bin]$ [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t show --all-backups --metadata-destination /tmp/gaussdb_backup/ start run show. | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | SUCCESS | |20200215_191153| INCR | DISK |2020-02-15 19:11:53|2020-02-15 19:11:55| 120 KB | DELETE | Performing show completed. --这里手工删除了增量备份
同时roach支持级联删除；如下：
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t show --all-backups --metadata-destination /tmp/gaussdb_backup/ start run show. | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | DELETE | |20200215_191153| INCR | DISK |2020-02-15 19:11:53|2020-02-15 19:11:55| 120 KB | DELETE | |20200215_193143| FULL | DISK |2020-02-15 19:31:43|2020-02-15 19:31:50| 47.8 MB | SUCCESS | |20200215_193204| INCR | DISK |2020-02-15 19:32:04|2020-02-15 19:32:06| 68 KB | SUCCESS | Performing show completed. [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t delete --cascade --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup --backup-key 20200215_193143 start run delete. start delete backup data. Successfully delete backup data. Performing delete completed. [roger@mysqldb bin]$ [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t show --all-backups --metadata-destination /tmp/gaussdb_backup start run show. | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | DELETE | |20200215_191153| INCR | DISK |2020-02-15 19:11:53|2020-02-15 19:11:55| 120 KB | DELETE | |20200215_193143| FULL | DISK |2020-02-15 19:31:43|2020-02-15 19:31:50| 47.8 MB | DELETE | |20200215_193204| INCR | DISK |2020-02-15 19:32:04|2020-02-15 19:32:06| 68 KB | DELETE | Performing show completed.
可以看到，全备的kacup_key；会同时把该全备对应的增量备份也一并删除，这视为级联删除。
+++ 观察备份key记录的视图信息
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t backup --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ start run backup. Successfully backup data, backup key: 20200215_194313, 00:00:04 Performing backup completed. [roger@mysqldb bin]$ [roger@mysqldb bin]$ [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t backup --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ --incremental-type all start run backup. Successfully backup data, backup key: 20200215_194322, 00:00:01 Performing backup completed. [roger@mysqldb bin]$ [roger@mysqldb bin]$ [roger@mysqldb bin]$ zsql / as sysdba -q connected. SQL> select TAG,STATUS,INCREMENTAL_LEVEL,START_TIME,COMPLETION_TIME,STAGE from sys_backup_sets; TAG STATUS INCREMENTAL_LEVEL START_TIME COMPLETION_TIME STAGE ---------------------------------------------------------------- ------------ ----------------- -------------------------------- -------------------------------- ------------ 635108386592814_775398459953153 0 0 2020-02-15 18:59:46.592814 2020-02-15 18:59:56.262452 4 20200215_190737 0 0 2020-02-15 19:07:37.227907 2020-02-15 19:07:42.639506 4 20200215_191153 0 1 2020-02-15 19:11:53.130457 2020-02-15 19:11:55.170908 4 20200215_193143 0 0 2020-02-15 19:31:43.283613 2020-02-15 19:31:50.283854 4 20200215_193204 0 1 2020-02-15 19:32:04.571360 2020-02-15 19:32:06.559191 4 20200215_194313 0 0 2020-02-15 19:43:13.558600 2020-02-15 19:43:17.609085 4 20200215_194322 0 1 2020-02-15 19:43:22.502070 2020-02-15 19:43:23.840184 4 7 rows fetched.
这里我们可以发现一个小问题；备份集无论是删除还是刚备份成功的，这里查询状态都是0。 没明白。gauss官方文档没有针对这个的详细解释。
+++插入数据进行再次增量备份然后再进行恢复测试
SQL> conn roger/Roger007@127.0.0.1:1611 connected. SQL> select * from test; A B ---------------------------------------- -------------------- 1 www.enmotech.com 1 www.killdb.com 2 rows fetched. SQL> select sysdate from sys_dummy; SYSDATE ---------------------- 2020-02-15 19:48:08 1 rows fetched. SQL> insert into test values(666,'www.modb.pro'); 1 rows affected. SQL> commit; Succeed. SQL> alter system checkpoint; Succeed. SQL>
+++增量备份
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t backup --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ --incremental-type all start run backup. Successfully backup data, backup key: 20200215_194938, 00:00:01 Performing backup completed. [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t show --all-backups --metadata-destination /tmp/gaussdb_backup start run show. | BACKUP KEY |BKP TYPE|MEDIA | START TIME | END TIME | BKP SIZE | STATUS | |===============|========|======|===================|===================|============|===========| |20200215_190737| FULL | DISK |2020-02-15 19:07:37|2020-02-15 19:07:42| 47.7 MB | DELETE | |20200215_191153| INCR | DISK |2020-02-15 19:11:53|2020-02-15 19:11:55| 120 KB | DELETE | |20200215_193143| FULL | DISK |2020-02-15 19:31:43|2020-02-15 19:31:50| 47.8 MB | DELETE | |20200215_193204| INCR | DISK |2020-02-15 19:32:04|2020-02-15 19:32:06| 68 KB | DELETE | |20200215_194313| FULL | DISK |2020-02-15 19:43:13|2020-02-15 19:43:17| 47.8 MB | SUCCESS | |20200215_194322| INCR | DISK |2020-02-15 19:43:22|2020-02-15 19:43:23| 68 KB | SUCCESS | |20200215_194938| INCR | DISK |2020-02-15 19:49:38|2020-02-15 19:49:40| 96 KB | SUCCESS | Performing show completed. [roger@mysqldb bin]$ [roger@mysqldb bin]$ ls -ltr /tmp/gaussdb_backup/roach/20200215_194938/data/ total 88 -rw------- 1 roger roger 9498 Feb 15 19:49 ctrl_0_0.bak -rw------- 1 roger roger 3845 Feb 15 19:49 data_SYSTEM_0_0.bak -rw------- 1 roger roger 9 Feb 15 19:49 data_TEMP_1_0.bak -rw------- 1 roger roger 1316 Feb 15 19:49 data_UNDO_3_1.bak -rw------- 1 roger roger 9 Feb 15 19:49 data_TEMP2_9_0.bak -rw------- 1 roger roger 9 Feb 15 19:49 data_TEMP2_UNDO_11_0.bak -rw------- 1 roger roger 16153 Feb 15 19:49 data_UNDO_3_0.bak -rw------- 1 roger roger 287 Feb 15 19:49 data_USERS_4_0.bak -rw------- 1 roger roger 9235 Feb 15 19:49 data_SYSAUX_12_0.bak -rw------- 1 roger roger 15590 Feb 15 19:49 arch_20_0.bak -rw------- 1 roger roger 5200 Feb 15 19:49 backupset
+++清空数据库文件
[roger@mysqldb gaussdata]$ rm -rf user* [roger@mysqldb gaussdata]$ rm -rf sys* [roger@mysqldb gaussdata]$ rm -rf log1 log2 log3 log4 log5 log6 [roger@mysqldb gaussdata]$ rm -rf undo [roger@mysqldb gaussdata]$ rm -rf archive_log/* [roger@mysqldb gaussdata]$ rm -rf temp* [roger@mysqldb gaussdata]$ [roger@mysqldb gaussdata]$ cd data/ [roger@mysqldb data]$ ls -ltr total 30720 -rw------- 1 roger roger 10485760 Feb 15 19:49 cntl1 -rw------- 1 roger roger 10485760 Feb 15 19:49 cntl3 -rw------- 1 roger roger 10485760 Feb 15 19:49 cntl2 [roger@mysqldb data]$ cp cntl1 cntl1_rm_before [roger@mysqldb data]$ rm -rf cntl1 cntl2 cntl3 [roger@mysqldb data]$ cd .. [roger@mysqldb gaussdata]$ ls -ltr total 4 drwx------ 2 roger roger 42 Feb 13 16:23 dbs -rw------- 1 roger roger 0 Feb 13 16:23 zengine.lck drwx------ 2 roger roger 4096 Feb 13 21:50 trc drwx------ 5 roger roger 64 Feb 14 18:58 log drwx------ 2 roger roger 62 Feb 15 11:11 cfg drwx------ 2 roger roger 85 Feb 15 12:23 protect drwx------ 2 roger roger 6 Feb 15 19:54 archive_log drwx------ 2 roger roger 28 Feb 15 19:55 data [roger@mysqldb gaussdata]$
++++开始数据库恢复操作
[roger@mysqldb gaussdata]$ zengine nomount -D /opt/gauss/gaussdata/ & [1] 96016 [roger@mysqldb gaussdata]$ starting instance(nomount) instance started [roger@mysqldb gaussdata]$ [roger@mysqldb gaussdata]$ pwd /opt/gauss/gaussdata [roger@mysqldb gaussdata]$ cd /opt/gauss/gauss100/bin/ --restore [roger@mysqldb bin]$ python Gauss_rman.py --mode single -t restore --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ --backup-key 20200215_194313 --restore-target-time '2020-02-15 19:49:40' start run restore. Successfully restore data, backup key: 20200215_194313, 00:00:46 Performing restore completed. [1]+ Done zengine nomount -D /opt/gauss/gaussdata/ (wd: /opt/gauss/gaussdata) (wd now: /opt/gauss/gauss100/bin) [roger@mysqldb bin]$ [roger@mysqldb bin]$ zsql / as sysdba -q connected. SQL> SQL> select status,open_status from v$database; STATUS OPEN_STATUS -------------------- -------------------- OPEN READ WRITE 1 rows fetched. SQL> select sysdate from sys_dummy; SYSDATE ---------------------- 2020-02-15 20:03:12 1 rows fetched. SQL> select * from roger.test; A B ---------------------------------------- -------------------- 1 www.enmotech.com 1 www.killdb.com 2 rows fetched.
我靠。。。。自动给打开了。 但是最新的一条信息丢失了。。。。
我们来看下此时的restore详细日志（roach-single.log）：
[2020-02-15 19:59:50,542] [DEBUG]: stopping dn instance. [roachinstancecontrol.py:112]
[2020-02-15 19:59:50,542] [DEBUG]: ps ux | grep -v grep | grep zengine | grep -w '-D /opt/gauss/gaussdata' |awk '{print $2}' [roachinstancecontrol.py:141]
[2020-02-15 19:59:50,560] [DEBUG]: instance process: 96016 [roachinstancecontrol.py:145]
[2020-02-15 19:59:50,560] [DEBUG]: /opt/gauss/gauss100/bin/zsql / as sysdba -D /opt/gauss/gaussdata -q -c 'shutdown abort' [roachinstancecontrol.py:121]
[2020-02-15 19:59:55,554] [DEBUG]: proc_pid_list=$(ps ux | grep /opt/gauss/gaussdata | grep -v grep | awk '{print $2}') && (if [ X"$proc_pid_list" != X"" ]; then echo "$proc_pid_list" | xargs kill -9 ; exit 0; fi) [roachinstancecontrol.py:167]
[2020-02-15 19:59:55,572] [DEBUG]: successfully stop dn instance. [roachinstancecontrol.py:127]
[2020-02-15 19:59:55,573] [DEBUG]: starting dn instance. [roachinstancecontrol.py:44]
[2020-02-15 19:59:55,573] [DEBUG]: nohup /opt/gauss/gauss100/bin/zengine nomount -D /opt/gauss/gaussdata >> /tmp/gaussdb_backup/roach/optgaussgaussdata.start 2>&1 & [roachinstancecontrol.py:58]
[2020-02-15 19:59:58,580] [DEBUG]: starting instance(nomount)
instance started
[roachinstancecontrol.py:104]
[2020-02-15 19:59:58,580] [DEBUG]: ps ux | grep -v grep | grep zengine | grep -w '-D /opt/gauss/gaussdata' |awk '{print $2}' [roachinstancecontrol.py:141]
[2020-02-15 19:59:58,596] [DEBUG]: instance process: 98967 [roachinstancecontrol.py:145]
[2020-02-15 19:59:58,596] [DEBUG]: successfully start dn instance. [roachinstancecontrol.py:72]
[2020-02-15 19:59:58,597] [DEBUG]: start restore clean stage. [roachsinglehandler.py:280]
[2020-02-15 19:59:58,600] [DEBUG]: end restore clean stage. [roachsinglehandler.py:306]
[2020-02-15 19:59:58,600] [DEBUG]: start restore data stage. [roachsinglehandler.py:315]
[2020-02-15 19:59:58,600] [DEBUG]: restore database from '/tmp/gaussdb_backup/roach/20200215_194313/data' parallelism 4 ; [roachsinglehandler.py:565]
[2020-02-15 20:00:36,445] [DEBUG]: [('Succeed.',)] [roachsinglehandler.py:575]
[2020-02-15 20:00:36,445] [DEBUG]: end restore data stage. [roachsinglehandler.py:332]
[2020-02-15 20:00:36,445] [DEBUG]: start restore recover stage. [roachsinglehandler.py:348]
[2020-02-15 20:00:36,445] [DEBUG]: recover database until time '2020-02-15 19:49:40'; [roachsinglehandler.py:565]
[2020-02-15 20:00:36,744] [DEBUG]: [('Succeed.',)] [roachsinglehandler.py:575]
[2020-02-15 20:00:36,744] [DEBUG]: end restore recover stage. [roachsinglehandler.py:359]
[2020-02-15 20:00:36,744] [DEBUG]: start restore alter stage. [roachsinglehandler.py:372]
[2020-02-15 20:00:36,745] [DEBUG]: alter database open resetlogs; [roachsinglehandler.py:565]
[2020-02-15 20:00:36,958] [DEBUG]: [('Succeed.',)] [roachsinglehandler.py:575]
[2020-02-15 20:00:36,958] [DEBUG]: end restore alter stage. [roachsinglehandler.py:387]
[2020-02-15 20:00:36,958] [INFO]: Successfully restore data, backup key: 20200215_194313, 00:00:46 [roachsinglehandler.py:127]++++继续恢复增量
[roger@mysqldb bin]$ python Gauss_rman.py --mode single -t restore --media-destination /tmp/gaussdb_backup/ --metadata-destinatio /tmp/gaussdb_backup/ --backup-key 20200215_194938 --restore-target-time '2020-02-15 19:49:41' start run restore. Successfully restore data, backup key: 20200215_194938, 00:00:49 Performing restore completed. [1]+ Done /opt/gauss/gauss100/bin/zengine nomount -D /opt/gauss/gaussdata [roger@mysqldb bin]$ [roger@mysqldb bin]$ [roger@mysqldb bin]$ zsql / as sysdba -q connected. SQL> select sysdate from sys_dummy; SYSDATE ---------------------- 2020-02-15 20:11:15 1 rows fetched. SQL> select * from roger.test; A B ---------------------------------------- -------------------- 1 www.enmotech.com 1 www.killdb.com 666 www.modb.pro 3 rows fetched.
可以看到最新最新的数据恢复出来了。 如下是增量恢复的日志信息：
[2020-02-15 20:10:04,205] [DEBUG]: stopping dn instance. [roachinstancecontrol.py:112]
[2020-02-15 20:10:04,205] [DEBUG]: ps ux | grep -v grep | grep zengine | grep -w '-D /opt/gauss/gaussdata' |awk '{print $2}' [roachinstancecontrol.py:141]
[2020-02-15 20:10:04,233] [DEBUG]: instance process: 106453 [roachinstancecontrol.py:145]
[2020-02-15 20:10:04,233] [DEBUG]: /opt/gauss/gauss100/bin/zsql / as sysdba -D /opt/gauss/gaussdata -q -c 'shutdown abort' [roachinstancecontrol.py:121]
[2020-02-15 20:10:10,314] [DEBUG]: proc_pid_list=$(ps ux | grep /opt/gauss/gaussdata | grep -v grep | awk '{print $2}') && (if [ X"$proc_pid_list" != X"" ]; then echo "$proc_pid_list" | xargs kill -9 ; exit 0; fi) [roachinstancecontrol.py:167]
[2020-02-15 20:10:10,332] [DEBUG]: successfully stop dn instance. [roachinstancecontrol.py:127]
[2020-02-15 20:10:10,334] [DEBUG]: starting dn instance. [roachinstancecontrol.py:44]
[2020-02-15 20:10:10,335] [DEBUG]: nohup /opt/gauss/gauss100/bin/zengine nomount -D /opt/gauss/gaussdata >> /tmp/gaussdb_backup/roach/optgaussgaussdata.start 2>&1 & [roachinstancecontrol.py:58]
[2020-02-15 20:10:13,340] [DEBUG]: starting instance(nomount)
instance started
[roachinstancecontrol.py:104]
[2020-02-15 20:10:13,341] [DEBUG]: ps ux | grep -v grep | grep zengine | grep -w '-D /opt/gauss/gaussdata' |awk '{print $2}' [roachinstancecontrol.py:141]
[2020-02-15 20:10:13,357] [DEBUG]: instance process: 106964 [roachinstancecontrol.py:145]
[2020-02-15 20:10:13,357] [DEBUG]: successfully start dn instance. [roachinstancecontrol.py:72]
[2020-02-15 20:10:13,357] [DEBUG]: start restore clean stage. [roachsinglehandler.py:280]
[2020-02-15 20:10:13,361] [DEBUG]: clean file: /opt/gauss/gaussdata/log1 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,370] [DEBUG]: clean file: /opt/gauss/gaussdata/log2 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,379] [DEBUG]: clean file: /opt/gauss/gaussdata/log3 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,389] [DEBUG]: clean file: /opt/gauss/gaussdata/log4 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,398] [DEBUG]: clean file: /opt/gauss/gaussdata/log5 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,408] [DEBUG]: clean file: /opt/gauss/gaussdata/log6 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,417] [DEBUG]: clean file: /opt/gauss/gaussdata/system [roachsinglehandler.py:300]
[2020-02-15 20:10:13,421] [DEBUG]: clean file: /opt/gauss/gaussdata/temp1_01 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,422] [DEBUG]: clean file: /opt/gauss/gaussdata/temp1_02 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,424] [DEBUG]: clean file: /opt/gauss/gaussdata/undo [roachsinglehandler.py:300]
[2020-02-15 20:10:13,432] [DEBUG]: clean file: /opt/gauss/gaussdata/user1 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,440] [DEBUG]: clean file: /opt/gauss/gaussdata/user2 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,446] [DEBUG]: clean file: /opt/gauss/gaussdata/user3 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,454] [DEBUG]: clean file: /opt/gauss/gaussdata/user4 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,465] [DEBUG]: clean file: /opt/gauss/gaussdata/user5 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,466] [DEBUG]: clean file: /opt/gauss/gaussdata/temp2_01 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,467] [DEBUG]: clean file: /opt/gauss/gaussdata/temp2_02 [roachsinglehandler.py:300]
[2020-02-15 20:10:13,469] [DEBUG]: clean file: /opt/gauss/gaussdata/temp2_undo [roachsinglehandler.py:300]
[2020-02-15 20:10:13,471] [DEBUG]: clean file: /opt/gauss/gaussdata/sysaux [roachsinglehandler.py:300]
[2020-02-15 20:10:13,473] [DEBUG]: end restore clean stage. [roachsinglehandler.py:306]
[2020-02-15 20:10:13,473] [DEBUG]: start restore data stage. [roachsinglehandler.py:315]
[2020-02-15 20:10:13,473] [DEBUG]: restore database from '/tmp/gaussdb_backup/roach/20200215_194938/data' parallelism 4 ; [roachsinglehandler.py:565]
[2020-02-15 20:10:53,157] [DEBUG]: [('Succeed.',)] [roachsinglehandler.py:575]
[2020-02-15 20:10:53,157] [DEBUG]: end restore data stage. [roachsinglehandler.py:332]
[2020-02-15 20:10:53,157] [DEBUG]: start restore recover stage. [roachsinglehandler.py:348]
[2020-02-15 20:10:53,158] [DEBUG]: recover database until time '2020-02-15 19:49:41'; [roachsinglehandler.py:565]
[2020-02-15 20:10:53,322] [DEBUG]: [('Succeed.',)] [roachsinglehandler.py:575]
[2020-02-15 20:10:53,323] [DEBUG]: end restore recover stage. [roachsinglehandler.py:359]
[2020-02-15 20:10:53,323] [DEBUG]: start restore alter stage. [roachsinglehandler.py:372]
[2020-02-15 20:10:53,323] [DEBUG]: alter database open resetlogs; [roachsinglehandler.py:565]
[2020-02-15 20:10:53,623] [DEBUG]: [('Succeed.',)] [roachsinglehandler.py:575]
[2020-02-15 20:10:53,623] [DEBUG]: end restore alter stage. [roachsinglehandler.py:387]
[2020-02-15 20:10:53,623] [INFO]: Successfully restore data, backup key: 20200215_194938, 00:00:49 [roachsinglehandler.py:127]我们可以看到总的来说roach备份工具还是行，python脚本。 能够完成数据库的全备/增量和恢复操作；也可以对备份进行删除和校验。但是通过前面对备份恢复测试；
我们可以得到如下几个结论：
1 每次备份后的备份记录可以在sys_backup_sets中查看，但是即使delete 删除备份后，sys_backup_sets中的信息并不清除，仍然存在；
2 增量备份的应用需要人工干预。
3 基于时间点的恢复方法，跟oracle类似。
