美文网首页
MySQL-备份恢复及迁移

MySQL-备份恢复及迁移

作者: 文娟_狼剩 | 来源:发表于2019-08-16 00:09 被阅读0次

    1、备份类型(笔试)

    1.1 冷备(cold backup)

    业务停止或数据库关闭时进行备份,业务影响最大.

    1.2 温备(warm backup)

    锁表备份、只读备份,阻塞所有的变更操作,只能读

    1.3 热备(hot backup)

    不锁表备份,只能针对具备事务型引擎的表(例如:innoDB),业务的影响最小.

    2、备份工具介绍

    2.1 mysqldump

    简称MDP
    优势:逻辑备份工具,备份的都是SQL语句,文本格式、压缩比高,可读性较强,便于二次处理、自带工具。
    劣势:相对较慢(从磁盘调取数据---->内存---->转换成SQL----->xxx.sql)、不能做增量备份

    2.2 Xtrabackup(percona)

    简称XBK、PBK
    物理备份工具,备份的是数据文件(类似cp),
    优势:速度快、支持热备、自带了增量备份
    劣势:需要安装、可读性比较差、不便于二次处理、压缩比低

    2.3 选择建议(了解)

    小于100G-----------MDP、XBK
    超过100G-1T------XBK
    超过TB级别--------XBK、MDP

    3、备份策略

    备份方式:
        全备:全库备份,备份所有数据
        增量:备份变化的数据
    
    逻辑备份=mysqldump+binlog
        物理备份
        XBK_full+xbk_inc+binlog
        或者
        xtrabackup_full+binlog
        
    备份周期:
        根据数据量设计备份周期
        比如:周日全备,周1-周6增量
    

    4、mysqldump

    4.1 客户端通用的参数

    -u:指定用户名
    -p:输入密码
    -h:指定远程ip地址
    -P:指定端口号
    -S:
    
    本地备份:
    mysqldump -uroot -p  -S /tmp/mysql.sock
    远程备份:
    mysqldump -uroot -p  -h 10.0.0.51 -P3306
    

    4.2 基础备份参数

    -A:全库备份
    -B:单库或多库备份
    

    4.2.1 实例练习

    1> 全库备份(参数 -A 的使用)
    [root@db01 ~]# mysqldump -uroot -p123456 -A >/root/full.sql
    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. 
    [root@db01 ~]# 
    
    2> 只备份world和test两个库(参数 -B 的使用)
    [root@db01 ~]# mysqldump -uroot -p123456 -B world test >/backup/world-test.sql
    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. 
    [root@db01 ~]# 
    
    3> 单库或多表备份
    [root@db01 ~]# mysqldump -uroot -p123456 world city country >/backup/tab.sql
    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. 
    [root@db01 ~]#
    
    说明:
    mysqldump -uroot -p123456  world   city    country >/backup/tab.sql
                               库名     表1      表2
    使用source恢复
    

    4.3 特殊功能参数

    4.3.1 -R --triggers -E 数据库特殊对象备份参数
    -R --triggers -E  数据库特殊对象备份参数
    -R:存储过程、函数
    --triggers:触发器
    -E:事件
    
    4.3.2 --master-data=2 ※※※※※

    功能:

    • 以注释形式,记录备份时binlog文件名和position号(截取二进制日志的起点)。
    • 自动锁表功能,加--single-transaction,减少锁表。自动解锁。
    • 自动解锁。
    4.3.3 --single-transaction ※※※※※

    快照备份、热备

    4.3.4 --set-gtid-purged=OFF (GTID模式独有的参数.)

    作用,去除gtid所有信息,在日常备份恢复时可加.
    做主从复制应用的时候,不能加此参数.

    4.3.5 --max-allowed-packet=512M

    注意:正式库上完整的备份语句
    mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --set-gtid-purged=OFF --max-allowed-packet=256M > /backup/full.sql

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

    5、企业故障恢复案例

    5.1 背景环境:

    正在运行的小型网站系统,mysql-5.7.20 数据库,数据量50G.
    每天23:00点,计划任务调用mysqldump执行全备脚本
    [root@db01 /backup]# mysqldump -uroot -p123 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz
    

    5.2 故障时间点:

    年底故障演练:模拟周三上午10点误删除数据库,并进行恢复.
    

    5.3 恢复思路

    (1) 停止故障业务,挂维护页.
    (2) 准备测试库,进行全库恢复
    (3) 截取从全备开始一直到故障时刻的binlog
    (4) 测试数据可用和完整性
    (5) 将故障数据导出,导入到生产
    (6) 撤维护页,开启业务.
    

    5.4 演练

    5.4.1 故障现场模拟

    (1)模拟数据

    wenjuan[(none)]>create database mdp charset utf8mb4;
    Query OK, 1 row affected (0.00 sec)
    
    wenjuan[(none)]>use mdp;
    Database changed
    
    wenjuan[mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
    Query OK, 0 rows affected (0.01 sec)
    
    wenjuan[mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
    ERROR 1050 (42S01): Table 't1' already exists
    wenjuan[mdp]>insert into t1 values(1),(2),(3);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[mdp]>commit;
    Query OK, 0 rows affected (0.00 sec)
    

    (2)模拟晚上23:00全备

    mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz
    

    (3)模拟备份后的数据变化

    wenjuan[mdp]>insert into t1 values(11),(12),(13);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[mdp]>commit;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[mdp]>
    

    (4)搞破坏

    wenjuan[mdp]>drop database mdp;
    Query OK, 1 row affected (0.00 sec)
    
    wenjuan[(none)]>
    
    5.4.1 恢复数据

    (1)停止故障业务,挂维护页。
    (2)全备准备

    [root@db01 /backup]# gunzip full_2019-08-16.sql.gz  
    
    ----binlog准备
    [root@db01 /backup]# vim full_2019-08-16.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=1867;
    
    wenjuan[(none)]>show binlog events in 'mysql-bin.000008';
    wenjuan[(none)]>show master status;
    | mysql-bin.000008 | 2128 | Gtid           |         6 |        2193 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:16' |
    
    
    [root@db01 /backup]# mysqlbinlog  --skip-gtids  --start-position=1867 --stop-position=2193 /data/3306/binlog/mysql-bin.000008 >/backup/binlog.sql
    

    (3)进行恢复

    wenjuan[(none)]>set sql_log_bin=0;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[(none)]>source /backup/full_2019-08-16.sql 
    wenjuan[mdp]>select * from t1;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)
    
    wenjuan[mdp]>
    
    
    wenjuan[mdp]>source /backup/binlog.sql;
    wenjuan[mdp]>select * from t1;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |   11 |
    |   12 |
    |   13 |
    +------+
    6 rows in set (0.00 sec)
    
    wenjuan[mdp]>
    

    6、XBK(Percona-Xtrabackup)---物理备份工具

    6.1 percona-xtrabackup工具安装

    安装依赖包:
    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
    
    下载并安装
    提前下载rpm包到本地,并上传到虚拟机
    yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
    

    6.2 介绍

    物理备份工具,类似于cp数据

    6.3 备份细节

    非InnoDB:例如:MyISAM,自动锁表备份,会有短暂的全局锁(FTWRL).
    InnoDB:
        立即进行CKPT,将当前所有的已提交事务的脏页,立即刷写到磁盘上。(to_lsn)
        拷贝所有InnoDB的数据文件,系统数据文件也一并拷贝。
        将备份过程中产生的redo截取并备份走。(last_lsn)
    

    6.4 innobackupex 备份应用

    6.4.1 修改配置文件
    [root@db01 ~]# vim /etc/my.cnf
    [client]
    socket=/tmp/mysql.sock
    
    [root@db01 ~]# /etc/init.d/mysqld restart
    Shutting down MySQL.... SUCCESS! 
    Starting MySQL. SUCCESSinnobackupex! 
    [root@db01 ~]# 
    
    6.4.2 全备
    [root@db01 ~]# innobackupex --user=root --password=123456  /backup/xbk
    [root@db01 ~]# ll /backup/xbk
    total 4
    drwxr-x--- 16 root root 4096 Aug 16 10:34 2019-08-16_10-34-04
    
    
    [root@db01 ~]# innobackupex --user=root --password=123456  --no-timestamp  /backup/xbk/full
    [root@db01 ~]# ll /backup/xbk
    total 12
    drwxr-x--- 17 root root 4096 Aug 16 12:33 full
    
    说明:
    --no-timestamp   不要用时间生成目录,可以自己制定目录名
    
    6.4.3 搞破坏
    [root@db01 ~]# pkill mysqld 
    [root@db01 ~]# rm -rf /data/3306/data/*
    [root@db01 ~]# 
    
    6.4.4 数据恢复准备(备份处理)
    [root@db01 /backup/xbk]# innobackupex --apply-log /backup/xbk/full/
    
    说明:
    --apply-log     数据处理
    
    6.4.5 恢复数据
    [root@db01 ~]# innobackupex --copy-back   /backup/xbk/full/ 
    授权
    [root@db01 /data/3306/data]# chown -R mysql.mysql *
    
    说明:
    --copy-back (依赖于my.cnf [mysqld]的配置)     把备份恢复回去,即cp
    恢复数据也可以用cp:
    [root@db01 ~]# cp -a /backup/xbk/full/* /data/3306/data/
    [root@db01 /data/3306/data]# chown -R mysql.mysql *
    
    6.4.6 重启mysql服务测试
    [root@db01 /data/3306/data]# /etc/init.d/mysqld start
    Starting MySQL.Logging to '/data/3306/data/mysql.log'.
     SUCCESS! 
    [root@db01 /data/3306/data]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.26-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    wenjuan[(none)]>
    

    --apply-log 参数说明(面试重点):

    • 模仿了InnoDB引擎的ACSR的过程。
    • 将备份集中的数据和日志 LSA追平。
    • 利用redo进行前滚。
    • 利用undo进行回滚。
    6.4.7 备份集的文件认识
    xtrabackup_binlog_info:存储的是binlog截取的起始点信息(position,gtid)
    xtrabackup_checkpoints
    xtrabackup_info
    xtrabackup_logfile
    xtrabackup_master_key_id
    
    
    [root@db01 /backup/xbk/full]# cat xtrabackup_binlog_info 
    mysql-bin.000002    194 63c5b880-bfc1-11e9-beaf-000c2980e248:1-6
    
    [root@db01 /backup/xbk/full]# cat xtrabackup_checkpoints
    backup_type = full-prepared     备份类型(分为全备、增备)
    from_lsn = 0                    整个备份包含的最起始的LSN号码
    to_lsn = 167153081              ckpt后数据页(ibd)的LSN
    last_lsn = 167153090           备份结束时,redo的LSN,(在当前5.7版本,会有9个LSN可以忽略)
    [root@db01 /backup/xbk/full]# 
    

    7、XBK增量(incremental)备份

    Xtrabackup企业级增量恢复实战
    背景:
    某大型网站,mysql数据库,数据量500G,每日更新量20M-30M
    备份策略:
    xtrabackup,每周日23:00进行全备,周一到周六23:00进行增量备份。
    故障场景:
    周三下午2点出现数据库意外删除表操作。
    如何恢复?

    7.1 清除以往备份

    [root@db01 /backup/xbk]# rm -rf /backup/xbk/*
    

    7.2 模拟数据

    wenjuan[(none)]>create database xbk charset utf8mb4;
    Query OK, 1 row affected (0.01 sec)
    
    wenjuan[(none)]>use xbk;
    Database changed
    wenjuan[xbk]>create table t1(id int) engine=innodb charset=utf8mb4;
    Query OK, 0 rows affected (0.01 sec)
    
    wenjuan[xbk]>insert into t1 values(1),(2),(3);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[xbk]>commit;
    Query OK, 0 rows affected (0.00 sec)
    

    7.3 模拟周日全备

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

    7.4 模拟周一的数据变化

    wenjuan[xbk]>use xbk;
    Database changed
    wenjuan[xbk]>insert into t1 values(11),(12),(13);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[xbk]>commit;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[xbk]>
    

    7.5 模拟周一晚上增加备份

    [root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/full /backup/xbk/inc1
    
    --incremental  打开增量备份的开关
    --incremental-basedir     设定增量备份的基备份(一般是上一天)
    

    7.6 模拟周二白天的数据变化

    wenjuan[xbk]>use xbk;
    Database changed
    wenjuan[xbk]>insert into t1 values(111),(222),(333);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[xbk]>commit;
    Query OK, 0 rows affected (0.01 sec)
    
    wenjuan[xbk]>
    

    7.7 模拟周二增量

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

    7.8 模拟周三白天的数据变化

    wenjuan[xbk]>use xbk;
    Database changed
    wenjuan[xbk]>insert into t1 values(1111),(2222),(3333);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[xbk]>commit;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[xbk]>
    

    7.9 搞破坏(比如删库)

    [root@db01 /data/3306/data]# pkill mysqld
    [root@db01 /data/3306/data]# rm -rf /data/3306/data/*
    [root@db01 /data/3306/data]# ll
    total 0
    [root@db01 /data/3306/data]# 
    

    7.10 恢复思路

    (1) 测试库,维护页
    (2) 处理备份
        合并
        准备 
    (3) 截取二进制日志
    (4) 数据恢复 
    

    7.11 开始恢复演练

    7.11.1 处理备份

    (1)处理原始全备

    [root@db01 ~]# innobackupex --apply-log --redo-only /backup/xbk/full
    
    --apply-log    
    --redo-only    只做前滚不做回滚
    为了保证LNS号码上一个号码和下一个号码有连接性
    

    (2)合并周一并处理

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

    (3)合并周二并处理

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

    (4)处理合并后的新全备数据

    [root@db01 ~]# innobackupex --apply-log  /backup/xbk/full
    
    7.11.2 恢复备份,并启动数据库
    [root@db01 ~]# cp -a /backup/xbk/full/* /data/3306/data/
    [root@db01 /data/3306/data]# chown -R mysql.mysql *
    
    [root@db01 ~]# /etc/init.d/mysqld start
    Starting MySQL.Logging to '/data/3306/data/mysql.log'.
    . SUCCESS! 
    [root@db01 ~]# 
    
    7.11.3 截取binlog
    [root@db01 ~]# cat /backup/xbk/inc2/xtrabackup_binlog_info
    mysql-bin.000010    1339    400dd8cc-bfd3-11e9-9a4a-000c290143b9:1-5,
    936b9a3f-b75a-11e9-bd16-000c290143b9:1-16
    [root@db01 ~]# 
    
    wenjuan[(none)]>show binlog events in 'mysql-bin.000010';
    | mysql-bin.000010 | 1569 | Xid            |         6 |        1600 | COMMIT /* xid=81 */                     
    
    
    [root@db01 ~]# mysqlbinlog --skip-gtids --start-position=1339 --stop-position=1600 /data/3306/binlog/mysql-bin.000010 >/backup/bin.sql
    
    
    wenjuan[(none)]>set sql_log_bin=0;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[(none)]>source /backup/bin.sql;
    
    
    wenjuan[(none)]>use xbk;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    wenjuan[xbk]>select * from t1;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |   11 |
    |   12 |
    |   13 |
    |  111 |
    |  222 |
    |  333 |
    | 1111 |
    | 2222 |
    | 3333 |
    +------+
    12 rows in set (0.00 sec)
    
    wenjuan[xbk]>
    

    相关文章

      网友评论

          本文标题:MySQL-备份恢复及迁移

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