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

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

《报告MySQL数据库备份恢复实施报告v1.docx》由会员分享，可在线阅读，更多相关《报告MySQL数据库备份恢复实施报告v1.docx（50页珍藏版）》请在冰豆网上搜索。

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

【关键字】报告

******

MySQL数据库备份恢复报告

VeritasConsultingServices

GreaterChina

1文档信息

1.1版本

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

Veritas大中国区顾问服务

1.0

2021-02-09

汪善平

1.2相关的文件

1.3文档更新记录

2021-02-09

文档创建

汪善平

1.0

2目录

3文档说明

此文档说明使用VeritasNetbackup通过OracleMySQLEnterpriseBackup来对MySQL数据库进行备份恢复的步骤；

此文档仅供******使用；

4环境说明

1）备份系统：

主机名

IP地址

操作系统

角色

NBU-PRIMARY1

RedHatEnterpriseLinuxServerrelease6.1

NBUMasterServer主机

NBU-PRIMARY1

RedHatEnterpriseLinuxServerrelease6.1

NBUMasterServer备机

NBUMASTER

RedHatEnterpriseLinuxServerrelease6.1

NBUMasterServer

NBU-QUCHONG

RedHatEnterpriseLinuxServerrelease6.1

NBUMediaServer

NBU-QUCHONG2

RedHatEnterpriseLinuxServerrelease6.1

NBUMediaServer

2）备份软件：

VeritasNetbackupEnterpriseServer7.6.0.4

3）MySQL服务器：

主机名

IP地址

操作系统

角色

yhzd-db3

RedHatEnterpriseLinuxServerrelease6.5

MySQL服务器

yhzd1

RedHatEnterpriseLinuxServerrelease6.5

MySQL恢复测试服务器

4）MySQL版本：

mysqlVer14.14Distrib5.6.22,forLinux（x86_64）usingEditLinewrapper

5软件安装

5.1NBU客户端软件安装

1）在NBUMasterServer，MediaServer的/etc/hosts中添加MySQL服务器的主机名解析：

#mysqldb

192.168.232.51yhzd-db1

192.168.232.52yhzd-db2

192.168.232.53yhzd-db3yhzd-

2）在MySQL服务器的/etc/hosts中添加NBUMasterServer，MediaServer的主机名解析：

192.168.238.21NBU-PRIMARY1

192.168.238.22NBU-PRIMARY2

192.168.238.16NBU-QUCHONG2

192.168.238.17NBU-QUCHONG

192.168.238.23NBUVIP-em1NBUMASTER

3）登陆NBUMasterServer，进行客户端软件推送：

[root@NBU-PRIMARY1RedHat2.6.18]#./sftp_to_clientyhzd-db3root

Connectingto192.168.232.53...

'spassword:

sftpcompletedsuccessfully.

Therootuseron192.168.232.53mustnowexecutethecommand

"sh/tmp/bp.10261/client_config[-L]".Theoptionalargument,"-L",

isusedtoavoidmodificationoftheclient'scurrentbp.conffile.

[root@NBU-PRIMARY1RedHat2.6.18]#

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

root@yhzd-db3tmp]#cdbp.10261/

[root@yhzd-db3bp.10261]#ls

bin_net.tarclient_configopenv

[root@yhzd-db3bp.10261]#./client_config

Checkingforrequiredsystemconditions...

Checkingforrecommendedsystemconditions...

oknb_7604_hotfix_auditor:

NopotentialforregressionofhotfixesorEEBswasdetected.

Blocksize=20records

./

./bp_servers

./bp_client_name

SavingclientbinariesforLinux/RedHat2.6.18.

InstallingPBX...

Pleasewaitwhileinstallationisinprogress...

InstallationcompletedSuccessfully

Installationloglocatedhere:

/var/tmp/installpbx-04325.log

UnpackingSYMCnbcltpackage.

Checkingforpre-existingSYMCnbcltpackage.

InstallingSYMCnbcltpackage.

InstallationofSYMCnbcltwassuccessful.

MoredetailsregardingSYMCnbcltcanbefoundinfile

/tmp/install_cltpkg_trace.1033onyhzd-db3.

TerminatingNetBackupand/orMediaManagerprocesses.

Checkingnetworkconnections.

bp.conf:

IP_ADDRESS_FAMILY=AF_INET:

defaultvalue,noupdateneeded

No[x]inetdprocessfound.

InstallingNB-Java.

UnpackingSYMCnbjavapackage.

Checkingforpre-existingSYMCnbjavapackage.

InstallingSYMCnbjavapackage.

InstallationofSYMCnbjavawassuccessful.

MoredetailsregardingSYMCnbjavacanbefoundinfile

/tmp/install_javapkg_trace.2272onyhzd-db3.

UnpackingSYMCnbjrepackage.

Checkingforpre-existingSYMCnbjrepackage.

InstallingSYMCnbjrepackage.

InstallationofSYMCnbjrewassuccessful.

MoredetailsregardingSYMCnbjrecanbefoundinfile

/tmp/install_jrepkg_trace.2272onyhzd-db3.

Directory/usr/openv/logsdoesnotexist.

Nologfilestomigrate.

CheckingforSYMCpddeapackage...

InstallingPDDEagentpackage（/tmp/bp.10261/openv/netbackup/client/Linux/RedHat2.6.18/pddeagent.tar.gz）...

ExtractingpackageSYMCpddeain/tmp/pdde_pkg_dir_2422.

PackageSYMCpddeaextractedto/tmp/pdde_pkg_dir_2422.

InstallingpackageSYMCpddea.

PackageSYMCpddeainstalled.

PDDEinstallfinishedsuccessfully.

Versionnowinstalled:

8.0004.0014.0917

FullPDDEinstallationlogsavedto:

/var/log/puredisk/2016-05-24_10:

48-pdde-install.log

Thefollowingautomaticstartupandshutdownscripts（respectively）

havebeeninstalled.TheywillcausetheNetBackupdaemonstobe

automaticallyshutdownandrestartedeachtimethesystemboots.

/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

Successfullyupdatedthesessioncacheparameters.

Startingvnetd...

Startingbpcd...

Startingnbftclnt...

Startingnbdisco...

Startingmtstrmd...

Startingbmrbd...

InstallationofJavaLiveUpdateagentsucceeded.Refertofile

/tmp/JLU-Log/JavaLiveUpdate-Install.logonyhzd-db3

forinstallationdetails.

CheckingLiveUpdateregistrationforthefollowingproducts:

CLT

Thismaytakeafewminutes.

ProductCLTisinstalledandwillberegistered.

UpdatingLiveUpdateregistrationnow...thismaytakesometime.

Clientinstallcomplete.

5）等待客户端软件安装完成；

5.2安装MEB插件

Netbackup通过Oracle提供的MEB（MySQLEnterpriseBackup）对MySQL数据库进行备份，在MySQL服务器上需要安装MEB插件；

1）下载MEB插件并上传到MySQL服务器上；

2）安装MEB插件：

[root@yhzd-db3tmp]#rpm-ivhmeb-3.12.2-el6.x86_64.rpm

warning:

meb-3.12.2-el6.x86_64.rpm:

HeaderV3DSA/SHA1Signature,keyID5072e1f5:

NOKEY

Preparing...###########################################[100%]

1:

meb###########################################[100%]

[root@yhzd-db3tmp]#rpm-qa|grep-imeb

meb-3.12.2-1.x86_64

6备份配置

6.1创建备份脚本

6.1.1MySQL备份说明

1）使用MEB备份MySQL数据库分为全备份和增量备份；

2）全备份是一份完整的备份数据；

3）增量备份通过参数：

--incremental-base来控制此次增量备份依据什么时候的数据来进行：

history:

backup，依据上一次成功的备份中的lsn

dir:

，依据上一次成功备份的目录中的metadata文件中的lsn

在这里我们通过dir:

来实现每次增量备份都使用上一次成功的全备份中的metadata文件的lsn进行增量，从而达到累计增量备份的效果；

4）增量备份参数：

--incremental-with-redo-log-only

此参数使得增量备份时通过MySQL的redolog来判断此次增量部分数据；由于redo要求log是循环使用的，因此使用此参数要求redolog的大小必须能容纳上次备份到此次备份之间的数据变化；

6.1.2备份脚本

1）在MySQL服务器上创建以下目录：

#mkdir-p/usr/openv/scripts/

#mkdir-p/usr/openv/scripts/logs/

2）创建全备份脚本：

#!

/bin/sh

#Copyright:

#

#mysqlbackupexecutablefilepath

MYSQLBACKUP=/opt/mysql/meb-3.12/bin/mysqlbackup

#theMysqlport

Port=3306

#theMysqlprotocol

Protocol=tcp

#theMysqlusernameandpasswordusedforbackup

USER=temsbackup

password=backuptems

#NetbackupMasterServer

NB_ORA_SERV=NBUMASTER

#MySqlDBServer

NB_ORA_CLIENT=yhzd-db3

#NetBackupMySQLDatabaseBackupPolicyName

NB_ORA_POLICY=yhzddb3_mysql_full

File_Backup=yhzddb3_mysql_metadata

CMD=/usr/openv/netbackup/bin/bpbackup

#thetemporarydirectoryusedbymysqlbackup

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-ne0];

then

echo"MYSQLBACKUPFAILEDat$CURDATE,PLEASECHECK">>$dOUTLOG

#rm-rf$backup_dir_home/*

else

echo"MYSQLBackupSuccessfulat$CURDATE">>$dOUTLOG

fi

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo>>$dOUTLOG

}

do_fullbackup（）

{

rm-rf$full_bkp_home

mkdir-p$full_bkp_home

echo>>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo"StartFullBackupat$CURDATE">>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo>>$dOUTLOG

echo"Executecommand:

">>$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=$dBACKUPIMAGENAMEbackup-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=$dBACKUPIMAGENAMEbackup-to-image2>>$dOUTLOG

echo>>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo"EndFullBackupat$CURDATE">>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo>>$dOUTLOG

cat$dOUTLOG|grep"mysqlbackupcompletedOK"|grep-vprint

BkpStatus=$?

}

do_conf_backup（）

{

if[$BkpStatus-eq0];

then

echo>>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo"BackupMetadatafilesat$CURDATE">>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo>>$dOUTLOG

echo"TheMetadatafilesare:

">>$dOUTLOG

ls-lR$backup_dir_home>>$dOUTLOG

echo>>$dOUTLOG

echo"Executecommand:

">>$dOUTLOG

echo"$CMD-p$File_Backup$backup_dir_home">>$dOUTLOG

$CMD-p$File_Backup$backup_dir_home

echo>>$dOUTLOG

echo"--------------------------------------------------------------------------------------">>$dOUTLOG

echo"EndMetadatafilesbackupat$CURDATE">>$dOUTLOG

echo"---------------------------------------------------------------