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

******
MySQL数据库备份恢复报告
Veritas Consulting Services
Greater China

1 文档信息
1.1 版本
Document Title
******-MySQL数据库备份恢复实施报告.doc
Subject
Veritas 大中国区顾问服务
Version
1.0
Date
2024-10-03
Author
汪善平
Filename

Comments

1.2 相关的文件
Id
Title
Organisation
Author
Version

1.3 文档更新记录
Date
Detail
Author
Version
2024-10-03
文档创建
汪善平
1.0

2 目录
1 文档信息 2
1.1 版本 2
1.2 相关的文件 2
1.3 文档更新记录 2
2 目录 3
3 文档说明 4
4 环境说明 5
5 软件安装 6
5.1 NBU客户端软件安装 6
5.2 安装MEB插件 9
6 备份配置 11
6.1 创建备份脚本 11
6.1.1 MySQL备份说明 11
6.1.2 备份脚本 11
6.2 创建备份策略 21
7 恢复步骤 37
7.1 准备工作 37
7.2 恢复说明 37
7.3 恢复步骤 37

3 文档说明
此文档说明使用Veritas Netbackup通过Oracle MySQL Enterprise Backup来对MySQL数据库进行备份恢复的步骤；
此文档仅供******使用；

4 环境说明
1) 备份系统：
主机名
IP地址
操作系统
角色
NBU-PRIMARY1

Red Hat Enterprise Linux Server release 6.1
NBU Master Server主机
NBU-PRIMARY1

Red Hat Enterprise Linux Server release 6.1
NBU Master Server备机
NBUMASTER

Red Hat Enterprise Linux Server release 6.1
NBU Master Server
NBU-QUCHONG

Red Hat Enterprise Linux Server release 6.1
NBU Media Server
NBU-QUCHONG2

Red Hat Enterprise Linux Server release 6.1
NBU Media Server

2) 备份软件：
Veritas Netbackup Enterprise Server 7.6.0.4

3) MySQL 服务器：
主机名
IP地址
操作系统
角色
yhzd-db3

Red Hat Enterprise Linux Server release 6.5
MySQL 服务器
yhzd1

Red Hat Enterprise Linux Server release 6.5
MySQL 恢复测试服务器

4) MySQL版本：
mysql Ver 14.14 Distrib 5.6.22, for Linux (x86_64) using EditLine wrapper

5 软件安装
5.1 NBU客户端软件安装
1) 在NBU Master Server，Media Server的/etc/hosts中添加MySQL服务器的主机名解析：

#mysql db

192.168.232.51 yhzd-db1

192.168.232.52 yhzd-db2
192.168.232.53 yhzd-db3 yhzd-

2) 在MySQL服务器的/etc/hosts中添加NBU Master Server，Media Server的主机名解析：

192.168.238.21 NBU-PRIMARY1
192.168.238.22 NBU-PRIMARY2
192.168.238.16 NBU-QUCHONG2
192.168.238.17 NBU-QUCHONG
192.168.238.23 NBUVIP-em1 NBUMASTER

3) 登陆NBU Master Server，进行客户端软件推送：

[root@NBU-PRIMARY1 RedHat2.6.18]# ./sftp_to_client yhzd-db3 root
Connecting to 192.168.232.53...
root@192.168.232.53's password:

sftp completed successfully.

The root user on 192.168.232.53 must now execute the command
"sh /tmp/bp.10261/client_config [-L]". The optional argument, "-L",
is used to avoid modification of the client's current bp.conf file.
[root@NBU-PRIMARY1 RedHat2.6.18]#

4) 推送完成后，登陆客户端yhzd-db3进行NBU客户端软件安装：

root@yhzd-db3 tmp]# cd bp.10261/
[root@yhzd-db3 bp.10261]# ls
bin_net.tar client_config openv
[root@yhzd-db3 bp.10261]# ./client_config

Checking for required system conditions...

Checking for recommended system conditions...

ok nb_7604_hotfix_auditor: No potential for regression of hotfixes or EEBs was detected.
Blocksize = 20 records
./
./bp_servers
./bp_client_name

Saving client binaries for Linux/RedHat2.6.18.

Installing PBX...
Please wait while installation is in progress...
Installation completed Successfully
Installation log located here: /var/tmp/installpbx-1183-052416104325.log

Unpacking 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 on yhzd-db3.

Terminating NetBackup and/or Media Manager processes.

Checking network connections.
bp.conf: IP_ADDRESS_FAMILY = AF_INET: default value, no update needed

No [x]inetd process found.
Installing NB-Java.

Unpacking SYMCnbjava package.
Checking for pre-existing SYMCnbjava package.
Installing SYMCnbjava 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 details 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 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.0917
Full PDDE installation log saved to: /var/log/puredisk/2016-05-24_10:48-pdde-install.log

The following automatic startup and shutdown scripts (respectively)
have been installed. They will cause the NetBackup daemons to be
automatically 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
/etc/rc.d/rc0.d/K01netbackup
/etc/rc.d/rc1.d/K01netbackup
/etc/rc.d/rc6.d/K01netbackup

Successfully 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-Log/JavaLiveUpdate-Install.log on yhzd-db3
for 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) 等待客户端软件安装完成；
5.2 安装MEB插件
Netbackup通过Oracle提供的MEB（MySQL Enterprise Backup）对MySQL数据库进行备份，在MySQL服务器上需要安装MEB插件；
1) 下载MEB插件并上传到MySQL服务器上；
2) 安装MEB插件：

[root@yhzd-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: NOKEY
Preparing... ########################################### [100%]
1:meb ########################################### [100%]
[root@yhzd-db3 tmp]# rpm -qa | grep -i meb
meb-3.12.2-1.x86_64

6 备份配置
6.1 创建备份脚本
6.1.1 MySQL备份说明
1) 使用MEB备份MySQL数据库分为全备份和增量备份；
2) 全备份是一份完整的备份数据；
3) 增量备份通过参数：--incremental-base来控制此次增量备份依据什么时候的数据来进行：
history:backup， 依据上一次成功的备份中的lsn
dir:<backup dir>， 依据上一次成功备份的目录中的meta data文件中的lsn

在这里我们通过dir:<full backup dir> 来实现每次增量备份都使用上一次成功的全备份中的meta 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:
#

#mysqlbackup executable file path
MYSQLBACKUP=/opt/mysql/meb-3.12/bin/mysqlbackup
#the Mysql port
Port=3306
#the Mysql protocol
Protocol=tcp
#the Mysql username and password used for backup
USER=temsbackup
password=backuptems
#Netbackup Master Server
NB_ORA_SERV=NBUMASTER
#MySqlDB Server
NB_ORA_CLIENT=yhzd-db3
#NetBackup MySQL Database Backup Policy Name
NB_ORA_POLICY=yhzddb3_mysql_full
File_Backup=yhzddb3_mysql_metadata
CMD=/usr/openv/netbackup/bin/bpbackup
#the temporary directory used by mysqlbackup
backup_dir_home=/usr/openv/scripts/metadata
full_bkp_home=$backup_dir_home/full
incr_bkp_home=$backup_dir_home/incr
BkpType="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=0
mkdir -p $LogDir
# mkdir -p $backup_dir_home
}

cleanup()
{
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 >> $dOUTLOG
}

do_fullbackup()
{
rm -rf $full_bkp_home
mkdir -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-lib-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-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 2>>$dOUTLOG
echo >> $dOUTLOG
echo "--------------------------------------------------------------------------------------" >> $dOUTLOG
echo " End Full Backup at $CURDATE" >> $dOUTLOG
echo "--------------------------------------------------------------------------------------" >> $dOUTLOG
echo >> $dOUTLOG
cat $dOUTLOG | grep "mysqlbackup completed OK" | 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 "$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
fi
}

do_validate()
{
echo >> $dOUTLOG
echo "-------------------------------------------" >> $dOUTLOG
echo " Start Validate at $CURDATE " >> $dOUTLOG
echo "-------------------------------------------" >> $dOUTLOG
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 --sbt-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 >> $dOUTLOG
}

do_incremental_with_redo_log_only()
{
rm -rf $incr_bkp_home
mkdir -p $incr_bkp_home
echo >> $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=$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 --incremental-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 $CURDATE" >> $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
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_home --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_POLICY" --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