阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL之备份和恢复(msyqldump、LVM、xtrabackup)

192次阅读
没有评论

共计 9831 个字符,预计需要花费 25 分钟才能阅读完成。

备份类型:
    根据备份时是服务器是否在线:
        热备份(HOT):
            读写操作不受影响
        温备份(WARM):
            进可以执行读操作,写操作不能执行
        冷备份(COLD):
            读写操作均不能进行

    根据备份时对数据文件的操作:
        物理备份:
            直接复制数据文件。速度快,但是跨平台移植可能受到底层文件系统格式的影响。
        逻辑备份:
            将数据导出至文本文件中。速度慢。但是保存文件中会丢失浮点数精度。可以使用文本处理工具进行二次处理。移植性强。备份出来的数据可能比原数据占用空间还大。

    根据备份时的数据大小:
        完全备份(full):备份全部数据
        增量备份(incremental):仅备份上次完全备份或增量备份后变化的数据。
        差异备份(differnetial):仅备份上次完全备份以来变化的数据。占用空间比增量大。

        增量备份和差异备份的区别:
            虽然都是对变化的数据备份,但是增量在还原时要还原此前一系列的增量 + 完全备份的内容。差异备份还原时只要选择最近一个备份 + 完全备份的内容即可。
 

 

需要备份的东西:
    数据、服务器配置文件、二进制日志、事务日志

    二进制日志注意点:
        备份时除了使用 FLUSH LOGS 以外,建议将 sync_binlog 的值设置为 1,这样可以在安全性上保证事务不会没有写入日志。

        还原的时候先暂时关闭二进制日志记录功能,还原过程没必要记录二进制日志中。

备份策略:
    考虑以下 2 点:
        1. 数据在某个周期的变化量
        2. 数据还原是所需时间

备份方案:
    完全 + 增量
    完全 + 差异

 

热备份:
    MyISAM:不支持热备份,可以使用温备份
    InnoDB:支持热备。可以使用 xtrabackup(物理备份)和 MySQLdump 等工具。

 

备份工具:
    MYSQL 自带:
        mysqldump  逻辑备份,对 MyISAM 可以实现温备份。InnoDB 可以实现热备。

        mysqlhotcopy  物理备份,冷备份工具。基于 perl 脚本,使用 FLUSH TABLES 和 LOCK TABLES 以及 scp 完成备份。

        SELECT INTO OUTFILE SELECT 语句,只能对单张表操作。

    文件工具:
        cp
            冷备份,直接复制文件
        lvm
            使用逻辑卷快照功能,实现几乎热备。需要手工执行 FLUSH TABLES 和 LOCK TABLES,然后创建快照,创建完成后执行 UNLOCK TABLES,复制快照文件。
            注意:MyISAM 可以直接锁表,但是对 InnoDB 而言,因为可能会涉及到事务的操作,例如从日志同步到磁盘中,或者事务未同步到磁盘中。此时如果锁表还得等待事务同步完成。

    商业工具:
        ibbackup
            InnoDB

    开源工具:
        xtrabackup

#mysqldump [OPTION] DBNAME[TBNAME1,…]
    默认要提前手工创建数据库,因为备份中不包含 CREATE DATABASE 命令。

    [OPTION]
        -u USERNAME
        -p PASSWORD
        -h HOST
        –master-data = 0|1|2 是否记录当前的二进制文件和事件位置
            0 不记录二进制日志文件及其事件位置
            1 以 CHANGE MASTER TO 的方式记录位置,可用于恢复后直接启动从服务器。
            2 以 CHANGE MASTER TO 的方式记录位置,但是记录默认被注释。

        –lock-all-tables 锁定所有表
        –lock-tables
        –flush-logs 执行日志滚动
        –single-transaction 启动热备事务,适用于 InnoDB,借助于 MVCC 生成一个隔离级别为 REPEATABLE-READ 的事务。这样无论后台如何变化,看到的数据都不会发生变化。会自动处理表锁,不能和 –lock-all-tables 或者 –lock-tables 同时使用。

        –all-databases 备份所有库,包含 CREATE DATABASE 命令,无需手动创建库
        –database DBNAME,… 备份指定库,包含 CREATE DATABASE 命令,无需手动创建库 

        –events 备份事件调度器
        –routines 备份存储过程和函数
        –triggers 备份触发器

    备份思路:
        锁表 –> 磁盘同步数据 –> 刷新二进制日志 –> 备份二进制日志文件(最好全部保留)–> 完全备份 –> 增量备份

    还原思路:
        如有必要重新初始化数据库 –> 临时关闭二进制日志记录 –> 还原完全备份 –> 还原增量备份 –> 即时点还原(将编号最大的二进制日至文件通过管道送给 mysql 服务器)–> 开启二进制日志记录

    备份策略:
        完全 + 增量:

    还原策略:
        完全 + 增量 + 即时点

    实例:
        1. 备份 jiaowu 数据库,假设为 MyISAM 引擎,使用温备份:
            mysql> LOCK TABLES;
            mysql> FLUSH TABLES;
            或
            mysql>FLUSH TABLES WITH READ LOCK;
            mysql>FULSH LOGS;
            mysql>SHOW BINARY LOGS;
            #mysqldump -uroot  -p jiaowu> /root/backup/jiaowu.sql
            mysql>UNLOCK TABLES;

            或
            #mysqldum -uroot -p –master-data=2 –lock-all-tables –flush-logs  jiaowu > /root/backup/jiaowu.sql

        2. 备份 jiaowu 数据库,假设为 InnoDB 引擎,使用热备份:
            #mysqldump -uroot -p –master-data=2 –flush-logs –single-transaction jiaowu > /root/backup/jiaowu.sql
           
        3. 完全备份:
            #mysqldump -uroot -p –lock-all-tables  –flush-logs –all-databases –master-data=2  > /root/backup/alldb-`date +%F-%H-%M-%S `.sql

        4. 增量备份:
            可以备份二进制日志或者是对某表进行备份,根据具体情况而定。
            假设刷新日志后,生成最新的日志为 mysql-bin.000008。

            mysql>FLUSH LOGS;
            #cp mysql-bin-* /root/binlog/

            #mysqlbinlog mysql-bin-000002 >/root/backup/mysql-inc/binlog-2.sql
            …
            #mysqlbinlog mysql-bin-000007 >/root/backup/mysql-inc/binlog-3.sql

        5. 即时点备份:
            #mysqlbinlog mysql-bin-0000008> /root/backup/mysql-inc/binlog-`date +%F+%H+%M+%S `.sql

        6. 假设数据所在磁盘损坏,进行还原:
            初始化数据库并启动服务
                #cd /usr/local/mysql
                #scripts/mysql_install_db –user=root –datadir=/mdata/data
                #service mysqld start

            导入完全备份和所有增量:
                #mysql  -uroot -p < /root/backup/alldb-xxxx.sql
                #mysql -uroot -p < /root/backup/mysql-inc/binlog-xxxxx.sql
                …
            导入即时点数据:
                #mysqlbinlog mysql-bin-LATEST > /root/lastbinlog.sql
                #mysql -uroot -p < /root/lastbinlog.sql
                或
                #mysqlbinlog mysql-bin-LATEST |mysql -uroot -p

        6. 使用 5 的方法虽然可以恢复,但是会被记录到二进制日志中,一般还原的操作是无需记录到二进制日志的。临时关闭当前会话的二进制日志。

            mysql>SET sql_log_bin=0;
            mysql>SOURCE /root/backup/alldb-xxxx.sql;
            mysql>SOURCE /root/backup/mysql-inc/binlog-xxxxx.sql;
            …
            mysql>SOURCE /root/lastbinlog.sql;
            mysql>SET sql_log_bin=1;

SELECT INTO OUTFILE:
    只能对单张表进行操作。恢复速度快,一般用作单个表的备份。
    存放的是纯粹的数据,各个字段和值之间用制表符(tab)分隔,无其他额外信息。

    备份:
        mysql>SELECT * INTO OUTFILE ‘FILE’ FROM TBNAME [WHERE …];

    还原:
        mysql>LOAD DATA INFILE ‘FILE’ INTO TABLE TBNAME;

    注意:备份文件必须放在 mysql 用户有访问权限的目录。还原的表得提前创建表格式。

    实例:
        1. 备份 tutors 表:
            mysql>use jiaowu;
            mysql>SELECT * INTO OUTFILE‘/root/backup/selbackup/tutors.txt’FROM tutors;

        2. 还原 tutors 表的内容到 tut 表中:
            mysql>use jiaowu;
            mysql>CREATE TABLE tut LIEK tutors;
            或
            mysql>CREATE TABLE tut (TID INT UNSIGNED NOT NULL AUTO_INCREMENT  PRIMARY KEY,Tname CHAR(20) NOT NULL ,Gender enum(‘F’,’M’),Age TINYINT NOT NULL);

            mysql>LOAD DATA INFILE ‘/root/backup/selbackup/tutors.txt’’ INTO TABLE tut;

LVM 快照:
    前提:
        1. 数据要在逻辑卷上
        2. 逻辑卷所在卷组必须有足够的空间使用快照卷
        3. 事务日志和数据必须在同一个卷上。

    备份思路:
        锁表 –> 滚动日志 –> 记录二进制日志名称和位置信息 –> 创建快照 –> 释放锁 –> 挂载并备份快照卷中的数据 –> 删除快照卷 –> 备份二进制日志文件

    恢复:

    实例:
        1. 备份 jiaowu 库:
            mysql>FLUSH TABLES WITH READ LOCK;
            mysql>FLUSH LOGS;

            mysql>SHOW MASTER STATUS; 记录二进制日志名称和位置,为即时点还原提供依据。
            或
            在另一个终端上执行
            #mysql -uroot -p -e `SHOW MASTER STATUS\G` > /root/backup/lvmbkup/master-`date +%F`.info

            #lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata
            mysql>UNLOCK TABLES;

            #mont /dev/myvg/mydata-snap /mnt -o ro
            #cd /mnt
            #cd jiaowu
            #mkdir -p /root/backup/lvmbackup/fullbackup-`data+%F`
            #cp -a ./* /root/backup/lvmbackup/fullbackup-xxxxx/
            #cp -a ../ibdataX /root/backup/lvmbackup/fullbackup-xxxxx/

            注意:默认 InnoDB 的表空间文件为共享,复制时需要一并复制。建议将 innodb_file _per_table=1,使用单独表空间文件。这样直接复制文件夹即可。

            #umount /mnt
            #lvremove –force /dev/myvg/mdata-snap

            #cd /root/backup/lvmbackup/fullbackup-xxxxx/
            #rm -f mysql-bin.*

            #cd /mydata/data
            #cat /root/backup/lvmbkup/master-xxxxx.info
            #mysqlbinlog –start-datetime=’2015-07-25 07:01:04′ mysql-bin.00000X mysql-bin.0000X+1 …  > /root/backup/lvmbackup/incremental-`date +%F-%H-%M-%S`.sql

        2. 还原:
            #service mysqld stop
            #cp -a /root/backup/lvmbackup/fullbackup-xxxx/*  /mydata/data
            #service msyqld start
            #msyql -uroot -p
            mysql>SET sql_log_bin=0;
            mysql>SOURCE /root/backup/lvmbackup/incremental-xxxxx.sql;
            mysql>SET sql_log_bin=1;

xtrabackup:
    一般直接在服务器上使用此工具。

    备份思路:
        完全备份 –> 增量备份 –> 即时点备份

    还原思路:
        完全备份准备 –> 增量备份准备 –> 关闭服务器 –> 还原完全备份 –> 修改恢复数据的属主属组 –> 即时点还原 –> 重做一次完全备份(重要)

    InnoDB:
        full
        incremental
        HOT

    MyISAM:
        WRAM
        full

    XtraDB
        增强功能的 InnoDB,若想使用则在编译 mysql 时替换掉 InnoDB 引擎。
   
    安装:
        #yum localinstall percona-xtrabackup-xxxx.rpm

    二进制文件:
        /usr/bin/innobackupex perl 脚本,调用具体的工具完成操作。

    备份目录中文件:
        xtrabackup_checkpoints 记录备份类型(完全 | 增量 …)、备份状态(prepared 状态等)、日志序列号(LSN)等。

            LSN:InnoDB 每个储存数据的数据块都有对应的日志序列号,如果块中的数据发生了改变,则对应的 LSN 会 +1

            内容如下:
                backup_type=
                from_lsn=  起始的 LSN 号,完全备份从 0 开始
                to_lsn= 结束的 LSN 号
                last_lsn= 最后一次的 LSN 号,如果是增量备份则会从此号开始。

 

        xtrabackup_binlog_info 二进制日志文件和位置
        xtrabackup_binlog_pos_innodb 二进制文件和位置以及用于 InnoDB 或 XtraDB 的二进制文件和位置
        xtrabackup_binary 备份时使用的具体工具
        xtrabackup_logfile
        backup~my.cnf 备份命令用到的配置选项信息

    #innobackupex [OPTION] DIR
        [OPTION]
            –user=DBUSERNAME
            –password=DBUSERPASSWD
            –host=DBHOST
            –port=DBSERVERPORT
            –no-timestamp 禁止自动生成以时间命名的文件夹
            –apply-log 对备份好的数据进行准备
            –use-memory NUM 指定准备时占用的内存大小,默认不指定为 100M
            –copy-back 恢复完全数据
            –incremental  增量备份
            –incremental-basedir=FULLBACKUPDIR 指定完全备份文件路径
            –incremental-dir=INCRBACKUPDIR 指定增量备份的文件路径
            –redo-only 指定只进行 redo 操作。
            –export  执行导出操作
            –incremental-force-scan 增量备份时强制扫描全部的数据页。在 mariadb 上增量备份不指定此参数会报错。

    实例:
        1. 创建专门用于备份的 mysql 账号:
            mysql>CREATE USER ‘bkuser’@’localhost’ IDENTIFIED BY ‘123456’
            mysql>REVOKE ALL PRIVILEGES,GRANT OPTION FROM ‘bkuser’;
            mysql>GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO ‘bkuser’;
            mysql>FLUSH PRIVILEGES;

        2. 完全备份:
            #innobackupex –user=bkuser –password=123456 –host=localhost  /root/backup/xtrabackup/full/
            或
            #innobackupex –user=root –password=123456 –host=localhost  /root/backup/xtrabackup/full/

        4. 第一次增量备份:
            对 MyISAM 只能是完全备份。
            #innobackupex –incremental /backup/xtrabackup/incr/ –incremental-basedir=/root/backup/xtrabackup/full/xxxxx/

        5. 第二次增量备份:
            第一次增量备份完成后,再进行增量备份的时候。–incremental-basedir 要指定增量备份的目录而不是完全备份的目录

            #innobackupex –incremental /backup/xtrabackup/incr/ –incremental-basedir=/root/backup/xtrabackup/incr/xxxxx1/

            以后增量备份只要指定上次增量即可

        6. 准备:
            备份好的数据不能直接还原,其中可能有部分事务没有提交或正在进行。已经执行的的事务同步到数据中,正在执行的事务进行回滚。

            完全备份准备:
                #innobackupex –apply-log –redo-only /root/backup/xtrabackup/xxxxx/

            第一次增量备份准备:
                #innobackupex –apply-log –redo-only /root/backup/xtrabackup/full/xxxxx/ –incremental-dir=/root/backup/xtrabackup/incr/xxxxx1/

                注意:有些在完全备份中未能提交的事务在后续增量备份中已经提交过。如果不指定 –redo-only 选项则有可能这些时间会被 undo,造成恢复后无法提交。

            第二次增量备份准备:
                #innobackupex –apply-log –redo-only /root/backup/xtrabackup/full/xxxxx/ –incremental-dir=/root/backup/xtrabackup/incr/xxxxx2/

            注意:所有备份的准备完成后后,所有的数据都将合并到完全备份中,恢复时只恢复完全备份即可。

        7. 恢复:
            备份二进制日志以便即时点恢复:
                mysql>FLUSH LOGS
                #service mysqld stop

                #cd /mdata/data
                #cp mysql-bin.0000* /root/backup/binlogfile

            还原完全备份:
                #innobackupex –copy-back /root/backup/xtrabackup/full/xxxxx/

            修改恢复完成数据的属主属组:
                #cd /mdata/data
                #chown -R mysql:mysql ./*

            启动服务
                #service  mysqld start

            即时点还原
                #cd /root/backup/binlogfile
                #mysqlbinlog mysql-bin-000001 > bin0001.sql
                …

                mysql>SET sql_bin_log=0
                mysql>SOURCE /root/backup/binlogfile/bin0001.sql
                …

        9. 导入导出单张表:
            注意:要进行此操作,必须保证导出操作所在数据库的 innodb__file_per_table 在创建要导出的表之前是启用的。同时要保证导入操作所在的数据库 innodb_file_per_table 和 innodb_expand_import 也是要启用的。

            导出:
                导出操作是在完全备份的准备阶段进行。会为每个 innodb 表的表空间创建以.exp 结尾的文件。

                #innobackupex –apply-log –export /root/backup/xtrabackup/full/xxxxx/

                复制.exp 和.ibd 文件

            导入:
                创建 innodb 表:
                    mysql>CREATE TABLES TBNAME (…) ENGINE=InnoDB;

                删除表空间:
                    mysql>ALTER TABLE DBNAME.TBNAME DISCARD TABLESPACE;

                导入.exp 文件和.ibd 文件:
                    复制.exp 和.ibd 文件到数据目录下:
                    mysql>ALTER TABLE DBNAME.TBNAME IMPORT TABLESPACE;

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-06/132467.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计9831字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中