本文共 21056 字，大约阅读时间需要 70 分钟。
MySQL逻辑备份恢复mysqldumper20210225
概述
逻辑备份:
备份内容:数据库的结构定义语句+数据内容的插入语句，备份出来的文件是可以编辑的。
适用场景:数据量少的数据库，比较适合100G数据量以内的。
逻辑备份的特点:
1) sql语句组成的文件,可以截取部分单独使用。
2)备份文件比物理的小。
3)可以细化到表/表的内容
4)速度慢
5)可以跨平台恢复/迁移
6）只能在线备份，在线恢复。
逻辑备份的工具:
1) mysqldump(单线程)、mysqlpump(多线程)∶官方MySQL
2) mydumper:开源的，基于mysqldump的一个优化，多线程，速度介于两者之间:
一 mydumper安装及介绍
开源的,第三方的
多线程,备份后会生成多个文件
备份时对myi sam表加ftwrl ( flush tables with read lock) ,会阻塞DML语句
备份数据一致性，支持文件压缩，支持导出binlog，支持多线程恢复。
1.1 mydumper安装 有问题安装
安装mydemper 需要mysql-devel，但是安装mysql-devel需要安装mysqlrmp,就会造成两个版本的mysql
mydumper --version
mydumper 0.9.1, built against MySQL 5.1.73
Fedora, RedHat and CentOS: yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel
yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake make -y
tar zxvf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1
cmake .
CMake Error: your CXX compiler: "CMAKE_CXX_COMPILER-NOTFOUND" was not found. Please set CMAKE_CXX_COMPILER to a valid compiler path or name.
yum install gcc-c++
make & make install
[100%] [100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Building C object CMakeFiles/myloader.dir/myloader.c.o
mydumper --version
mydumper 0.9.1, built against MySQL 5.1.73
有重复版本
[root@mysqldb mydumper-0.9.1]# mydumper --version
mydumper 0.9.1, built against MySQL 5.1.73
可能会有小问题
yum list mysql
已加载插件：refresh-packagekit, security, ulninfo
已安装的软件包
mysql.x86_64 5.1.73-8.0.1.el6_8
1.2 mydumper 安装正常
Fedora, RedHat and CentOS: yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel
yum install glib2-devel zlib-devel pcre-devel openssl-devel cmake make -y
tar zxvf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1
cmake .
CMake Error: your CXX compiler: "CMAKE_CXX_COMPILER-NOTFOUND" was not found. Please set CMAKE_CXX_COMPILER to a valid compiler path or name.
yum install gcc-c++
make & make install
[100%] [100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Building C object CMakeFiles/myloader.dir/myloader.c.o
cp -p /mysql/app/mysql-5.7.32-linux-glibc2.12-x86_64/lib/libmysqlclient.so.20 /usr/lib/libmysqlclient.so.20
ldconfig -v
mydumper -V
mydumper 0.9.1, built against MySQL 5.7.32
1.3 mydumper参数
Usage:
mydumper [OPTION...] multi-threaded MySQL dumping
Application Options:
-B, --database Database to dump
-T, --tables-list Comma delimited table list to dump (does not exclude regex option)
##备份的表，可以用分隔符
-o, --outputdir Directory to output files to
##输出目录
-s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
##生产1M一次的插入语句。
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
##生产的单个文件多少M
-c, --compress Compress output files
##压缩
-e, --build-empty-files Build dump files even if no data available from table
##空表也生产一个文件
-x, --regex Regular expression for 'db.table' matching
##备份表的简单方法，数据库.表明
-i, --ignore-engines Comma delimited list of storage engines to ignore
##那些引擎不备份
-m, --no-schemas Do not dump table schemas with the data
###不导出表的结构
-d, --no-data Do not dump table data
##不导出表的数据
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions
-k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
### 千万不能加，不加锁，会导致备份不一致
--less-locking Minimize locking time on InnoDB tables.
### 减少innodb的加锁时间
-l, --long-query-guard Set long query timer in seconds, default 60
-K, --kill-long-queries Kill long running queries (instead of aborting)
### 杀长事务
-D, --daemon Enable daemon mode
### 启动守护模式
-I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60
-L, --logfile Log file name to use, by default stdout is used
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
--skip-tz-utc
--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
--lock-all-tables Use LOCK TABLE for all, instead of FTWRL
-U, --updated-since Use Update_time to dump only tables updated in the last U days
--trx-consistency-only Transactional consistency only
-h, --host The host to connect to
-u, --user Username with privileges to run the dump
-p, --password User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression on the MySQL connection
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
##### 输出信息必须要
1.4 myloader 参数
Application Options:
-d, --directory Directory of the dump to import
## 指定恢复目录
-q, --queries-per-transaction Number of queries per transaction, default 1000
## 还原的时候，每个事务插入的数量，模式1000
-o, --overwrite-tables Drop tables if they already exist
## 表存在删除
-B, --database An alternative database to restore into
-s, --source-db Database to restore
-e, --enable-binlog Enable binary logging of the restore data
-h, --host The host to connect to
-u, --user Username with privileges to run the dump
-p, --password User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression on the MySQL connection
## 压缩协议
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
二 .mydumper常用备份案例
2.1 mydumper 压缩不带目录备份
mydumper -u root -p root -c -B syj
export-20210228-101419
不指定目录，会自动生成一个单日期的目录。
du -sm *
27 20210228syj
10 export-20210228-102609
三倍压缩
2.2 mydumper 不压缩带目录备份
[root@mysqldb backup]# mydumper -u root -p root -B syj -o /mysql/backup/20210228syj
[root@mysqldb backup]# ll
drwx------ 2 root root 4096 2月 28 10:24 20210228syj
2.3 备份所有数据库
mydumper -u root -p root -o /mysql/backup/20210228alldb
2.4 备份所有数据库（存储过程-函数-触发器-事件）
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions
mydumper -u root -p root -R -G -E -o /mysql/backup/20210228alldb
2.5 备份单表（不备份结构）
mydumper -u root -p root -B syj -T m5 -m -o /mysql/backup/20210228table
mydumper -u root -p root -T syj.m5 -m -o /mysql/backup/20210228table
2.6 备份多表
mydumper -u root -p root -B syj -T m5,gw -o /mysql/backup/20210228table
2.7 匹配制定数据库开头的所有表
mydumper -u root -p root --regex=syj -o /mysql/backup/20210228table
2.8 过滤不备份数据库
mydumper -u root -p root --regex='^(?!(sys|information_schema|mysql|performance_schema))' -o /mysql/backup/20210228table
2.9 设置长查询-备份的时候，长查询kill
mydumper -u root -p root -K --regex='^(?!(sys|information_schema|mysql|performance_schema))' -o /mysql/backup/20210228table
2.10 单表超过1G就分成1G一个文件
mydumper -u root -p root -K -B syj -F 1000 -o /mysql/backup/20210228table
955M syj.m5.00001.sql
955M syj.m5.00002.sql
955M syj.m5.00003.sql
955M syj.m5.00004.sql
955M syj.m5.00005.sql
862M syj.m5.00006.sql
4.0K syj.m5-schema.sql
2.11 设置多线程默认4线程
-t, --threads Number of threads to use, default 4
** Message: Thread 8 dumping data for `itpuxdb`.`dq`
生产系统CPU-2个线程
mydumper -u root -p root -K -t 8 -F 1000 -o /mysql/backup/20210228table
2.12 全库备份的时候二进制文件刷新点
cat metadata
Started dump at: 2021-02-28 15:47:31
SHOW MASTER STATUS:
Log: binlog.000059
Pos: 10237528
GTID:
Finished dump at: 2021-02-28 15:47:32
三 myloader恢复案例-多库-单库-单表-跨库
3.1 恢复多库
如果是另一台机器:需要提前安装mysql，初始化数据库，导入上面的数据，再加载权限信息。
mydumper -u root -p root --regex='^(?!(sys|information_schema|mysql|performance_schema))' -o /mysql/backup/20210228table
mysql> drop database syj;
mysql> drop database itpuxdb;
myloader -u root -p root -o -d /mysql/backup/20210228table/
-d, --directory Directory of the dump to import
指定待恢复的备份目录，注意，该目录必须是mydumper生成的，myloader会判断该目录下是否存在metadata文件；
-o, --overwrite-tables Drop tables if they already exist
3.2 恢复单库
-B 要还原到的备用数据库 是把所有的mydumper生产的SQL导入到制定的库。
myloader -u root -p root -B syj -o -d /mysql/backup/20210228table/
恢复单库只能用备份的单库文件，或者用mydumper生产的文件，把不需要的文件MV走。
mv itpuxdb* itpuxdb2012
myloader -u root -p root -B syj -o -d /mysql/backup/20210228table/
3.2.1 方法2
-d, --directory Directory of the dump to import
### 恢复目录
-B, --database An alternative database to restore into ## 还原目标数据库
-s, --source-db Database to restore ## 还原的数据库文件
-t, --threads Number of threads to use, default 4
### 并行度
myloader -u root -p root -t 6 -s syj -B syjdb -d /mysql/backup/20210228table/
3.3 恢复单表
drop tables sales;
Query OK, 0 rows affected (0.02 sec)
mysql -uroot -proot -D syj < syj.sales-schema.sql
mysql -uroot -proot -D syj < syj.sales.sql
3.4 跨库恢复
3.4.1 从全库备份文件中A库恢复到C库
mydumper -u root -p root --regex='^(?!(sys|information_schema|mysql|performance_schema))' -o /mysql/backup/20210228table
CREATE DATABASE `syjdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
myloader -u root -p root -t 6 -s syj -B syjdb -d /mysql/backup/20210228table/
-d, --directory Directory of the dump to import
### 恢复目录
-B, --database An alternative database to restore into ## 还原目标数据库
-s, --source-db Database to restore ## 还原的数据库文件
-t, --threads Number of threads to use, default 4
### 并行度
四 mydumper工作流程与原理
4.1 备份过程
解析参数> connect db > show processlist > flush tables with read lock > start transaction> create thread > dump database > dump non-innodb table>dump innodb table > dump schema > thread shutdown
4.2 恢复过程
01.解析参数
02.connect db
03.create thread
04.create db and create table
05.创建导入数据的任务并加入队列>>>导入数据的任务队列
06.创建对应的函数、过程、事件。
07.创建表视图和触发器。
五 mydumper生产环境自动化备份案例
5.1 备份脚本
#!/bin/bash
# script from www.itpux.com and fgedu,use mydumper to Full backup mysql data per day!
# 数据库分开目录备份
#set parameter
now_date=`date +%Y%m%d%H%M`
dir_date=`date +%Y%m%d`
MyCNF=/mysql/data/3306/my.cnf
mydumper=/usr/local/bin/mydumper #根据自己的实际情况设置
dirbackup=/mysql/backup
mysqlbin=/mysql/app/mysql/bin/mysql
dir_backup=$dirbackup/fullbackup$dir_date #根据自己的实际情况设置
OutLogFile=mysql_mydumper_backup_full.out #备份过程中输出的日志文件名,与crontab里面相同
mysql_host=192.168.247.131 #根据自己的实际情况设置
mysql_port=3306 #根据自己的实际情况设置
mysql_user=root #根据自己的实际情况设置
mysql_pass=root #根据自己的实际情况设置
echo "--------------------------------------------------------"
echo " 备份任务开始: `date +%F' '%T' '%w`"
echo "--------------------------------------------------------"
if [ ! -d $dir_backup ]; then
echo -e "\e[1;31m 保存备份的主目录:$dir_backup不存在,将自动新建. \e[0m"
mkdir -p ${dir_backup}
fi
fgedudate_01="fgedudate01.`date +%y%m%d%h%m%s`.txt"
schema_fgedu01="select schema_name from information_schema.schemata ;"
$mysqlbin -h${mysql_host} -P${mysql_port} -u${mysql_user} -p${mysql_pass} -e"${schema_fgedu01}" >${fgedudate_01}
echo -e "\e[1;31m script from www.itpux.com and fgedu,use mydumper to Full backup mysql data per day!. \e[0m"
echo -e "\e[1;31m The databases name in current instance is: \e[0m"
awk 'NR==2,NR==0 { print $1}' ${fgedudate_01}
echo " "
#Get the current script path and create a file named database.list
#in order to save the backup databases name.
filepath=$(cd "$(dirname "$0")"; pwd)
if [ ! -s "${filepath}/database.list" ];then
echo "将在当前目录下新建databases.list."
touch ${filepath}/database.list
echo "#Each line is stored a valid database name">${filepath}/database.list
chmod 700 ${filepath}/database.list
fi
#1.优化后默认备份所有的数据库。
#2.优化前读取当前目录下database.list文件,备份部分数据库，如果database.list为空,则执行全备或备份指定的个别数据库
#Remove the comment line
awk 'NR==2,NR==0 { print $1}' ${filepath}/database.list> ${filepath}/tmpdatabases.list
#To determine whether a file is empty
if [ -s ${filepath}/tmpdatabases.list ];then
#开始时间
started_time=`date "+%Y-%m-%d %H:%M:%S"`
echo "备份开始时间:${started_time}"
db_num00=`awk 'NR==1,NR==0 { print NR}' ${filepath}/tmpdatabases.list |tail -n1`
echo "此次将备份${db_num00}个数据库:"
echo
or_dbnum=0
for i in `awk 'NR==1,NR==0 { print $1}' ${filepath}/tmpdatabases.list`;
do
((or_dbnum+=1))
mysql_databases=$i
db_dpname=$dir_backup/${i}.${now_date}
echo -e "\e[1;32m mydumper开始备份第${or_dbnum}个数据库$i..... \e[0m"
sleep 2
echo -e "\e[1;32m mydumper玩命备份中.....稍等片刻.... \e[0m"
${mydumper} \
--database=${mysql_databases} \
--host=${mysql_host} \
--port=${mysql_port} \
--user=${mysql_user} \
--password=${mysql_pass} \
--outputdir=${db_dpname} \
--rows=50000 \
--build-empty-files \
--threads=4 \
--compress-protocol \
--triggers \
--events \
--routines \
--kill-long-queries
if [ "$?" -eq 0 ];then
echo -e "\e[1;32m mydumper成功将数据库$i备份到:${db_dpname}. \e[0m"
echo
else
echo -e "\e[1;31m 备份异常结束. \e[0m"
fi
done
else
dbname='a'
# dbname=''
# read -p "Please input you want to backup database name[a|A:ALL]:" dbname
#开始循环
#开始时间
started_time=`date "+%Y-%m-%d %H:%M:%S"`
echo "备份开始时间:${started_time}"
if [ "$dbname" = "a" -o "$dbname" = "A" ];then
db_num=`awk 'NR==2,NR==0 { print NR-1}' ${fgedudate_01} |tail -n1`
echo "此次将备份${db_num}个数据库:"
echo
mysql_databases=$dbname
or_dbnum=0
for i in `awk 'NR==2,NR==0 { print $1}' ${fgedudate_01}`;
do
((or_dbnum+=1))
mysql_databases=$i
db_dpname=$dir_backup/${i}.${now_date}
echo -e "\e[1;32m mydumper开始备份第${or_dbnum}个数据库$i..... \e[0m"
sleep 2
echo -e "\e[1;32m mydumper玩命备份中.....稍等片刻.... \e[0m"
${mydumper} \
--database=${mysql_databases} \
--host=${mysql_host} \
--port=${mysql_port} \
--user=${mysql_user} \
--password=${mysql_pass} \
--outputdir=${db_dpname} \
--rows=50000 \
--build-empty-files \
--threads=4 \
--compress-protocol \
--triggers \
--events \
--routines \
--kill-long-queries
if [ "$?" -eq 0 ];then
echo -e "\e[1;32m mydumper成功将数据库$i备份到:${db_dpname}. \e[0m"
echo
else
echo -e "\e[1;31m 备份异常结束. \e[0m"
fi
done
else
echo "此次备份的数据库名为:$dbname"
echo
#开始时间
started_time=`date "+%Y-%m-%d %H:%M:%S"`
mysql_databases=$dbname
db_dpname=$dir_backup/${mysql_databases}.${now_date}
fgedudate_02="fgedudate02.`date +%y%m%d%h%m%s`.txt"
schema_fgedu02="select schema_name from information_schema.schemata where schema_name='${dbname}';"
$mysqlbin -h${mysql_host} -P${mysql_port} -u${mysql_user} -p${mysql_pass} -e"${schema_fgedu02}" >${fgedudate_02}
if [ ! -s "${fgedudate_02}" ];then
echo " "
echo -e "\e[1;31m ******************************************************************* \e[0m"
echo -e "\e[1;31m !o(︶︿︶)o! The schema_name ${dbname} not exits,pleae check . ~~~~(>_<)~~~~ \e[0m"
echo -e "\e[1;31m ********************************************************************** \e[0m"
echo " "
rm -rf ${fgedudate_01}
rm -rf ${fgedudate_02}
exit 0
else
echo -e "\e[1;32m mydumper开始备份请稍等..... \e[0m"
sleep 2
echo -e "\e[1;32m mydumper玩命备份中.....稍等片刻.... \e[0m"
${mydumper} \
--database=${mysql_databases} \
--host=${mysql_host} \
--port=${mysql_port} \
--user=${mysql_user} \
--password=${mysql_pass} \
--outputdir=${db_dpname} \
--rows=50000 \
--build-empty-files \
--threads=4 \
--compress-protocol \
--triggers \
--events \
--routines \
--kill-long-queries
if [ "$?" -eq 0 ];then
echo -e "\e[1;32m mydumper成功将数据库备份到:${db_dpname}. \e[0m"
else
echo -e "\e[1;31m 备份异常结束. \e[0m"
fi
fi
# 循环结束
fi
fi
mysql_exp_grants()
{
$mysqlbin -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
$mysqlbin -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \
sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}'
$mysqlbin -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
$mysqlbin -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
mysql_exp_grants > $dir_backup/mysql_exp_grants_$dir_date.sql
rm -rf ${fgedudate_01}
rm -rf ${fgedudate_02}
rm -rf ${filepath}/tmpdatabases.list
alias cp='cp -f'
cp $dirbackup/$OutLogFile $dir_backup/
tar -zcvPf $dir_backup.tar.gz $dir_backup $MyCNF
find $dirbackup -mtime +3 -name "fullbackup20*" -exec rm -rf {} \;
find $dirbackup -mtime +15 -name "fullbackup20*.gz" -exec rm -rf {} \;
echo "--------------------------------------------------------"
echo " 备份任务完成于: `date +%F' '%T' '%w`"
echo "--------------------------------------------------------"
mydumper开始备份第3个数据库mysql.....
mydumper玩命备份中.....稍等片刻....
mydumper成功将数据库mysql备份到:/mysql/backup/fullbackup20210228/mysql.202102282304.
mydumper开始备份第4个数据库performance_schema.....
mydumper玩命备份中.....稍等片刻....
mydumper成功将数据库performance_schema备份到:/mysql/backup/fullbackup20210228/performance_schema.202102282304.
mydumper开始备份第5个数据库syj.....
mydumper玩命备份中.....稍等片刻....
mydumper成功将数据库syj备份到:/mysql/backup/fullbackup20210228/syj.202102282304.
备份任务完成于: 2021-02-28 23:04:40 0
六 mydumper全库与增量恢复案例
6.1 创建测试数据
sh /mysql/script/backup_mydumper_full.sh
select count(*) from m1;
1000
delete from m1 where SEX='男';
6.2 删除数据库
mv data/ data20210302
6.3 恢复数据库
1 初始化数据库
mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data/ --pid-file=/mysql/data/3306/mysql.pid
tail -100f /mysql/log/3306/itpuxdb-error.err
2021-03-02T22:45:27.283734+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-03-02T22:45:27.406936+08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: eda834a3-7b65-11eb-9166-000c29e0c627.
2021-03-02T22:45:27.407571+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-03-02T22:45:28.164293+08:00 0 [Warning] CA certificate ca.pem is self signed.
2021-03-02T22:45:28.319156+08:00 1 [Note] A temporary password is generated for root@localhost: F;W%#Hxk8=?R
6ytiMz;2C1k=
service mysql start
mysql -uroot -p --connect-expired-password
alter user 'root '@'localhost' identified by 'root';
2远程登陆问题
远程登陆表
mysql> select host,user from user where user='root';
+-----------+------+
| host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
再所有的数据库和所有的表to root用户 什么主机%（全部主机），密码root
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新权限
mysql> flush privileges;
增加了一条所有主机的权限
mysql> select host,user from user where user='root';
+-----------+------+
| host | user |
+-----------+------+
| % | root |
| localhost | root |
3 恢复数据库
myloader -u root -p root -d /mysql/backup/fullbackup20210302/itpuxdb.202103021859/
myloader -u root -p root -d /mysql/backup/fullbackup20210302/syj.202103021859/
4 恢复增量
cat /mysql/backup/fullbackup20210302/syj.202103022358/metadata
Started dump at: 2021-03-02 23:58:21
SHOW MASTER STATUS:
Log: binlog.000063
Pos: 154
GTID:
Finished dump at: 2021-03-02 23:58:22
binlog.000063 | 1016197 |
| binlog.000064 | 177 |
| binlog.000065 | 848 |
binlog.000063 154 Anonymous_Gtid 1313306 219 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000063 219 Query 1313306 290 BEGIN
binlog.000063 290 Rows_query 1313306 344 # delete from m1 where SEX='男'
binlog.000063 1015497 Delete_rows 1313306 1016143 table_id: 152 flags: STMT_END_F
binlog.000063 1016143 Xid 1313306 1016174 COMMIT /* xid=437 */
binlog.000063 1016174 Stop 1313306 1016197
mysqlbinlog binlog.000063 > 63.sql
mysql -uroot -proot <63.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
select * from m1 where SEX='男';
0 条OK
七 mydumper全库与时间点恢复案例
7.1 创建测试数据
sh /mysql/script/backup_mydumper_full.sh
2021-03-03 21:25:59
select count(*) from m1;
1000
delete from m1 where SEX='男';
select now()
2021-03-03 21:27:30
7.2 删除数据库
drop database syj;
Query OK, 5 rows affected (0.01 sec)
7.3 恢复数据库
myloader -u root -p root --source-db=syj --database=syj -d /mysql/backup/fullbackup20210303/syj.202103032125/
select count(*) from m1;
10000
7.4 增量时间点恢复
cat /mysql/backup/fullbackup20210303/syj.202103032125/metadata
Started dump at: 2021-03-03 21:25:34
SHOW MASTER STATUS:
Log: 3306db-binlog.000003
Pos: 154
GTID:
Finished dump at: 2021-03-03 21:25:35
show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| 3306db-binlog.000001 | 3079229 |
| 3306db-binlog.000002 | 205 |
| 3306db-binlog.000003 | 1016225 |
| 3306db-binlog.000004 | 205 |
| 3306db-binlog.000005 | 308 |
show binlog events in '3306db-binlog.000003';
3306db-binlog.000003 290 Rows_query 3306 344 # delete from m1 where SEX='男'
3306db-binlog.000003 1016143 Xid 3306 1016174 COMMIT /* xid=279 */
mysqlbinlog --base64-output=decode-rows -v -v /mysql/log/3306/3306db-binlog.000003 | grep '210303 21:'
#210303 21:27:30 server id 3306 end_log_pos 1016143 CRC32 0xf214fd4e Delete_rows: table id 129 flags: STMT_END_F
#210303 21:27:30 server id 3306 end_log_pos 1016174 CRC32 0xfd004351 Xid = 279
#210303 21:32:27 server id 3306 end_log_pos 1016225 CRC32 0x1a5bf748 Rotate to 3306db-binlog.000004 pos: 4
mysqlbinlog --stop-datetime='2021-03-03 21:28:00' /mysql/log/3306/3306db-binlog.000003 >3.sql
mysql -uroot -proot <3.sql
恢复正常OK
转载地址：http://vabai.baihongyu.com/