NetBackup结合MEB备份恢复MySQL

文本丢失80后 06-06 128

前言：

今天我们对“tsm备份mysql”大约比较着重，朋友们都想要分析一些“tsm备份mysql”的相关资讯。那么小编同时在网上网罗了一些有关“tsm备份mysql””的相关文章，希望姐妹们能喜欢，小伙伴们快快来了解一下吧！

一、环境摘要1.1 NBU备份MySQL方案概述

NetBackup可采用以下几种方案对MySQL进行备份：

ü 关闭MySQL并以文件形式离线备份（无法做到热备份）

ü 采用脚本或工具（如mysqldump）将MySQL数据先导出到文件系统然后再通过NBU对其备份

ü 采用Zmanda代理或MySQL Enterprise Backup与NetBackup结合使用

补充说明：

Ø 自带的mysqldump备份恢复速度慢（只要是逻辑备份数据量较大均不推荐），备份出来的为sql语句（即DDL+insert），增备利用mysql binlog，必须开启二进制日志

Ø NetBackup 8.1之前版本并无MySQL Agent，NBU Agent从8.1才开始引入，是通过创建快照来进行MySQL备份（适用于Windows的卷影复制服务（VSS）或Linux的逻辑卷管理器（LVM）须可获取数据库快照）

注：NetBackup for MySQL Agent支持完全备份、完全恢复以及重定向恢复（即异机恢复），不支持增量备份恢复、表级备份恢复。

Ø Zmanda或MEB对MySQL进行按需（全备、增备等）备份，NetBackup提供备份介质和策略管理（作业调度计划、保留周期等），NBU与其结合将MySQL直接备份至指定介质（如磁盘、磁带等）

Ø Zmanda已从开源转向Enterprise且使用率较低，配置麻烦因此不再考虑使用

Ø MySQL Enterprise Backup 3.6版本开始支持与介质管理软件（MMS）（如Symantec NetBackup，Oracle Secure Backup（OSB），IBM Tivoli Storage Manager（TSM））连接，以通过系统备份到磁带（SBT）接口执行备份和还原操作。SBT是作为共享库（Linux中的libobk.so和Windows中的orasbt.dll）实现的Oracle API，用于通过第三方软件供应商备份/恢复数据，MEB可通过MetaLink下载正常使用但属于商业软件（本次仅用于测试）

1.2 备份工具指标对比

工具

指标

MySQL Enterprise Backup

MySQL Utilities

mysqldump

Physical copy

LVM/ZFS snapshot

XtraBackup

备份类型

Physical

Logical

Physical

备份速度

Medium

Slow

Fast

Medium

恢复速度

Fast

Slow

Fast

系统支持

All

LVM only

All

数据库引擎

All

InnoDB

XtraDB

MyISAM

注：以上数据仅供参考，表格中所有工具并未全部测试，数据来源于网络

备注：XtraBackup 是一款可免费使用的开源工具，功能、使用率及备份速度均不错（与MEB难分伯仲），但此工具仅备份至文件系统或其他服务器，无法直接通过NetBackup lib库（libobk.so）结合将数据备份至NBU备份介质。

1.3 测试环境简述

本次测试采用NetBackup 7.7.3 Linux Server通过MEB（MySQL Enterprise Backup）对MySQL 5.6和5.7分别进行备份恢复操作（采用LAN备份），备份拓扑如下图所示：

说明（经验值，需根据具体环境而定）：

Ø 数据量小于500GB可优先考虑采用LAN模式备份（部署相对简单方便且Standard Client无需访问备份介质）

Ø 数据量大于500GB可考虑采用LAN_FREE模式备份（需Enterprise Client且可访问备份介质）

Ø 数据量小于2TB可考虑每日完全备份，大于2TB考虑每周1-2次全备+增备组合方式

Ø 在备份数据量较大、保留周期较长、备份频率较高等环境中若后端备份介质支持压缩、去重功能可节省大量空间

二、参数核查

2.1 系统版本检查

cat /etc/redhat-release

CentOS release 6.10 （Final）

2.2 MySQL版本检查

以下几种方法任选其一即可：

方法一：用户登录后便可看到版本信息

方法二：通过status命令查看

方法三：通过select命令查看

方法四：通过mysql --help命令查看

方法五：通过rpm –qa命令查看

2.3 MySQL其他参数查看

1、查看port：

mysql> show global variables like 'port';

2、查看datadir：

mysql> show global variables like "%datadir%";

三、创建用户并赋权

备份需要MySQL特定用户连接到MySQL服务器，因此需要单独创建备份用户并赋权（亦可使用如root等管理账户操作）。

注：请根据之前核查的MySQL版本对应选择：

3.1 MySQL 5.7.21 and later

CREATE USER 'mysqlbkuser'@'localhost' IDENTIFIED BY 'P@ssw0rd';

GRANT RELOAD ON *.* TO 'mysqlbkuser'@'localhost';

GRANT CREATE,INSERT,DROP,UPDATE ON mysql.backup_progress TO 'mysqlbkuser'@'localhost';

GRANT CREATE,INSERT,DROP,UPDATE,SELECT,ALTER ON mysql.backup_history TO 'mysqlbkuser'@'localhost';

GRANT REPLICATION CLIENT ON *.* TO 'mysqlbkuser'@'localhost';

GRANT SUPER ON *.* TO 'mysqlbkuser'@'localhost';

GRANT PROCESS ON *.* TO 'mysqlbkuser'@'localhost';

GRANT SELECT ON performance_schema.replication_group_members TO 'mysqlbkuser'@'localhost';

备1：For using transportable tablespaces (TTS) to back up and restore InnoDB tables还须赋予以下权限，否则跳过：

GRANT LOCK TABLES,ALTER,SELECT,CREATE,DROP,FILE ON *.* TO 'mysqlbkuser'@'localhost';

备2：For creating tape backups using the System Backup to Tape (SBT) API还须赋予以下权限，否则跳过：

GRANT CREATE,INSERT,DROP,UPDATE ON mysql.backup_sbt_history TO 'mysqlbkuser'@'localhost';

3.2 MySQL 5.7.9 to 5.7.20

CREATE USER 'mysqlbkuser'@'localhost' IDENTIFIED BY 'P@ssw0rd';

GRANT RELOAD ON *.* TO 'mysqlbkuser'@'localhost';

GRANT CREATE,INSERT,DROP,UPDATE ON mysql.backup_progress TO 'mysqlbkuser'@'localhost';

GRANT CREATE,INSERT,SELECT,DROP,UPDATE ON mysql.backup_history TO 'mysqlbkuser'@'localhost';

GRANT REPLICATION CLIENT ON *.* TO 'mysqlbkuser'@'localhost';

GRANT SUPER ON *.* TO 'mysqlbkuser'@'localhost';

GRANT PROCESS ON *.* TO 'mysqlbkuser'@'localhost';

备1：For using transportable tablespaces (TTS) to back up and restore InnoDB tables还须赋予以下权限，否则跳过：

GRANT LOCK TABLES,ALTER,SELECT,CREATE,DROP,FILE ON *.* TO 'mysqlbkuser'@'localhost';

备2：For creating tape backups using the System Backup to Tape (SBT) API还须赋予以下权限，否则跳过：

GRANT CREATE,INSERT,DROP,UPDATE ON mysql.backup_sbt_history TO 'mysqlbkuser'@'localhost';

3.3 MySQL 5.7.9 earlier and 5.6

CREATE USER 'mysqlbkuser'@'localhost' IDENTIFIED BY 'P@ssw0rd';

GRANT RELOAD ON *.* TO 'mysqlbkuser'@'localhost';

GRANT CREATE,INSERT,DROP,UPDATE ON mysql.backup_progress TO 'mysqlbkuser'@'localhost';

GRANT CREATE,INSERT,SELECT,DROP,UPDATE ON mysql.backup_history TO 'mysqlbkuser'@'localhost';

GRANT REPLICATION CLIENT ON *.* TO 'mysqlbkuser'@'localhost';

GRANT SUPER ON *.* TO 'mysqlbkuser'@'localhost';

备1：For using transportable tablespaces (TTS) to back up and restore InnoDB tables还须赋予以下权限，否则跳过：

GRANT LOCK TABLES,ALTER,SELECT,CREATE,DROP,FILE ON *.* TO 'mysqlbkuser'@'localhost';

备2：For creating tape backups using the System Backup to Tape (SBT) API还须赋予以下权限，否则跳过：

GRANT CREATE,INSERT,DROP,UPDATE ON mysql.backup_sbt_history TO 'mysqlbkuser'@'localhost';

四、创建备份脚本

使用《Create_NBU_Scripts_V1.2.sh》可快速规范创建NBU备份MySQL或ORACLE脚本，提高效率并降低出错率，具体步骤如下：

1、将脚本上传至客户端任意目录并赋权：

chmod +x Create_NBU_Scripts_V1.2.sh

2、运行脚本并输入相关信息（根据提示输入需要的信息即可），如下所示：

[root@backup tmp]# ./Create_NBU_Scripts_V1.2.sh

Create NetBackup Script, Do you wish to continue? [y,n] (y)

Choose the Backup Type you wish to create

1) NetBackup Script for MySQL DataBase(Use MEB)

2) NetBackup Script for Oracle DataBase

3) NetBackup Script for Oracle ArchiveLog

4) NetBackup Script for Oracle DataBase and ArchiveLog

x) Exit from this Script

Choose an option: 1

Input script directory and name: (default: /usr/openv/scripts)

Input MySQL port number: (default: 3306)

Input MySQL user name: (default: mysqlbkuser)

Input MySQL User mysqlbkuser's password: (default: P@ssw0rd)

Input MySQL IP address: (default: 127.0.0.1)

Input MySQL the directory to store the backup data: (default: /mysqlbak_nbu)

"/mysqlbak_nbu" directory does not exist, create the directory? [y,n] (y)

Input script name: (default: hot_mysql.sh)

Input MEB mysqlbackup file path: (/opt/mysql/meb-3.12/bin/mysqlbackup)

"/usr/openv/scripts/hot_mysql.sh" has been created.

This script can be deleted after you are sure the create was successful.

3、完成后的脚本信息如下所示：

#!/bin/sh

#2018.08.07_09:53:29 by yto

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

MYSQL_PORT=3306

MYSQL_INSTALL_USER=root

MYSQL_DB_USER=mysqlbkuser

MYSQL_PWD=P@ssw0rd

MYSQL_HOST=127.0.0.1

MYSQL_BAK_DIR=/mysqlbak_nbu

eecho(){ echo "$1">>$BAK_LOG_FILE 2>&1;}

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

if [[ "$NB_ORA_INCR" = "1" || "$NB_ORA_CINC" = "1" ]];then BKTP=INC;else BKTP=FUL;fi

BAK_LOG_FILE=${0}_${BKTP}_`date +%Y%m%d%H%M%S`.out

eecho

chmod 666 $BAK_LOG_FILE

eecho "Script $0"

eecho "====== started on `date` ======"

eecho

eecho "MEB: $MEB"

eecho "MYSQL_HOST: $MYSQL_HOST"

eecho "MYSQL_INSTALL_USER: $MYSQL_INSTALL_USER"

eecho "MYSQL_PORT: $MYSQL_PORT"

eecho "MYSQL_DB_USER: $MYSQL_DB_USER"

eecho "MYSQL_BAK_DIR: $MYSQL_BAK_DIR"

eecho

eecho "NB_ORA_FULL: $NB_ORA_FULL"

eecho "NB_ORA_INCR: $NB_ORA_INCR"

eecho "NB_ORA_CINC: $NB_ORA_CINC"

eecho "NB_ORA_SERV: $NB_ORA_SERV"

eecho "NB_ORA_CLIENT: $NB_ORA_CLIENT"

eecho "NB_ORA_POLICY: $NB_ORA_POLICY"

eecho

if [ "$NB_ORA_FULL" = "1" ];then eecho "Full backup requested";BACKUP_TYPE="0"

elif [ "$NB_ORA_INCR" = "1" ];then eecho "Differential incremental backup requested";BACKUP_TYPE="1"

elif [ "$NB_ORA_CINC" = "1" ];then eecho "Cumulative incremental backup requested";BACKUP_TYPE="1"

elif [ "$BACKUP_TYPE" = "" ];then eecho "Default - Full backup requested";BACKUP_TYPE="0"

eecho "-----------------------------------------------------"

eecho

eecho "List all files in the $MYSQL_BAK_DIR"

ls -ltr $MYSQL_BAK_DIR >>$BAK_LOG_FILE 2>&1;

eecho "-----------------------------------------------------"

eecho

eecho "Delete all files in the $MYSQL_BAK_DIR"

rm -fr ${MYSQL_BAK_DIR}/datadir ${MYSQL_BAK_DIR}/meta ${MYSQL_BAK_DIR}/*.cnf

eecho "-----------------------------------------------------"

eecho

if [ $BACKUP_TYPE = "0" ];then CMD_STR="$MEB --port=$MYSQL_PORT --user=$MYSQL_DB_USER --password=$MYSQL_PWD --host=$MYSQL_HOST --backup-image=sbt:FULL_bkpsbtNB_`date +%y%m%d%H%M%S` --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--sbt-environment="NB_ORA_SERV=${NB_ORA_SERV},NB_ORA_CLIENT=${NB_ORA_CLIENT},NB_ORA_POLICY=${NB_ORA_POLICY}" --backup-dir=$MYSQL_BAK_DIR backup-to-image >/dev/null 2>>$BAK_LOG_FILE"; else CMD_STR="$MEB --port=$MYSQL_PORT --user=$MYSQ

L_DB_USER --password=$MYSQL_PWD --host=$MYSQL_HOST --incremental=optimistic --incremental --incremental-base=history:last_backup --backup-image=sbt:INC_bkpsbtNB_`date +%y%m%d%H%M%S` --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--sbt-environment="NB_ORA_SERV=${NB_ORA_SERV},NB_ORA_CLIENT=${NB_ORA_CLIENT},NB_ORA_POLICY=${NB_ORA_POLICY}" --backup-dir=$MYSQL_BAK_DIR backup-to-image >/dev/null 2>>$BAK_LOG_FILE"; fi

if [ "$CUSER" = "root" ];then su - $MYSQL_INSTALL_USER -c "$CMD_STR";RSTAT=$?;else /bin/sh -c "$CMD_STR";RSTAT=$?;fi

if [ "$RSTAT" = "0" ];then LOGMSG="ended successfully"; else LOGMSG="ended in error"; fi

eecho

eecho "Script $0"

eecho "====== $LOGMSG on `date` ======"

eecho

exit $RSTAT

4、附件（脚本）如下：

五、创建备份策略

1、创建一个新策略并输入策略名称：

2、 “Attributes”选项卡中修改Policy type为“Oracle”并设置好Policy storage和Policy volume pool：

3、 “Clients”选项卡中修改为“Clients for use with scripts or templates”并添加客户端名称（须可解析）或IP地址：

4、 “Backup Selections”选项卡中选择并添加之前创建好的备份脚本：

5、 “Schedules”选项卡中添加调度策略并设置保留周期：

六、手工发起备份测试

1、右键单击之前创建好的策略并选择“Manual Backup…”：

2、选择全备：

3、此时在“Activity Monitor”界面中可看到备份作业已在运行中：

4、等待备份完成后在之前的脚本所在目录中可生成备份作业日志：

5、可查看此out文件查看备份情况以及后期排故：

[root@backup scripts]# more hot_mysql.sh_FUL_20180807113438.out

Script /usr/openv/scripts/hot_mysql.sh

====== started on Tue Aug 7 11:34:38 CST 2018 ======

MEB: /opt/mysql/meb-3.12/bin/mysqlbackup

MYSQL_HOST: 127.0.0.1

MYSQL_INSTALL_USER: root

MYSQL_PORT: 3306

MYSQL_DB_USER: mysqlbkuser

MYSQL_BAK_DIR: /mysqlbak_nbu

NB_ORA_FULL: 1

NB_ORA_INCR: 0

NB_ORA_CINC: 0

NB_ORA_SERV: backup

NB_ORA_CLIENT: backup

NB_ORA_POLICY: mysql_test

Full backup requested

-----------------------------------------------------

List all files in the /mysqlbak_nbu

total 32

-rw-r--r-- 1 root root 245 Aug 7 11:30 backup-my.cnf

drwx------ 2 root root 4096 Aug 7 11:30 meta

-rw-r--r-- 1 root root 3478 Aug 7 11:30 server-my.cnf

-rw-r--r-- 1 root root 12805 Aug 7 11:30 server-all.cnf

drwx------ 2 root root 4096 Aug 7 11:30 datadir

-----------------------------------------------------

Delete all files in the /mysqlbak_nbu

-----------------------------------------------------

MySQL Enterprise Backup version 3.12.3 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2016/05/05]

mysqlbackup: INFO: Starting with following command line ...

/opt/mysql/meb-3.12/bin/mysqlbackup --port=3306 --user=mysqlbkuser

--password=xxxxxxxxxxxx --host=127.0.0.1

--backup-image=sbt:FULL_bkpsbtNB_180807113438

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--sbt-environment=NB_ORA_SERV=backup,NB_ORA_CLIENT=backup,NB_ORA_POLICY=mysql_test

--backup-dir=/mysqlbak_nbu backup-to-image

mysqlbackup: INFO:

mysqlbackup: INFO: MySQL server version is '5.6.28'.

mysqlbackup: INFO: Got some server configuration information from running server.

mysqlbackup: INFO: sbt-environment: 'NB_ORA_SERV=backup,NB_ORA_CLIENT=backup,NB_ORA_POLICY=mysql_test'

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'backup-to-image' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 11:34:38 mysqlbackup: INFO: MEB logfile created at /mysqlbak_nbu/meta/MEB_2018-08-07.11-34-38_image_backup.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir =

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

innodb_undo_directory = /var/lib/mysql/

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /mysqlbak_nbu/datadir

innodb_data_home_dir = /mysqlbak_nbu/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /mysqlbak_nbu/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

innodb_undo_directory = /mysqlbak_nbu/datadir

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

Backup Image Path = FULL_bkpsbtNB_180807113438

mysqlbackup: INFO: Unique generated backup id for this is 15336128784440242

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

180807 11:34:40 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 11:34:40 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

180807 11:34:47 mysqlbackup: INFO: Full Image Backup operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 11:34:47 mysqlbackup: INFO: System tablespace file format is Antelope.

180807 11:34:47 mysqlbackup: INFO: Starting to copy all innodb files...

mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup.

Point-In-Time-Recovery will not be possible.

If this is online backup then server may not have started with --log-bin.

You may specify its location with --log-bin-index option.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/backup-my.cnf.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/backup_create.xml.

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Found checkpoint at lsn 1630743.

180807 11:34:47 mysqlbackup: INFO: Starting log scan from lsn 1630720.

180807 11:34:47 mysqlbackup: INFO: Copying log...

180807 11:34:47 mysqlbackup: INFO: Log copied, lsn 1630743.

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Copying /var/lib/mysql/test/test.ibd (Antelope file format).

180807 11:34:47 mysqlbackup: INFO: Completing the copy of innodb files.

180807 11:34:47 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.

180807 11:34:47 mysqlbackup: INFO: Starting to lock all the tables...

180807 11:34:47 mysqlbackup: INFO: All tables are locked and flushed to disk

180807 11:34:47 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'

180807 11:34:47 mysqlbackup: INFO: Starting to backup all non-innodb files in

subdirectories of '/var/lib/mysql/'

180807 11:34:47 mysqlbackup: INFO: Adding database directory: datadir/mysql

e_schema 47 mysqlbackup: INFO: Adding database directory: datadir/performanc--More--(74%)

180807 11:34:47 mysqlbackup: INFO: Adding database directory: datadir/test

180807 11:34:47 mysqlbackup: INFO: Completing the copy of all non-innodb files.

180807 11:34:48 mysqlbackup: INFO: A copied database page was modified at 1630743.

(This is the highest lsn found on page)

Scanned log up to lsn 1630743.

Was able to parse the log up to lsn 1630743.

Maximum page number for a log record 0

180807 11:34:48 mysqlbackup: INFO: All tables unlocked

180807 11:34:48 mysqlbackup: INFO: All MySQL tables were locked for 0.689 seconds.

180807 11:34:48 mysqlbackup: INFO: Reading all global variables from the server.180807 11:34:48 mysqlbackup: INFO: Completed reading of all global variables from the server.

180807 11:34:48 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /mysqlbak_nbu

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/backup_variables.txt.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/datadir/ibbackup_logfile.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/server-all.cnf.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/server-my.cnf.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/backup_content.xml.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/image_files.xml.

180807 11:34:49 mysqlbackup: INFO: meb_sbt_backup_close: blocks: 15 size: 1048576 bytes: 14990540

180807 11:34:49 mysqlbackup: INFO: Full Image Backup operation completed successfully.

180807 11:34:49 mysqlbackup: INFO: Backup image created successfully.

mysqlbackup: INFO: Image Path = sbt:FULL_bkpsbtNB_180807113438

-------------------------------------------------------------

Parameters Summary

-------------------------------------------------------------

Start LSN : 1630720

End LSN : 1630743

-------------------------------------------------------------

mysqlbackup completed OK!

Script /usr/openv/scripts/hot_mysql.sh

====== ended successfully on Tue Aug 7 11:34:49 CST 2018 ======

6、同样操作发起增量备份：

[root@backup scripts]# cat hot_mysql.sh_INC_20180807113830.out

Script /usr/openv/scripts/hot_mysql.sh

====== started on Tue Aug 7 11:38:30 CST 2018 ======

MEB: /opt/mysql/meb-3.12/bin/mysqlbackup

MYSQL_HOST: 127.0.0.1

MYSQL_INSTALL_USER: root

MYSQL_PORT: 3306

MYSQL_DB_USER: mysqlbkuser

MYSQL_BAK_DIR: /mysqlbak_nbu

NB_ORA_FULL: 0

NB_ORA_INCR: 1

NB_ORA_CINC: 0

NB_ORA_SERV: backup

NB_ORA_CLIENT: backup

NB_ORA_POLICY: mysql_test

Differential incremental backup requested

-----------------------------------------------------

List all files in the /mysqlbak_nbu

total 32

-rw-r--r-- 1 root root 245 Aug 7 11:34 backup-my.cnf

drwx------ 2 root root 4096 Aug 7 11:34 meta

-rw-r--r-- 1 root root 3478 Aug 7 11:34 server-my.cnf

-rw-r--r-- 1 root root 12805 Aug 7 11:34 server-all.cnf

drwx------ 2 root root 4096 Aug 7 11:34 datadir

-----------------------------------------------------

Delete all files in the /mysqlbak_nbu

-----------------------------------------------------

MySQL Enterprise Backup version 3.12.3 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2016/05/05]

mysqlbackup: INFO: Starting with following command line ...

/opt/mysql/meb-3.12/bin/mysqlbackup --port=3306 --user=mysqlbkuser

--password=xxxxxxxxxxxx --host=127.0.0.1 --incremental=optimistic

--incremental --incremental-base=history:last_backup

--backup-image=sbt:INC_bkpsbtNB_180807113830

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--sbt-environment=NB_ORA_SERV=backup,NB_ORA_CLIENT=backup,NB_ORA_POLICY=mysql_test

--backup-dir=/mysqlbak_nbu backup-to-image

Warning: mysqlbackup: ignoring option '--incremental' due to invalid value 'optimistic'

mysqlbackup: INFO:

mysqlbackup: INFO: MySQL server version is '5.6.28'.

mysqlbackup: INFO: Got some server configuration information from running server.

mysqlbackup: INFO: sbt-environment: 'NB_ORA_SERV=backup,NB_ORA_CLIENT=backup,NB_ORA_POLICY=mysql_test'

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'backup-to-image' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 11:38:30 mysqlbackup: INFO: MEB logfile created at /mysqlbak_nbu/meta/MEB_2018-08-07.11-38-30_inc_img_backup.log

180807 11:38:30 mysqlbackup: INFO: Location of last successful backup: /mysqlbak_nbu.

180807 11:38:30 mysqlbackup: INFO: End time of last successful backup: 2018-08-07 11:34:48.

180807 11:38:30 mysqlbackup: INFO: Last backup type: FULL.

180807 11:38:30 mysqlbackup: INFO: Using start_lsn=1630743, calculated from backup_history table of MySQL server.

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir =

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

innodb_undo_directory = /var/lib/mysql/

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /mysqlbak_nbu/datadir

innodb_data_home_dir = /mysqlbak_nbu/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /mysqlbak_nbu/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

innodb_undo_directory = /mysqlbak_nbu/datadir

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

Backup Image Path = INC_bkpsbtNB_180807113830

mysqlbackup: INFO: Unique generated backup id for this is 15336131104603398

mysqlbackup: INFO: Creating 15 buffers each of size 16908288.

180807 11:38:32 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 11:38:32 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

180807 11:38:39 mysqlbackup: INFO: Incremental Image Backup operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 11:38:39 mysqlbackup: INFO: System tablespace file format is Antelope.

180807 11:38:39 mysqlbackup: INFO: Starting to copy all innodb files...

mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup.

Point-In-Time-Recovery will not be possible.

If this is online backup then server may not have started with --log-bin.

You may specify its location with --log-bin-index option.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/backup-my.cnf.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/backup_create.xml.

180807 11:38:39 mysqlbackup: INFO: Found checkpoint at lsn 1692774.

180807 11:38:39 mysqlbackup: INFO: Starting log scan from lsn 1692672.

180807 11:38:39 mysqlbackup: INFO: Copying log...

180807 11:38:39 mysqlbackup: INFO: Log copied, lsn 1692774.

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Copying /var/lib/mysql/test/test.ibd (Antelope file format).

180807 11:38:39 mysqlbackup: INFO: Completing the copy of innodb files.

180807 11:38:39 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.

180807 11:38:39 mysqlbackup: INFO: Starting to lock all the tables...

180807 11:38:39 mysqlbackup: INFO: All tables are locked and flushed to disk

180807 11:38:39 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'

180807 11:38:39 mysqlbackup: INFO: Starting to backup all non-innodb files in

subdirectories of '/var/lib/mysql/'

180807 11:38:39 mysqlbackup: INFO: Adding database directory: datadir/mysql

180807 11:38:39 mysqlbackup: INFO: Adding database directory: datadir/performance_schema

180807 11:38:39 mysqlbackup: INFO: Adding database directory: datadir/test

180807 11:38:39 mysqlbackup: INFO: Completing the copy of all non-innodb files.

180807 11:38:40 mysqlbackup: INFO: A copied database page was modified at 1692774.

(This is the highest lsn found on page)

Scanned log up to lsn 1692774.

Was able to parse the log up to lsn 1692774.

Maximum page number for a log record 0

180807 11:38:40 mysqlbackup: INFO: All tables unlocked

180807 11:38:40 mysqlbackup: INFO: All MySQL tables were locked for 0.941 seconds.

180807 11:38:40 mysqlbackup: INFO: Reading all global variables from the server.

180807 11:38:40 mysqlbackup: INFO: Completed reading of all global variables from the server.

180807 11:38:40 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /mysqlbak_nbu

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/backup_variables.txt.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/datadir/ibbackup_logfile.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/server-all.cnf.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/server-my.cnf.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/datadir/ibbackup_ibd_files.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/backup_content.xml.

mysqlbackup: INFO: Copying meta file /mysqlbak_nbu/meta/image_files.xml.

180807 11:38:41 mysqlbackup: INFO: meb_sbt_backup_close: blocks: 3 size: 1048576 bytes: 2474554

180807 11:38:41 mysqlbackup: INFO: Incremental Image Backup operation completed successfully.

180807 11:38:41 mysqlbackup: INFO: Backup image created successfully.

mysqlbackup: INFO: Image Path = sbt:INC_bkpsbtNB_180807113830

180807 11:38:41 mysqlbackup: INFO: Backup contains changes from lsn 1630744 to lsn 1692774

-------------------------------------------------------------

Parameters Summary

-------------------------------------------------------------

Start LSN : 1630744

End LSN : 1692774

-------------------------------------------------------------

mysqlbackup completed OK!

Script /usr/openv/scripts/hot_mysql.sh

====== ended successfully on Tue Aug 7 11:38:41 CST 2018 ======

七、全备恢复测试

恢复主要分三大步：restore、apply-log、copy-back

1、第一步：restore

/opt/mysql/meb-4.1/bin/mysqlbackup --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --backup-dir=/mysql_restore --backup-image=sbt:FULL_bkpsbtNB_180804003931 image-to-backup-dir

2、第二步：apply-log

/opt/mysql/meb-4.1/bin/mysqlbackup --backup-dir=/mysql_restore apply-log

3、第三步：copy-back

拷贝前先关闭数据库或停止mysqld服务

mysqladmin -u root -p shutdown 或 service mysqld stop

/opt/mysql/meb-4.1/bin/mysqlbackup --backup-dir=/mysql_restore copy-back --force

注：若原有datadir目录存在且不为空则会报错，删除原有文件或使用--force选项

180804 01:47:10 MAIN ERROR: The datadir exists and is not empty. Use the "--force" option, if you want to overwrite the datadir on your own risk.

说明：以上分步操作亦可使用“copy-back-and-apply-log”参数一步完成：

1、关闭数据库或停止mysqld服务：mysqladmin -u root -p shutdown

2、恢复操作：

/opt/mysql/meb-3.12/bin/mysqlbackup --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --datadir=/var/lib/mysql/ --backup-dir=/mysql_restore --backup-image=sbt:FULL_bkpsbtNB_180807113438 copy-back-and-apply-log --force

注1：--datadir参数是否必须根据MEB版本有所不同

注2：恢复完成后mysqld服务若无法正常启动首先确认数据文件用户及所属组属性设置是否正确（通过chown mysql:mysql xxx 修改）

3、命令运行后会在NBU JAVA CONSOLE的“Activity Monitor”界面中看到恢复作业已在运行中：

4、恢复输出仅供参考：

# /opt/mysql/meb-3.12/bin/mysqlbackup --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --datadir=/var/lib/mysql/ --backup-dir=/mysql_restore --backup-image=sbt:FULL_bkpsbtNB_180807113438 copy-back-and-apply-log --force

MySQL Enterprise Backup version 3.12.3 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2016/05/05]

mysqlbackup: INFO: Starting with following command line ...

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

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--datadir=/var/lib/mysql/ --backup-dir=/tmp/mysql_restore

--backup-image=sbt:FULL_bkpsbtNB_180807113438 copy-back-and-apply-log

--force

mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 11:57:35 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 11:57:35 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

mysqlbackup: INFO: Backup Image MEB version string: 3.12.3 [2016/05/05]

180807 11:57:40 mysqlbackup: INFO: MEB logfile created at /tmp/mysql_restore/meta/MEB_2018-08-07.11-57-40_copy_back_img_to_datadir.log

mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.

mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.

mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir = /var/lib/mysql/

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = Null

innodb_checksum_algorithm = innodb

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /tmp/mysql_restore/datadir

innodb_data_home_dir = /tmp/mysql_restore/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /tmp/mysql_restore/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

180807 11:57:40 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 11:57:40 mysqlbackup: INFO: Copying database directory: meta

180807 11:57:40 mysqlbackup: INFO: Copying datadir/ibdata1.

180807 11:57:40 mysqlbackup: INFO: Copying datadir/mysql/innodb_index_stats.ibd.

180807 11:57:40 mysqlbackup: INFO: Copying datadir/mysql/innodb_table_stats.ibd.

180807 11:57:40 mysqlbackup: INFO: Copying datadir/mysql/slave_master_info.ibd.

180807 11:57:40 mysqlbackup: INFO: Copying database directory: datadir/mysql

180807 11:57:40 mysqlbackup: INFO: Copying datadir/mysql/slave_relay_log_info.ibd.

180807 11:57:40 mysqlbackup: INFO: Copying datadir/mysql/slave_worker_info.ibd.

180807 11:57:40 mysqlbackup: INFO: Copying database directory: datadir/performance_schema

180807 11:57:40 mysqlbackup: INFO: Copying database directory: datadir/test

180807 11:57:40 mysqlbackup: INFO: Copying datadir/test/test.ibd.

180807 11:57:40 mysqlbackup: INFO: Copying database directory: datadir/mysql

180807 11:57:40 mysqlbackup: INFO: Copying database directory: datadir/performance_schema

180807 11:57:40 mysqlbackup: INFO: Copying database directory: datadir/test

180807 11:57:40 mysqlbackup: INFO: Total files as specified in image: 157

180807 11:57:41 mysqlbackup: INFO: meb_sbt_restore_close: blocks: 15 size: 1048576 bytes: 14990540

180807 11:57:41 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql/

180807 11:57:41 mysqlbackup: INFO: Copy-back operation completed successfully.

mysqlbackup: INFO: Source Image Path = sbt:FULL_bkpsbtNB_180807113438

mysqlbackup: INFO: Creating 14 buffers each of size 65536.

180807 11:57:41 mysqlbackup: INFO: Apply-log operation starts with following threads

1 read-threads 1 process-threads

mysqlbackup: INFO: Using up to 100 MB of memory.

180807 11:57:41 mysqlbackup: INFO: ibbackup_logfile's creation parameters:

start lsn 1630720, end lsn 1630743,

start checkpoint 1630743.

InnoDB: Doing recovery: scanned up to log sequence number 1630743

mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

mysqlbackup: INFO: InnoDB: Setting log file size to 50331648

180807 11:57:42 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to

lsn 1630743.

180807 11:57:42 mysqlbackup: INFO: The first data file is '/var/lib/mysql/ibdata1'

and the new created log files are at '/var/lib/mysql/'

180807 11:57:42 mysqlbackup: INFO: Apply-log operation completed successfully.

180807 11:57:42 mysqlbackup: INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings

5、确认并修改权限：

6、更改用户及所属组并启动mysql服务：

7、登录数据库并进行数据校验、业务测试等。

八、增量恢复测试

1、恢复操作：

/opt/mysql/meb-3.12/bin/mysqlbackup --datadir=/var/lib/mysql/ --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --incremental-backup-dir=/mysql_restore --backup-image=sbt:INC_bkpsbtNB_180807113830 --incremental copy-back-and-apply-log

注1：--datadir参数是否必须根据MEB版本有所不同

注2：恢复完成后mysqld服务若无法正常启动首先确认数据文件用户及所属组属性设置是否正确（通过chown mysql:mysql xxx 修改）

2、命令运行后会在NBU JAVA CONSOLE的“Activity Monitor”界面中看到恢复作业已在运行中；

3、恢复输出仅供参考：

# /opt/mysql/meb-3.12/bin/mysqlbackup --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --incremental-backup-dir=/mysql_restore --backup-image=sbt:INC_bkpsbtNB_180807113830 --incremental copy-back-and-apply-log

MySQL Enterprise Backup version 3.12.3 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2016/05/05]

mysqlbackup: INFO: Starting with following command line ...

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

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--incremental-backup-dir=/tmp/mysql_restore

--backup-image=sbt:INC_bkpsbtNB_180807113830 --incremental

copy-back-and-apply-log

mysqlbackup: INFO:

mysqlbackup: ERROR: Missing or invalid option:

Need --datadir for incremental copy-back-and-apply-log.

Specified Operation:copy-back-and-apply-log

Use --help option for usage description.

mysqlbackup failed with errors!

[root@backup scripts]# /opt/mysql/meb-3.12/bin/mysqlbackup --datadir=/var/lib/mysql/ --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --incremental-backup-dir=/tmp/mysql_restore --backup-image=sbt:INC_bkpsbtNB_180807113830 --incremental copy-back-and-apply-log

MySQL Enterprise Backup version 3.12.3 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2016/05/05]

mysqlbackup: INFO: Starting with following command line ...

/opt/mysql/meb-3.12/bin/mysqlbackup --datadir=/var/lib/mysql/

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--incremental-backup-dir=/tmp/mysql_restore

--backup-image=sbt:INC_bkpsbtNB_180807113830 --incremental

copy-back-and-apply-log

mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 12:13:48 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 12:13:48 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

mysqlbackup: INFO: Backup Image MEB version string: 3.12.3 [2016/05/05]

mysqlbackup: INFO: The input backup image contains incremental backup.

180807 12:13:53 mysqlbackup: INFO: MEB logfile created at /tmp/mysql_restore/meta/MEB_2018-08-07.12-13-53_copy_back_inc_img_to_datadir.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir = /var/lib/mysql/

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = Null

innodb_checksum_algorithm = innodb

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir = /var/lib/mysql/

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = Null

innodb_checksum_algorithm = innodb

mysqlbackup: INFO: Creating 15 buffers each of size 16908288.

180807 12:13:53 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 12:13:53 mysqlbackup: INFO: Deleting non-innodb files in datadir...

180807 12:13:53 mysqlbackup: INFO: Deleting the database directory /var/lib/mysql/performance_schema

180807 12:13:53 mysqlbackup: INFO: Applying diff pages from `datadir/ibdata1` to `/var/lib/mysql/ibdata1`.

180807 12:13:53 mysqlbackup: INFO: Applying diff pages from `datadir/mysql/innodb_index_stats.ibd` to `/var/lib/mysql/mysql/innodb_index_stats.ibd`.

180807 12:13:53 mysqlbackup: INFO: Applying diff pages from `datadir/mysql/innodb_table_stats.ibd` to `/var/lib/mysql/mysql/innodb_table_stats.ibd`.

180807 12:13:53 mysqlbackup: INFO: Applying diff pages from `datadir/test/test.ibd` to `/var/lib/mysql/test/test.ibd`.

180807 12:13:53 mysqlbackup: INFO: Total files as specified in image: 155

180807 12:13:54 mysqlbackup: INFO: meb_sbt_restore_close: blocks: 3 size: 1048576 bytes: 2474554

180807 12:13:54 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql/

180807 12:13:54 mysqlbackup: INFO: Apply-incremental-pages operation completed successfully.

mysqlbackup: INFO: Source Image Path = sbt:INC_bkpsbtNB_180807113830

180807 12:13:54 mysqlbackup: INFO: Starting deletion of obsolete ibd files in datadir...

180807 12:13:54 mysqlbackup: INFO: Deleting dropped ibd files in datadir.

180807 12:13:54 mysqlbackup: INFO: Deletion of obsolete ibd files in backup-dir is completed.

mysqlbackup: INFO: Creating 14 buffers each of size 65536.

180807 12:13:54 mysqlbackup: INFO: Apply-log operation starts with following threads

1 read-threads 1 process-threads

mysqlbackup: INFO: Using up to 100 MB of memory.

180807 12:13:54 mysqlbackup: INFO: ibbackup_logfile's creation parameters:

start lsn 1692672, end lsn 1692774,

start checkpoint 1692774.

InnoDB: Doing recovery: scanned up to log sequence number 1692774

mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...

mysqlbackup: INFO: InnoDB: Setting log file size to 50331648

180807 12:13:55 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to

lsn 1692774.

180807 12:13:55 mysqlbackup: INFO: The first data file is '/var/lib/mysql/ibdata1'

and the new created log files are at '/var/lib/mysql/'

180807 12:13:55 mysqlbackup: INFO: Apply-log operation completed successfully.

180807 12:13:55 mysqlbackup: INFO: Incremental backup applied successfully.

mysqlbackup completed OK! with 3 warnings

4、登录数据库并进行数据校验、业务测试等。

九、使用TTS备份9.1 TTS选项简介

若后期需要从备份中恢复某些表（即表级别恢复），那么在备份时必须要以transportable tablespaces (TTS) 方式备份，否则无法仅恢复指定表。

transportable tablespaces (TTS) 主要使用两个参数（ --include-tables 和 --exclude-tables）来指定或排除表（后面值可使用正则表达式），运行机制是先执行include-tables后在执行exclude-tables，两个参数可同时使用。

例1：备份所有数据库中的所有表：

--include-tables="." 或 --include-tables=".*"

例2：备份test数据库中的所有表：

--include-tables="test\."

例3：备份“test”数据库中的所有表，但不包括名称为“test2”的表：

--include-tables="test\." --exclude-tables="test\.test2"

9.2 使用TTS选项备份

本次测试采用TTS选项多所有数据库中所有表进行备份操作：

# /opt/mysql/meb-4.1/bin/mysqlbackup --port=3306 --user=mysqlbkuser --password=P@ssw0rd --host=127.0.0.1 --use-tts --include-tables=”.” --backup-image=sbt:bkpsbtNB_UseTTS --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 –sbt environment="NB_ORA_SERV=backup,NB_ORA_CLIENT=rhel6,NB_ORA_POLICY=mysql_test" --backup-dir=/mysql_backup backup-to-image

MySQL Enterprise Backup version 4.1.2 Linux-4.1.12-37.4.1.el6uek.x86_64-x86_64 [2018/07/23 14:01:56]

[Build ID : 13401143.32e59573fc6d0520d268d395dda7c9fd8b5427f5]

180807 14:35:21 MAIN INFO: A thread created with Id '140004834608928'

180807 14:35:21 MAIN INFO: Starting with following command line ...

/opt/mysql/meb-4.1/bin/mysqlbackup --port=3306 --user=backupuser

--password=xxxxxxxx --host=127.0.0.1 --use-tts --include-tables=.*

--backup-image=sbt:bkpsbtNB_usetts

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--sbt-environment=NB_ORA_SERV=backup,NB_ORA_CLIENT=rhel6,NB_ORA_POLICY=test1

--backup-dir=/mysql_backup backup-to-image

180807 14:35:21 MAIN INFO:

180807 14:35:22 MAIN INFO: Not using SSL.

180807 14:35:22 MAIN INFO: MySQL server version is '5.7.23'

180807 14:35:22 MAIN INFO: MySQL server compile os version is 'Linux'

180807 14:35:22 MAIN INFO: Got some server configuration information from running server.

180807 14:35:22 MAIN INFO: MySQL query 'SHOW ENGINE INNODB STATUS': 1227, Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

180807 14:35:22 MAIN INFO: Cannot get InnoDB redo log flush status

180807 14:35:22 MAIN INFO: Server system variable 'old_alter_table' was set to '0'. Setting it to '1'.

180807 14:35:22 MAIN INFO: sbt-environment: 'NB_ORA_SERV=backup,NB_ORA_CLIENT=rhel6,NB_ORA_POLICY=test1'

180807 14:35:22 MAIN INFO: Skipping binlogs and relaylogs in case of TTS backup.

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'backup-to-image' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 14:35:22 MAIN INFO: MEB logfile created at /mysql_backup/meta/MEB_2018-08-07.14-35-22_image_backup.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir =

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_undo_directory = /var/lib/mysql/

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

innodb_buffer_pool_filename = ib_buffer_pool

innodb_page_size = 16384

innodb_checksum_algorithm = crc32

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /mysql_backup/datadir

innodb_data_home_dir = /mysql_backup/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /mysql_backup/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_undo_directory = /mysql_backup/datadir

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

innodb_buffer_pool_filename = ib_buffer_pool

innodb_page_size = 16384

innodb_checksum_algorithm = crc32

Backup Image Path = bkpsbtNB_usetts

180807 14:35:22 MAIN INFO: Unique generated backup id for this is 15336237221874227

180807 14:35:24 MAIN INFO: Creating 14 buffers each of size 16777216.

180807 14:35:24 MAIN INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 14:35:24 MAIN INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

180807 14:35:29 MAIN INFO: Full Image Backup operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 14:35:29 MAIN INFO: Found checkpoint at lsn 1014735833.

180807 14:35:29 MAIN INFO: Starting log scan from lsn = 1014735360 at offset = 5468672 and checkpoint = 1014735833 in file /var/lib/mysql/ib_logfile0.

180807 14:35:29 PCR3 INFO: A thread created with Id '140004442052352'

180807 14:35:29 PCR4 INFO: A thread created with Id '140004431562496'

180807 14:35:29 PCR5 INFO: A thread created with Id '140004421072640'

180807 14:35:29 PCR6 INFO: A thread created with Id '140004410582784'

180807 14:35:29 WTR1 INFO: A thread created with Id '140004400092928'

180807 14:35:29 RLR1 INFO: A thread created with Id '140004389603072'

180807 14:35:29 RLW1 INFO: A thread created with Id '140004379113216'

180807 14:35:29 RLP1 INFO: A thread created with Id '140004368623360'

180807 14:35:29 PCR2 INFO: A thread created with Id '140004452542208'

180807 14:35:29 PCR1 INFO: A thread created with Id '140004463032064'

180807 14:35:29 RDR1 INFO: A thread created with Id '140004473521920'

180807 14:35:29 RDR1 INFO: Copying meta file /mysql_backup/backup-my.cnf.

180807 14:35:29 RDR1 INFO: Copying meta file /mysql_backup/meta/backup_create.xml.

180807 14:35:29 RDR1 INFO: System tablespace file format is Antelope.

180807 14:35:29 RDR1 INFO: Starting to copy all innodb files...

180807 14:35:29 RDR1 INFO: Copying /var/lib/mysql/mysql/backup_history.ibd (Barracuda file format).

180807 14:35:29 RDR1 INFO: Copying /var/lib/mysql/mysql/engine_cost.ibd (Barracuda file format).

180807 14:35:29 RDR1 INFO: Copying /var/lib/mysql/mysql/gtid_executed.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/help_category.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/help_keyword.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/help_relation.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/help_topic.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/plugin.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/server_cost.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/servers.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_leap_second.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_name.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_transition.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_transition_type.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/sys/sys_config.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Copying /var/lib/mysql/test/test.ibd (Barracuda file format).

180807 14:35:30 RDR1 INFO: Completing the copy of innodb files.

180807 14:35:30 RDR1 INFO: Preparing to lock tables: Connected to mysqld server.

180807 14:35:30 RDR1 INFO: Starting to lock all the tables...

180807 14:35:30 RDR1 INFO: All tables are locked and flushed to disk

180807 14:35:30 RDR1 INFO: Completed the copy of binlog files...

180807 14:35:30 RDR1 INFO: Opening backup source directory '/var/lib/mysql'

180807 14:35:30 RDR1 INFO: Starting to backup .frm files in the subdirectories of /var/lib/mysql

180807 14:35:30 RDR1 INFO: Copying the database directory 'mysql'

180807 14:35:30 RDR1 INFO: Copying the database directory 'sys'

180807 14:35:30 RDR1 INFO: Copying the database directory 'test'

180807 14:35:30 RDR1 INFO: Completing the copy of all non-innodb files.

180807 14:35:30 RDR1 INFO: Requesting completion of redo log copy. Rapid: 0

180807 14:35:30 RLR1 INFO: Signaled '1' to complete log copy.

180807 14:35:30 RLR1 INFO: Redo log reader waited = 310.00 ms for logs to generate.

180807 14:35:30 RLW1 INFO: A copied database page was modified at 1014735643.

(This is the highest lsn found on page)

Scanned log up to lsn 1014735842.

Was able to parse the log up to lsn 1014735842.

180807 14:35:31 RDR1 INFO: All tables unlocked

180807 14:35:31 RDR1 INFO: All MySQL tables were locked for 1.074 seconds.

180807 14:35:31 RDR1 INFO: Setting server system variable 'old_alter_table' back to '0'.

180807 14:35:31 RDR1 INFO: Reading all global variables from the server.

180807 14:35:31 RDR1 INFO: Completed reading of all global variables from the server.

180807 14:35:31 RDR1 INFO: Writing config file for server '5.7.23'.

180807 14:35:31 RDR1 INFO: Creating server config files server-my.cnf and server-all.cnf in /mysql_backup

180807 14:35:31 RDR1 INFO: Copying meta file /mysql_backup/meta/backup_variables.txt.

180807 14:35:31 RDR1 INFO: Copying meta file /mysql_backup/datadir/ibbackup_logfile.

180807 14:35:31 RDR1 INFO: Copying meta file /mysql_backup/server-all.cnf.

180807 14:35:31 RDR1 INFO: Copying meta file /mysql_backup/server-my.cnf.

180807 14:35:31 RDR1 INFO: Copying meta file /mysql_backup/meta/backup_content.xml.

180807 14:35:31 RDR1 INFO: Copying meta file /mysql_backup/meta/image_files.xml.

180807 14:35:32 MAIN INFO: backup_file_name : bkpsbtNB_usetts

180807 14:35:32 MAIN INFO: volume_label : /nbubak//rhel6_1533623726_C1_F1

180807 14:35:32 MAIN INFO: meb_sbt_backup_close: blocks: 12 size: 1048576 bytes: 12131897

180807 14:35:32 MAIN INFO: Full Image Backup operation completed successfully.

180807 14:35:32 MAIN INFO: Backup image created successfully.

180807 14:35:32 MAIN INFO: Image Path = sbt:bkpsbtNB_usetts

-------------------------------------------------------------

Parameters Summary

-------------------------------------------------------------

Start LSN : 1014735360

End LSN : 1014735842

-------------------------------------------------------------

mysqlbackup completed OK!

备注：执行命令后在NBU JAVA CONSOLE中“Activity Monitor”页面会有作业生成且为Active状态

十、使用TTS恢复10.1 必要条件

使用—use-tts选项恢复时需注意：

ü MySQL Server须处于运行状态

ü 目标端须使用相同的page size

ü innodb_file_per_table选项须开启（值=ON）

ü 需要恢复的表在目标端不存在

ü MEB各版本稍有不同，4.0之前版本必须指定—datadir参数

10.2 删除源表恢复

1、在test数据库中删除test表进行恢复操作：

mysql> use test;

mysql> drop table test;

2、恢复test数据库中的test表：

# /opt/mysql/meb-4.1/bin/mysqlbackup --port=3306 --user=mysqlbkuser --password=P@ssw0rd --host=127.0.0.1 --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --backup-dir=/mysql_restore --backup-image=sbt:bkpsbtNB_UseTTS --include-tables=”test\.test” copy-back-and-apply-log

MySQL Enterprise Backup version 4.1.2 Linux-4.1.12-37.4.1.el6uek.x86_64-x86_64 [2018/07/23 14:01:56]

[Build ID : 13401143.32e59573fc6d0520d268d395dda7c9fd8b5427f5]

180807 14:47:33 MAIN INFO: A thread created with Id '140529244796704'

180807 14:47:33 MAIN INFO: Starting with following command line ...

/opt/mysql/meb-4.1/bin/mysqlbackup --port=3306 --user=mysqlbkuser

--password=xxxxxxxx --host=127.0.0.1

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--backup-dir=/mysql_restore --backup-image=sbt:bkpsbtNB_UseTTS

--include-tables=test.test copy-back-and-apply-log

180807 14:47:33 MAIN INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 14:47:33 MAIN INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 14:47:33 MAIN INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

180807 14:47:37 MAIN INFO: Backup Image MEB version string: 4.1.2 [2018/07/23 14:01:56]

180807 14:47:37 MAIN INFO: The input backup image has partial backup.

180807 14:47:37 MAIN INFO: The backup image contains tts backup with minimum locking mode.

180807 14:47:37 MAIN INFO: MySQL server version is '5.7.23'

180807 14:47:37 MAIN INFO: Not using SSL.

180807 14:47:37 MAIN INFO: MySQL server version is '5.7.23'

180807 14:47:37 MAIN INFO: MySQL server compile os version is 'Linux'

180807 14:47:37 MAIN INFO: Got some server configuration information from running server.

180807 14:47:37 MAIN INFO: MEB logfile created at /mysql_restore/meta/MEB_2018-08-07.14-47-37_copy_back_img_to_datadir.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir =

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = ./

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_undo_directory = ./

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

innodb_buffer_pool_filename = ib_buffer_pool

innodb_page_size = 16384

innodb_checksum_algorithm = crc32

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /mysql_restore/datadir

innodb_data_home_dir = /mysql_restore/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /mysql_restore/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_buffer_pool_filename = ib_buffer_pool

innodb_page_size = 16384

innodb_checksum_algorithm = crc32

180807 14:47:37 MAIN INFO: Creating 14 buffers each of size 16777216.

180807 14:47:37 MAIN INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 14:47:37 PCR1 INFO: A thread created with Id '140528893241088'

180807 14:47:37 PCR2 INFO: A thread created with Id '140528882751232'

180807 14:47:37 PCR3 INFO: A thread created with Id '140528872261376'

180807 14:47:37 PCR4 INFO: A thread created with Id '140528861771520'

180807 14:47:37 PCR5 INFO: A thread created with Id '140528851281664'

180807 14:47:37 PCR6 INFO: A thread created with Id '140528840791808'

180807 14:47:37 WTR1 INFO: A thread created with Id '140528830301952'

180807 14:47:37 RDR1 INFO: A thread created with Id '140528903730944'

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/backup_history.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/engine_cost.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/gtid_executed.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/help_category.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/help_keyword.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/help_relation.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/help_topic.ibd.

180807 14:47:37 PCR1 INFO: Extract: [Dir]: /mysql_restore/meta

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/innodb_index_stats.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/innodb_table_stats.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/plugin.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/server_cost.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/servers.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/slave_master_info.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/slave_relay_log_info.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/slave_worker_info.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/time_zone.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/time_zone_leap_second.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/time_zone_name.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/time_zone_transition.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/mysql/time_zone_transition_type.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/sys/sys_config.ibd.

180807 14:47:37 RDR1 INFO: Copying datadir/test/test.ibd.

180807 14:47:37 RDR1 INFO:

Importing tables

180807 14:47:38 MAIN INFO: Total files as specified in image: 97

180807 14:47:39 MAIN INFO: meb_sbt_restore_close: blocks: 12 size: 1048576 bytes: 12131897

180807 14:47:39 MAIN INFO: Extracting from image operation completed successfully.

180807 14:47:39 MAIN INFO: Source Image Path = sbt:bkpsbtNB_usetts

180807 14:47:39 MAIN INFO: MySQL server version is '5.7.23'

180807 14:47:39 MAIN INFO: Restoring ...5.7.23 version

180807 14:47:39 MAIN INFO: Creating 14 buffers each of size 65536.

180807 14:47:39 MAIN INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 1 process-threads 6 apply-threads

180807 14:47:39 MAIN INFO: Using up to 100 MB of memory.

180807 14:47:39 MAIN INFO: ibbackup_logfile's creation parameters:

start lsn 1014735360, end lsn 1014735842,

start checkpoint 1014735833.

180807 14:47:39 MAIN INFO: Backup was originally taken with the --include regexp option

180807 14:47:39 ALW4 INFO: A thread created with Id '140529138632448'

180807 14:47:39 ALW5 INFO: A thread created with Id '140529128142592'

180807 14:47:39 ALW6 INFO: A thread created with Id '140529117652736'

180807 14:47:39 RDR1 INFO: A thread created with Id '140529107162880'

180807 14:47:39 PCR1 INFO: A thread created with Id '140529096673024'

180807 14:47:39 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 1014735842.

180807 14:47:39 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 180807 14:47:39 ALW3 INFO: A thread created with Id '140528851281664'

32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 180807 14:47:39 ALW2 INFO: A thread created with Id '140528840791808'

59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 180807 14:47:39 ALW1 INFO: A thread created with Id '140528830301952'

80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

180807 14:47:39 PCR1 INFO: We were able to parse ibbackup_logfile up to

lsn 1014735842.

180807 14:47:39 MAIN INFO: Apply-log operation completed successfully.

180807 14:47:39 MAIN INFO: Creating 14 buffers each of size 16777216.

180807 14:47:39 MAIN INFO: Copy-back operation starts with following threads

1 read-threads 1 write-threads

180807 14:47:39 MAIN INFO: Starting to copy all innodb files...

180807 14:47:39 MAIN INFO: Creating table: `test`.`test`.

180807 14:47:39 WTR1 INFO: A thread created with Id '140529107162880'

180807 14:47:39 RDR1 INFO: A thread created with Id '140529096673024'

180807 14:47:39 RDR1 INFO: Copying /mysql_restore/datadir/test/test.ibd.

180807 14:47:39 RDR1 INFO: Could not find binlog index file. binlogs will not be copied for this backup.

Point-In-Time-Recovery will not be possible.

If this is online backup then server may not have started with --log-bin.

You may specify its location with --log-bin-index option.

180807 14:47:39 RDR1 INFO: Completed the copy of binlog files...

180807 14:47:39 RDR1 INFO: Completing the copy of all non-innodb files.

180807 14:47:39 RDR1 INFO: Importing table: test.test.

180807 14:47:39 RDR1 ERROR: MySQL query 'ALTER TABLE `test`.`test` IMPORT TABLESPACE': 1815, Internal error: Cannot reset LSNs in table `test`.`test` : Tablespace not found

180807 14:47:39 RDR1 ERROR: Failed to import tablespace test.test.

mysqlbackup failed with errors!

备1：最后一步报错是由于权限问题引起，详见后面操作。

备2：执行命令后在NBU JAVA CONSOLE中“Activity Monitor”页面可看到Restore作业运行：

3、进入MySQL datadir目录查看test文件已恢复，但是权限（所属用户组）不正确：

[root@rhel6 test]# pwd

/var/lib/mysql/test

[root@rhel6 test]# ls -ltr

total 116

-rw-r--r-- 1 mysql mysql 65 Aug 6 20:38 db.opt

-rw-r----- 1 mysql mysql 8578 Aug 7 14:47 test.frm

-rw-r--r-- 1 root root 98304 Aug 7 14:47 test.ibd

-rw-r--r-- 1 root root 404 Aug 7 14:47 test.cfg

[root@rhel6 test]# chown mysql:mysql test*

[root@rhel6 test]# ls -ltr

total 116

-rw-r--r-- 1 mysql mysql 65 Aug 6 20:38 db.opt

-rw-r----- 1 mysql mysql 8578 Aug 7 14:47 test.frm

-rw-r--r-- 1 mysql mysql 98304 Aug 7 14:47 test.ibd

-rw-r--r-- 1 mysql mysql 404 Aug 7 14:47 test.cfg

4、登录MySQL进行import操作并确认是否正确：

[root@rhel6 test]# mysql -uroot -pP@ssw0rd

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed, 1 warning

mysql> alter table test import tablespace;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from test;

+-----+------+

| a | b |

+------+-----+

| 1 | 1 |

| 2 | 2 |

+------+-----+

2 rows in set (0.00 sec)

10.3 重命名恢复

在恢复时亦可使用“--rename”选项对指定表进行重命名

可以使用完全限定表明：源数据库名.源表名 to 新数据库名.新表名

若在同一个数据库中重命名表则可不使用完全限定：源表名 to 新表名

1、恢复test数据库中的test表并重命名为test2：

MySQL Enterprise Backup version 4.1.2 Linux-4.1.12-37.4.1.el6uek.x86_64-x86_64 [2018/07/23 14:01:56]

[Build ID : 13401143.32e59573fc6d0520d268d395dda7c9fd8b5427f5]

180807 15:05:31 MAIN INFO: A thread created with Id '139864903730976'

180807 15:05:31 MAIN INFO: Starting with following command line ...

/opt/mysql/meb-4.1/bin/mysqlbackup --port=3306 --user=backupuser

--password=xxxxxxxx --host=127.0.0.1

--sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64

--backup-dir=/mysql_restore --backup-image=sbt:bkpsbtNB_usetts

--include-tables=test.test --rename=test.test to test.test2

copy-back-and-apply-log

180807 15:05:31 MAIN INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

prints "mysqlbackup completed OK!".

180807 15:05:31 MAIN INFO: meb_sbt_session_open: MMS version '5.0.0.0'

180807 15:05:31 MAIN INFO: meb_sbt_session_open: MMS is 'Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)'

180807 15:05:34 MAIN INFO: Backup Image MEB version string: 4.1.2 [2018/07/23 14:01:56]

180807 15:05:34 MAIN INFO: The input backup image has partial backup.

180807 15:05:34 MAIN INFO: The backup image contains tts backup with minimum locking mode.

180807 15:05:34 MAIN INFO: MySQL server version is '5.7.23'

180807 15:05:34 MAIN INFO: Not using SSL.

180807 15:05:34 MAIN INFO: MySQL server version is '5.7.23'

180807 15:05:34 MAIN INFO: MySQL server compile os version is 'Linux'

180807 15:05:34 MAIN INFO: Got some server configuration information from running server.

180807 15:05:34 MAIN INFO: MEB logfile created at /mysql_restore/meta/MEB_2018-08-07.15-05-34_copy_back_img_to_datadir.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /var/lib/mysql/

innodb_data_home_dir =

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = ./

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_undo_directory = ./

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

innodb_buffer_pool_filename = ib_buffer_pool

innodb_page_size = 16384

innodb_checksum_algorithm = crc32

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /mysql_restore/datadir

innodb_data_home_dir = /mysql_restore/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /mysql_restore/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_buffer_pool_filename = ib_buffer_pool

innodb_page_size = 16384

innodb_checksum_algorithm = crc32

180807 15:05:34 MAIN INFO: Creating 14 buffers each of size 16777216.

180807 15:05:34 MAIN INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

180807 15:05:34 PCR1 INFO: A thread created with Id '139864552175360'

180807 15:05:34 PCR2 INFO: A thread created with Id '139864541685504'

180807 15:05:34 PCR3 INFO: A thread created with Id '139864531195648'

180807 15:05:34 PCR4 INFO: A thread created with Id '139864520705792'

180807 15:05:34 PCR5 INFO: A thread created with Id '139864510215936'

180807 15:05:34 PCR6 INFO: A thread created with Id '139864499726080'

180807 15:05:34 WTR1 INFO: A thread created with Id '139864489236224'

180807 15:05:34 RDR1 INFO: A thread created with Id '139864562665216'

180807 15:05:34 PCR1 INFO: Extract: [Dir]: /mysql_restore/meta

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/backup_history.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/engine_cost.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/gtid_executed.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/help_category.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/help_keyword.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/help_relation.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/help_topic.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/innodb_index_stats.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/innodb_table_stats.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/plugin.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/server_cost.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/servers.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/slave_master_info.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/slave_relay_log_info.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/slave_worker_info.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/time_zone.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/time_zone_leap_second.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/time_zone_name.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/time_zone_transition.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/mysql/time_zone_transition_type.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/sys/sys_config.ibd.

180807 15:05:34 RDR1 INFO: Copying datadir/test/test.ibd.

180807 15:05:34 RDR1 INFO:

Importing tables

180807 15:05:34 MAIN INFO: Total files as specified in image: 97

180807 15:05:35 MAIN INFO: meb_sbt_restore_close: blocks: 12 size: 1048576 bytes: 12131897

180807 15:05:35 MAIN INFO: Extracting from image operation completed successfully.

180807 15:05:35 MAIN INFO: Source Image Path = sbt:bkpsbtNB_usetts

180807 15:05:35 MAIN INFO: MySQL server version is '5.7.23'

180807 15:05:35 MAIN INFO: Restoring ...5.7.23 version

180807 15:05:35 MAIN INFO: Creating 14 buffers each of size 65536.

180807 15:05:35 MAIN INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 1 process-threads 6 apply-threads

180807 15:05:35 MAIN INFO: Using up to 100 MB of memory.

180807 15:05:35 MAIN INFO: ibbackup_logfile's creation parameters:

start lsn 1014735360, end lsn 1014735842,

start checkpoint 1014735833.

180807 15:05:35 MAIN INFO: Backup was originally taken with the --include regexp option

180807 15:05:35 ALW4 INFO: A thread created with Id '139864797566720'

180807 15:05:35 ALW5 INFO: A thread created with Id '139864787076864'

180807 15:05:35 ALW6 INFO: A thread created with Id '139864776587008'

180807 15:05:35 RDR1 INFO: A thread created with Id '139864766097152'

180807 15:05:35 PCR1 INFO: A thread created with Id '139864755607296'

180807 15:05:35 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 1014735842.

180807 15:05:35 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 180807 15:05:35 ALW3 INFO: A thread created with Id '139864510215936'

180807 15:05:35 ALW2 INFO: A thread created with Id '139864499726080'

180807 15:05:35 ALW1 INFO: A thread created with Id '139864489236224'

56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

180807 15:05:35 PCR1 INFO: We were able to parse ibbackup_logfile up to

lsn 1014735842.

180807 15:05:35 MAIN INFO: Apply-log operation completed successfully.

180807 15:05:35 MAIN INFO: Creating 14 buffers each of size 16777216.

180807 15:05:35 MAIN INFO: Copy-back operation starts with following threads

1 read-threads 1 write-threads

180807 15:05:35 MAIN INFO: Starting to copy all innodb files...

180807 15:05:35 MAIN INFO: Creating table: `test`.`test`.

180807 15:05:35 WTR1 INFO: A thread created with Id '139864766097152'

180807 15:05:35 RDR1 INFO: A thread created with Id '139864755607296'

180807 15:05:35 RDR1 INFO: Copying /mysql_restore/datadir/test/test.ibd.

180807 15:05:35 RDR1 INFO: Could not find binlog index file. binlogs will not be copied for this backup.

Point-In-Time-Recovery will not be possible.

If this is online backup then server may not have started with --log-bin.

You may specify its location with --log-bin-index option.

180807 15:05:35 RDR1 INFO: Completed the copy of binlog files...

180807 15:05:35 RDR1 INFO: Completing the copy of all non-innodb files.

180807 15:05:35 RDR1 INFO: Importing table: test.test and renaming it to test.test2.

180807 15:05:35 RDR1 ERROR: MySQL query 'ALTER TABLE `test`.`test2` IMPORT TABLESPACE': 1815, Internal error: Cannot reset LSNs in table `test`.`test2` : Tablespace not found

180807 15:05:35 RDR1 ERROR: Failed to import tablespace test.test2.

mysqlbackup failed with errors!

备1：最后一步报错是由于权限问题引起，详见后面操作。

备2：执行命令后在NBU JAVA CONSOLE中“Activity Monitor”页面可看到Restore作业运行：

2、进入MySQL datadir目录查看test2数据文件已生成，但是权限（所属用户组）不正确：

[root@rhel6 test]# pwd

/var/lib/mysql/test

[root@rhel6 test]# ls -ltr

total 228

-rw-r--r-- 1 mysql mysql 65 Aug 6 20:38 db.opt

-rw-r----- 1 mysql mysql 8578 Aug 7 14:47 test.frm

-rw-r--r-- 1 mysql mysql 404 Aug 7 14:47 test.cfg

-rw-r--r-- 1 mysql mysql 98304 Aug 7 14:50 test.ibd

-rw-r----- 1 mysql mysql 8578 Aug 7 15:05 test2.frm

-rw-r--r-- 1 root root 98304 Aug 7 15:05 test2.ibd

-rw-r--r-- 1 root root 404 Aug 7 15:05 test2.cfg

[root@rhel6 test]# chown mysql:mysql test2*

[root@rhel6 test]# ls -ltr

total 228

-rw-r--r-- 1 mysql mysql 65 Aug 6 20:38 db.opt

-rw-r----- 1 mysql mysql 8578 Aug 7 14:47 test.frm

-rw-r--r-- 1 mysql mysql 404 Aug 7 14:47 test.cfg

-rw-r--r-- 1 mysql mysql 98304 Aug 7 14:50 test.ibd

-rw-r----- 1 mysql mysql 8578 Aug 7 15:05 test2.frm

-rw-r--r-- 1 mysql mysql 98304 Aug 7 15:05 test2.ibd

-rw-r--r-- 1 mysql mysql 404 Aug 7 15:05 test2.cfg

3、登录MySQL进行import操作并确认是否正确：

[root@rhel6 test]# mysql -uroot -pP@ssw0rd

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed, 1 warning

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| test |

| test2 |

+----------------+

2 rows in set (0.00 sec)

mysql> select * from test2;

ERROR 1814 (HY000): Tablespace has been discarded for table 'test2'

mysql> alter table test2 import tablespace;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from test2;

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 2 | 2 |

+------+------+

2 rows in set (0.00 sec)

本文地址：http://www.longkongtuishu.com/ca706BARsBFIFD1Jd.html

NetBackup结合MEB备份恢复MySQL

IT运维之道知多少？

NetBackup结合MEB备份恢复MySQL

Time Series DBMS 综述