Mysql 备份

Mysql 备份
=============================================================================
=============================================================================
    
MySQL备份原理及策略
               备份和恢复(数据)
备份:存储的数据副本;
原始数据:持续改变;
恢复:把副本应用到线上系统;
仅能恢复至备份操作时刻的数据状态; 
为什么备份?
灾难恢复:硬件故障(冗余)、软件故障(bug)、自然灾害、黑客攻击、误操作、…
测试;
即使是靠谱的raid 10 并不是万无一失的 , 如 : 同组均坏了一个磁盘 , 存储设备本身解决不了逻辑错误 , 假设运行的drop命令删除了表

备份时应该注意事项:
能容忍最多丢失多少数据;
恢复数据需要在多长时间内完成;
需要恢复哪些数据;
做恢复演练:
测试备份的可用性;
增强恢复操作效率;

                           ———————————————————————————————————————

备份类型
备份的数据的集范围:
完全备份和部分备份
完全备份:整个数据集;
部分备份:数据集的一部分,比如部分表;
完全备份、增量备份、差异备份:
完全备份
增量备份:仅备份自上一次完全备份或 增量备份以来变量的那部数据;数据是增长型的 , 建议使用增量备份
差异备份:仅备份自上一次完全备份以来变量的那部数据;数据是变化性的 , 建议使用差异备份
物理备份、逻辑备份:
物理备份:复制数据文件进行备份;
逻辑备份:从数据库导出数据另存在一个或多个文件中;
根据数据服务是否在线:
热备:读写操作均可进行的状态下所做的备份;
温备:可读但不可写状态下进行的备份;
冷备:读写操作均不可进行的状态下所做的备份;
                               ———————————————————————————————————————

备份需要考虑因素
锁定资源多长时间?
备份过程的时长?
备份时的服务器负载?
恢复过程的时长?
                           ———————————————————————————————————————

备份策略:
完全+差异+binlog  :  恢复的时候需要完全备份与最后一次的差异备份
完全+增量+binlog  :  恢复的时候需要完全备份与自完全备份到恢复数据的时候的所有增量备份 , 过程极容易出错
备份手段:物理、逻辑
                               ———————————————————————————————————————

备份什么
数据
二进制日志、InnoDB的事务日志;
代码(存储过程、存储函数、触发器、事件调度器)
服务器的配置文件
                           ———————————————————————————————————————

备份工具

mysqldump:mysql服务自带的备份工具;逻辑备份工具;
完全、部分备份;
InnoDB:热备;
MyISAM:温备;

                        ———————————————————————————————————————

cp/tar
lvm2:快照(请求一个全局锁),之后立即释放锁,达到几乎热备的效果;物理备份;
注意:不能仅备份数据文件;要同时备份事务日志;
前提:要求数据文件和事务日志位于同一个逻辑卷;

                        ———————————————————————————————————————

xtrabackup:
由Percona提供,开源工具,支持对InnoDB做热备,物理备份工具;
完全备份、部分备份;
完全备份、增量备份;

                        ———————————————————————————————————————

mysqlhotcopy 冷备工具
select:
备份:SELECT cluase INTO OUTFILE ‘FILENAME’;
恢复:CREATE TABLE 
导入:LOAD DATA 


=======================================================================

mysql备份工具

mysqldump:

                              需要通过协议 , 连接至MySQL服务端 , 发起select * from table , 抽取全部库的全部表的数据 , 把查找到的数据改成insert语句基于create table 语句 ,  形成sql脚本 . 恢复的时候 , 通过mysql服务器 , 导入备份脚本即可
                         基于这种方法备份数据有个极大缺陷 , 会丢失精度 , 数据库中的数据信息是二进制的 , 备份出来的sql文件是文本形式的 , 二者之间的转换有可能会存在精度损失 
                          基于这种备份是无法备份索引

逻辑备份、完全备份、部分备份;
                        
二次封装工具:
mydumper
phpMyAdmin
Usage: 
mysqldump [OPTIONS] database [tables]     #只备份指定库中的表 , 但是备份文件中是没有create database 的语句 , 即默认不创建库
OR     mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]  #备份整个指定的数据库 , 会有create database
OR     mysqldump [OPTIONS] –all-databases [OPTIONS]     #备份全部的数据库 , 会有create database
    
                                    -A / –all-databases     备份全部数据库
                                    -B / –databases          备份指定的数据库
                                    ———————————————————————————————————————

MyISAM存储引擎:支持温备,备份时要锁定表;
-x, –lock-all-tables:锁定所有库的所有表,读锁;
-l, –lock-tables:锁定指定库所有表;
                                ———————————————————————————————————————

InnoDB存储引擎:支持温备和热备;
–single-transaction:创建一个事务,基于此快照执行备份;与实务隔离级别有关
其它必给的选项:
-R, –routines:存储过程和存储函数;
–triggers      触发器
-E, –events      时间调度器
 
–master-data[=#]    记录备份的时候 , binlog所处的文件的位置
1:记录为CHANGE MASTER TO语句,此语句不被注释;
2:记录为CHANGE MASTER TO语句,此语句被注释;
–flush-logs:锁定表完成后,即进行日志刷新操作;

                         ———————————————————————————————————————
                         ———————————————————————————————————————

使用mysqldump进行备份

            Mysql 备份

         从文件大小可初步看出这三条命令的区别
            ~]# mysqldump -hlocalhost -uroot -ppo hellodb > hellodb.sql.1 #直接创建表 , 不会创建相应的数据库
                Mysql 备份

 

~]# mysqldump -hlocalhost -uroot -ppo –database hellodb > hellodb.sql.2 #会创建相应的数据库 , 然后再创建表
               Mysql 备份

 
~]# mysqldump -hlocalhost -uroot -ppo –all-database > backup.sql.3 #–all-database 则会备份该数据库服务器上的所有服务器 , 此处不作展开查看

# 如果数据集只有几个G的时候 , mysqldump没有什么问题 , 如果使用mysqldump来备份1T的数据 , 大概需要2-3小时

                    #备份性能差 , 只启用一个备份线程 , 如果有多个数据库需要备份的时候 , 会轮流进行备份 , 没有并行能力 ,


                                            ———————————————————————————————————————

                使用mysqldump备份出来的sql文件进行数据恢复

 
假如不小心删除了库 (此前必须要定期做备份 可以是完全备份+增量+binlog  或者是 完全备份+差异+binlog  ,  但是此处先跳过增量和差异的恢复)
Mysql 备份
         
如果只有完全备份 , 则抽取完全备份的那一刻的binlog日志时间点的binlog数据 (有增量或者差异备份的话 , 就抽取最后的增量备份或者最后的差异备份那一刻的binlog日志时间点的binlog数据)
但是由于此前备份的时候 , 使用了选项 –master-data=2 带注释的记录binlog位置 以及刷新了二进制日志
mysqldump -uroot -ppo -hlocalhost –database hellodb -R –triggers -E –master-data=1 –flush-logs > /root/hello.sql
所以只需要照出备份的时候二进制日志的名字 , 之后的二进制日志文件就行了
Mysql 备份
 此处一共有6 个二进制日志文件
Mysql 备份
 备份的文件中显示 , 备份的时候maser_log已经在master_log.000006开始记录了 , 但是要记住将drop hellodb 的误操作语句给屏蔽 
Mysql 备份
 由上图可见 , 是本分后才删除的 (加入不是备份后才删除 , 以及记录 pos位置   再使用    mysqldump –stop-position= POS 然后再重定向输出)
先登录mysql , 将二进制日志记录功能关闭 (sql文件恢复是大量写语句的操作 , 如果开启则会记录没必要记录的写操作)
Mysql 备份
 
再执行sql命令MariaDB [(none)]> \. /root/hello.sql
Mysql 备份
 
然后再按时间点恢复 , 恢复从二进制日志文件导出来的sql文件
———————————————————————————————————————
———————————————————————————————————————
基于lvm2的备份:
前提:数据目录位于逻辑卷,包含了数据文件和事务日志
(1) 请求锁定所有表;
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制文件事件位置;
mysql> FLUSH LOGS;

mysql> SHOW MASTER STATUS;
mysql  -e  ‘SHOW MASTER STATUS;’ >> /PATH/TO/SOME_POS_FILE
(3) 创建快照卷
lvcreate  -L # -s -p r – SNAM-NAME /dev/VG-NAME/LV-NAME 
(4) 释放锁
mysql> UNLOCK TABLES
(5) 挂载快照卷,并执行备份;
(6) 周期性备份二进制日志; 


———————————————————————————————————————
———————————————————————————————————————

Xtrabackup:

Xtrabackup 官方原理

在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

Xtrabackup在启动时会记住log sequence number(LSN),并且复制所有的数据文件。复制过程需要一些时间,所以这期间如果数据文件有改动,那么将会使数据库处于一个不同的时间点。这时,xtrabackup会运行一个后台进程,用于监视事务日志,并从事务日志复制最新的修改。Xtrabackup必须持续的做这个操作,是因为事务日志是会轮转重复的写入,并且事务日志可以被重用。所以xtrabackup自启动开始,就不停的将事务日志中每个数据文件的修改都记录下来。

上面就是xtrabackup的备份过程。接下来是准备(prepare)过程。

prepare这个过程中,xtrabackup使用之前复制的事务日志,对各个数据文件执行灾难恢复(就像mysql刚启动时要做的一样)。当这个过程结束后,数据库就可以做恢复还原了。

以上的过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup,直到xtrabackup复制数据文件后,然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上,之后复制MyISAM数据文件,最后释放锁。

备份MyISAM和InnoDB表最终会处于一致,在准备(prepare)过程结束后,InnoDB表数据已经前滚到整个备份结束的点,而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同,所以myisam表数据与InnoDB表数据是同步的。类似oracle的,InnoDB的prepare过程可以称为recover(恢复),myisam的数据复制过程可以称为restore(还原)。



lsn 日志序列号

innodb是使用表空间存储数据 , 而表空间自身就是一个建立在操作系统的文件系统之上的一个独立的文件系统 , 专用于保存关系型数据的文件系统 , 它拥有与操作系统的文件系统的特性。

在表空间中 , 存储引擎会事前规划好一个个的dateblock , 每一个dateblock在表空间中都是一个自制的数据块(其中元数据包每一个dateblock的括版本号,如有在其datablock中的真实数据发生变化,则会自动增加;行id号,存储引擎自建的id号,并非使用mysql客户端登陆所看到的行id号;等等) 

日志序列号(lsn)则是记录所有数据块的版本号



MyISAM:温备,不支持增量备份;
InnoDB:热备,增量;
                Xtrebackup: 是物理备份 , 速度快 , 可靠 ; 由于支持热备 , 所以不会打断正在执行的事务 , 直接进行热备 . 支持压缩功能 , 传输压缩 , 或者备份完之后压缩 . 备份完成后自动校验备份结果集是否可用 ; 还原速度极快
                    基于协议 , 连接至需要备份的mysql服务端 , 并非像mysqldump那样简单粗暴的复制全部sql语句(行数据) , xtrabackup可以理解为复制底层数据块 


————————————————————————————–

              用法:
Usage: [innobackupex [–defaults-file=#] –backup | innobackupex [–defaults-file=#] –prepare] [OPTIONS]
             $ innobackupex –user=USER –password=PASSWD /path/to/backup/dir/ #完全备份数据库至本地/path/to/backup/dir/ 目录下
$ innobackupex –user=USER –password=PASSWD –stream=tar ./ | bzip2 – #完全备份数据库至本地当前目录下 , 并使用zip压缩
$ xtrabackup –user=USER –password=PASSWD –backup –target-dir=/data/bkps/
      
                   备份单库(本分指定的库):
–databases 


                  ———————————————————————————————————————————————

完全+binlog(总结)
备份:innobackupex  –user  –password=  –host=  /PATH/TO/BACKUP_DIR 
准备:innobackupex –apply-log  /PATH/TO/BACKUP_DIR 
恢复:innobackupex –copy-back 
注意:–copy-back需要在mysqld主机本地进行,mysqld服务不能启动;
innodb_log_file_size可能要重新设定;
总结:完全+增量+binlog 
备份:完全+增量+增量+…
          完全+差异
准备:
innobackupex –apply-log –redo-only BASEDIR 
innobackupex –apply-log –redo-only BASEDIR  –incremental-dir=INCREMENTAL-DIR
恢复:
innobackupex –copy-back BASEDIR

 ———————————————————————————————————————————————
 ———————————————————————————————————————————————


具体操作

       xtrabackup 备份
                    使用登陆mysql服务端进行备份的账号 , 应授予的最低权限有 : 
(1)RELOAD  and  LOCK  TABLES (2)REPLICATION CLIENT (3)CREATE TABLESPACE (4)PROCESS (5)SUPER (6)INSERT (7)SELECT
Mysql 备份
 相应的sql语句如下 :
mysql> CREATE USER ‘bkpuser’@’localhost’ IDENTIFIED BY ‘s3cret’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;


                            然后运行innobackupex程序
                                ~]# innobackupex –user=root –password=po /tmp
Mysql 备份

 

运行的时候会提醒,当程序运行结束的时候,如果运行成功,则会有completed OK!的字样显示;接下来会连接服务端,检查服务端的各种信息(版本号,程序的版本号,二者是否兼容,获取innodb的各种信息)

获取innodb的各种信息:

Mysql 备份

 如果没有开启每表使用独立的表空间的话(innodb_file_per_table = ON),innobackupex的高级功能部分备份是没办法用的

记录事务日志的大小以及日志序列号(lsn)然后就开始备份

先复制事务引擎的数据库文件,最后才是非事务存储引擎的数据库文件
Mysql 备份
工作的具体流程:
           对于事务性存储引擎:先记录lsn,然后复制表空间文件,继而是数据文件,复制完之后会再次检查lsn是否发生变化,以及检查是否有数据写入binlog文件,如果有,则会同步到备份文件中,同步完的那一刻,会立即执行FLUSH TABLES WITH READ LOCK,刷写非事务性存储引擎在内存中的缓存到磁盘上和请求读锁
           对于非事务存储引擎:在事务性存储引擎结束及请求完读锁之后,就将所有有关的表(.frm表格式定义; .myd数据文件;.myi索引文件)复制到备份文件中,复制完之后会立即释放所有非事务性表读锁
Mysql 备份
 
 最后:会记录当前使用的二进制日志文件的名称及位置;报告lsn号码;及报告备份了lsn长度的日志(0-#位增量备份;#-#为完全备份)


备份后所生产的文件:
Mysql 备份
 
backup-my.cnf : 数据还原时,指明数据格式的文件
xtrabackup补充文件:
xtrabackup_binlog_info #备份完那一刻的二进制日志文件的名字及pos位置
xtrabackup_checkpoints #备份的时候xtrabackup的检查点
xtrabackup_info #备份时xtrabackup 自身的信息
xtrabackup_logfile #二进制格式文件,xtrabackup复制的事务日志文件           

                    
                              ———————————————————————————————————————————————



        xtrabackup数据恢复

将数据文件复制到另外一台服务器上,由于是物理还原,目标主机不能启用mysql服务,以及清空数据目录,该服务器也需要安装xtrabackup程序


恢复前的准备:
prepare这个过程中,xtrabackup使用之前复制的事务日志,将该提价的事务进行提交,将该回滚的事务进行回滚。当这个过程结束后,数据库就可以做恢复还原了。
 ~]# rm -rf /var/lib/mysql/*
 ~]# innobackupex –apply-log /root/2016-11-20_17-21-12/
Mysql 备份
成功运行完之后也会出现 completed OK !字样 
然后再运行恢复操作,将备份目录中的数据复制回数据目录中
~]# innobackupex –copy-back /root/2016-11-20_17-21-12/
Mysql 备份
 当然,成功运行完之后也会提示completed OK !
Mysql 备份
 由于使用root账号恢复,复制完之后需要修改文件属性才能使用,而且在启动前,还需要注意的是,表空间文件的大小不再是5M了,所以还需要在my.cnf中重新定义innodb_log_file_size
Mysql 备份
如果数据从备份那一刻开始有数据发生变化时,需要从放二进制日志
到此,恢复已经完成

 



                                ———————————————————————————————————————————————
        
                     增量备份 : 
                               innobackupex –user=USER –password=PASSWD –incremental –incremental-basedir=/上一次完全备份目录或者上一次增量备份目录 /保存路径
                                –incremental 增量备份
                                –incremental-basedir 相对增量备份的目录;但是,如果是相对于完全备份来再次做备份的话,就是差异备份;如果是相对于上次增量备份来再次做备份的话,就是增量备份




                             ———————————————————————————————————————————————  
                    存在增量备份或者差异备份的时候做恢复:
                            需要准备好
                                    最后一次的完全备份+全部增量备份+binlog
                                    最后一次的完全备份+最后一次差异备份+binlog
                                    binlog的提取是根据最后一个增量备份文件中的xtrabackup_binlog_info信息,然后通过mysqlbinlog提取出最后时间点的sql语句


                           准备好之后,就开始讲数据文件进行合并:
                                    第一步:使用innobackupex对完全备份和增量备份(出了最后一个增量备份)做apply-log的redo-only操作
                            ~ ]# innobackupex –apply-log –redo-only     /完全备份目录/ –incremental-dir=/最后一个增量备份目录的绝对路径/
                                    第二步 :最后一个增量的时候,就不用redo-only了
                            ~ ]# innobackupex –apply-log –redo-only     /完全备份目录/ –incremental-dir=/最后一个增量备份目录的绝对路径/
                                    第三步:使copy-log将数据复制到数据目录下
                            ~ ]# innobackupex –copy-back  /完全备份路径/
                                    第四步:更改数据的属性(数主组),开启mysql服务,重放binlog筛选出来的sql语句
                                        

===============================================================================================



总结:
mysqldump+binlog
lvm2+cp/tar+binlog
xtrabackup(innodb)+binlog 

 

原创文章,作者:hunter,如若转载,请注明出处:http://www.178linux.com/60437