在备份恢复中的职责
1.1 备份策略的设计

(1) 备份周期:

根据数据量.

(2)备份工具:

mysqldump (MDP) , XBK (PBK) percona Xtrabackup , MEB(MySQL Enterprise BACKUP MEB) ,mysqlbinlog

(3)备份方式:

逻辑:

全备 mysqldump

增量 binlog (flush logs ,cp)

物理备份:

全备 : XBK

增量 : XBK

1.2 检查备份可用性

crontab -l ----->

备份脚本 ----->

备份路径 ----->

看备份日志,检查备份文件(大小,内容)

1.3 定期的恢复演练

1.4 数据恢复

只要备份和日志是完整的,恢复到故障之前的时间点(快速)

1.5 数据迁移 ***

操作系统不同的迁移

mysql -> mysql

其他 -> mysql

mysql -> 其他

备份的介绍
2.1 备份的策略

2.2 备份的工具

2.3 备份类型

热备 : 对于业务影响最小 InnoDB，把备份过程中产生的数据也备份了

温备 : 长时间锁表备份 MyISAM

冷备 : 业务关闭情况下备份

mysqldump
3.1 连接数据库

-u -p -S -h P

3.2 基础备份参数

-A =all 备份所有的库

mysqldump -uroot -p123 -A >/backup/full.sql

-B 备份指定的库

mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql

world oldguo wordpress 是三个库名

库表备份指定库的指定表

mysqldump -uroot -p123 world city country > /backup/tab.sql

world为库名 city country 为表名

3.3 特殊备份参数

-R 存储过程和函数

-E 事件

--triggers 触发器

--master-data=2 *****

(1) 记录备份时刻的binlog信息

(2) 自动锁表

不加--single-transaction ,温备份

加了--single-transaction,对于InnoDB表不锁表备份(快照备份)，假设10点开始备份，10点以后产生数据没有备份

--single-transaction *****

对于InnoDB的表,进行一致性快照备份,不锁表.

4.5 故障模拟演练

4.5.1 准备数据

create database backup;

use backup

create table t1 (id int);

insert into t1 values(1),(2),(3);

commit;

4.5.2 周二 23：00全备

mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

4.5.3 模拟周二 23：00到周三 10点之间数据变化

use backup

insert into t1 values(11),(22),(33);

commit;

create table t2 (id int);

insert into t2 values(11),(22),(33);

commit;

4.5.4 模拟故障,删除表(只是模拟，不代表生产操作)

drop database backup;

4.6 恢复过程

4.6.1 准备临时数据库（多实例3307）

systemctl start mysqld3307

4.6.2 准备备份

（1）准备全备：

cd /backup

gunzip full_2018-10-14.sql.gz

（2）截取二进制日志

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;

753

1519

mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 /data/binlog/mysql-bin.000002 >/backup/bin.sql

mysqlbinlog --skip-gtids --start-position=793 --stop-position=1903 /data/binlog/mysql-bin.000004 >/backup/bin.sql #我自己的

4.6.3 恢复备份到临时库

mysql -S /data/3307/mysql.sock #以socket的方式登录

set sql_log_bin=0;

source /backup/full_2019-07-15.sql

source /backup/bin.sql

4.6.4 将故障表导出并恢复到生产

mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sql

mysql -uroot -p123

set sql_log_bin=0

source /backup/bak.sql;

练习：

1、创建一个数据库 oldboy

2、在oldboy下创建一张表t1

3、插入5行任意数据

4、全备

5、插入两行数据，任意修改3行数据，删除1行数据

6、删除所有数据

7、再t1中又插入5行新数据，修改3行数据

需求，跳过第六步恢复表数据

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/backup/full_bak.sql

mysqlbinlog --skip-gtids --include-gtids='f4809b66-24df-11eb-9d88-000c294ef024:15-20' --exclude-gtids='f4809b66-24df-11eb-9d88-000c294ef024:18' /data/binlog/mysql-bin.000004 >/backup/gtid.sql

扩展参数 ***

在构建主从时,使用AUTO/ON

--set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加

--set-gtid-purged=OFF

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

--max_allowed_packet=128M 控制的是备份时传输数据包的大小.

mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

innobackupex 使用
7.3.1 备份核心理念

针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件
针对InnoDB表,立即触发CKPT,将内存中的脏页刷写到磁盘，copy所有InnoDB表相关的文件(ibdata1,ibd,frm).

并且将备份过程中产生的数据变化的部分redo一起备份走

在恢复时,xbk会调用InnoDB引擎的CSR=crash safe recover过程,将数据和redo的LSN追平,然后进行一致性恢复.

7.3.2 备份过程

(1) 全备

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full

(2) 利用全备进行恢复

[root@db01 ~]# pkill mysqld

[root@db01 ~]# \rm -rf /data/mysql/data/*

[root@db01 ~]# innobackupex --apply-log /backup/full/

[root@db01 full]# cp -a /backup/full/* /data/mysql/data/

[root@db01 full]# chown -R mysql.mysql /data/mysql/data/*

[root@db01 full]# /etc/init.d/mysqld start

XBK 全备和恢复体验

innobackupex --user=root --password=123 --no-timestamp /backup/full

innobackupex --apply-log /backup/full

备份产生的文件介绍

(1) xtrabackup_binlog_info *****

记录备份时刻的二进制日志信息. 可以作为binlog截取的起点.

(2) xtrabackup_checkpoints *****

from : 备份中包含的LSN号的起点,全备:0,增量:上次备份的结束位置

to : ckpt 时的LSN

last-9 : 备份结束时的LSN.下次增量备份的起始位置.

全备

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp --host=127.0.0.1 /backup/full #红色是我自己加的

增量

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp --host=127.0.0.1 --incremental --incremental-basedir=/backup/full /backup/inc1

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp --host=127.0.0.1 --incremental --incremental-basedir=/backup/inc1 /backup/inc2

mysqlbinlog --skip-gtids --include-gtids='f4809b66-24df-11eb-9d88-000c294ef024:30-32' /data/binlog/mysql-bin.000006>/backup/binlog.sql

5.12 恢复前的准备

(1) 整理full

innobackupex --apply-log --redo-only /backup/full

(2) 合并inc1到full,并整理备份

innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

(3) 合并inc2到full,并整理备份

innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full

(4) 最后一次整理full

innobackupex --apply-log /backup/full

innobackupex --help

--redo-only This option should be used when preparing the base full

                 backup and when merging all incrementals except the last

                 one. This forces xtrabackup to skip the "rollback" phase

                 and do a "redo" only. This is necessary if the backup

                 will have incremental changes applied to it later. See

                 the xtrabackup documentation for details.

5.13 截取二进制日志

起点:

cat /data/mysql/data/xtrabackup_binlog_info

终点:

mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET'

SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/!/;

mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql

5.14 恢复备份数据

[root@db01 /]# cp -a /backup/full/* /data/mysql/data/

[root@db01 /]# chown -R mysql. /data/

[root@db01 /]# /etc/init.d/mysqld start

mysql> set sql_log_bin=0;

mysql> source /backup/binlog.sql

发表于 2022-02-14 17:08 近知阅读(200) 评论(0) 编辑收藏举报

备份恢复

world oldguo wordpress 是三个库名

公告