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 条评论
登录 后参与评论

相关文章

来自专栏C/C++基础

MySQL设置远程访问

在MySQL Server端,执行mysql 命令进入mysql 命令模式。使用grant命令用来建立新用户,指定用户口令并增加用户权限。命令格式如下:

1591
来自专栏醉梦轩

Ubuntu 16.04 部署MySQL服务

MySQL是一款开源的关系型数据库管理系统,大量公司都在使用它,或是在它的基础上做二次开发。有时,我们会需要在Linux系统上部署MySQL服务用于测试。

1832
来自专栏日常分享

Oracle常用数据库系统表单以及SQL的整理

  因为最近涉及到了一些数据库的归档,备份等工作,所以一部分的重心放在了数据库上,毕竟之前对数据库的了解也只停留在了一般的建表,查询,最多最多再写一写触发器之类...

1421
来自专栏林欣哲

MySQL数据库备份和恢复

2312
来自专栏PHP在线

MySQL事务隔离级别详解

SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销...

3897
来自专栏散尽浮华

Mysql主从同步(1)-主从/主主环境部署梳理

Mysql复制概念说明 Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一...

3565
来自专栏前端vue

对数据进行一些基本操作(四)

完整代码 ↓ ↓ ↓ ↓ github地址:https://github.com/jgsrty/jianshu_node 码云地址:https://gite...

1654
来自专栏乐沙弥的世界

MySQL多实例配置(一)

    MySQL数据库的集中化运维,可以通过在一台MySQL数据库服务器上,部署多个MySQL实例。该功能是通过mysqld_multi来实现。mysqld_...

1632
来自专栏Danny的专栏

SQL Server 2008 附加数据库时出错

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

2.5K3
来自专栏张善友的专栏

SQL Server : Browser服务

SQL Server : Browser服务是SQL Server 2005新增的,简单的说,如果一个物理服务器上面有多个SQL Server实例,那么为了确保...

2195

扫码关注云+社区

领取腾讯云代金券