导航菜单
首页 » 无极荣耀注册 » 正文

动车-数据库材料解惑 | 跟码神学MySQL:备份康复

java数据库材料解惑 | 跟码神学MySQL:备份康复

1. 运维在数据库备份康复方面的责任


1.1 规划备份战略

全备、增量、时间、主动

1.2 日常备份查看

备份存在性

备份空间够用否

1.3 定时康复演练(测验库)

一季度 或许 半年

1.4 毛病康复

通过现有备份,能够将数据库康复到毛病之前的时间点

1.5 搬迁

1. 停机时间

2. 回退计划

2. 备份类型


2.1 热备

在数据库正常事务时,备份数据,而且能够一致性康复(只能是innodb)

对事务影响十分小

2.2 温备

锁表备份,只能查询不能修正(myisam)

影响到写入操作

2.3 冷备

封闭数据库事务,数据库没有任何改变的情况下,进行备份数据.

事务中止

3. 备份办法及东西介绍


3.1 逻辑备份东西

依据SQL句子进行备份

mysqldump *****

mysqlbinlog *****

3.2 物理备份东西

依据磁盘数据文件备份

xtrabackup(XBK) :percona 第三方 *****

MySQL Enterprise Backup(MEB)

4. 逻辑备份和物理备份的比较


4.1 mysqldump (MDP)

长处:

1.不需求下载装置

2.备份出来的是SQL,文本格式,可读性高,便于备份处理

3.紧缩比较高,节约备份的磁盘空间

缺陷:

4.依靠于数据库引擎,需求从磁盘把数据读出

然后转换成SQL进行转储,比较消耗资源,数据量大的话功率较低

主张:

100G以内的数据量级,能够运用mysqldump

超越TB以上,咱们也或许挑选的是mysqldump,合作分布式的体系

1EB =1024 PB =1000000 TB

4.2 xtrabackup(XBK)

长处:

1.类似于直接cp数据文件,不需求管逻辑结构,相对来动车-数据库材料解惑 | 跟码神学MySQL:备份康复说功用较高

缺陷:

2.可读性差

3.紧缩比低,需求更多磁盘空间

主张:

>100G

5.备份战略


备份办法:

全备:全库备份,备份一切数据

增量:备份改变的数据

逻辑备份=mysqldump+mysqlbinlog

物理备份=xtrabackup_full+xtrabackup_incr+binlog或许xtrabackup_full+binlog

备份周期:

依据数据量规划备份周期

比方:周日全备,周1-周6增量

6.备份东西运用-mysqldump


6.1 mysqldump (逻辑备份的客户端东西)

6.1.1 客户端通用参数

-u -p -S -h -P

本地备份:

mysqldump -uroot -p -S /tmp/mysql.sock

长途备份:

mysqldump -uroot -p -h 10.0.0.51 -P3306

6.1.2 备份专用基本参数

比方1:

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

mysqldump -uroot -p -A >/data/backup/full.sql

Enter password:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

# 弥补:

# 1.惯例备份是要加 --set-voa慢速英语gtid-purged=OFF,处理备份时的正告

# [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=OFF >/backup/full.sql

# 2.构建主从时,做的备份,不需求加这个参数

# [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=ON >/backup/full.sql

-B db1 db2 db3 备份多个单库

阐明:出产中需求备份,出产相关的库和MySQL库

比方2 :

mysqldump -B mysql gtid --set-gtid-purged=OFF >/data/backup/b.sql

备份单个或多个表

比方3 world数据库下的city,country表

mysqldump -uroot -p world city country >/backup/bak1.sql

以上备份康复时:有必要库事前存在,而且ues才干source康复

6.1.3 高档参数运用

特别参数1运用(有必要要加)

-R 备份存储进程及函数

--triggers 备份触发器

-E 备份事情

比方4:

[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql

(5) 特别参数2运用

-F 在备份开端时,改写一个新binlog日志

比方5:

mysqldump -uroot -p -A -R --triggers -F >/bak/full.sql

--master-data=2

以注释的办法,保存备份开端时间点的binlog的状况信息

mysqldump -uroot -p -A -R --triggers --master-data=2 >/back/world.sql

[root@db01 ~]# grep 'CHANGE' /backup/world.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功用:

(1)在备份时,会主动记载,二进制日志文件名和方位号

0 默认值

1 以change master to指令办法,能够用作主从复制

2 以注释的办法记载,备份时间的文件名+postion号

(2) 主动锁表

(3)假如合作--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行“热“”备,实际上是完成快照备份。

--single-transaction

innodb 存储引擎敞开热备(快照备份)功用

master-data能够主动加锁

(1)在不加--single-transaction ,发动一切表的温备份,一切表都确定

(1)加上--single-transaction ,对innodb进行快照备份,对非innodb表能够完成主动锁表功用

比方6: 备份必加参数

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

--set-gtid-purged=auto

auto , on

off

运用场景:

1. --set-gtid-purged=OFF,能够运用在日常备份参数中.

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

2. auto , on:在构建主从复制环境时需求的参数装备

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-t动车-数据库材料解惑 | 跟码神学MySQL:备份康复ransaction --set-gtid-purged=ON >/data/backup/full.sql

--max-allowed-packet=#

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

--max-allowed-packet=#

The maximum packet length to send to or receive from server.

6.2 小操练:

6.2.1. 完成一切表的独自备份

提示:

information_schema.tables

mysqldump -uroot -p123 world city >/backup/world_city.sql

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single动车-数据库材料解惑 | 跟码神学MySQL:备份康复-transaction --set-gtid-purged=0 -R -E --triggers>/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');

6.2.2.模仿毛病事例并康复

(1)每天全备

(2)binlog日志是完好

(3)模仿白日的数据改变

(4)模仿下午两点误删去数据库

需求: 运用全备+binlog回复数据库误删去之前。

毛病模仿及康复:

1. 模仿周一23:00的全备

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

2. 模仿白日的数据改变

Master [(none)]>create database day1 charset utf8;

Master [(none)]>use day1

Master [day1]>create table t1(id int);

Master [day1]>insert into t1 values(1),(2),(3);

Master [day1]>commit;

Master [world]>update city set countrycode='CHN';

Master [world]>commit;

模仿磁盘损坏:

[root@db01 data]# \rm -rf /data/mysql/data/*3. 康复毛病

[root@db01 data]# pkill mysqld[root@db01 data]# \rm -rf /data/mysql/data/*4. 康复思路

1.查看备份可用性

2.从备份中获取二进制日志方位

3.依据日志方位截取需求的二进制日志

4.初始化数据库,并发动

5.康复全备

6.康复二进制日志

6.3. 紧缩备份并添加时间戳

比方:

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

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

mysqldump备份的康复办法(在出产中康复要慎重,康复会删去重复的表)

set sql_log_bin=0;

source /backup/full_2018-06-28.sql

留意:1、mysqldump在备份和康复时都需求mysql实例发动为条件。2、一般数据量级100G以内,大约15-45分钟能够康复,数据量级很大很大的时分(PB、EB)3、mysqldump是掩盖办法康复的办法。

一般咱们以为,在同数据量级,物理备份要比逻辑备份速度快.

逻辑备份的优势:1、可读性强2、紧缩比很高

7、企业毛病康复事例


7.1 布景环境:

正在运转的网站体系,mysql-5.7.20 数据库,数据量50G,日事务增量1-5M。

7.2 备份战略:

天23:00点,计划任务调用mysqldump履行全备脚本

7.3 毛病时间点:

年末毛病演练:模仿周三上午10点误删去数据库,并进行康复

7动车-数据库材料解惑 | 跟码神学MySQL:备份康复.4 思路:

1、停事务,防止数据的二次损伤

2、找一个暂时库,康复周三23:00全备

3、截取周二23:00 --- 周三10点误删去之间的binlog,康复到暂时库

4、测验可用性和完好性

5、

5.1 办法一:直接运用暂时库代替原出产库,前端运用割接到新库

5.2 办法二:将误删去的表导出,导入到原出产库

6、敞开事务

处理结果:通过20分钟的处理,终究事务康复正常

7.5 毛病模仿演练

7.5.1 预备数据

create database backup;

use backup

create table t1 (id int);

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

commit;

rm -rf /backup/*

7.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

7.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);

7.5.4 模仿毛病,删去表(仅仅模仿,不代表出产操作)

drop database backup;

7.6 康复进程

7.6.1 预备暂时数据库(多实例3307)

systemctl start mysqld3307

7.6.2 预备备份

(1)预备全备:

cd /backup

gunzip full_2018-10-17.sql.gz

(2)截取二进制日志

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=793;

mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:6-7' /data/binlog/mysql-bin.000036 >/backup/bin.sql

7.6.3 康复备份到暂时库

mysql -S /data/3307/mysql.sock

set sql_log_bin=0;

source /backup/full_2018-10-17.sql

source /backup/bin.sql

7.6.4 将毛病表导出并康复到出产

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

mysql -uroot -p123

set sql_log_bin=0

use backup

source /backup/t1.sql;

8. 课下作业:


操练:

1、创立一个数据库 oldboy

2、在oldboy下创立一张表t1

3、刺进5行恣意数据

4、全备

5、刺进两行数据,恣意修正3行数据,删去1行数据

6、删去一切数据

7、再t1中又刺进5行新数据,修正3行数据

需求,越过第六步康复表数据

写备份脚本和战略

9. 备份时优化参数:

(1) max_allowed_packet 最大的数据包巨细

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

(2) 添加key_buffer_size (暂时表有关)

(3) 分库分表并发备份 (作业)

(4) 架构别离,别离备份 (架构拆分,分布式备份)

10. MySQL物理备份东西-xtrabackup(XBK、Xbackup)

10.1装置

10.1.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

10.1.2 下载软件并装置

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

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

10.2 备份指令介绍:

xtrabackup

innobackupex ******

10.3 备份办法——物理备份

(1)关于非Innodb表(比方 myisam)是,锁表cp数据文件,归于一种温备份。

(2)关于Innodb的表(支撑事务的),不锁表,复制数据页,终究以数据文件的办法保存下来,把一部分redo和undo同时备走,归于热备办法。

面试题: xbk 在innodb表备份康复的流程

0、xbk备份履行的瞬间,当即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记载此刻的LSN号

1、备份时,复制磁盘数据页,而且记载备份进程中发生的redo和undo一同复制走,也便是checkpoint LSN之后的日志

2、在康复之前,模仿Innodb“主动毛病康复”的进程,将redo(前滚)与undo(回滚)进行运用

3、康复进程是cp 备份到本来数据目录下

10.4、innobackupex运用

10.4.1 全备

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

自主定制备份路径名

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

备份会集多出来的文件:

-rw-r----- 1 root root 24 Jun 29 09:59 xtrabackup_binlog_info

-rw-r----- 1 root root 119 Jun 29 09:59 xtrabackup_checkpoints

-rw-r----- 1 root root 489 Jun 29 09:59 xtrabackup_info

-rw-r----- 1 root root 2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info :(备份时间的binlog方位)

[root@db01 full]# cat xtrabackup_binlog_info

mysql-bin.000003 536749

79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7

记载的是备份时间,binlog的文件姓名和其时的完毕的position,能够用来作为截取binlog时的起点。

xtrabackup_checkpoints :

backup_type = full-backuped

from_lsn = 0 前次所抵达的LSN号(关于全备便是从0开端,关于增量有其他显现办法)

to_lsn = 160683027 备份开端时间(ckpt)点数据页的LSN

last_lsn = 160683036 备份完毕后,redo日志终究的LSN

compact = 0

recover_binlog_info = 0

(1)备份时间,当行将现已commit过的,内存中的数据页改写到磁盘(CKPT).开端备份数据,数据文件的LSN会停留在to_lsn方位。

(2)备份时间有或许会有其他的数据写入,已备走的数据文件就不会再发生改变了。

(3)在备份进程中,备份软件会一向监控着redo的undo,假如一旦有改变会将日志也同时备走,并记载LSN到last_lsn。

从to_lsn ----》last_lsn 便是,备份进程中发生的数据改变.

10.4.2 全备的康复

预备备份(Prepared)

将redo进行重做,已提交的写到数据文件,未提交的运用undo回滚掉。模仿了CSR的进程

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

康复备份

条件:

1、被康复的目录是空

2、被康复的数据库的实例是封闭

systemctl stop mysqld

创立新目录

[root@db01 backup]# mkdir /data/mysql1

数据授权

chown -R mysql.mysql /data/mysql1

康复备份

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

发动数据库

vim /etc/my.cnf

datadir=/data/mysql1

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

systemctl start mysqld

10.4.3 innobackupex 增量备份(incremental)

(1)增量备份的办法,是依据上一次备份进行增量。

(2)增量备份无法独自康复。有必要依据全备进行康复。

(3)一切增量有必要要按次序兼并到全备中。

增量备份指令

(1)删掉本来备份

略.

(2)全备(周日)

[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log

(3)模仿周一数据改变

db01 [(none)]>create database cs charset utf8;

db01 [(none)]>use cs

db01 [cs]>create table t1 (id int);

db01 [cs]>insert into t1 values(1),(2),(3);

db01 [cs]>commit;

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

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

(5)模仿周二数据

db01 [cs]>create table t2 (id int);

db01 [cs]>insert into t2 values(1),(2),(3);

db01 [cs]>commit;

(6)周二增量

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

(7)模仿周三数据改变

db01 [cs]>create table t3 (id int);

db01 [cs]>insert into t3 values(1),(2),(3);

db01 [cs]>commit;

db01 [cs]>drop database cs;

康复到周三误drop之前的数据状况

康复思路:

1. 挂出保护页,中止当天的主动备份脚本

2. 查看备份:周日full+周一inc1+周二inc2,周三的完好二进制日志

3. 进行备份收拾(细节),截取要害的二进制日志(从备份——误删去之前)

4. 测验库进行备份康复及日志康复

5. 运用进行测验无误,敞开事务

6. 此次作业的总结

康复进程

1. 查看备份

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

2. 备份收拾(apply-log)+兼并备份(full+inc1+inc2)

(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

[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/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

Xtrabackup企业级增量康复实战

布景:

某大型网站,mysql数据库,数据量500G,每日更新量20M-30M

备份战略:

xtrabackup,每周日0:00进行全备,周一到周六00:00进行增量备份。

毛病场景:

周三下午2点呈现数据库意外删去表操作。

怎么康复?

作业2

操练:mysqldump备份康复比方

1、创立一个数据库 oldboy

2、在oldboy下创立一张表t1

3、刺进5行恣意数据

4、全备

5、刺进两行数据,恣意修正3行数据,删去1行数据

6、删去一切数据

7、再t1中又刺进5行新数据,修正3行数据

需求,越过第六步康复表数据

作业3

别离写备份脚本和战略

作业4:备份会集独自康复表

考虑:在之前的项目事例中,假如误删去的表只要10M,而备份有500G,该怎么快速康复误删去表?

提示:

drop table city;

create table city like city_bak;

alter table city discard tablespace;

cp /backup/full/world/city.ibd /application/mysql/data/world/

chown -R mysql.mysql /application/mysql/data/world/city.ibd

alter table city import tablespace;

作业5:从mysqldump全备中获取库和表的备份

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 >wo

二维码