数据备份脚本： mysqldump, bcp-sqlserver, imp和exp-oracle

最新推荐文章于 2022-10-12 21:47:11 发布

根哥的博客

最新推荐文章于 2022-10-12 21:47:11 发布

阅读量683

点赞数

分类专栏： linux编程-shell 文章标签： mysql数据备份 docker数据备份 oracle导入导出 sqlserver导入导出

本文链接：https://blog.csdn.net/eyeofeagle/article/details/102919672

版权

linux编程-shell 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

文章目录

a, oracle 数据备份
b, mysql数据备份
c, sqlserver备份数据(保留１周)
d, hadoop元数据备份

a, oracle 数据备份

#普通用户备份/还原
#exp test/123456@orcl  file=test.dmp #tables=test2,test1 
#imp t1/123456@orcl    file=test.dmp #tables=test2,test1   #fromuser=test touser=t1

#管理员控制用户数据备份/还原
#exp system/helowin file=/tmp/test.dmp  log=/tmp/1.log     owner=test 
#备份回原用户:   imp system/helowin file=/tmp/test.dmp full=y 
#备份回其他用户: imp system/helowin file=/tmp/test.dmp fromuser=test touser=test1 

#docker run -d --name=oracle2020_1205 -p 15210:1521 \
#-v oracle_data_2020_1205:/home/oracle/app/oracle/oradata \
#-v oracle_recovery_2020_1205:/home/oracle/app/oracle/flash_recovery_area \
#--restart=always registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

######################
#1,容器中添加备份脚本
[oracle@97fbea6c61b3 /]$ cat /tmp/a.sh
back_dir=/home/oracle/app/oracle/oradata/backup
suffix=$(date +%Y_%m_%d)

source ~/.bash_profile
sqlplus -S / as sysdba > /tmp/oracle_users.txt <<EOF
  select distinct username from dba_users where username not in (
  'XS\$NULL','OWBSYS_AUDIT','MDSYS','XS','DIP'
  ,'SPATIAL_WFS_ADMIN_USR','MGMT_VIEW','OUTLN','FLOWS_FILES','CTXSYS'
  ,'OWBSYS','OLAPSYS','HR','SPATIAL_CSW_ADMIN_USR','EXFSYS'
  ,'APEX_030200','SCOTT','ORACLE_OCM','DBSNMP','SYSMAN'
  ,'ORDSYS','ORDPLUGINS','PM','MDDATA','SH'
  ,'OE','APEX_PUBLIC_USER','APPQOSSYS','ORDDATA','XDB'
  ,'BI','IX','SYS','WMSYS','ANONYMOUS','SI_INFORMTN_SCHEMA' );
EOF

#remain 7 days backup
del_old_bak(){
  param_dir=$1
  #
  cd $param_dir
  filecount=$( ls |wc -l)
  [ ! $filecount -gt 7 ] && continue
  #
  oldbak=$(ls -tr |head -1)
  echo "del old backup $oldbak remain 7 days..."
  rm -f $oldbak
}

#avoid error where exp system user: insufficient shm-object space
echo "alter system set JAVA_JIT_ENABLED= FALSE scope = both;  "    |sqlplus / as sysdba
for i in $(cat /tmp/oracle_users.txt |awk 'NR >3 {print}')
do
   [ -z $i ] && continue
   echo $i;
   [ ! -d  $back_dir/$i ]      && mkdir  -p  $back_dir/$i
   [ ! -d  $back_dir/log/$i ]  && mkdir  -p  $back_dir/log/$i
   exp system/helowin file=$back_dir/$i/$suffix  log=$back_dir/log/${i}/${suffix}.log  owner=$i

   #del old backup
   [ ! $? -eq 0 ] && continue
   del_old_bak $back_dir/$i
   del_old_bak $back_dir/log/$i
done



######################
#2，宿主机定时备份
[root@docker ~]# crontab -l
1 1 * * * /usr/local/bin/oracle_dump.sh

[root@docker ~]# cat /usr/local/bin/oracle_dump.sh
docker exec  oracle2020_1205  bash -c " sh /tmp/a.sh" >> /var/log/cron 2>&1

b, mysql数据备份

需求：docker安装的mysql, 需要数据备份（保留最近7天）

# mysql 导出导入：
# 导出：mysqldump -uroot -p123456 --master-data --single-transaction --databases hive hue scm > /mysql_dmp.sql
# 导入：mysql -uroot -p123456 < /mysql_dmp.sql
back_dir=/backup/mysql
suffix=$(date +%Y_%m_%d)

[ ! -d  $back_dir ]  && mkdir  -p  $back_dir
del_old_bak(){
  param_dir=$1
  #
  cd $param_dir
  filecount=$( ls |wc -l)
  [ ! $filecount -gt 7 ] && exit 0
  #
  oldbak=$(ls -tr |head -1)
  echo "del old backup $oldbak remain 7 days..."
  rm -f $oldbak
}


#backup data
mysqldump   --databases scm hive hue oozie  |gzip  > $back_dir/mysql.$suffix.gz
[ $? -eq 0 ] && del_old_bak $back_dir

c, sqlserver备份数据(保留１周)

backup database 数据库名 to disk=“1.bak”
restore database 数据库名 from disk=“1.bak” with REPLACE

1> backup database master to disk='/tmp/1.bak'
2> go
Processed 456 pages for database 'master', file 'master' on file 1.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 459 pages in 0.027 seconds (132.812 MB/sec).

[root@hadoop01 ~]# head /tmp/1.bak
TAPE▒▒▒ ,^▒▒ +Microsoft SQL ServerRAID....

linux安装sqlserver客户端工具sqlcmd: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15#RHEL

# sqlserver 导入导出：
#导出：bcp per out ${bak_file_totname}.bak.csv -d master -S win7  -Usa -P 123456   -t, -w 
#导入：bcp per in ${bak_file_totname}.bak.csv -d master -Slocalhost -Usa -P123456 -t, -w 

#1, 安装sqlcmd
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
sudo yum remove -y mssql-tools unixODBC-utf16-devel
sudo yum install -y  mssql-tools unixODBC-utf16-devel
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc


#2, 测试连接
sqlcmd -b -S sqlserver主机名  -U 用户名 -P 密码  -Q "select * from 库名.表名" #q: 表示与用户交互的模式，阻塞直到手动退出



#3, 定时备份脚本
[root@docker bin]#  cat sqlserver_bak.sh 
back_dir=/backup/sqlserver
suffix=$(date +%Y_%m_%d_%s)

[ ! -d  $back_dir ]  && mkdir  -p  $back_dir
del_old_bak(){
  param_dir=$1
  #
  cd $param_dir
  filecount=$( ls |wc -l)
  [ ! $filecount -gt 5 ] && continue
  #
  oldbak=$(ls -tr |head -1)
  echo "del old backup $oldbak remain 7 days..."
  rm -f $oldbak
}
###################backup data###################
#a,get all database name
sqlcmd -b -S 192.168.56.1 -U sa -P'123456' -d test \
    -Q "SELECT name FROM  sysdatabases WHERE name NOT IN (  'tempdb','model',  'msdb', 'ReportServer\$MSSQL', 'ReportServer\$MSSQLTempDB')" -W \
         |awk 'NR>2{print}' |grep -Ev '\(.*\)|^$' > /tmp/sqlserver.db

#b,get all table names in all db
>/tmp/sqlserver.table
for line in $(cat /tmp/sqlserver.db)
do
   res=$(echo SELECT \'"$line."\' "+" name FROM $line..sysobjects Where xtype=\'U\' )
   sqlcmd -b -S 192.168.56.1 -U sa -P'123456' -d test -Q "$res" -W  \
         |awk 'NR>2{print}' |grep -Ev '\(.*\)|^$' >> /tmp/sqlserver.table
done

#c,backup all tables
for db in $(cat /tmp/sqlserver.db)
do
  #bak table schema
  [ ! -d $back_dir/$db/schemas ] && mkdir -p $back_dir/$db/schemas
  sqlcmd -b -S 192.168.56.1 -U sa -P'123456' -Q "use $db"
  sqlcmd -b -S 192.168.56.1 -U sa -P'123456' -i /tmp/a.sql  -W -o $back_dir/$db/schemas/all_tab.$suffix.txt
  [ $? -eq 0 ] && del_old_bak $back_dir/$db/schemas
done

while read line
do
  tab=${line#*.}
  db=${line%.*}
  [ ! -d $back_dir/$db/$tab ] && mkdir -p $back_dir/$db/$tab
  back_file_name=$back_dir/$db/$tab/$suffix.csv
  bcp  $tab  out $back_file_name  -S 192.168.56.1  -Usa -P '123456'  -d  $db   -t, -w
  gzip $back_file_name
  #del old backups
  [ $? -eq 0 ] && del_old_bak $back_dir/$db/$tab
done </tmp/sqlserver.table

获取sqlserver表结构

[root@docker ~]# cat /tmp/a.sql.2
SELECT  CASE WHEN col.colorder = 1 THEN obj.name ELSE ''END AS 表名,
        col.colorder AS 序号 ,
        col.name AS 列名 ,
        ISNULL(ep.[value], '') AS 列说明 ,
        t.name AS 数据类型 ,
        col.length AS 长度 ,
        CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '1'
             ELSE ''
        END AS 标识 ,
        CASE WHEN EXISTS ( SELECT   1
                           FROM     dbo.sysindexes si
                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                              AND si.indid = sik.indid
                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                              AND sc.colid = sik.colid
                                    INNER JOIN dbo.sysobjects so ON so.name = si.name
                                                              AND so.xtype = 'PK'
                           WHERE    sc.id = col.id
                                    AND sc.colid = col.colid ) THEN 'yes'
             ELSE ''
        END AS 主键 ,
        CASE WHEN col.isnullable != 1 THEN 'NOT NULL' else ''
        END AS 允许空 ,
        ISNULL(comm.text, '') AS 默认值
FROM    dbo.syscolumns col
        LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
        inner JOIN dbo.sysobjects obj ON col.id = obj.id
                                         AND obj.xtype = 'U'
                                         AND obj.status >= 0
        LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
        LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                      AND col.colid = ep.minor_id
                                                      AND ep.name = 'MS_Description'
        LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                                                         AND epTwo.minor_id = 0
                                                         AND epTwo.name = 'MS_Description'
WHERE 1 =1 --obj.name = 'Users'--表名
ORDER BY obj.name ,col.colorder ;

d, hadoop元数据备份

# hadoop元数据备份 
hadoop_meta_bak(){
		#nn节点备份数据
		cp /mp/dfs/nn/current/VERSION 　 $dump_dir/$month/nn-current-VERSION.txt
		
		#dn节点备份数据
		for dn in hadoop0{2,3,4,5}
		do
		　    echo " ....... $dn　backup ............."
			  	scp $dn:/tmp/dfs/dn/current/VERSION   $dump_dir/$month/$dn-dn-current-VERSION.txt
			  	scp $dn:/tmp/dfs/dn/current/BP-*/current/VERSION    $dump_dir/$month/$dn-dn-current_BP_current_VERSION.txt
		done
		
		#文件归档
		tar  -cvf  $dump_dir/$month/$day.dump.sql   $dump_dir/$month/*.txt  ; rm -f *.txt
}