美文网首页
MySQL逻辑和物理备份

MySQL逻辑和物理备份

作者: 吃可爱长大鸭 | 来源:发表于2019-12-11 14:28 被阅读0次

MySQL逻辑备份-企业案例

-t:仅备份表结构

-d:仅备份数据

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

企业故障恢复案例

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

备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本

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

故障时间点：

周三上午10点开发人员误删除一个核心业务表，如何恢复？

思路:

1.停库(避免二次伤害数据)

# 先停tomcat,php-fpm,nginx
[root@db02 ~]# /etc/init.d/mysqld stop

2.准备一个新环境

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

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

3.将昨天的全备,发送给新环境

[root@db02 ~]# scp /tmp/full_2019-12-11.sql.gz 172.16.1.51:/tmp

4.将全备导入新环境

[root@db01 scripts]# zcat /tmp/full_2019-12-11.sql.gz |mysql

5.截取昨天23点到今天10点之间的数据

[root@db02 data]# zcat /backup/full_2019-12-11.sql.gz |head -22

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=809492;

[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003|less
818556

[root@db02 data]# mysqlbinlog --start-position=809492 --stop-position=818556 mysql-bin.000003 > /backup/inc.sql


818679
863424

[root@db02 data]# mysqlbinlog --start-position=818679 --stop-position=863424 mysql-bin.000003 > /backup/inc2.sql

[root@db02 data]# scp /backup/inc* 172.16.1.51:/root

6.恢复业务

- 将业务应用割接
- 导出核心业务表,恢复到旧库

MySQL物理备份

备份方式

1.cp data

2.Xtrabackup

备份策略

1.全量备份

2.增量备份

3.差异备份

[root@db02 ~]# yum localinstall -y 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@db02 ~]# mkdir /backup/

1.全量备份

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

[root@db01 2019-12-11_10-17-27]# cat xtrabackup_binlog_info 
mysql-bin.000004    48922

[root@db01 2019-12-11_10-17-27]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2308823
last_lsn = 2340198
compact = 0
recover_binlog_info = 0


[root@db01 2019-12-11_10-17-27]# cat xtrabackup_info
uuid = 650301eb-1bbc-11ea-b4c8-000c29e98743
name = 
tool_name = innobackupex
tool_command = --user=root --password=... --socket=/opt/mysql.sock /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.40-log
start_time = 2019-12-11 10:17:27
end_time = 2019-12-11 10:17:35
lock_time = 0
binlog_pos = filename 'mysql-bin.000004', position '48922'
innodb_from_lsn = 0
innodb_to_lsn = 2308823
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

恢复全备

#前提1：被恢复的目录是空的
#前提2：被恢复的数据库的实例是关闭的

#1.停库
[root@db01 2019-12-11_10-17-27]# /etc/init.d/mysqld stop

2.模拟CSR,做一遍redo,再做一遍undo
[root@db01 2019-12-11_10-17-27]# innobackupex --apply-log /backup/2019-12-11_10-17-27
3.保证data目录是空的
[root@db01 mysql]# rm -fr /application/mysql/data
4.恢复数据
[root@db01 mysql]# innobackupex --copy-back /backup/2019-12-11_10-54-55/
5.授权
[root@db01 mysql]# chown -R mysql.mysql /application/mysql/data

增量备份

1.先做全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

[root@db01 backup]# cat full_2019-12-11/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2578215
last_lsn = 2586862
compact = 0
recover_binlog_info = 0


2.第一次，增量备份（基于全备）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F) /backup/inc_$(date +%F-%H)

[root@db01 backup]# cat inc_2019-12-11-11/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2578215
to_lsn = 2662633
last_lsn = 2662633
compact = 0
recover_binlog_info = 0


3.第二次，增量备份（基于第一次增备）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F-%H -d "-1 hour") /backup/inc_$(date +%F-%H)

[root@db01 backup]# cat inc_2019-12-11-12/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2662633
to_lsn = 2738509
last_lsn = 2739784
compact = 0
recover_binlog_info = 0


4.第三次，增量备份（基于第二次增备）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F-%H -d "-1 hour") /backup/inc_$(date +%F-%H)

[root@db01 backup]# cat inc_2019-12-11-13/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2738509
to_lsn = 2773700
last_lsn = 2777706
compact = 0
recover_binlog_info = 0

增量备份恢复

1）基于上一次备份进行增量
2）增量备份无法单独恢复，必须基于全备进行恢复
3）所有增量必须要按顺序合并到全备当中

准备备份

1）full+inc1+inc2
2）需要将inc1和inc2按顺序合并到full中
3）分步骤进行--apply-log

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

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

[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_2019-12-11/

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

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-11-11 /backup/full_2019-12-11/

第三步：合并inc2合并到full中，只做redo不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-11-12 /backup/full_2019-12-11/

第四步：将inc3合并到full中，redo undo都做

[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc_2019-12-11-13 /backup/full_2019-12-11/

第五步：整体full执行apply-log，redo和undo都应用

[root@db01 backup]# innobackupex --apply-log /backup/full_2019-12-11/

第六步：恢复数据

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

[root@db01 backup]# innobackupex --copy-back /backup/full_2019-12-11/

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

脚本：

备份脚本

data=`date +%H`
case ${data} in
     00)
    innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
        echo "全被。"
    ;;
     01)
        innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F) /backup/inc_$(date +%F-%H)
        echo "第一次增备。"
    ;;
      *)
        innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F-%H -d "-1 hour") /backup/inc_$(date +%F-%H)
    echo "每小时增备一次。"
        ;;
esac

恢复脚本

#定义变量
data=`date +%H`
#全备只做redo不做ondo
innobackupex --apply-log --redo-only /backup/full_$(date +%F)
if [ $? -eq 0 ];then
    echo "--redo-only 成功！"
else
    echo "--redo-only 不成功"
fi
#
for i in `seq -f "%02" 1 10 $(date +%H)`
do
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_$(date +%F-%H) /backup/full_$(date +%F)
if [ $? -eq 0 ];then
    echo "--redo-only 成功！"
else
    echo "--redo-only 不成功"
fi
done
innobackupex --apply-log --incremental-dir=/backup/inc_$(date +%F-%H) /backup/full_$(date +%F)
innobackupex --apply-log /backup/full_$(date +%F)
/etc/init.d/mysqld stop
mv /application/mysql/data/ /tmp/
mkdir /application/mysql/data/ -p
innobackupex --copy-back /backup/full_$(date +%F)
chown -R mysql.mysql /application/mysql/data
/etc/init.d/mysqld start

企业案例

思考:

企业级增量恢复实战

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

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

故障场景：
周三下午2点出现数据库意外删除表操作。

全备：1次

增备：4次

00:00~14:00增量数据

模拟数据

#全备：周五的 24点（周六00点）
innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
[root@db01 backup]# cat full_2019-12-14/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23174130
last_lsn = 23174130
compact = 0
recover_binlog_info = 0


#第一次增备：周六的24点（周日的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23174130
to_lsn = 23199586
last_lsn = 23219800
compact = 0
recover_binlog_info = 0

#第二次增备：周日的24点（周一的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-16/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23199586
to_lsn = 23250777
last_lsn = 23250777
compact = 0
recover_binlog_info = 0

#第三次增备：周一的24点（周二的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23250777
to_lsn = 23250777
last_lsn = 23268489
compact = 0
recover_binlog_info = 0

#第四次增备：周二的24点（周三的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-18/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23250777
to_lsn = 23299475
last_lsn = 23299475
compact = 0
recover_binlog_info = 0

思路：

1.先停库

/etc/init.d/mysqld stop

2.准备新环境

./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

3.清空新环境的data目录

rm -fr /application/mysql/data

4.数据合并

  • 将周六的全备，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_2019-12-14/
  • 将周日的增备合并到全备中，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-15 /backup/full_2019-12-14/
  • 将周一的增备合并到全备中，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-16 /backup/full_2019-12-14/
  • 将周二的增备合并到全备中，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-17 /backup/full_2019-12-14/
  • 将周三的增备合并到全备中，redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc_2019-12-18 /backup/full_2019-12-14/

  • 将合并后的full目录整体做一次redo和undo

    [root@db01 backup]# innobackupex --apply-log /backup/full_2019-12-14/

恢复数据

[root@db01 mysql]# innobackupex --copy-back /backup/full_2019-12-14/

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

找数据位置点

#1.起始位置点
[root@db01 full_2019-12-14]# cat xtrabackup_binlog_info
mysql-bin.000001    13620168

#2.结束位置点
[root@db01 full_2019-12-14]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data_bak/mysql-bin.000001 |less
13638936

截取数据

[root@db01 full_2019-12-14]# mysqlbinlog --start-position=13620168 --stop-position=13638936 /application/mysql/data_bak/mysql-bin.000001 > /backup/inc_00_14.sql

13639060
13691523

[root@db01 full_2019-12-14]# mysqlbinlog --start-position=13639060 --stop-position=13691523 /application/mysql/data_bak/mysql-bin.000001 > /backup/inc_14.sql

恢复业务

  • 应用割接(修改代码，连接数据库的IP)
  • 备份表，导入旧库
[root@db01 mysql]# mysqldump zls1 student2 > /backup/student2.sql
[root@db01 mysql]# mysql zls1 < /backup/student2.sql

脚本

备份

data=`date +%w-%H`
case ${data} in 
       6-00)
            innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
        echo "全备"
            ;;
       0-00)
        innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
            echo "第一次增备"
            ;;
          *)
            innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
            echo "周一到到周五的增备"
            ;;
esac

恢复

#先停库
/etc/init.d/mysqld stop 
#全备只做redo不做ondo
innobackupex --apply-log --redo-only /backup/full_$(date +%F)
if [ $? -eq 0 ];then
    echo "--redo-only 成功！"
else
    echo "--redo-only 不成功"
fi
#循环周日到周三合并到全备里只做redo不做ondo
for i in `seq 0 3 $(date +%w)`
do
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_$(date +%F -d "-1 day") /backup/full_$(date +%F)
if [ $? -eq 0 ];then
    echo "--redo-only 成功！"
else
    echo "--redo-only 不成功"
fi
done
#将周三的增备合并到全备中，redo和undo都做
innobackupex --apply-log --incremental-dir=/backup/inc_$(date +%F -d "-1 day") /backup/full_$(date +%F)
#将合并后的full目录整体做一次redo和undo
innobackupex --apply-log /backup/full_$(date +%F)
#恢复数据
innobackupex --copy-back /backup/full_$(date +%F)
#移动data目录
mv /application/mysql/data/ /tmp/
#授权目录
chown -R mysql.mysql data
#启动数据库
/etc/init.d/mysqld start

差异备份，恢复

#全备
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp /backup/full
[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23436422
last_lsn = 23436422
compact = 0
recover_binlog_info = 0


#第一次差异备份
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi1
[root@db01 backup]# cat chayi1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23436422
to_lsn = 23445236
last_lsn = 23445236
compact = 0
recover_binlog_info = 0


#第二次差异备份
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi2
[root@db01 backup]# cat chayi2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23436422
to_lsn = 23450202
last_lsn = 23450202
compact = 0
recover_binlog_info = 0


#第三次差异备份
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi3
[root@db01 backup]# cat chayi3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23436422
to_lsn = 23452936
last_lsn = 23453235
compact = 0
recover_binlog_info = 0

恢复数据

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

#2.准备新环境
#3.合并数据
    #3.1全备只做redo不做undo
    [root@db01 backup]# innobackupex --apply-log --redo-only /backup/full
    #3.2合并最后一次差异，redo和undo都做
    [root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi3 /backup/full
    #3.3全备整体做一次redo和undo
    [root@db01 backup]# innobackupex --apply-log /backup/full
#4.清空data目录
[root@db01 backup]# mv /application/mysql/data /application/mysql/data_bak

#5.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/full
#6.授权
[root@db01 backup]# chown -R mysql.mysql /application/mysql/data
#7.启动
[root@db01 backup]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/application/mysql/data/db01.err'.
. SUCCESS!

相关文章

  • 重新认识mysqldump

    mysql数据备份从备份方式上可分为物理备份和逻辑备份，物理备份有mysql企业版自带工具，或者选择percon...

  • Mysql的备份方式与备份策略

    1、MYSQL数据的备份方式有哪些？ 答：备份方式有：物理备份、逻辑备份、冷热备份； 物理备份：通...

  • MySQL逻辑和物理备份

    MySQL逻辑备份-企业案例 -t:仅备份表结构 -d:仅备份数据 企业故障恢复案例 故障时间点： 周三上午10点...

  • Xtrabackup备份mysql

    xtrabackup备份mysql，是逻辑物理备份，主要备份表空间。 1.当mysql是新的，只执行过set pa...

  • Mysql备份还有这么多套路，还不了解下？

    逻辑备份和物理备份 逻辑备份 逻辑备份用于备份数据库的结构（CREAET DATABASE、CREATE TABL...

  • 2019-11-29 Mysql备份数据

    逻辑备份和物理备份 逻辑备份 逻辑备份用于备份数据库的结构（CREAET DATABASE、CREATE TABL...

  • 迁移 RDS for MySQL 数据到本地 MySQL

    阿里云数据库 MySQL 版支持通过物理备份文件和逻辑备份文件两种途径将云上数据迁移到本地数据库。利用物理备份文件...

  • mysql主从备份

    备份 一般分为物理备份(物理文件)和逻辑备份(sql语句)物理备份 只要备份物理文件 速度快不跨平台 linux ...

  • mysql之数据备份

    数据备份主要有2种：物理备份和逻辑备份。物理备份：通过把数据文件复制出来，达到备份的目的，需要收费。逻辑备份：把描...

  • MySQL 逻辑备份、物理备份、增量备份

    备份 逻辑备份mysqldump -u用户 -p密码 -h服务器 ip --all-databases > x...

网友评论

      本文标题：MySQL逻辑和物理备份

      本文链接：https://www.haomeiwen.com/subject/gfbogctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|MySQL逻辑和物理备份|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！