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

太过爱你忘了你带给我的痛 2023-05-29 09:22 8阅读 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][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
    }

[https_docs.microsoft.com_zh-cn_sql_linux_sql-server-linux-setup-tools_view_sql-server-ver15_RHEL]: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15#RHEL