备份恢复

备份恢复

mysqldump(逻辑)

  • 说明

    覆盖形式恢复的方法
单线程工作

  • 参数

    mysqldump -uroot -p -h 10.0.0.51 -P3306  链接数据库
-A                                       全备参数
-B db1 db2 db3                           备份多个单库
-R                                       备份存储过程及函数
-E                                       备份事件
-F                                       在备份开始时,刷新一个新binlog日志
--master-data=2                          以注释的形式,保存备份开始时间点的binlog的状态信息 不开启二进制日志无法用
--triggers                               备份触发器
--single-transaction                     对InnoDB表进行 热备，
--set-gtid-purged=auto                   加了--set-gtid-purged=OFF时，生成的sql文件会开启二进制日志
--max-allowed-packet=256M                太小会导致备份失败

  • 备份命令

    mysqldump -uroot -proot -A -R -E -F --master-data=2 --triggers --single-transaction --max-allowed-packet=500M  > full.sql

    恢复命令

    mysqlbinlog --skip-gtids=true --include-gtids='5c351518-78ec-11e7-8e7a-005056a610c3:1297-3000' --exclude-gtids='排除那个GDIT号码' /data/binlog/mysql-bin.000004

mysqlbinlog --start-position=813 --stop-position=1384 -d  oldboy   /data/mysql/mysql-bin.000006 >/tmp/bin1.sql   # 恢复数据时候-d 指定库

    --skip-gtids=true 数据库有相同 GTID 信息跳过

mydumper(逻辑)

  • 说明

    因为是多线程逻辑备份，备份后会生成多个备份文件
支持多线程恢复
不要备份全库 ,还原有问题
不是官方备份工具

  • 参数

    -B, --database 要备份的数据库，不指定则备份所有库
-T, --tables-list 需要备份的表，名字用逗号隔开
-o, --outputdir 备份文件输出的目录
-c, --compress 压缩输出文件
-e, --build-empty-files 如果表数据是空，还是产生一个空文件
-x, --regex 是同正则表达式匹配 'db.table'
-m, --no-schemas 不备份表结构
-t, --threads 开启的备份线程数，默认是4

  • 准备工作

    下载地址：
https://github.com/maxbube/mydumper/releases/
wget https://github.com/mydumper/mydumper/releases/download/v0.11.3-5/mydumper-0.11.3-5.el7.x86_64.rpm
安装
yum install -y mydumper-0.11.3-5.el7.x86_64.rpm
安装客户端工具
vi /etc/my.cnf
[client]
socket=/tmp/mysql.sock
创建备份目录
mkdir -p /data/backup/

  • 备份命令

    备份text数据库
mydumper -u text -p 'text' -h 10.0.0.11 -P 3306 -B text -o /data/backup/
还原text库
myloader -u text -p 'text' -h 10.0.0.11 -P 3306  -B text -d /data/backup/

备份多张表(t1,t2)：
mydumper -u text -p 'text' -h 10.0.0.11 -P 3306  -B text -T t1,t2 -o /data/backup/
还原多表
myloader -u text -p 'text' -h 10.0.0.11 -P 3306 -B text -o t1 -o t2 -d /data/backup/

  • 备份文件说明

     metadata                  记录 二进制日志文件名 ,pos号 ,gtid号 ,备份完成时间
 text-schema-create.sql    建库语句
 text.t1.00000.sql         t1表的数据
 text.t1-metadata
 text.t1-schema.sql        t1表 建表语句
 text.t2-metadata
 text.t2-schema.sql        t2表 建表语句
mysql shell(逻辑)

  • 说明

    官方文档
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
多线程逻辑备份
官方备份工具

  • 安装软件

    下载
https://dev.mysql.com/downloads/shell/
tar xf mysql-shell-8.0.24-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-shell-8.0.24-linux-glibc2.12-x86-64bit myshell
mv myshell /usr/bin/
vi /etc/profile
export PATH=/usr/bin/myshell/bin:$PATH
source /etc/profile

  • 连接

    方法一
mysqlsh -utext -ptext -h127.0.0.1
方法二
mysqlsh
\connect root@localhost:3306      # 输入密码
\connect --mysqlx root@localhost:33060
\connect root@(/tmp/mysql.sock)
方法三
var s1 = mysql.getClassicSession('root@localhost:3306', 'root');  # 指定用户名root 密码root
shell.setSession(s1);

  • 模式切换

    \sql  切换到 SQL
\js  切换到 json
\py  切换到 python

查看状态\s

查看所有线程
\show threads --foreground -o tid,ptid,cid,user,host,progname,command,memory
查看tid为218的线程做了啥
\show thread --tid 218 --general --client --locks

  • 备份(登录后JS模式下执行)

    准备
创建全备目录: mkdir -p /data/backup/
登录: mysqlsh -utext -ptext -h127.0.0.1

全备:
util.dumpInstance("/data/backup/full",{threads : 8,ocimds:true,compatibility:["strip_restricted_grants"],bytesPerChunk:"1M"})

备份指定库 (text库和kxm库)
util.dumpSchemas(["text","kxm"],'/data/backup/text',{threads:8,bytesPerChunk:"10M"})

指定表备份dumpTables()
util.dumpTables("text",["t2","t1"],"/data/backup/biao/",{threads:8,bytesPerChunk:"10M"})

  • 还原

    数据库设置
SET GLOBAL local_infile=1;
注意:还原库前要先把库删掉,否则会导致数据不一致
全备还原
util.loadDump("/data/backup/full",{threads:8,ignoreExistingObjects:true,analyzeTables:"on"})

还原两个库
util.loadDump("/data/backup/text",{threads:8,ignoreExistingObjects:true,analyzeTables:"on"})

还原单表 (表不能存在)
util.loadDump("/data/backup/biao",{resetProgress:true,loadDdl:true})

  • 说明

    /data/backup/full         备份目录,需要为空
excludeSchemas:["test"]   排除那个库
analyzeTables:"on"        数据导入完成之后执行analyze table
loadData:false            不导入数据
ddlOnly:true              只备份表结构
dryRun:true               测试运行
threads : 8               使用8个线程进行备份
Percona Xtrabackup(物理)

  • 原理

    xbk 执行瞬间    内存-刷入-磁盘 记录lsn  备份时  cp数据叶 + redo+undo（备份时产生的新数据）记录lsn恢复之前  手动 redo+undo回滚恢复   cp到数据目录下
  0、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
  1、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
  2、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
  3、恢复过程是cp 备份到元数据目录下

  • 安装

    rpm包 7.0用
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

网址下载二进制包(打开即用)
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/    7.0使用
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/        8.0以上使用

tar xf percona-xtrabackup***.tar.gz
mv  percona-xtrabackup***  xbk
mv xbk /usr/bin/
vi /etc/profile
export PATH=/usr/bin/xbk/bin/:$PATH
source /etc/profile

  • 全备

    mkdir -p /data/backup/
xtrabackup --defaults-file=/etc/my.cnf   --parallel=4  --user=root --password=root --host=localhost --port=3306 --backup --target-dir=/data/backup/full

  • 全备还原

    准备
xtrabackup --prepare --parallel=4 --target-dir=/data/backup/full
停数据库
pkill mysqld
清掉数据目录文件
rm -rf /data/3306/data/*
开始还原
xtrabackup --copy-back --target-dir=/data/backup/full
# 加上 --datadir=/root/kxm  可以指定恢复到那个目录，不加恢复到数据库数据目录根据配置文件里写的路径走
修改权限
chown -R mysql.mysql /data/3306/data/
启动数据库
systemctl start mysqld    
清一下日志记录
reset master

  • 增量备份

    全量备份的目录为： mkdir -p /data/backup/full
增量备份的目录为： mkdir -p /data/backup/inc
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=root --port=3306 --host=localhost --backup --parallel=4 --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full

  • 增量还原

    准备全备份的日志：
xtrabackup --prepare --apply-log-only --parallel=4 --target-dir=/data/backup/full
准备增量备份的日志
xtrabackup --prepare --apply-log-only --parallel=4 --target-dir=/data/backup/full --incremental-dir=/data/backup/inc
再次整合全备  (如果有多个增量 用全备跟最后一个增量恢复就好了 )
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
停数据库
pkill mysqld
清掉数据目录文件
rm -rf /data/3306/data/*
开始还原
xtrabackup --copy-back --parallel=4  --target-dir=/data/backup/full
修改权限
chown -R mysql.mysql /data/3306/data/
启动数据库
systemctl start mysqld    
清一下日志记录
reset master
Clone plugin(物理)

  • 介绍

    本地克隆: 本地重新生成一个新的数据目录 ,启动数据库用新的数据目录启动
远程克隆: 客户端数据库 去 服务端数据库 拉数据 ,覆盖客户端数据库

  • 原理

    开启redo archiving功能，从当前点开始存储新增的redo log，这样从当前 点开始所有的增量修改都不会丢失。
同时上一步在page track的page被发送 到目标端确保当前点之前所做的变更一定发送到目标端。
停止Redo Archiving", 所有归档的日志被发送到目标端，这些日志包含了从 page copy阶段开始到现在的所有日志，另外可能还需要记下当前的复制点， 例如最后一个事务提交时的binlog位点或者gtid信息，在系统页中可以找到。

  • 限制

    备份时候不能执行DDL语句
不能夸版本使用8.017-拷贝到8.18也不行
MySQL 8.0（8.0.17+）版本以上才能使用这个工具
只支持innodb引擎

  • 准备阶段

    加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或者 vi /etc/my.cnf
[mysqld] 
plugin-load-add=mysql_clone.so 
clone=FORCE_PLUS_PERMANENT
查看插件开没开
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';
创建克隆专用用户（用root也可以）
CREATE USER clone_user@'%' IDENTIFIED with mysql_native_password by 'password';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

  • 本地克隆 (本地保留一份数据目录)

    mkdir -p /data/test/
chown -R mysql.mysql /data/test/
mysql -uclone_user -ppassword 
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';
观测状态（root用户）
SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
启动新实例
mysqld_safe --datadir=/data/test/clonedir --port=3306 --socket=/tmp/mysql.sock --user=mysql --mysqlx=OFF &

  • 远程克隆 (直接把主库数据覆盖过来)

    创建远程clone用户
  服务端(12)
    create user test_s@'%' identified by '123'; 
    grant backup_admin on *.* to test_s@'%';
    客户端
    create user test_t@'%' identified by '123'; 
    grant clone_admin on *.* to test_t@'%';

客户端开始克隆(11)
  允许克隆主库的数据
  SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';
  登录客户端
  mysql -utest_t -p123 -h10.0.0.11 -P3306
  开始克隆
  CLONE INSTANCE FROM test_s@'10.0.0.12':3306 IDENTIFIED BY '123';
my2sql日志截取

  • 基于pos号截取

    mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

  • 基于GTID号的截取

    mysqlbinlog --skip-gtids --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6'--exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004

    --include-gtids   截取日志
--exclude-gtids   排除那个日志
--skip-gtids      跳过GTID检查 实际上是把导出的sql文件中gtid信息删除了，避免相同的gtid不执行 
                  如果用position号截取也要加--skip-gtids只要开了gtid就要加

  • 过滤日志取得pos号

    mysql> pager grep "DROP"                                       # 定义过滤DROP字样 Delete
       show binlog events in 'mysql-bin.000009';               # 查询除了DROP的行其他的行都不显示
       nopager                                                 # 取消

  • my2sql 解析二进制文件

    下载
    https://github.com/liuhr/my2sql
    安装
     chmod +x my2sql
 cp my2sql /usr/bin/
    限制
    使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
只能回滚DML， 不能回滚DDL
支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区， 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析
准备工作
创建使用用户
用户要有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
create user text@"%" identified with mysql_native_password by 'text';
grant all on *.* to text1@"%"; 

创建指定目录
mkdir /tmp/kxm -p  

查询数据库日志
show master status;     要查看哪个日志的记录就写哪个文件名
参数
-mode                     repl: 伪装成从库解析binlog文件，file: 离线解析binlog文件, 默认repl
-work-type                2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息
-output-dir               将生成的结果存放到制定目录
-output-toScreen          将生成的结果打印到屏幕，默认写到文件
-threads                  线程数，默认8个
-add-extraInfo            把position等信息以注释的方式加入生成的每条sql前，默认false
-big-trx-row-limit n      找出满足n条sql的事务，默认500条
-databases , -tables      库及表条件过滤, 以逗号分隔
-sql                      要解析的sql类型，可选参数insert、update、delete，默认全部解析
-doNotAddPrifixDb         默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，也可以生成不带库名的sql
-file-per-table           为每个表生成一个sql文件
解析例子

解析出二进制文件[3,5)的全部sql

my2sql -user text -password text -host 10.0.0.11 -port 3306  -databases text -tables t1 -work-type 2sql --start-file mysql-bin.000003 --stop-file mysql-bin.000005  -output-dir /tmp/kxm -add-extraInfo

根据pos点解析出标准SQL -start-pos 2089 -stop-pos 2789

my2sql -user text -password text -host 10.0.0.11 -port 3306  -databases text -tables t1 -work-type 2sql --start-file mysql-bin.000003 --stop-file mysql-bin.000005 -start-pos 2089 -stop-pos 2789  -output-dir /tmp/kxm -add-extraInfo

根据时间点解析出标准SQL --start-datetime --stop-datetime

my2sql -user text -password text -host 10.0.0.11 -port 3306 -databases text -tables t1 -work-type 2sql --start-file mysql-bin.000003 --stop-file mysql-bin.000005 --start-datetime "2021-12-19 13:29:33" --stop-datetime "2021-12-19 13:29:44"  -output-dir /tmp/kxm -add-extraInfo

解析出回滚SQL -work-type rollback

my2sql -user text -password text -host 10.0.0.11 -port 3306 -databases text -tables t1  -work-type rollback --start-file mysql-bin.000003 --stop-file mysql-bin.000005 -output-dir /tmp/kxm -add-extraInfo

统计DML以及大事务 -work-type stats -big-trx-row-limit 500 -long-trx-seconds 300

# 统计一个事务操作大于500行、时间大于300秒的事务
my2sql -user text -password text -host 10.0.0.11 -port 3306 -databases text -tables t1  -work-type stats -big-trx-row-limit 500 -long-trx-seconds 300 --start-file mysql-bin.000003 --stop-file mysql-bin.000005 -output-dir /tmp/kxm -add-extraInfo

从一个pos点开始持续输出到屏幕 -output-toScreen

my2sql -user text -password text -host 10.0.0.11 -port 3306 -databases text -tables t1 -work-type 2sql --start-file mysql-bin.000003 -output-toScreen

END

**

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • mysql备份恢复
    1. 运维在数据库备份恢复方面的职责 1.1 设计备份策略 全备 、增量、时间、自动 1.2 日常备份检查 备份存...
    醉舞经阁半卷书A阅读 2,924评论 0赞 0
  • 09-备份和恢复
    1. 运维人员在备份恢复方面的职责 1.设计备份策略 备份内容: 数据,二进制日志 数据容量: 大小 备份周期:...
    UncleZ_strive阅读 3,535评论 0赞 0
  • Mysql 备份与恢复(学习笔记六)
    https://blog.csdn.net/flyfish778/article/details/52451738...
    SkTj阅读 3,833评论 0赞 4
  • MySQL-lesson07-备份恢复
    1. 运维在数据库备份恢复方面的职责 1.1 设计备份策略 全备 、增量、时间、自动 1.2 日常备份检查 备份存...
    小一_d28d阅读 2,939评论 0赞 0
  • 备份恢复
    1.DBA（运维）在备份恢复方面需要做哪些工作 1.1 设计备份策略 1.1.1 备份周期 天，周，月 1.1.2...
    学Linux的小子阅读 4,079评论 0赞 1