概述

物理备份:二进制文件，不可编辑，数据库变化，数据库的一个副本（逻辑备份是SQL文件，可编辑）。

特点:

1)由数据文件和目录组成，是mysql数据目录的部拷贝/部分拷贝。

2〉速度快,文件复制

3) 备份粒度小

4）除了备份数据以外，还可以备份日志和配置文件

适合场景：最合适大数据量的备份，比如百GB,TB。

物理备份方法:

1) xtrabackup(percona公司开源免费的，早期对应的官方收费版本innobackup)

2) mysql enterprise backup(MEB/mysqlbackup),官方收费

一 mysqlbackup介绍与安装

mysql enterprise backup: 简称: MEB/mysqlbackup，官方，整个产品的思路类似于oracle的rman，功能可以对比成orqcle的expdp。

支持在线全备，增量，压缩，加密解密，并行备份,时间点还原。

1.1 mysqlbackup安装

mkdir meb4.1.1

unzip MEB4.1.zip

-rw-r--r-- 1 root root 3924881 4月 24 2018 meb-4.1.1-linux-glibc2.12-x86-64bit.tar.gz

-rw-r--r-- 1 root root 173 4月 25 2018 meb-4.1.1-linux-glibc2.12-x86-64bit.tar.gz.asc

-rw-r--r-- 1 root root 77 4月 25 2018 meb-4.1.1-linux-glibc2.12-x86-64bit.tar.gz.md5

-rw-r--r-- 1 root root 3929687 3月 15 23:01 MEB4.1.zip

-rw-r--r-- 1 root root 3942 4月 27 2018 README.txt

tar zxvf meb-4.1.1-linux-glibc2.12-x86-64bit.tar.gz

ln -sf meb-4.1.1-linux-glibc2.12-x86-64bit mysqlbakup

pwd

/mysql/app/meb4.1.1/mysqlbakup/bin

vim ~/.bash_profile

source ~/.bash_profile

mysqlbackup --version

MySQL Enterprise Backup version 4.1.1 Linux-4.1.12-37.4.1.el6uek.x86_64-x86_64 [2018/04/24 08:26:22]

1.2 建立备份用户

create user 'backup '@'localhost' identified by 'backup';

create user 'backup '@' localhost ' identified by 'backup';

grant reload,lock tables,process,replication client,super on *.* to 'backup'@' localhost';

grant create,select,insert,drop,update on mysql.backup_progress to 'backup'@' localhost';

grant create,select,insert,drop,update on mysql.backup_history to 'backup'@' localhost';

grant alter on mysql.backup_history to 'backup'@' localhost';

flush privileges;

1.3 建立目录

mkdir -p /mysql/backup/db-tmp

mkdir -p /mysql/backup/restore-tmp

chown -R mysql:mysql /mysql/backup

1.4 mysqlbackup备份原理：

同xtrabackup一样,mysqlbackup同样使用了3个步骤备份(--backup）》应用日志恢复一致性(--apply-log)>>复制(--copy-back)

1.mysqlbackdp对innodb表空间进行的是物理复制，记录了lsn点，在备份过程中新变化的数据是写到ibbackup_logfile，同时记录了最后的LSN点。

2.对myisam朝廷的是锁表全备

3.还原的时候,检查对比ibbackup_logfile文件里面的与表空间里面的差值，如果有差别，会应用ibbackup_logfile中的数据。

4.在备份期间同时会创建一个备份信息表mysql.backup_progress。

select * from mysql.backup_progress;

2021-03-17 22:20:56 | Started mysqlbackup.

5.在表很多的时候mysqlbackup比xtrabackup锁表的时间还短

6.物理备份的时候mysqlbackup必须要与数据库在同一个服务器。

二 mysqlbackup备份与恢复案例(image全备)

参数说明:

backup-to-image，使用了备份为image文件的方式，这是官方mysql推荐的方法，只产生一个备份的二进制文件。

backup-image，备份输出路径。

--with-timestamp，是根据时间戳生成一个文件，保存在一个空的目录中。

apply-log:因为在备份的时候是在线的,如果有新插入的SQL语句,会记录新增加的LSN点,然后新修改的页面会放到这个文件里面（ibbackup_logfile）,同时也会放到表空间里面.当还原使用这个参数的时候,mysqlbackup会检测ibbackup_logfile和表空间的LSN点,然后比较ibbackup_logfile文件表空间LSN的差值,把这个值放到事务日志LOG里面.(事务日志如果填满了,会进入表空间的)

2.1 image全备

备份报错，远程无法连接

Connection open fails with error "Access denied for user 'backup'@'localhost' (using password: YES)"

ALTER user 'backup'@'localhost' IDENTIFIED BY 'backup';

flush privileges;

grant all PRIVILEGES on *.* to backup@'%'identified by 'backup' WITH GRANT OPTION;

flush privileges;

backup 可以使用任何主机登录。

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup-image=/mysql/backup/alldb`date +%Y%m%d%H%M`.mbi --with-timestamp backup-to-image --backup-dir=/mysql/backup/db-tmp/

210317 22:21:18 MAIN INFO: MySQL binlog position: filename binlog.000088, position 7904

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

Parameters Summary

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

Start LSN : 177666048

End LSN : 177666153

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

mysqlbackup completed OK!

cat backup_variables.txt

# This file is auto generated by mysqlbackup.

[backup_variables]

apply_log_done=0

binlog_index=binlog.index

binlog_position=binlog.000088:7904

consistency_time_utc=1615990878461335

end_lsn=177666153

end_time_utc=1615990878468868

has_tde_tables=0

is_compressed=0

is_incremental=0

is_incremental_with_redo_log_only=0

is_onlyinnodb=0

is_partial=0

is_skip_unused_pages=0

meb_version=4.1.1

mysql_version=5.7.32-log

mysql_version_comment=MySQL Community Server (GPL)

start_lsn=177666048

start_time_utc=1615990874344230

2.2 image有效性检查

mysqlbackup --backup-image=/mysql/backup/alldb202103172221.mbi validate

210317 22:41:18 MAIN INFO: Validate operation completed successfully.

210317 22:41:18 MAIN INFO: Backup Image validation successful.

210317 22:41:18 MAIN INFO: Source Image Path = /mysql/backup/alldb202103172221.mbi

mysqlbackup completed OK!

2.3 image 备份内用list

mysqlbackup --backup-image=/mysql/backup/alldb202103172221.mbi list-image | more

2.4 破坏数据库

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

2.5 恢复数据

参数说明:

copy-back-and-apply-log:解压之后再恢复增量的log，使数据文件达到一致性，在COPY文件到数据目录。

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/alldb202103172221.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

210317 22:57:27 MAIN INFO: Backup Image MEB version string: 4.1.1 [2018/04/24 08:26:22]

210317 22:57:27 MAIN INFO: MySQL server version is '5.7.32'

210317 22:57:27 MAIN ERROR: 0 is not a valid innodb_checksum_algorithm.

210317 22:57:27 MAIN ERROR: Invalid innodb_checksum_algorithm name found.

解决方法：

My.cnf

innodb_checksum_algorithm=0

修改为

innodb_checksum_algorithm=crc32

210317 23:01:14 MAIN INFO: Apply-log operation completed successfully.

210317 23:01:14 MAIN INFO: Full Backup has been restored successfully.

mysqlbackup completed OK!

chown -R mysql:mysql /mysql/data/3306/data

三 mysqlbackup备份与恢复案例 (目录方式全备)

3.1 backup备份

1 目标备份

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --with-timestamp backup --backup-dir=/mysql/backup/alldb1 --read-threads=2 --write-threads=2 --process-threads=8

cat /mysql/backup/alldb1/2021-03-18_22-38-55/meta/backup_variables.txt

binlog_position=binlog.000089:154

start_lsn=177666560

start_time_utc=1616078335015300

2 验证完整性

mysqlbackup --backup-dir=/mysql/backup/alldb1/2021-03-18_22-38-55/ validate

mysqlbackup completed OK!

3 恢复-应用日志

参数说明:

apply-log:因为备份期间的数据库还在读写，把这期间产生的数据的log还在应用，达到数据的一致性。

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf apply-log --backup-dir=/mysql/backup/alldb1/2021-03-18_22-38-55/

210318 22:47:56 MAIN INFO: Apply-log operation completed successfully.

210318 22:47:56 MAIN INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

4 破坏数据库

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

5 恢复数据库

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-dir=/mysql/backup/alldb1/2021-03-18_22-38-55/ copy-back --read-threads=2 --write-threads=2 --process-threads=8

210318 22:52:45 MAIN ERROR: 0 is not a valid innodb_checksum_algorithm.

210318 22:52:45 MAIN ERROR: Invalid innodb_checksum_algorithm name found.

210318 22:52:45 MAIN INFO: No Keyring file to process.

mysqlbackup failed with errors!

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

### 最后一行插入

sed -i '1d' /mysql/data/3306/my.cnf

删除文档的第一行

sed -i '$d' /mysql/data/3306/my.cnf

删除文档的最后一行

mysqlbackup completed OK!

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

3.2 backup-and-apply-log备份

参数说明:

backup-and-apply-log:备份后并应用日志

backup-and-apply-log 这个参数,在备份的时候,就把事务日志检测的功能就完成了,并把ibbackup_logfile与表空间差值的LSN内容放入到了事务日志里面,所以在还原的时候,就只需要copy物理备份文件就行

1 目标备份

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --with-timestamp backup-and-apply-log --backup-dir=/mysql/backup/alldb1 --read-threads=2 --write-threads=2 --process-threads=8

cat /mysql/backup/alldb1/2021-03-18_22-38-55/meta/backup_variables.txt

binlog_position=binlog.000072:86373467

start_lsn=178906624

start_time_utc=1616082610768049

2 验证完整性

mysqlbackup --backup-dir=/mysql/backup/alldb1/2021-03-18_22-38-55/ validate

mysqlbackup completed OK!

3 破坏数据库

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

5 恢复数据库

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-dir=/mysql/backup/alldb1/2021-03-18_23-50-10/ copy-back --read-threads=2 --write-threads=2 --process-threads=8

210318 22:52:45 MAIN ERROR: 0 is not a valid innodb_checksum_algorithm.

210318 22:52:45 MAIN ERROR: Invalid innodb_checksum_algorithm name found.

210318 22:52:45 MAIN INFO: No Keyring file to process.

mysqlbackup failed with errors!

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

### 最后一行插入

sed -i '1d' /mysql/data/3306/my.cnf

删除文档的第一行

sed -i '$d' /mysql/data/3306/my.cnf

删除文档的最后一行

mysqlbackup completed OK!

chown -R mysql:mysql /mysql/data/3306/data

service mysql restart

mysql -uroot -proot -e "select count(*) from syj.gw"

| count(*) |

| 19 |

四 mysqlbackup备份与恢复-加密-增量

4.1 加密备份与恢复

1 设置秘钥

openssl rand -base64 24

f1nYxwDryAoNdEeULItU8tFczURiKc/0

echo "f1nYxwDryAoNdEeULItU8tFczURiKc/0" >>/mysql/script/keyfile

2 加密备份

mysqlbackup completed OK!

cat /mysql/backup/db-tmp/2021-03-21_14-07-30/meta/backup_variables.txt

binlog_position=binlog.000074:154

mysqlbackup --backup-image=/mysql/backup/alldb202103211407.mbi validate

mysqlbackup completed OK!

mysqlbackup --backup-image=/mysql/backup/alldb202103211407.mbi list-image | more

3 破坏数据

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

4 恢复数据

用KEY文件备份就必须KEY文件恢复，用密码备份就必须用密码恢复。

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --key-file=/mysql/script/keyfile --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/alldb202103211407.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

sed -i '$d' /mysql/data/3306/my.cnf

chown -R mysql:mysql /mysql/data/3306/data

service mysql restart

mysql -uroot -proot -e "select count(*) from syj.gw"

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --encrypt-password=” f1nYxwDryAoNdEeULItU8tFczURiKc/0” --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/alldb202103211407.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

4.2 增量备份与恢复

增量备份基本使用目录全备方式加增量备份

1 全备

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --with-timestamp backup --backup-dir=/mysql/backup/alldb`date +%Y%m%d%H%M` --read-threads=2 --write-threads=2 --process-threads=8

cat /mysql/backup/alldb202103211544/2021-03-21_15-44-21/meta/backup_variables.txt

binlog_position=binlog.000076:154

2 数据变化与累积增量

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syjdb` /*!40100 DEFAULT CHARACTER SET utf8 */

create table syjdb.m1db select * from syj.m1;

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup backup --with-timestamp --incremental --incremental-base=dir:/mysql/backup/alldb202103211611/2021-03-21_16-11-08/ --incremental-backup-dir=/mysql/backup/incdb`date +%Y%m%d%H%M` --read-threads=2 --write-threads=2 --process-threads=8;

create table syjdb.gwdb select * from syj.gw;

3 破坏数据库

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

4 恢复数据库

mysqlbackup --backup-dir=/mysql/backup/alldb202103211611/2021-03-21_16-11-08/ apply-log

mysqlbackup --backup-dir=/mysql/backup/alldb202103211611/2021-03-21_16-11-08/ --incremental-backup-dir=/mysql/backup/incdb202103211616/2021-03-21_16-16-27/ apply-incremental-backup

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --datadir=/mysql/data/3306/data --backup-dir=/mysql/backup/alldb202103211611/2021-03-21_16-11-08/ copy-back-and-apply-log --read-threads=2 --write-threads=2 --process-threads=8

sed -i '$d' /mysql/data/3306/my.cnf

chown -R mysql:mysql /mysql/data/3306/data

service mysql restart

mysql -uroot -proot -e "select count(*) from syj.gw"

五 mysqlbackup备份结合binlog增量恢复

5.1 恢复到最新binlog

cat db-tmp/2021-03-21_17-30-18/meta/backup_variables.txt

binlog_position=binlog.000080:154

show binlog events in 'binlog.000080';

show master status;

purge master logs to "binlog.000080";

purge master logs before "2021-03-20 01:00:00";

mysqlbinlog --base64-output=decode-rows -v -v /mysql/log/3306/binlog.000080

CREATE table syjdb.m5db select * from syj.m5;

show binlog events in 'binlog.000080';

binlog.000080 10237326 Xid 1313306 10237357 COMMIT /* xid=260 */

show binary logs;

1 破坏数据库

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

2恢复数据

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/alldb202103211745.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

sed -i '$d' /mysql/data/3306/my.cnf

chown -R mysql:mysql /mysql/data/3306/data

service mysql restart

3 使用binlog日志

mysqlbinlog /mysql/log/3306/binlog.000080 >80.sql

mysql -uroot -proot <80.sql

5.2 恢复到binlog时间点

1 备份以后的日志起点

cat 2021-03-21_17-59-18/meta/backup_variables.txt

binlog_position=binlog.000086:24067374

2 A时间修改

alter table syjdb.gwdb add yer int(6);

3 B时间修改

update syjdb.gwdb set MIN_SALARY=MIN_SALARY+40000;

select * from syjdb.gwdb;

4 删除数据库与恢复全备

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

echo innodb_checksum_algorithm=crc32 >>/mysql/data/3306/my.cnf

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/alldb202103211801.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

sed -i '$d' /mysql/data/3306/my.cnf

chown -R mysql:mysql /mysql/data/3306/data

service mysql restart

5 恢复到A操作点。

show binlog events in 'binlog.000086';

开始点

binlog_position=binlog.000086:24067374

结束点

binlog.000086 24068259 Query 1313306 24068330 BEGIN

24067374至24068259

start-position

stop-position

mysqlbinlog --start-position=24067374 --stop-position=24068259 /mysql/log/3306/binlog.000086 >86.sql

mysql -uroot -proot <86.sql

good

六 mysqlbackup参数介绍

6.1 常用参数

--backup-dir--backup-image

--backup-to-image

--compress

--compress-level

--uncompress

--datadir

--defaults-file

--force

--incremental

--incremental-backup-dir

--incremental-base

--with-timestamp

--user

--password

--database

6.2 简单说明

备份阶段: backup, backup-and-apply-log,backup-to-image

增量阶段: apply-log,apply-incremental-backup

恢复阶段: copy-back , copy-back-and-apply-log

校验操作: validate

image操作: image-to-backup-dir,backup-dir-to-image,list-image, extract

7 mysqlbackup自动化备份脚本

cat /mysql/script/backup-mysqlbackup-full.sh

#!/bin/bash

# script use mysqlbackup to Full backup mysql data per day!

BAKDIR=/mysql/backup

BAKTEMP=/mysql/backup/db-tmp

MEBBIN=/mysql/app/meb4.1.1/mysqlbakup/bin/mysqlbackup

BAKFILEPRE=fullbackup

BAKFILE=$BAKDIR/$BAKFILEPRE-`date +%Y%m%d%H%M`

LOGFILE=$BAKDIR/$BAKFILEPRE-`date +%Y%m%d%H%M`.log

REMOTE_HOST=192.168.247.131

REMOTE_BAKDIR=/mysql/backup

MYCNF=/mysql/data/3306/my.cnf

BAKMYCNF=$BAKDIR/my-`date +%Y%m%d%H%M`.cnf

USER=backup

PWD=backup

#--read-threads=2

#--write-threads=2

#--process-threads=8

echo "---------------------------------" > $LOGFILE

echo "mysqlbackup backup start......" >> $LOGFILE

echo "---------------------------------" >> $LOGFILE

/mysql/app/mysql/bin/mysqladmin -uroot -proot -h192.168.247.131 flush-logs

$MEBBIN --defaults-file=$MYCNF --user=$USER --password=$PWD --backup-image=$BAKFILE.mbi --with-timestamp backup-to-image --backup-dir=$BAKTEMP 2>> $LOGFILE

echo "---------------------------------" >> $LOGFILE

echo "mysqlbackup validate start....." >> $LOGFILE

echo "---------------------------------" >> $LOGFILE

$MEBBIN --backup-image=$BAKFILE.mbi validate 2>> $LOGFILE

gzip -c $BAKFILE.mbi > $BAKFILE.mbi.gz

cp $MYCNF $BAKMYCNF

# check backup log

CHECKOK=`tail -10 $LOGFILE | grep "mysqlbackup completed OK\!" | wc -l`

if [ $CHECKOK -ne 1 ]

then

echo "[ WARNING ] Backup failed!"

exit