MySQL数据备份

1 数据库备份

1.1为什么需要备份数据？

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种：

• 硬件故障
• 软件故障
• 自然灾害
• 黑客攻击
• 误操作 (占比最大)

所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略：

• 能够容忍丢失多少数据
• 恢复数据需要多长时间
• 需要恢复哪一些数据

1.2 MySQL备份类型

在MySQl中我们备份数据一般有几种方式

• 热备份：指的是当数据库进行备份时, 数据库的读写操作均不是受影响
• 温备份：指的是当数据库进行备份时, 数据库的读操作可以执行, 但不能执行写操作
• 冷备份：指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

MySQL中进行不同方式的备份还要考虑存储引擎是否支持

备份方式	MyISAM	InnoDB
热备	×	√
温备	√	√
冷备	√	√

我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式
物理备份:一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
逻辑备份:一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

1.3 备份需要考虑的问题

定制备份策略前, 我们还需要考虑一些问题,我们要备份什么?

备份的数据

一般情况下, 我们需要备份的数据分为以下几种

• 数据
• 数据文件日志文件（比如事务日志，二进制日志）
• InnoDB事务日志
• 代码(存储过程、存储函数、触发器、事件调度器)
• 服务配置文件（十分重要，各个配置文件都要备份）
• 用于实现数据库备份的脚本，数据库自身清理的crontab等……

备份工具

这里我们列举出常用的几种备份工具：

mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
cp, tar 等归档复制工具 : 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
lvm2 snapshot : 几乎热备, 借助文件系统管理工具进行备份
mysqlhotcopy : 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎
xtrabackup : 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份.

1.4 设计合适的备份策略

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下几种：
（1）直接cp,tar复制数据库文件
（2）mysqldump+复制BIN LOGS
（3）lvm2快照+复制BIN LOGS
（4）xtrabackup

以上的几种解决方案分别针对于不同的场景

1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后,定期使用xtrabackup进行增量备份或差异备

1.5 MySQL数据备份

逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
mysqldump mydumper

逻辑备份的优点：
在备份速度上两种备份要取决于不同的存储引擎
物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表
逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理
逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高
逻辑备份也对保持数据的安全性有保证

逻辑备份的缺点：
逻辑备份要对RDBMS产生额外的压力，而裸备份无压力
逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
逻辑备份可能会丢失浮点数的精度信息
物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到异构系统中如Windows。

建议：

# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1 #5.7 必须配置server-id 启用binlog功能
binlog_format=statement  #5.7 默认binlog只记录create、drop和alter，要记录insert语句必须设置格式
datadir = /usr/local/mysql/data   #添加此行，数据存放目录
innodb_file_per_table = 1     #启用InnoDB独立表空间，默认所有数据库使用一个表空间

1.6 mysqladmin

1.6.1 功能
执行管理操作的工具

1.6.2 使用场景
检查服务器配置、当前运行状态 创建、删除数据库 设置新密码 flush-privileges、reload重新加载权限数据表 refresh刷新数据表、重启日志 start-slave从服务器上启动复制 stop-slave从服务器上停止复制

1.6.3 应用演示