本文共 11744 字，大约阅读时间需要 39 分钟。
MySQL逻辑备份恢复mysqlpump20210217
概述
逻辑备份:
备份内容:数据库的结构定义语句+数据内容的插入语句，备份出来的文件是可以编辑的。
适用场景:数据量少的数据库，比较适合100G数据量以内的。
逻辑备份的特点:
1) sql语句组成的文件,可以截取部分单独使用。
2)备份文件比物理的小。
3)可以细化到表/表的内容
4)速度慢
5)可以跨平台恢复/迁移
6）只能在线备份，在线恢复。
逻辑备份的工具:
1) mysqldump(单线程)、mysqlpump(多线程)∶官方MySQL
2) mydumper:开源的，基于mysqldump的一个优化，多线程，速度介于两者之间:
一 mysqlpump工具介绍
mysql5.7.8开始官方提供的一个逻辑备份工具:mysqlpump(mysql 5.7.11+),用法和mysqldump类似区别在于mysqlpump是多线程,而mysqldump是单线程。
mysqldump: oracle exp
mysqlpump: oracle expdp
mysqlpump --help | more
1.1 mysqlpump参数说明
1--add-drop-user
在create user语句之前增加drop user，这个参数要和--user一起使用，否则不生效。
2--compress-output
默认不压缩输出，目前可以使用压缩的算法有lz4和zlib.
3--default-parallelis并行单个数据库中的表
指定并行线程数据，默认是2个，如果设置0就不并行备份，如果是单表则并行无效。
每个线程在导入的时候，先写数据，最后再创二级索引(主键索引在创建表的时候建立）。
4 --parallel-schemas指定并行备份的库
--parallel-schemas=6:db1,3:db3
5--defer-table-indexes延迟创建索引good
延迟创建索引，直到所有的数据都加载完了之后再创建索引，默认开启。
关闭--skip-defer-table-indexes,就和mysqldump类似了，先创建表和索引，再导数据，就慢了。基本都是默认选项。延迟创建索引。
6 --skip-dump-rows
只备表结构，不备份数据。而mysqldump支持--no-data,
7 --users
备份数据库用户，如只备份用户，而不备份数据库:mysqlpump --exclude-databases=% --users
8 --watch-progress
显示完成进度。
9 排除对象-默认没有
exclude-databases (No default value)
exclude-events (No default value)
exclude-routines (No default value)
exclude-tables (No default value)
exclude-triggers (No default value)
exclude-users (No default value)
10 指定包含的对象
include-databases (No default value)
include-events (No default value)
include-routines (No default value)
include-tables (No default value)
include-triggers (No default value)
include-users (No default value)
11 -A -all-databases
mysqldump时会备份权限表相关信息
mysqlpump不会备份，如果要备份权限信息需要加—users
二 mysqlpump常用备份命令使用案例
Mysqlpump触发器，存储过程和函数，事件默认都是备份的，但是mysqldump默认都是不备份的。
triggers TRUE 触发器 默认都是备份的
routines TRUE 存储过程和函数默认都是备份的
events TRUE 时间默认都是备份的
2.1 导出所有数据库-不压缩
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --all-databases --users >/mysql/backup/alldbpump.sql
Dump progress: 1/2 tables, 0/50 rows
Dump progress: 38/39 tables, 545031/5019579 rows
Dump progress: 38/39 tables, 4765281/5019579 rows
Dump completed in 7955 milliseconds
2.2 导出所有数据库压缩LZ4
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --compress-output=LZ4 --all-databases --users >/mysql/backup/alldbpump.sql.lz4
压缩率 一般是40%
2.3 导出所有数据库压缩ZLIB-压缩效果最好
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --compress-output=ZLIB --all-databases --users >/mysql/backup/alldbpump.sql.ZLIB
压缩率 一般是25%
2.4 导出单个数据库压缩ZLIB
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --compress-output=ZLIB -B syj >/mysql/backup/syjdbpump.sql.ZLIB
压缩率 一般是25%
2.4 导出多个数据库并行-压缩ZLIB
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --parallel-schemas=3:syj,3:syjutf8 --compress-output=ZLIB -B syj syjutf8>/mysql/backup/syjdbpump.sqlp3.ZLIB
2.5 导出所有数据库的结构
可以不加--add-drop-table，有这个参数会删除表
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --all-databases --add-drop-table --skip-dump-rows >/mysql/backup/alldbpumpnodata.sql
2.6 导出一个数据库的结构
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 -B syj --skip-dump-rows >/mysql/backup/syjpumpnodata.sql
2.7 导出一个数据库的数据
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 -B syj –no-create-info>/mysql/backup/syjpumpdata.sql
2.7 导出一个表的数据
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --include-databases=syj --include-tables=gw –no-create-info>/mysql/backup/syjgwpumpdata.sql
2.7 导出一个数据库排除部分表
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --include-databases=syj --exclude-tables=m1 --exclude-tables=sales –no-create-info>/mysql/backup/syjgwpumpdata.sql
三 mysqlpump生产环境自动化备份案例
3.1 备份日志脚本
[root@mysqldb script]# cat backup-mysql-binlog.sh
#!/bin/bash
# #script backup mysql binlog everyday!
BinLogBakDir=/mysql/backup/backup-binlog ##二进制备份的目录
BinLogDir=/mysql/log/3306 ##MySQL二进制文件目录
LogOutFile=/mysql/backup/backup-binlog/bak-bin.log ##工作日志文件
/mysql/app/mysql/bin/mysqladmin -uroot -proot -h192.168.247.131 flush-logs
BinIndexFile=/mysql/log/3306/binlog.index ##二进制的索引文件
NextLogFile=`tail -n 1 $BinIndexFile`
LogCounter=`wc -l $BinIndexFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
echo "--------------------------------------------------------------------" >> $LogOutFile
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Start... >> $LogOutFile
for binfile in `cat $BinIndexFile`
do
base=`basename $binfile`
#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $LogCounter ]
then
echo $base skip! >> $LogOutFile
else
dest=$BinLogBakDir/$base
if(test -e $dest)
#test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去
then
echo $base exist! >> $LogOutFile
else
cp $BinLogDir/$base $BinLogBakDir
echo $base copying >> $LogOutFile
fi
fi
done
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Complete! Next LogFile is: $NextLogFile >> $LogOutFile
find $BinLogBakDir -mtime +30 -name "*binlog.**" -exec rm -rf {} \;
3.2 备份数据库脚本
##删除30天以前的binlog日志
[root@mysqldb script]# cat backup-mysqlpump-full.sh
#!/bin/bash
# script from www.itpux.com and fgjy,use mysqlpump to Full backup mysql data per day!
DataBakDir=/mysql/backup/ ##备份的目录
MySQLPUMPDIR=/mysql/app/mysql/bin/mysqlpump
LogOutFile=/mysql/backup/bak-db.log
LogErrOutFile=/mysql/backup/bak-err.log
BinLogBakDir=/mysql/backup/backup-binlog ##二进制日志备份的地方
MyCNF=/mysql/data/3306/my.cnf
mysql_host=192.168.247.131 #根据自己的实际情况设置
mysql_port=3306 #根据自己的实际情况设置
mysql_user=root #根据自己的实际情况设置
mysql_pass=root #根据自己的实际情况设置
Date=`date +%Y%m%d`
Begin=`date +"%Y-%m-%d %H:%M:%S"`
cd $DataBakDir
DumpFile="dbbackup-alldb-$Date.sql"
GZDumpFile=dbbackup-alldb-$Date.sql.tar.gz
#mysqlpump -u${mysql_user} -p${mysql_pass} --single-transaction --default-character-set=utf8 --default-parallelism=2 --include-databases=itpux,itpuxdb,itpuxdb1> $DumpFile
$MySQLPUMPDIR -u${mysql_user} -p${mysql_pass} --single-transaction --default-character-set=utf8 --default-parallelism=2 --all-databases --exclude-databases=performance_schema,information_schema,sys,mysql > $DumpFile 2> $LogErrOutFile
$MySQLPUMPDIR u${mysql_user} -p${mysql_pass} --single-transaction --default-character-set=utf8 --default-parallelism=2 -B performance_schema information_schema sys mysql | gzip > dbbackup-per-inf-sys-mysql-$Date.sql.gz
tar -zcvf $GZDumpFile $DumpFile $MyCNF
Last=`date +"%Y-%m-%d %H:%M:%S"`
#Function export user privileges
mysql_exp_grants()
{
mysql -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
mysql -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \
sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}'
mysql -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
mysql -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 > ./mysql_exp_grants_out_$Date.sql
echo "data-backup---Start:$Begin;Complete:$Last;$GZDumpFile Out Complete!" > $LogOutFile
find $DataBakDir -mtime +1 -name "*.sql" -exec rm -rf {} \; ##保留一天的原文件
find $DataBakDir -mtime +15 -name "*.gz" -exec rm -rf {} \; ##保留15天的文件
四 mysqlpump全库和增量恢复案例
1 全备
sh /mysql/script/backup-mysqlpump-full.sh
select now() from dual;
2021-02-19 23:32:29
select count(*) from syj.gw;
19
全备日志是binlog.000058以前的都可以删除
-rw-r----- 1 mysql mysql 154 2月 19 23:34 binlog.000058
purge binary logs to 'binlog.000038';
Query OK, 0 rows affected (0.00 sec)
show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| binlog.000028 | 154 | | |
2 时间点A 修改数据
update syj.gw set min_salary=min_salary+5000;
2021-02-16 23:40:46
3 时间点B 删除数据
insert into syj.gw VALUES ('pgdba','开源高级DB工程师',15000,24000),('mydba','开源高级DB工程师',12000,21000),('db2dba','高级DB工程师',16000,25000)
2021-02-19 23:37:38
4 恢复到时间A修改数据单个数据库
说明单库的时候，备份也是单库。
drop database syj;
[root@mysqldb backup-binlog]# more /mysql/backup/dbbackup-alldb-20210219.sql | grep 'CREATE DATABASE'
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `itpuxdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syj` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syjutf8` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syj` /*!40100 DEFAULT CHARACTER SET utf8 */;
mysql -uroot -proot -o syj </mysql/backup/dbbackup-syjdb-20210216.sql
more /mysql/backup/dbbackup-alldb-20210216.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000038', MASTER_LOG_POS=154;
show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000038 | 106006780 |
| binlog.000039 | 105311235 |
| binlog.000040 | 105298657 |
| binlog.000041 | 105334272 |
| binlog.000042 | 105320674 |
| binlog.000043 | 105301328 |
| binlog.000044 | 55424946 |
show binlog events in 'binlog.000038';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
| binlog.000038 | 4 | Format_desc | 1313306 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| binlog.000038 | 123 | Previous_gtids | 1313306 | 154 | |
| binlog.000038 | 154 | Anonymous_Gtid | 1313306 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 219 | Query | 1313306 | 292 | BEGIN |
| binlog.000038 | 292 | Rows_query | 1313306 | 361 | # update syj.gw set min_salary=min_salary+5000 |
| binlog.000038 | 361 | Table_map | 1313306 | 416 | table_id: 156 (syj.gw) |
| binlog.000038 | 416 | Update_rows | 1313306 | 1566 | table_id: 156 flags: STMT_END_F |
| binlog.000038 | 1566 | Xid | 1313306 | 1597 | COMMIT /* xid=2764 */
mysqlbinlog --base64-output=decode-rows -v -v /mysql/backup/backup-binlog/binlog.000038 | grep '210216 23:4'|more
#210216 23:40:26 server id 1313306 end_log_pos 361 CRC32 0x425b59b4 Rows_query
#210216 23:40:26 server id 1313306 end_log_pos 416 CRC32 0x5134129a Table_map: `syj`.`gw` mapped to number 156
#210216 23:40:26 server id 1313306 end_log_pos 1566 CRC32 0x0e8c6f08 Update_rows: table id 156 flags: STMT_END_F
#210216 23:40:26 server id 1313306 end_log_pos 1597 CRC32 0x168631ce Xid = 2764
#210216 23:43:08 server id 1313306 end_log_pos 1662 CRC32 0xc1d1b8f7 Anonymous_GTID last_committed=1 seq
uence_number=2 rbr_only=yes
在 2021-02-16 23:41:00 可以恢复单个数据库
mysqlbinlog --stop-datetime='2021-02-16 23:41:00' --database=syj /mysql/backup/backup-binlog/binlog.000038 >38.sql
mysql -uroot -proot syj <38.sql
5 恢复到时间A修改数据全局
show binlog events in 'binlog.000058';
binlog.000058 708 Anonymous_Gtid 1313306 773 SET @@SESSION.GTID_NEXT= 'ANONYMOUS' 773 Query 1313306
862 drop database syj
QOS 点在773
210219 23:38:30
[root@mysqldb backup-binlog]# mysqlbinlog --base64-output=decode-rows -v -v /mysql/backup/backup-binlog/binlog.000058 | grep 'pos'|more
#210219 23:37:21 server id 1313306 end_log_pos 708 CRC32 0x3678f436 Xid = 55915
#210219 23:38:30 server id 1313306 end_log_pos 773 CRC32 0x080d7ab1 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
mysqlbinlog --stop-datetime='2021-02-19 23:38:30' /mysql/backup/backup-binlog/binlog.000058 >58.sql
mysql -uroot -proot <58.sql
数据恢复
转载地址：http://pabai.baihongyu.com/