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中添加的选项,并重启;

上一篇:mysql备份还原


下一篇:MySQL备份方式简介