专栏首页散尽浮华Mysql备份系列(1)--备份方案总结性梳理

Mysql备份系列(1)--备份方案总结性梳理

mysql数据库备份有多么重要已不需过多赘述了,废话不多说!以下总结了mysql数据库的几种备份方案:

一、binlog二进制日志通常作为备份的重要资源,所以再说备份方案之前先总结一下binlog日志~~ 1.binlog日志内容 1)引起mysql服务器改变的任何操作。 2)复制功能依赖于此日志。 3)slave服务器通过复制master服务器的二进制日志完成主从复制,在执行之前保存于中继日志(relay log)中。 4)slave服务器通常可以关闭二进制日志以提升性能。

2.binlog日志文件的文件表现形式 1)默认在安装目录下,存在mysql-bin.00001, mysql-bin.00002的二进制文件(binlog日志文件名依据my.cnf配置中的log-bin参数后面的设置为准) 2)还有mysql-bin.index用来记录被mysql管理的二进制文件列表 3)如果需要删除二进制日志时,切勿直接删除二进制文件,这样会使得mysql管理混乱。

3.binlog日志文件查看相关mysql命令 1)SHOW MASTER STATUS ; 查看正在使用的二进制文件 MariaDB [(none)]> SHOW MASTER STATUS ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 245 | | | +------------------+----------+--------------+------------------+ 2)FLUSH LOGS; 手动滚动二进制日志 MariaDB [(none)]> FLUSH LOGS; MariaDB [(none)]> SHOW MASTER STATUS ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 245 | | | +------------------+----------+--------------+------------------+ 滚动以后,mysql重新创建一个新的日志mysql-bin.000004 3)SHOW BINARY LOGS 显示使用过的二进制日志文件 MariaDB [(none)]> SHOW BINARY LOGS ; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 30373 | | mysql-bin.000002 | 1038814 | | mysql-bin.000003 | 288 | | mysql-bin.000004 | 245 | 4)SHOW BINLOG EVENTS 以表的形式查看二进制文件 命令格式:SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G; *************************** 99. row *************************** Log_name: mysql-bin.000001 Pos: 30225 Event_type: Query Server_id: 1 End_log_pos: 30354 Info: use `mysql`; DROP TEMPORARY TABLE `tmp_proxies_priv` /* generated by server */

4.MySQL二进制文件读取工具mysqlbinlog 命令格式:mysqlbinlog [参数] log-files 有以下四种参数选择: --start-datetime --stop-datetime --start-position --stop-position [root@test-huanqiu ~]# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001 截取一下结果: # at 30225 #151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0 use `mysql`/*!*/; SET TIMESTAMP=1448858615/*!*/; SET @@session.pseudo_thread_id=1/*!*/

根据以上截取结果第二行,进行解释二进制日志内容 1)时间点: 151130 12:43:35 2)服务器ID: server id 1 服务器ID主要用于标记日志产生的服务器,主要用于双主模型中,互为主从,确保二进制文件不会被相互循环复制 3)记录类型: Query 4) 线程号: thread_id = 1 5) 语句的时间戳和写入二进制日志文件的时间差; exec_time=0 6) 事件内容 7)事件位置 #at 30225 8) 错误代码 error_code=0 9) 事件结束位置 end_log_pos也就是下一事件开始的位置

5.二进制日志格式 由bin_log_format={statement|row|mixed}定义 1)statement: 基于语句,记录生成数据的语句 缺点在于如果当时插入信息为函数生成,有可能不同时间点执行结果不一样, 例如: INSERT INTO t1 VALUE (CURRENT_DATE()); 2)row: 基于行数据 缺点在于,有时候数据量会过大 3)mixed: 混合模式,又mysql自行决定何时使用statement, 何时使用row 模式

6.二进制相关参数总结 1)log_bin = {ON|OFF} 还可以是个文件路径,自定义binlog日志文件名,使用“log_bin=“或“log-bin=“都可以,主要用于控制全局binlog的存放位置和是否开启binlog日志功能。 比如:log_bin=mysql-bin 或者 log-bin=mysql-bin,这样binlog日志默认会和mysql数据放在同一目录下。 2) log_bin_trust_function_creators 是否记录在 3) sql_log_bin = {ON|OFF} 会话级别是否关闭binlog, 如果关闭当前会话内的操作将不会记录 4) sync_binlog 是否马上同步事务类操作到二进制日志中 5) binlog_format = {statement|row|mixed} 二进制日志的格式,上面单独提到了 6) max_binlog_cache_size = 二进制日志缓冲空间大小,仅用于缓冲事务类的语句; 7) max_binlog_stmt_cache_size = 语句缓冲,非事务类和事务类共用的空间大小 8) max_binlog_size = 二进制日志文件上限,超过上限后则滚动 9) 删除二进制日志 命令格式:PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } MariaDB> PURGE BINARY LOGS TO 'mysql-bin.010'; MariaDB> PURGE BINARY LOGS BEFORE '2016-11-02 22:46:26'; 建议:切勿将二进制日志与数据文件放在一同设备;可以将binlog日志实时备份到远程设备上,以防出现机器故障进行数据恢复;

二、接下来说下binlog二进制日志备份和恢复 1.为什么做备份: (1)灾难恢复 (2)审计,数据库在过去某一个时间点是什么样的 (3)测试

2.备份的目的: (1)用于恢复数据 (2)备份结束后,需要周期性的做恢复测试

3.备份类型: (1)根据备份时,服务器是否在线 1)冷备(cold backup): 服务器离线,读写操作都不能进行 2)温备份: 全局施加共享锁,只能读不能写 3)热备(hot backup):数据库在线,读写照样进行 (2)根据备份时的数据集分类 1)完全备份(full backup) 2)部分备份(partial backup) (3)根据备份时的接口 1)物理备份(physical backup):直接复制数据文件 ,打包归档 特点: 不需要额外工具,直接归档命令即可,但是跨平台能力比较差;如果数据量超过几十个G,则适用于物理备份 2)逻辑备份(logical backup): 把数据抽取出来保存在sql脚本中 特点: 可以使用文本编辑器编辑;导入方便,直接读取sql语句即可;逻辑备份恢复时间慢,占据空间大;无法保证浮点数的精度;恢复完数据库后需要重建索引。 (4)根据备份整个数据还是变化数据 1) 全量备份 full backup 2) 增量备份 incremental backup 在不同时间点起始备份一段数据,比较节约空间;针对的是上一次备份后有变化的数据,备份数据少,备份快,恢复慢 3) 差异备份 differential backup 备份从每个时间点到上一次全部备份之间的数据,随着时间增多二增多;比较容易恢复;对于很大的数据库,可以考虑主从模型,备份从服务器的内容。针对的是上一次全量备份后有变化的数据,备份数据多,备份慢,恢复快。 (5)备份策略,需要考虑因素如下 备份方式 备份实践 备份成本 锁时间 时长 性能开销 恢复成本 恢复时长 所能够容忍丢失的数据量 (6)备份内容 1)数据库中的数据 2)配置文件 3)mysql中的代码: 存储过程,存储函数,触发器 4)OS 相关的配置文件,chrontab 中的备份策略脚本 5)如果是主从复制的场景中: 跟复制相关的信息 6)二进制日志文件需要定期备份,一旦发现二进制文件出现问题,需马上对数据进行完全备份

(7)Mysql最常用的三种备份工具: 1)mysqldump: 通常为小数据情况下的备份 innodb: 热备,温备 MyISAM, Aria: 温备 单线程备份恢复比较慢 2)Xtrabackup(通常用innobackupex工具): 备份mysql大数据 InnoDB热备,增量备份; MyISAM温备,不支持增量,只有完全备份 属于物理备份,速度快; 3)lvm-snapshot: 接近于热备的工具:因为要先请求全局锁,而后创建快照,并在创建快照完成后释放全局锁; 使用cp、tar等工具进行物理备份; 备份和恢复速度较快; 很难实现增量备份,并且请求全局需要等待一段时间,在繁忙的服务器上尤其如此;

除此之外,还有其他的几个备份工具: -->mysqldumper: 多线程的mysqldump -->SELECT clause INTO OUTFILE '/path/to/somefile' LOAD DATA INFILE '/path/from/somefile' 部分备份工具, 不会备份关系定义,仅备份表中的数据; 逻辑备份工具,快于mysqldump,因为不备份表格式信息 -->mysqlhotcopy: 接近冷备,基本没用

mysqldump工具基本使用 1. mysqldump [OPTIONS] database [tables…] 还原时库必须存在,不存在需要手动创建     --all-databases: 备份所有库     --databases db1 db2 ...: 备份指定的多个库,如果使用此命令,恢复时将不用手动创建库。或者是-B db1 db2 db3 ....     --lock-all-tables:请求锁定所有表之后再备份,对MyISAM、InnoDB、Aria做温备     --lock-table: 对正在备份的表加锁,但是不建议使用,如果其它表被修改,则备份后表与表之间将不同步     --single-transaction: 能够对InnoDB存储引擎实现热备; 启动一个很大的大事物,基于MOCC可以保证在事物内的表版本一致 自动加锁不需要,再加--lock-table, 可以实现热备 备份代码:    --events: 备份事件调度器代码    --routines: 备份存储过程和存储函数    --triggers:备份触发器 备份时滚动日志:    --flush-logs: 备份前、请求到锁之后滚动日志; 方恢复备份时间点以后的内容 复制时的同步位置标记:主从架构中的,主服务器数据。效果相当于标记一个时间点。    --master-data=[0|1|2]    0: 不记录    1:记录为CHANGE MASTER语句    2:记录为注释的CHANGE MASTER语句

2. 使用mysqldump备份大体过程: 1) 请求锁:--lock-all-tables或使用–singe-transaction进行innodb热备; 2) 滚动日志:--flush-logs 3) 选定要备份的库:--databases 4) 记录二进制日志文件及位置:--master-data= FLUSH TABLES5 WITH READ LOCK;

3. 恢复: 恢复过程无需写到二进制日志中 建议:关闭二进制日志,关闭其它用户连接;

4. 备份策略:基于mysqldump 备份:mysqldump+二进制日志文件;(“mysqldump >”) 周日做一次完全备份:备份的同时滚动日志 周一至周六:备份二进制日志; 恢复:(“mysql < ”)或在mysql数据库中直接执行“source sql备份文件;”进行恢复。如果sql执行语句比较多,可以将sql语句放在一个文件内,将文件名命名为.sql结尾,然后在mysql数据库中使用"source 文件.sql;"命令进行执行即可! 完全备份+各二进制日志文件中至此刻的事件

5. 实例说明: 参考:Mysql备份系列(2)--mysqldump备份(全量+增量)方案操作记录

lvm-snapshot:基于LVM快照的备份 1.关于快照: 1)事务日志跟数据文件必须在同一个卷上; 2)刚刚创立的快照卷,里面没有任何数据,所有数据均来源于原卷 3)一旦原卷数据发生修改,修改的数据将复制到快照卷中,此时访问数据一部分来自于快照卷,一部分来自于原卷 4)当快照使用过程中,如果修改的数据量大于快照卷容量,则会导致快照卷崩溃。 5)快照卷本身不是备份,只是提供一个时间一致性的访问目录。

2.基于快照备份几乎为热备: 1)创建快照卷之前,要请求MySQL的全局锁;在快照创建完成之后释放锁; 2)如果是Inoodb引擎, 当flush tables 后会有一部分保存在事务日志中,却不在文件中。 因此恢复时候,需要事务日志和数据文件 但释放锁以后,事务日志的内容会同步数据文件中,因此备份内容并不绝对是锁释放时刻的内容,由于有些为完成的事务已经完成,但在备份数据中因为没完成而回滚。 因此需要借助二进制日志往后走一段

3.基于快照备份注意事项: 1)事务日志跟数据文件必须在同一个卷上; 2)创建快照卷之前,要请求MySQL的全局锁;在快照创建完成之后释放锁; 3)请求全局锁完成之后,做一次日志滚动;做二进制日志文件及位置标记(手动进行);

4.为什么基于MySQL快照的备份很好? 原因如下几点: 1)几乎是热备 在大多数情况下,可以在应用程序仍在运行的时候执行备份。无需关机,只需设置为只读或者类似只读的限制。 2)支持所有基于本地磁盘的存储引擎 它支持MyISAM, Innodb, BDB,还支持 Solid, PrimeXT 和 Falcon。 3)快速备份 只需拷贝二进制格式的文件,在速度方面无以匹敌。 4)低开销 只是文件拷贝,因此对服务器的开销很细微。 5)容易保持完整性 想要压缩备份文件吗?把它们备份到磁带上,FTP或者网络备份软件 -- 十分简单,因为只需要拷贝文件即可。 6)快速恢复 恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快,甚至可能更快,将来会更快。 7)免费 无需额外的商业软件,只需Innodb热备工具来执行备份。

快照备份mysql的缺点: 1)需要兼容快照 -- 这是明显的。 2)需要超级用户(root) 在某些组织,DBA和系统管理员来自不同部门不同的人,因此权限各不一样。 3)停工时间无法预计,这个方法通常指热备,但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。 4)多卷上的数据问题 如果你把日志放在独立的设备上或者你的数据库分布在多个卷上,这就比较麻烦了,因为无法得到全部数据库的一致性快照。不过有些系统可能能自动做到多卷快照。

5.备份与恢复的大体步骤 备份: 1)请求全局锁,并滚动日志 mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH LOGS; 2)做二进制日志文件及位置标记(手动进行); [root@test-huanqiu ~]# mysql -e 'show master status' > /path/to/orignal_volume 3)创建快照卷 [root@test-huanqiu ~]# lvcreate -L -s -n -p r /path/to/some_lv 4)释放全局锁 5)挂载快照卷并备份 6)备份完成之后,删除快照卷

恢复: 1)二进制日志保存好; 提取备份之后的所有事件至某sql脚本中; 2)还原数据,修改权限及属主属组等,并启动mysql 3)做即时点还原 4)生产环境下, 一次大型恢复后,需要马上进行一次完全备份。

备份与恢复实例说明: 环境, 实现创建了一个test_vg卷组,里面有个mylv1用来装mysql数据,挂载到/data/mysqldata

备份实例: 1. 创建备份专用的用户,授予权限FLUSH LOGS 和 LOCK TABLES MariaDB > GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'192.168.1.%' IDENTIFIED BY 'lvm'; MariaDB > FLUSH PRIVILEGES;

2. 记录备份点 [root@test-huanqiu ~]# mysql -ulvm -h192.168.1.10 -plvm -e 'SHOW MASTER STATUS' > /tmp/backup_point.txt

3. 创建快照卷并挂载快照卷 [root@test-huanqiu ~]# lvcreate -L 1G -s -n lvmbackup -p r /dev/test_vg/mylv1 [root@test-huanqiu ~]# mount -t ext4 /dev/test_vg/lvmbackup /mnt/

4. 释放锁 [root@test-huanqiu ~]# mysql -ulvm -h192.168.98.10 -plvm -e 'UNLOCK TABLES' 做一些模拟写入工作 MariaDB [test]> create database testdb2

5. 复制文件 [root@test-huanqiu ~]# cp /data/mysqldata /tmp/backup_mysqldata -r

6. 备份完成卸载,删除快照卷 [root@test-huanqiu ~]# umount /mnt [root@test-huanqiu ~]# lvmremove /dev/test_vg/lvmbackup

还原实例: 假如整个mysql服务器崩溃,并且目录全部被删除

1. 数据文件复制回源目录 [root@test-huanqiu ~]# cp -r /tmp/backup_mysqldata/* /data/mysqldata/ MariaDB [test]> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | mysqldata | | openstack | | performance_schema | | test | +--------------------+ 此时还没有testdb2, 因为这个是备份之后创建的,因此需要通过之前记录的二进制日志

2. 查看之前记录的记录点。向后还原 [root@test-huanqiu ~]# cat /tmp/backup_point.txt FilePositionBinlog_Do_DBBinlog_Ignore_DB mysql-bin.000001245 [root@test-huanqiu ~]# mysqlbinlog /data/binlog/mysql-bin.000001 --start-position 245 > tmp.sql MariaDB [test]> source /data/mysqldata/tmp.sql MariaDB [test]> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | mysqldata | | openstack | | performance_schema | | test | | testdb2 | +--------------------+ 8 rows in set (0.00 sec) testdb2 已经被还原回来。

具体实例说明,参考:Mysql备份系列(4)--lvm-snapshot备份mysql数据(全量+增量)操作记录

使用Xtrabackup进行MySQL备份:

参考:Mysql备份系列(3)--innobackupex备份mysql大数据(全量+增量)操作记录

-------------------------------------------------------------------------------------- 关于备份和恢复的几点经验之谈

备份注意: 1. 将数据和备份放在不同的磁盘设备上;异机或异地备份存储较为理想; 2. 备份的数据应该周期性地进行还原测试; 3. 每次灾难恢复后都应该立即做一次完全备份; 4. 针对不同规模或级别的数据量,要定制好备份策略; 5. 二进制日志应该跟数据文件在不同磁盘上,并周期性地备份好二进制日志文件;

从备份中恢复应该遵循步骤: 1. 停止MySQL服务器; 2. 记录服务器的配置和文件权限; 3. 将数据从备份移到MySQL数据目录;其执行方式依赖于工具; 4. 改变配置和文件权限; 5. 以限制访问模式重启服务器;mysqld的--skip-networking选项可跳过网络功能; 方法:编辑my.cnf配置文件,添加如下项: skip-networking socket=/tmp/mysql-recovery.sock 6. 载入逻辑备份(如果有);而后检查和重放二进制日志; 7. 检查已经还原的数据; 8. 重新以完全访问模式重启服务器; 注释前面第5步中在my.cnf中添加的选项,并重启;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 全量备份/增量备份/差异备份说明

    作为一名运维工程师,在日常工作中会时常对各类重要数据进行备份,为了方便管理,运用何种备份方案是至关重要的。 今天在此简单说明下Linux运维中的备份种类:全量备...

    洗尽了浮华
  • Oracle数据库冷备份与热备份操作梳理

    Oracle数据库的备份方式有冷备份和热备份两种,针对这两种备份的实施过程记录如下: 一、Oracle冷备份 概念 数据库在关闭状态下完成所有物理系统文件拷贝的...

    洗尽了浮华
  • Mysql备份系列(2)--mysqldump备份(全量+增量)方案操作记录

    在日常运维工作中,对mysql数据库的备份是万分重要的,以防在数据库表丢失或损坏情况出现,可以及时恢复数据。 线上数据库备份场景: 每周日执行一次全量备份,然后...

    洗尽了浮华
  • MySQL数据库备份和恢复

    linxinzhe
  • 6个虚拟机备份和恢复的最佳实践

    虚拟机的架构与传统的物理机有着天壤之别,因此不适合于传统的备份方案。在本文中,我们将介绍一些虚拟机备份的最佳实践。

    Techeek
  • 经典案例:遭遇RMAN-06004,RMAN-20003后

    黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 故障描述: 最近遇到11.2.0.3 RAC FOR AIX平台在出...

    数据和云
  • MySQL备份原理详解 转

    备份是数据安全的最后一道防线,对于任何数据丢失的场景,备份虽然不一定能恢复百分之百的数据(取决于备份周期),但至少能将损失降到最低。衡量备份恢复有两个重要的指标...

    阿dai学长
  • mongodb副本集的备份与恢复

    shirley
  • Win7系统电脑如何完成备份?这个方法你应该会

    电脑是现在人们每天在工作的时候不可缺少的,但是电脑的系统却是不一样的,很多人还是比较喜欢使用Win7系统,今天呢就来给大家分享Win7系统电脑如何完成备份,这个...

    高效办公
  • MySQL备份恢复深度优化计划

    备份恢复之前和同事投入了一些精力来完善,算是走上了平台化对接的一个开始,在满足功能的前提下,能够基本实现数据全备,增备和DML闪回,但是在性能和可控性方面还是存...

    jeanron100

扫码关注云+社区

领取腾讯云代金券