mysql备份

database

一.备份的原因

运维工作的核心简单概括就两件事:

1)第一个是保护公司的数据.

2)第二个是让网站能7*24小时提供服务(用户体验)。

1)备份就是为了恢复。

2)尽量减少数据的丢失(公司的损失)

二.备份的类型

冷备份:

这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

停库,停服务,备份

温备份:(锁表)

这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

热备份:

这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能。使用热备份时,系统仍可供读取和修改数据的操作访问。

三.备份的方式

逻辑备份:

基于SQL语句的备份

1)binlog

2)into outfile

mysql> select * from world.city into outfile '/tmp/world_city.data'; #配置文件中创建/tmp

3)mysqldump只支持全备

4)replication

5 ) mysqlbinlog

物理备份:

cp(停库)

基于数据文件的备份

1)Xtrabackup(percona公司)

四.备份策略

1)全量备份 full

2)增量备份 increamental

3)差异备份

五.备份工具

1.逻辑

1)mysqldump(逻辑)

mysql原生自带很好用的逻辑备份工具

2)mysqlbinlog(逻辑)

实现binlog备份的原生态命令

3)into outfile

4)replication

2.xtrabackup(物理)

precona公司开发的性能很高的物理备份工具

备份工具使用

mysqldump的备份

mysqldump常用参数

1)连接服务端参数(基本参数):-u -p -h -P -S

2)-A, --all-databases:全库备份

[root@db01 ~]# mysqldump -uroot -poldboy123 -A > /backup/full.sql

3)不加参数:单库、单表多表备份

[root@db01 ~]# mysqldump -uroot -poldboy123 db1 > /backup/db1.sql

[root@mysql-db01 backup]# mysqldump -uroot -poldboy123 world city > /backup/city.sql

4)-B:指定库备份或者多个库

[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql

[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql

5)-F:flush logs在备份时自动刷新binlog(不怎么常用)

[root@db01 backup]# mysqldump -uroot -p123 -A -R –triggers -F > /backup/full_2.sql

6)--master-data=2:备份时加入change master语句0没有1不注释2注释

[root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql

7)-d:仅表结构

8)-t:仅数据

备份额外扩展项

1)-R, --routines:备份存储过程和函数数据

2)--triggers:备份触发器数据

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers > /backup/full_2.sql

mysqldump特殊参数

1)-x:锁表备份(myisam温备份)

2)--single-transaction:快照备份

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction>/backup/$(date +%F)full.sql

3)gzip:压缩备份

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction|gzip>/backup/full_$(date +%F)_sql.gz

mysqldump的恢复

方法一:

#先不记录二进制日志

mysql> set sql_log_bin=0;

#库内恢复操作,指定库

mysql> source /backup/full.sql

#库外恢复操作,指定库

[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql

方法二:zcat full_$(date +%F)_sql.gz | mysql -uroot -p1

注意:

1)mysqldump在备份和恢复时都需要MySQL实例启动为前提

2)一般数据量级100G以内,大约15-30分钟可以恢复(PB、EB就需要考虑别的方式-物理备份)

3)mysqldump是以覆盖的形式恢复数据的

企业故障恢复案例

背景:

正在运行的网站系统,MySQL数据库,数据量25G,日业务增量10-15M。

备份策略:

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

故障时间点:

上午10点开发人员误删除一个核心业务表,如何恢复?

思路:

1)停业务避免数据的二次伤害

2)找一个临时的库,恢复前一天的全备

3)截取前一天23:00到第二天10点误删除之间的binlog,恢复到临时库

4)测试可用性和完整性

5)开启业务前的两种方式

a.直接使用临时库顶替原生产库,前端应用割接到新库

b.将误删除的表单独导出,然后导入到原生产环境

6)开启业务

故障模拟演练:

准备数据:

#刷新binlog使内容更清晰

mysql> flush logs;

#查看当前使用的binlog

mysql> show master status;

#创建backup库

mysql> create database backup;

#进入backup库

mysql> use backup

#创建full表

mysql> create table full select * from world.city;

#创建full_1表

mysql> create table full_1 select * from world.city;

#查看表

mysql> show tables;

全备:

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

模拟数据变化:

#进入backup库

mysql> use backup

#创建new表

mysql> create table new select * from mysql.user;

#创建new_1表

mysql> create table new_1 select * from world.country;

#查看表

mysql> show tables;

#查看full表中所有数据

mysql> select * from full;

#把full表中所有的countrycode都改成CHN

mysql> update full set countrycode='CHN' where 1=1;

#提交

mysql> commit;

#删除id大于200的数据

mysql> delete from full where id>200;

#提交

mysql> commit;

模拟故障:

#删除new表

mysql> drop table new;

#查看表

mysql> show tables;

恢复过程:

==停业务,在停数据库==

1)准备临时数据库

#多实例

[root@db02 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &

#新的服务(初始化一个新环境,二进制安装mysql)

[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

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

2)拷贝数据到新库上

[root@db01 ~]# scp /backup/full_2018-08-16.sql.gz root@10.0.0.52:/tmp

3)解压全备数据文件

#进入tmp目录

[root@db02 ~]# cd /tmp/

#查询全备的起始点

[root@db01 ~]# zcat /tmp/full_2019-11-13.sql.gz |head -25

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=268002

#找到drop语句执行的位置点(结束位置点)

#方法一

[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000031|less

#按/搜索DROP

#方法二

[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000031|grep -i -B 5 'drop table'

# at 408012

#191113 19:25:24 server id 1 end_log_pos 408135 CRC32 0xf636afe1 Querythread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1573644324/*!*/;

DROP TABLE `full_1` /* generated by server */

#方法三(数据大,不好查看)

mysql>show binary logs;

mysql>show binlog events in 'mysql-bin.000004';

#截取二进制

[root@db01 tmp]#mysqlbinlog -uroot -p1 --start-position=268002 --stop-position=671148 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql

#发送增量数据到新库

[root@db01 tmp]# scp /tmp/inc.sql root@10.0.0.52:/tmp

在新库内恢复数据

mysql>use +库

#不记录二进制日志

mysql> set sql_log_bin=0;

#恢复全备数据

mysql> source /tmp/full_2018-08-16.sql

#库外导入

mysql backup </tmp/inc.sql

#进入backup库

mysql> use backup

# 查看表

mysql> show tables;

#恢复增量数据

mysql> source /tmp/inc.sql

#查看表

mysql> show tables;

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

#导出new表

[root@db02 ~]# mysqldump -uroot -p123 -S /data/3307/mysql.sock backup new > /tmp/new.sql

#发送到生产库

[root@db02 ~]# scp /tmp/new.sql root@10.0.0.51:/tmp/

#进入backup库

mysql> use backup

#在生产库恢复数据

mysql>set sql_log_bin=0;

mysql> source /tmp/new.sql

#查看表

mysql> show tables;

5)启动服务

tomcat   php  ....

物理备份(Xtrabackup)

Xtrabackup安装

#下载epel源

wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo

#安装依赖

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#下载Xtrabackup

wget httpss://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

备份方式(物理备份)

1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。

2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。

3)备份时读取配置文件/etc/my.cnf

全量备份

#全备

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

#避免时间戳,自定义路径名

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

#查看备份路径中的内容

[root@db01 backup]# ll /backup/full

#记录binlog文件名和binlog的位置点

-rw-r----- 1 root root 21 Aug 16 06:23 xtrabackup_binlog_info

#备份时刻,立即将已经commit过的内存中的数据页刷新到磁盘

#备份时刻有可能会有其他数据写入,已备走的数据文件就不会再发生变化了

#在备份过程中,备份软件会一直监控着redo和undo,一旦有变化会将日志一并备走

-rw-r----- 1 root root 117 Aug 16 06:23 xtrabackup_checkpoints

#备份汇总信息

-rw-r----- 1 root root 485 Aug 16 06:23 xtrabackup_info

#备份的redo文件

-rw-r----- 1 root root 2560 Aug 16 06:23 xtrabackup_logfile

全备的恢复

准备备份

将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程

[root@db01 full]# innobackupex --user=root --password=123 --apply-log /backup/full

恢复备份(==注意配置文件中指定basedir .datadir,sock, 客户端指定sock路径==)

前提1:被恢复的目录是空的

前提2:被恢复的数据库的实例是关闭的

#停库

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

#进入mysql目录

[root@db01 full]# cd /application/mysql

#删除data目录(在生产中可以备份一下)

[root@db01 mysql]# rm -fr data/

#恢复redo跟undo文件

[root@db01 full]# innobackupex --user=root --password=123 --apply-log /backup/full

#拷贝数据(停库不需要用户跟密码)

[root@db01 mysql]# innobackupex --copy-back /backup/ful

#授权

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

#启动MySQL

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

增量备份及恢复

备份方式

1)基于上一次备份进行增量

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

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

#不使用之前的全备,执行一次全备

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

模拟数据变化

mysql> create database inc1;

mysql> use inc1

mysql> create table inc1_tab(id int);

mysql> insert into inc1_tab values(1),(2),(3);

mysql> commit;

mysql> select * from inc1_tab;

第一次增量备份

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

参数说明:

--incremental:开启增量备份功能

--incremental-basedir:上一次备份的路径

再次模拟数据变化

mysql> create database inc2;

mysql> use inc2

mysql> create table inc2_tab(id int);

mysql> insert into inc2_tab values(1),(2),(3);

mysql> commit;

第二次增量备份

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2

增量恢复

#破坏数据

[root@db01 ~]# rm -fr /application/mysql/data/

准备备份

1)full+inc1+inc2

2)需要将inc1和inc2按顺序合并到full中

3)分步骤进行--apply-log

第一步:在全备中apply-log时,只应用redo,不应用undo

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

第二步:合并inc1合并到full中,并且apply-log,只应用redo,不应用undo

[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-basedir=/backup/inc1/ /backup/full/

第三步:合并inc2合并到full中,redo和undo都应用

[root@db01 ~]# innobackupex --apply-log --incremental-basedir=/backup/inc2/ /backup/full/

第四步:整体full执行apply-log,redo和undo都应用

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

copy-back

[root@db01 ~]# innobackupex --copy-back /backup/full/

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

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

差异备份

[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp /backup/full_1

backup_type = full-backuped

from_lsn = 0

to_lsn = 1025176220

#1.第一次差异备份

[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --

incremental-basedir=/backup/full_1 /backup/chayi1

[root@db01 backup]# cat chayi1/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1025176220

to_lsn = 1025202474

#2.第二次差异备份

[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --

incremental-basedir=/backup/full_1 /backup/chayi2

backup_type = incremental

from_lsn = 1025176220

to_lsn = 1025208985

#3.第三次差异备份

[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --

incremental-basedir=/backup/full_1 /backup/chayi3

#4.第四次差异备份

[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --

incremental-basedir=/backup/full_1 /backup/chayi4

backup_type = incremental

from_lsn = 1025176220

to_lsn = 1025234244

恢复数据

#1.停数据库

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

#2.删除data目录或者备份

[root@db01 backup]# rm -fr /application/mysql/data/*

#3.模拟CSR合并数据

full_1 + chayi1 + chayi2 + chayi3 + chayi4

1)full_1只做redo 不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_1/

2)将chayi4合并到full_1 redo undo 都做

[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi4

/backup/full_1/

3)将full_1 redo undo 都做

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

#4.copy back

[root@db01 backup]# innobackupex --copy-back /backup/full_1/

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

模拟生产环境

#!/bin/bash

while true;do

mysql -uroot -p1 -e 'insert into mysqldump.mysqldump values(1);commit;'

sleep 2

done

优点:

1.备份的时候,方便

2.恢复的时候也方便

缺点:

重复数据多,占用磁盘空间大

思考:

企业级增量恢复实战

背景:

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

备份策略:

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

故障场景:

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

如何恢复???

全量备份

1.全备

innobackupex --user=root --password=1 --no-timestamp /backup/full_$(data +%F-%H)

2.增备

innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_$(data +%F-%H) /backup/cre_$(date +%F-%H)

意外发生

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

解决办法:

1准备新环境

2.停库

==3.先备份,删除/data目录==(找寻截止点)

4.恢复数据

1最近的全备中执行apply-log 只应用redo

innobackupex --apply-log --redo-only /backup/full_$(data +%F-%H)

2.找寻周一,二晚上00:00的增量备份,合并

innobackupex --apply-log --redo-only --incremental-dir=/backup/cre_$(date +%F-%H) /backup/full_$(date +%F-%H)

3.redo.undo都应用

innobackupex --apply-log --incremental-basedir=cre_$(date +%F-%H) /backup/full_$(date +%F-%H)

4.整体全备执行

innobackupex --apply-log /backup/full_$(date +%F-%H)

5.copy-back

innobackupex --copy-back /backup/full_$(date +%F-%H)

6.授权

chown -R mysql.mysql /application/mysql/data

7.启动mydql

/etc/init.d/mysqld start

5.确定起始点

[root@db01 full_2019-12-12-00]# cat xtrabackup_binlog_info 

mysql-bin.000001 15724

6.确定截止点

mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.00000.1| grep -i -B 5'drop table'

7.截取binlog

mysqlbinlog -uroot -p1 --start-position=    --stop-position= /application/mysql/data/mysql-bin.00000.1 >/tmp/inc.sql

8.发送增量数据到新库

scp /tmp/inc.sql root@10.0.0.52:/tmp

9.在新库内恢复

mysql>use +库

#不记录二进制日志

mysql> set sql_log_bin=0;

#恢复全备数据

mysql> source /tmp/full_2018-08-16.sql

10.恢复生产

修改链接代码

11.启动服务

以上是 mysql备份 的全部内容, 来源链接: utcz.com/z/531374.html

回到顶部