mysql备份到带库_MySQL数据备份

MySQL数据备份

一、 MySQL数据损坏类型

1.1、物理损坏

磁盘损坏:

硬件，磁道坏，dd,格式化

文件损坏: 数据文件损坏，redo损坏

1.2、逻辑损坏

drop

delete

truncate

update

二、DBA运维人员在备份、恢复的职责

2.1 、设计备份、容灾策略

2.1.1、 备份策略:

备份工具选择

备份周期设计

备份监控方法

2.1.2、 容灾策略

备份:用什么 备份。

架构:

高可用，演示从库，灾备库

2.2、定期的备份、容灾检查

备份软件----->带库

每周北京---d-->天津货运

2.3、定期的故障恢复演练

2.4、数据损坏时的快速且准确恢复

2.5、数据迁移工作

三、MySQL常用备份工具

3.1 逻辑备份方式

mysqldump (MDP ) 重点重点重点

repl ication

mydumper ( 自行扩展)

load data in file (自行扩展)

3.2物理备份方式

MySQL Enterprise Backup (企业版)

Percona Xtrabackup ( PBK, XBK ) 重点重点重点

四、mysqldump应用

最终语法:

mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A --max-allowed-packet=64M > /data/backup/full.sql

4.1、介绍

逻辑备份工具。备份的是SQL语句。

4.2、备份方式:

4.2.1、 InnoDB表

可以采取快照备份的方式。

开启一个独立的事务，获取当前最新的--致性快照。

将快照数据，放在临时表中，转换成SQL (Create database, Create table，insert) ，保存到sq1文件中。

4.2.2、非InnoDB表

需要锁表备份。触发FTWRL, 全局锁表。转换成SQL (Create database, Create table，insert) ，保存到sq1文件中。

4.3 、mysqldump的核心参数

4.3.1、连接参数

-P端口

-hip

-P密码

-Sscokt那玩意----可省略

4.3.2、备份参数

-A：全备

# 准备下备份目录

mkdir /data/backup

chown -R mysql.mysql /data/backup/

# 完整命令

mysqldump -uroot -p123 -S /tmp/mysql.sock -A > /data/backup/full.sql

-B：备份单库或者多库

# 备份一个或者多个

mysqldump -uroot -p123 -B school gtdb > /data/backup/BB.sql

备份单表或者多表

mysqldump -uroot -p123 school course student > /data/backup/CC.sql

参数：固定写法

school #库名

course studen#school 库下的表名

-F 备份的时候切割一次日志

mysqldump -uroot -p123 -F school course student > /data/backup/CC.sql

面试题：

验证一下:

以下两个命令的备份结果区别?

mysqldump -uroot -p123 -B world >/data/backup/db1.sql

create database world;

use world;

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

以下命令，应用时，world库不存在，需要手工创建，并且use到world库下再恢复。

mysqldump -uroot -p123 world > /data/backup/db2.sql

4.3.3、高级备份参数

4.3.3.1、--master-data=2

区别：1和2的区别就是一个注释一个注释，有一行记录

--master-data=2

--master-data=1

功能：

1.备份时自动记录binlog信息

2.自动锁表和解锁

3.配合single transaction 可以减少锁表时间?

场景:

每周日23:00 全备，周1-6 binlog备份。所有备份是完整的。

周三时，有一个核心运维人员进行了删库操作。

恢复全备到周日23:00的

所有需要binlog恢复

痛点: binlog的截取

起点查找比较困难:

方法一:备份开始时，切割日志。-F

方法二:备份开始时，自动记录日志文件信息，--master-data=2

终点:drop之前的位置点。

用法：

# 备份语句加上--master-data=2即可，那种备份方式都行

mysqldump -uroot -p123 --master-data=2 -A > /data/backup/full.sql

4.3.3.2、--single-transaction

作用：

对于InnoDB引擎表备份时，开启一个独立事务，获取一致性快照，进行备份。

用法：

mysqldump -uroot -p123 --master-data=2 --single-transaction -A > /data/backup/full.sql

4.3.3.3、-R -E --trigger

-R # 备份存储过程及函数

--triggers # 备份触发器

-E # 备份事件

用法：

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

4.3.3.4、--max-allowed-packet=

作用：

表过大，加上这个参数就行，64不够就128----

用法：

mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A --max-allowed-packet=64M > /data/backup/full.sql

4.4、数据恢复案例

4.4.1、案例场景：

基础环境:

Centos 7.6 + MySQL 5.7.28 , LNMT网站业务上数据量100G,每天5-10M数据增长。

备份策略: mysqldump每天全备， binlog定时 备份。

故障模拟:

周三 上午10点数据故障，例如:核心业务库被误删除。

恢复思路:

1.挂维护页。

2.找测试库。

3.恢复周二全备。

4.截取周二全备---> 周三上午10点误删除之前的binlog, 并恢复

5.测试业务功能正常

6.恢复业务:

方案1:故障库导回到原生产

方案2:直接用测试库称当生产，先跑着。

4.4.2、数据准备：

create database dumpdb charset utf8mb4;

use dumpdb;

create table t1 (id int);

begin;

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

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

commit;

begin;

insert into t1 values(4),(5),(6);

insert into t1 values(44),(55),(66);

commit;

4.4.3、模拟周二晚上全备

mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A --max-allowed-packet=64M > /data/backup/full_`date +%F`.sql

4.4.4、模拟周三白天的数据变化

create database mdp charset utf8mb4;

use mdp;

create table t2 (id int);

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

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

commit;

4.4.5、模拟数据破坏

# 因为还没到晚上11点，此时的数据没备份，所以只能手动恢复

drop database mdp;

4.4.6、数据恢复！

1、检查全备，找到全备使用时候的二进制文件(截取起点用)

[root@master backup]# grep ^"-- CHANGE MASTER TO MASTER_LOG_FILE" full_2020-11-15.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1270;

2、恢复全备

tzh>set sql_log_bin=0; #5.7以后不用加

tzh>source /data/backup/full_2020-11-15.sql;

3、截取起点

起点：

[root@master backup]# grep ^"-- CHANGE MASTER TO MASTER_LOG_FILE" full_2020-11-15.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1270;

终点:

# 看看现在在使用那个日志文件

tzh>show master status;

# 查看详细信息找到dorp前的gtid

tzh>show binlog events in 'mysql-bin.000006';

| mysql-bin.000006 | 1958 | 2023 | SET @@SESSION.GTID_NEXT= 'f9511c39-1e28-11eb-8a8e-000c29af70c3:16' |

| mysql-bin.000006 | 2023 | 2112 | drop database mdp

position截取：

起点：MASTER_LOG_POS=1270

终点：MASTER_LOG_POS=2023

命令：

mysqlbinlog --skip-gtids --start-position=1270 --stop-position=2023 /data/mysql/binlog/mysql-bin.000006 > /data/backup/pos.sql

恢复：

source /data/backup/pos.sql

验证:

tzh>show databases;

tzh>select * from mdp.t2; #跟插入时的数据一样，成功恢复！nice！

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 11 |

| 22 |

| 33 |

+------+

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

# 再次drop掉mdp库，模拟另一个方法，此时的日志是不写入日志文件的set sql_log_bin=0;了

查看建库时候使用的日志文件找到gtid

tzh>show binlog events in 'mysql-bin.000006';

| mysql-bin.000006 | 1270 | 1335 | SET @@SESSION.GTID_NEXT= 'f9511c39-1e28-11eb-8a8e-000c29af70c3:13' |

| mysql-bin.000006 | 1335 | 1442 | create database mdp charset utf8mb4

gtid截取：

起点：f9511c39-1e28-11eb-8a8e-000c29af70c3:13

终点：f9511c39-1e28-11eb-8a8e-000c29af70c3:16-1 得减1，要不然又drop了

命令：

mysqlbinlog --skip-gtids --include-gtids='f9511c39-1e28-11eb-8a8e-000c29af70c3:13-15' /data/mysql/binlog/mysql-bin.000006 > /data/backup/gtid.sql

恢复：

source /data/backup/gtid.sql

验证:

tzh>show databases;

tzh>select * from mdp.t2; #跟插入时的数据一样，成功恢复！nice！

最后：

tzh>set sql_log_bin=1; #5.7以后不用加

4.5、mysqldump总结

(在生产中恢复要谨慎，恢复会删除重复的表)

参数:

-u P

-S -h -P

-A -B

- master-data=2 --single-transaction -R -E --triggers --max allowed packet=64

选择场景:

优点:可读性比较强，压缩比，节省空间，不需要下载安装。

缺点:备份时间相对较长。恢复时间长。

数据量较少，建议mysqldump.100G以内。

数据量巨大:分布式架构，数据量较大时候，可以采用分布式备份。也可以选择mysqldump

五、percona Xtrbackup

5.1、安装

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

5.2、介绍

物理备份工具.拷贝数据文件．InnoDB表:

热备份：业务正常发生的时候，影响较小的备份方式。

1. checkpoint，将已提交的数据页刷新到磁盘。记录一个LSN。

2．拷贝InnoDB表相关的文件(ibdatal , frm ibd..)

3．备份期间产生新的数据变化的redo也会备份走。

非工nnoDB表:

温备份：锁表备份。

1.FTWRL ，触发全局锁。

2.拷贝非工nnoDB表的数据

3.解锁

再次统计LSN,写入到专用文件。记录二进制日志位置记录下来。

所有备份文件统一存放在一个目录下。

5.3、应用

5.3.1、使用该工具前提条件

(1)数据库启动

(2)能连上数据库

# my.cnf文件中指定Xtrbackup要连接的sock号，也可以加参数指定

[client]

socket=/ tmp/ mysql.sock

(3)默认会读取[mysqld]---> datadir=

(4)属于服务端工具

5.3.2、全备

[root@master backup]# innobackupex --user=root --password=123 /data/backup

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`

全备查看:

[root@master ~]# cd /data/backup/

[root@master backup]# ll

total 804

drwxr-x--- 12 root root 328 Nov 17 07:04 2020-11-17_07-04-36

[root@master backup]# cd 2020-11-17_07-04-36/

[root@master 2020-11-17_07-04-36]# ll

total 536628

-rw-r----- 1 root root 500 Nov 17 07:04 backup-my.cnf

drwxr-x--- 2 root root 48 Nov 17 07:04 bindb

drwxr-x--- 2 root root 48 Nov 17 07:04 dumpdb

drwxr-x--- 2 root root 104 Nov 17 07:04 gtdb

-rw-r----- 1 root root 423 Nov 17 07:04 ib_buffer_pool

-rw-r----- 1 root root 12582912 Nov 17 07:04 ibdata1

-rw-r----- 1 root root 536870912 Nov 17 07:04 ibdata2

drwxr-x--- 2 root root 48 Nov 17 07:04 mdp

drwxr-x--- 2 root root 4096 Nov 17 07:04 mysql

drwxr-x--- 2 root root 8192 Nov 17 07:04 performance_schema

drwxr-x--- 2 root root 160 Nov 17 07:04 school

drwxr-x--- 2 root root 8192 Nov 17 07:04 sys

drwxr-x--- 2 root root 20 Nov 17 07:04 test

-rw-r----- 1 root root 63 Nov 17 07:04 xtrabackup_binlog_info

-rw-r----- 1 root root 113 Nov 17 07:04 xtrabackup_checkpoints

-rw-r----- 1 root root 539 Nov 17 07:04 xtrabackup_info

-rw-r----- 1 root root 2560 Nov 17 07:04 xtrabackup_logfile

drwxr-x--- 2 root root 48 Nov 17 07:04 zabbix

文件分析：

xtrabackup_binlog_info ：

# 备份起点日志,binlig的截取起点

[root@master 2020-11-17_07-04-36]# cat xtrabackup_binlog_info

mysql-bin.000008194f9511c39-1e28-11eb-8a8e-000c29af70c3:1-16

xtrabackup_checkpoints：

# 记录备份类型、lsn，方便做增量备份

[root@master 2020-11-17_07-04-36]# cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 4222237

last_lsn = 4222246

compact = 0

recover_binlog_info = 0

xtrabackup_info :

# 备份总信息

5.3.3、备份恢复步骤

# 模拟数据丢失

pkill mysqld

# 全备恢复必须是空目录

rm -rf /data/mysql/*

# 备份处理：perpare

redo 前滚，undo回滚、模仿CRS过程

[root@master backup]# innobackupex --apply-log /data/backup/full_2020-11-17/

# 数据恢复

cp -r /data/backup/full_2020-11-17/* /data/mysql/

chown -R mysql:mysql /data/mysql/

/etc/init.d/mysqld start

5.4、增量备份

增量备份原理图解：

5.4.1、模拟xbk增量备份

周三的from_lsn—9==一定等于=周二的last_lsn (说明备份成功)

同一次备份：to_lsn+9=last_lsn=说明本次备份没有发生数据的变化

(1)增量备份的方式，是基于上一次备份进行增量。

(2)增量备份无法单独恢复。必须基于全备进行恢复。

(3)所有增量必须要按顺序合并到全备中。

(1)删掉原来备份

略.

(2)全备(周日)

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F` >&/tmp/xbk_full.log

(3)模拟周一数据变化

create database cs charset utf8;

use cs

create table t1 (id int);

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

commit;

(4)第一次增量备份(周一)

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/full_2020-11-17/ /data/backup/inc1 &>/tmp/inc1.log

参数说明：

--incremental 开启增量备份开关

--incremental-basedir 增量备份参照物(上一天的)

/data/backup/inc1 增量备份路径

(5)模拟周二数据

create table t2 (id int);

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

commit;

(6)周二增量,切忌--incremental-basedir要是前一天的数据目录

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2 &>/tmp/inc2.log

(7)模拟周三数据变化

create table t3 (id int);

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

commit;

drop database cs;

5.4.2、恢复到周三误drop之前的数据状态

恢复思路：

1. 挂出维护页，停止当天的自动备份脚本

2. 检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志

3. 进行备份整理(细节)，截取关键的二进制日志(从备份——误删除之前)

4. 测试库进行备份恢复及日志恢复

5. 应用进行测试无误，开启业务

6. 此次工作的总结

增量备份恢复逻辑(恢复到周三)：

恢复过程：

1. 检查备份

1afe8136-601d-11e9-9022-000c2928f5dd:7-9

- 周三的from_lsn—9==一定等于=周二的last_lsn (说明备份成功)

- 同一次备份：to_lsn+9=last_lsn=说明本次备份没有发生数据的变化

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

2. 备份整理(apply-log)+合并备份(full+inc1+inc2)

# 参数说明

--apply-log 只合并redo日志，且最后一次合并不能加！！！

# 四步操作：

(1) 全备的整理

[root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full

(2) 合并inc1到full中

[root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full

(3) 合并inc2到full中

[root@db01 one]# innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full

(4) 最后一次整理全备

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

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

3. 截取周二 23:00 到drop 之前的 binlog

#起点：最后一次备份记录的d6caac23-2863-11eb-9bba-000c29af70c3:1-9，就是起点，恢复什么数据都是拿这个做起点

[root@master backup]# cat /data/backup/inc2/xtrabackup_binlog_info

mysql-bin.0000022263d6caac23-2863-11eb-9bba-000c29af70c3:1-9,

f9511c39-1e28-11eb-8a8e-000c29af70c3:1-16

#终点：找到drop时间的gtid，如果是rm就是末尾，不用指定gtid终点

| mysql-bin.000002 | 2112 | Gtid 2177 | SET @@SESSION.GTID_NEXT= 'd6caac23-2863-11eb-9bba-000c29af70c3:9'

| mysql-bin.000002 | 2177 | Query 2263 | drop database cs

[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='d6caac23-2863-11eb-9bba-000c29af70c3:1-8' /data/mysql/binlog/mysql-bin.000002 >/data/backup/binlog.sql

4. 进行恢复

[root@db01 backup]# mkdir /data/mysql/data2 -p

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

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

[root@db01 backup]# systemctl stop mysqld

vim /etc/my.cnf

datadir=/data/mysql/data2

systemctl start mysqld

Master [(none)]>set sql_log_bin=0;

Master [(none)]>source /data/backup/binlog.sql

拓展:

1、获得表结构

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`' full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql