美文网首页
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!

    相关文章

      网友评论

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

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

          延伸阅读

          深度阅读

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

          栏目导航

          热点阅读

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

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

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

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

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

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