PG、MySQL、SQLServer、Oracle、DM、Highgo、Oscar、KingBase、GBase数据库的备份还原方案
PG&HG:
create user wangshn01 with password 'Wsn@Gwx&Za2019'
create database wangshn01 owner wangshn01
grant all PRIVILEGES on DATABASE wangshn01 to wangshn01
#备份
./pg_dump -c -d wangshn01 -h ip -p 5432 -U wangshn01 > /home/backup.sql
#还原
./psql -U postgres -h ip -p 5432 -d wangshn01 -f /home/backup.sql
SQLServer:
#备份
//创建数据库备份文件夹
mkdir -pv /usr/local/databackup
//给mussql授予/usr/local/databackup文件夹的权限
chown -R mssql:mssql /usr/local/databackup/
//登录数据库
sqlcmd -S 127.0.0.1 -U sa
//备份数据库到指定路径
1> backup database Test to disk='/usr/local/databackup/Test0411.bak'
2> go
#还原
// 登录数据库
sqlcmd -S 127.0.0.1 -U sa
// 查出备份文件的逻辑文件名(很重要!!!不然会报错)
RESTORE FILELISTONLY FROM DISK = '/usr/local/databackup/Test0411.bak'
// 这个时候会显示两条数据,可能在Linux下数据会很乱,你找到里面的第一列LogicalName对应的值,我这里一个为standard,另一个为standard_log,standard是数据文件逻辑名,standard_log是日志文件逻辑名
// 然后进行还原
1> RESTORE DATABASE TestDB FROM DISK = '/usr/local/databackup/Test0414.bak'
2> WITH MOVE 'standard' TO '/var/opt/mssql/data/TestDB.mdf',
3> MOVE 'standard_log' TO '/var/opt/mssql/data/TestDB_Log.ldf'
4> go
Oracle:
#设置dir
create or replace directory dir as '/u01/dmp'
#备份
expdp ‘test01/“aaaaaa”’@ip/servicename directory=dir schemas=test dumpfile=20210525.dump logfile=20210525.log
#还原
impdp test02/aaaaaa@ip/servicename directory=dir dumpfile=20210525.DUMP logfile=20210525.log remap_tablespace=test01:test02 remap_schema=test01:test02 table_exists_action=replace
DM:
#备份
./dexp wangshn01/\"Test1027?\!\"@ip file=/home/test01.dmp log=/home/daochu.txt
#还原
./dimp wangshn01/\"Test1027?\!\"@ip fromuser=wangshn01 touser=wangshn01 table_exists_action=replace file=/home/wangshn01.dmp log=/home/daoru.txt
MySQL:
#备份
mysqldump -hip -uroot -p --skip-lock-tables wangshn01 > /home/wangshn01.sql
#还原
mysql -hip -uroot -p wangshn01 < /home/wangshn01.sql
Oscar:
#备份
./osrexp -usysdba/"password" -hip -p2003 -dOSRDB level=schema file=/home/wangshn01.osr log=/home/wangshn01.log schema=wangshn01
#还原
./osrimp -usysdba/"passwd" -hip -p2003 -dosrdb level=schema file=/home/wangshn01.osr log=/home/wangshn01.log schema=wangshn02 fromuser=wangshn01 touser=wangshn02 ignore=true
KB:
#备份
./sys_dump -h 10.110.81.141 -U igix20220610v8r6 -b -d igix20220610v8r6 -f /opt/Kingbase/igix20220610v8r6.sql -c
#还原
./ksql -h 10.110.81.141 -U wangshn02 -d wangshn02 -f /opt/Kingbase/igix20220610v8r6.sql
#还原 未测试
#./sys_restore -h 10.110.XX.XX -U wangshn01 -f /opt/Kingbase/igix20220610v8r6.sql -c -E -g igix20220610v8r6 -G wangshn01
或者直接从另外一个库还原 未测试
#./sys_restore -h 10.110.XX.XX -U wangshn01 -d igix20220610v8r6 -c -E -g igix20220610v8r6 -G wangshn01
GBase:
#备份
gs_dump -U gbase -W gbasetest@10.110.XX.XX -f backup/MPPDB_backup.sql -p 15432
或者(所有实例全量恢复???待确认) gs_dumpall -f backup/bkp2.sql -p 15432
gs_dump对应还原
gs_restore -W gbasetest@10.110.XX.XX backup/MPPDB_backup.dmp -p 15400 -d postgres
gs_dumpall 对应还原
gsql -d gbasetest -p 15432 -W gbasetest@10.110.XX.XX -f /home/omm/test/MPPDB_backup.sql
highgoV9创建用户及实例记录:
CREATE DATABASE passport_auth_test;
CREATE USER passport_auth_test password 'Wsn@Gwx&Za2019';
GRANT ALL ON database passport_auth_test TO passport_auth_test;