MySQL数据库备份恢复实施报告-v1.0

1、* MySQL数据库备份恢复报告Veritas Consulting ServicesGreater China1 文档信息1.1 版本*-MySQL数据库备份恢复实施报告.docVeritas 大中国区顾问服务1.02020-07-01汪善平1.2 相关的文件 1.3 文档更新记录2020-07-01文档创建汪善平1.02 目录1文档信息21.1版本21.2相关的文件21.3文档更新记录22目录33文档说明44环境说明55软件安装65.1NBU客户端软件安装65.2安装MEB插件96备份配置116.1创建备份脚本116.1.1MySQL备份说明116.1.2备份脚本116.2创建备份策略21

2、7恢复步骤377.1准备工作377.2恢复说明377.3恢复步骤373 文档说明此文档说明使用Veritas Netbackup通过Oracle MySQL Enterprise Backup来对MySQL数据库进行备份恢复的步骤；此文档仅供*使用；4 环境说明1) 备份系统：主机名IP地址操作系统角色NBU-PRIMARY1Red Hat Enterprise Linux Server release 6.1 NBU Master Server主机NBU-PRIMARY1Red Hat Enterprise Linux Server release 6.1 NBU Master Server

3、备机NBUMASTERRed Hat Enterprise Linux Server release 6.1 NBU Master ServerNBU-QUCHONGRed Hat Enterprise Linux Server release 6.1 NBU Media ServerNBU-QUCHONG2Red Hat Enterprise Linux Server release 6.1 NBU Media Server2) 备份软件：Veritas Netbackup Enterprise Server 3) MySQL 服务器：主机名IP地址操作系统角色yhzd-db3

4、Red Hat Enterprise Linux Server release 6.5MySQL 服务器yhzd1Red Hat Enterprise Linux Server release 6.5MySQL 恢复测试服务器4) MySQL版本：mysql Ver 14.14 Distrib 5.6.22, for Linux (x86_64) using EditLine wrapper5 软件安装5.1 NBU客户端软件安装1) 在NBU Master Server，Media Server的/etc/hosts中添加MySQL服务器的主机名解析：#mysql db192.168.232

5、.51 yhzd-db12 yhzd-db23 yhzd-db3 2) 在MySQL服务器的/etc/hosts中添加NBU Master Server，Media Server的主机名解析：1 NBU-PRIMARY12 NBU-PRIMARY26 NBU-QUCHONG27 NBU-QUCHONG3 NBUVIP-em1 NBUMASTER3) 登陆NBU Master Serve

6、r，进行客户端软件推送：rootNBU-PRIMARY1 RedHat2.6.18# ./sftp_to_client yhzd-db3 rootConnecting to 3.root3s password: sftp completed successfully.The root user on 3 must now execute the commandsh /tmp/bp.10261/client_config -L. The optional argument, -L,is used to avoid mo

7、dification of the clients current bp.conf file.rootNBU-PRIMARY1 RedHat2.6.18#4) 推送完成后，登陆客户端yhzd-db3进行NBU客户端软件安装：rootyhzd-db3 tmp# cd bp.10261/rootyhzd-db3 bp.10261# lsbin_net.tar client_config openvrootyhzd-db3 bp.10261# ./client_config Checking for required system conditions.Checking for recommende

8、d system conditions.ok nb_7604_hotfix_auditor: No potential for regression of hotfixes or EEBs was detected.Blocksize = 20 records././bp_servers./bp_client_nameSaving client binaries for Linux/RedHat2.6.18.Installing PBX.Please wait while installation is in progress.Installation completed Successful

9、lyInstallation log located here: /var/tmp/installpbx-1183-052416104325.logUnpacking SYMCnbclt package.Checking for pre-existing SYMCnbclt package.Installing SYMCnbclt package.Installation of SYMCnbclt was successful.More details regarding SYMCnbclt can be found in file/tmp/install_cltpkg_trace.1033

10、on yhzd-db3.Terminating NetBackup and/or Media Manager processes.Checking network connections.bp.conf: IP_ADDRESS_FAMILY = AF_INET: default value, no update neededNo xinetd process found.Installing NB-Java.Unpacking SYMCnbjava package.Checking for pre-existing SYMCnbjava package.Installing SYMCnbjav

11、a package.Installation of SYMCnbjava was successful.More details regarding SYMCnbjava can be found in file/tmp/install_javapkg_trace.2272 on yhzd-db3.Unpacking SYMCnbjre package.Checking for pre-existing SYMCnbjre package.Installing SYMCnbjre package.Installation of SYMCnbjre was successful.More det

12、ails regarding SYMCnbjre can be found in file/tmp/install_jrepkg_trace.2272 on yhzd-db3.Directory /usr/openv/logs does not exist.No log files to migrate.Checking for SYMCpddea package.Installing PDDE agent package (/tmp/bp.10261/openv/netbackup/client/Linux/RedHat2.6.18/pddeagent.tar.gz).Extracting

13、package SYMCpddea in /tmp/pdde_pkg_dir_2422. Package SYMCpddea extracted to /tmp/pdde_pkg_dir_2422.Installing package SYMCpddea. Package SYMCpddea installed.PDDE install finished successfully.Version now installed: 8.0004.0014.0917Full PDDE installation log saved to: /var/log/puredisk/2016-05-24_10:

14、48-pdde-install.logThe following automatic startup and shutdown scripts (respectively)have been installed. They will cause the NetBackup daemons to beautomatically shut down and restarted each time the system boots./etc/rc.d/rc2.d/S77netbackup/etc/rc.d/rc3.d/S77netbackup/etc/rc.d/rc5.d/S77netbackup/

15、etc/rc.d/rc0.d/K01netbackup/etc/rc.d/rc1.d/K01netbackup/etc/rc.d/rc6.d/K01netbackupSuccessfully updated the session cache parameters.Starting vnetd.Starting bpcd.Starting nbftclnt.Starting nbdisco.Starting mtstrmd.Starting bmrbd.Installation of Java LiveUpdate agent succeeded. Refer to file/tmp/JLU-

16、Log/JavaLiveUpdate-Install.log on yhzd-db3for installation details.Checking LiveUpdate registration for the following products: CLT This may take a few minutes.Product CLT is installed and will be registered.Updating LiveUpdate registration now.this may take some time.Client install complete.5) 等待客户

17、端软件安装完成；5.2 安装MEB插件Netbackup通过Oracle提供的MEB（MySQL Enterprise Backup）对MySQL数据库进行备份，在MySQL服务器上需要安装MEB插件；1) 下载MEB插件并上传到MySQL服务器上；2) 安装MEB插件：rootyhzd-db3 tmp# rpm -ivh meb-3.12.2-el6.x86_64.rpm warning: meb-3.12.2-el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing. # 100% 1:meb

18、 # 100%rootyhzd-db3 tmp# rpm -qa | grep -i mebmeb-3.12.2-1.x86_646 备份配置6.1 创建备份脚本6.1.1 MySQL备份说明1) 使用MEB备份MySQL数据库分为全备份和增量备份；2) 全备份是一份完整的备份数据；3) 增量备份通过参数：-incremental-base来控制此次增量备份依据什么时候的数据来进行：history:backup， 依据上一次成功的备份中的lsndir:， 依据上一次成功备份的目录中的meta data文件中的lsn在这里我们通过dir: 来实现每次增量备份都使用上一次成功的全备份中的meta

19、data文件的lsn进行增量，从而达到累计增量备份的效果；4) 增量备份参数：-incremental-with-redo-log-only此参数使得增量备份时通过MySQL的redo log来判断此次增量部分数据；由于redo要求log是循环使用的，因此使用此参数要求redo log的大小必须能容纳上次备份到此次备份之间的数据变化；6.1.2 备份脚本1) 在MySQL服务器上创建以下目录：# mkdir -p /usr/openv/scripts/# mkdir -p /usr/openv/scripts/logs/2) 创建全备份脚本：#!/bin/sh#Copyright:#mysql

20、backup executable file pathMYSQLBACKUP=/opt/mysql/meb-3.12/bin/mysqlbackup#the Mysql portPort=3306#the Mysql protocolProtocol=tcp#the Mysql username and password used for backupUSER=temsbackuppassword=backuptems#Netbackup Master ServerNB_ORA_SERV=NBUMASTER#MySqlDB ServerNB_ORA_CLIENT=yhzd-db3#NetBac

21、kup MySQL Database Backup Policy NameNB_ORA_POLICY=yhzddb3_mysql_fullFile_Backup=yhzddb3_mysql_metadataCMD=/usr/openv/netbackup/bin/bpbackup#the temporary directory used by mysqlbackupbackup_dir_home=/usr/openv/scripts/metadatafull_bkp_home=$backup_dir_home/fullincr_bkp_home=$backup_dir_home/incrBkp

22、Type=Full#BkpStatus=0#LogDir=/usr/openv/scripts/logs#initialize() CURDATE=date +%Y%m%d_%H%M%S dLIB=/usr/openv/netbackup/bin/libobk.so64 dBACKUPIMAGENAME=sbt:bkNB$BkpType$CURDATE dOUTLOG=$LogDir/$NB_ORA_POLICY-$CURDATE.log dERRORLOG=$LogDir/$NB_ORA_POLICY-$CURDATE-error.log dCLEANUP=0mkdir -p $LogDir

23、# mkdir -p $backup_dir_homecleanup() CURDATE=date +%Y%m%d_%H%M%S echo $dOUTLOG echo - $dOUTLOG if $BkpStatus -ne 0 ; then echo MYSQL BACKUP FAILED at $CURDATE, PLEASE CHECK $dOUTLOG # rm -rf $backup_dir_home/* else echo MYSQL Backup Successful at $CURDATE $dOUTLOG fi echo - $dOUTLOG echo $dOUTLOGdo_

24、fullbackup()rm -rf $full_bkp_homemkdir -p $full_bkp_home echo $dOUTLOG echo - $dOUTLOG echo Start Full Backup at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG echo Execute command: $dOUTLOG echo $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=* -backup-dir=$full_bkp_home -sbt-li

25、b-path=$dLIB -sbt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY -backup-image=$dBACKUPIMAGENAME backup-to-image $dOUTLOG $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=$password -backup-dir=$full_bkp_home -sbt-lib-path=$dLIB -sbt-e

26、nvironment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY -backup-image=$dBACKUPIMAGENAME backup-to-image 2$dOUTLOG echo $dOUTLOG echo - $dOUTLOG echo End Full Backup at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG cat $dOUTLOG | grep mysqlbackup completed OK |

27、 grep -v print BkpStatus=$?do_conf_backup() if $BkpStatus -eq 0 ; then echo $dOUTLOG echo - $dOUTLOG echo Backup Metadata files at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG echo The Metadata files are: $dOUTLOG ls -lR $backup_dir_home $dOUTLOG echo $dOUTLOG echo Execute command: $dOUTLOG echo

28、$CMD -p $File_Backup $backup_dir_home $dOUTLOG $CMD -p $File_Backup $backup_dir_home echo $dOUTLOG echo - $dOUTLOG echo End Metadata files backup at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG fido_validate() echo $dOUTLOG echo - $dOUTLOG echo Start Validate at $CURDATE $dOUTLOG echo - $dOUTLOG

29、echo $dOUTLOG echo Execute command: $dOUTLOG echo $MYSQLBACKUP -backup-image=$dBACKUPIMAGENAME -sbt-lib-path=$dLIB -sbt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY validate $dOUTLOG $MYSQLBACKUP -backup-image=$dBACKUPIMAGENAME -sbt-lib-path=$dLIB -s

30、bt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY validate 2$dOUTLOG echo $dOUTLOG echo - $dOUTLOG echo End Validate at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOGdo_incremental_with_redo_log_only() rm -rf $incr_bkp_home mkdir -p $incr_bkp_home ech

31、o $dOUTLOG echo - $dOUTLOG echo Start redo log only at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG echo Execute command: $dOUTLOG echo $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=password -incremental-with-redo-log-only -incremental-base=history:last_backup -sbt-lib-path=$

32、dLIB -sbt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY -backup-dir=$incr_bkp_home -backup-image=$dBACKUPIMAGENAME backup-to-image $dOUTLOG $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=$password -incremental-with-redo-log-only -i

33、ncremental-base=history:last_backup -sbt-lib-path=$dLIB -sbt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY -backup-dir=$incr_bkp_home -backup-image=$dBACKUPIMAGENAME backup-to-image 2 $dOUTLOG echo $dOUTLOG echo - $dOUTLOG echo End redo log only at $C

34、URDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG cat $dOUTLOG | grep mysqlbackup completed OK | grep -v print BkpStatus=$?do_incremental_backup() rm -rf $incr_bkp_home mkdir -p $incr_bkp_home echo $dOUTLOG echo - $dOUTLOG echo Start incremental backup at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG

35、echo Execute command: $dOUTLOG echo $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=* -incremental -incremental-base=dir:$full_bkp_home -sbt-lib-path=$dLIB -sbt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_POLICY -backup-dir=$incr_bkp_hom

36、e -backup-image=$dBACKUPIMAGENAME backup-to-image $dOUTLOG $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=$password -incremental -incremental-base=dir:$full_bkp_home -sbt-lib-path=$dLIB -sbt-environment=NB_ORA_SERV=$NB_ORA_SERV,NB_ORA_CLIENT=$NB_ORA_CLIENT,NB_ORA_POLICY=$NB_ORA_P

37、OLICY -backup-dir=$incr_bkp_home -backup-image=$dBACKUPIMAGENAME backup-to-image 2 $dOUTLOG echo $dOUTLOG echo - $dOUTLOG echo End Incremental Backup at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG cat $dOUTLOG | grep mysqlbackup completed OK | grep -v print BkpStatus=$?initializedo_fullbackup#do

38、_incremental_with_redo_log_onlydo_conf_backup#do_validate#cleanupexit $BkpStatus3) 创建增量备份脚本：#!/bin/sh#Copyright:#mysqlbackup executable file pathMYSQLBACKUP=/opt/mysql/meb-3.12/bin/mysqlbackup#the Mysql portPort=3306#the Mysql protocolProtocol=tcp#the Mysql username and password used for backupUSER=

39、temsbackuppassword=backuptems#Netbackup Master ServerNB_ORA_SERV=NBUMASTER#MySqlDB ServerNB_ORA_CLIENT=yhzd-db3#NetBackup MySQL Database Backup Policy NameNB_ORA_POLICY=yhzddb3_mysql_incrFile_Backup=yhzddb3_mysql_metadataCMD=/usr/openv/netbackup/bin/bpbackup#the temporary directory used by mysqlback

40、upbackup_dir_home=/usr/openv/scripts/metadatafull_bkp_home=$backup_dir_home/fullincr_bkp_home=$backup_dir_home/incrBkpType=Incr#BkpStatus=0#LogDir=/usr/openv/scripts/logs#initialize() CURDATE=date +%Y%m%d_%H%M%S dLIB=/usr/openv/netbackup/bin/libobk.so64 dBACKUPIMAGENAME=sbt:bkNB$BkpType$CURDATE dOUT

41、LOG=$LogDir/$NB_ORA_POLICY-$CURDATE.log dERRORLOG=$LogDir/$NB_ORA_POLICY-$CURDATE-error.log dCLEANUP=0mkdir -p $LogDir# mkdir -p $backup_dir_homecleanup() CURDATE=date +%Y%m%d_%H%M%S echo $dOUTLOG echo - $dOUTLOG if $BkpStatus -ne 0 ; then echo MYSQL BACKUP FAILED at $CURDATE, PLEASE CHECK $dOUTLOG

42、# rm -rf $backup_dir_home/* else echo MYSQL Backup Successful at $CURDATE $dOUTLOG fi echo - $dOUTLOG echo $dOUTLOGdo_fullbackup()rm -rf $full_bkp_homemkdir -p $full_bkp_home echo $dOUTLOG echo - $dOUTLOG echo Start Full Backup at $CURDATE $dOUTLOG echo - $dOUTLOG echo $dOUTLOG echo Execute command: $dOUTLOG echo $MYSQLBACKUP -port=$Port -protocol=$Protocol -user=$USER -password=* -backup-d