MySQL数据库的备份 mysqldump和innobackupex

为什么要做备份？

在运维的世界里，数据备份是一件十分重要的事情，
毕竟如果说你的数据因为某些错误操作或系统的一些灾害出现都会导致数据丢失，如果没做备份的话，后果是不可想的。

此片文章介绍三种备份方法（详讲两种）

首先，要做MySQL的数据备份，首先就要打开MySQL的二进制文件。 /etc/my.cnf

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

我就具体说 mysqldump和Xtrabackup(通常用innobackupex)这两个工具

mysqldump是安装好MySQL服务自带的备份工具；
使用方法：
备份完整数据命令及语法

mysqldump -u root –p --databases xsy > /opt/xsy.sql

-u 指定用户
-p 说明要输入密码 没密码可以不跟
–databases 指定数据库
xsy 是我个人的数据库名称
> 指定到什么地方
/opt/xsy.sql 我存放的路径 要以.sql为结尾
还原数据库
mysql -u root –p < /opt/xsy.sql

备份所有的数据库
mysqldump -u root -p --opt --all-databases > /opt/all-data.sql
- -opt 是选项 后面指定所有数据库
备份xsy库的users表（备份指定表 不是所有）
mysqldump -u root -p xsy users > /opt/xsy-users.sql
备份xsy的users表和student表
mysqldump -u root -p xsy users student > /opt/xsy_table.sql

还原数据表
mysql -u root -p xsy < /opt/xsy-users.sql
**mysql的包含关系：
数据库(数据表)

数据表(表结构+数据)**

19.导出结构不导出数据
默认将xsy库下所有表的表结构全部导出
mysqldump -u root -p -d xsy > /opt/jiegou.sql
导出单个数据表的表结构
mysqldump -u root -p -d xsy users > /opt/jiegou_users.sql

20.导出数据不导出结构
mysqldump -u root -p -t xsy > /opt/shuju.sql
mysqldump -u root -p -t xsy users > /opt/shuju.sql

mysql数据库的增量备份
1、在mysql的配置文件开启二进制日志
vi /etc/my.cnf
server_id = 1
log_bin = mysqlbin

2、service restart mysqld

3、查看二进制日志
mysqlbinlog 二进制日志文件名

commit 提交

4、根据mysql二进制日志文件位置进行备份
mysqlbinlog --start-position=‘194’ --stop-position=‘625’ mysqlbin.000001 > /opt/hehe.sql
mysql -u root -p123123 < /opt/hehe.sql
–start-position 起始位置
–stop-position 结束位置

根据mysql二进制日志文件位置恢复

 mysqlbinlog --start-position='194' --stop-position='625' mysqlbin.000001  | mysql -u root -p123123

5、根据mysql二进制日志文件时间恢复
mysqlbinlog --start-datetime=‘2018-12-11 23:55:24’ --stop-datetime=‘2018-12-12 0:03:27’ mysqlbin.000001 |mysql -u root -p

–start-datetime 起始时间
–stop-datetime 结束时间

mysqladmin -u root -p

flush-logs; 手动滚动二进制文件

在数据库中查看当前的二进制日志的位置信息
show master status;

查看所有二进制日志

MySQL二进制文件读取工具mysqlbinlog
命令格式：mysqlbinlog [参数] log-files
有以下四种参数选择：
–start-datetime #用来指定二进制日志的起始日期
–stop-datetime #用来指定二进制日志的结束日期
–start-position #用来指定二进制日志的起始位置
–stop-position #用来指定二进制日志的结束位置
Mysqlbinlog可以完成mysql的增量备份
查看当前二进制日志记录格式
mysql> show variables like ‘binlog_format’;

建议：切勿将二进制日志与数据文件放在一同设备；可以将binlog日志实时备份到远程设备上，以防出现机器故障进行数据恢复；

3.备份类型：
（1）根据备份时，mysql服务是否在线
1）冷备(cold backup)： 停掉mysql这个服务，读写操作都不能进行
2）温备份： 全局施加共享锁，只能读不能写
3）热备(hot backup)：不停掉mysql这个服务，读写照样进行
（2）根据备份时的数据集分类
1）完全备份(full backup)
2）增量备份(partial backup)
（3）根据备份时的接口
1）物理备份（physical backup）：直接复制数据文件 ，打包归档，就是对mysql的数据存放路径进行打包
特点：
不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个G，则适用于物理备份
2）逻辑备份(logical backup)： 把数据抽取出来保存在sql脚本中，mysqldump就属于逻辑备份
特点：
可以使用文本编辑器编辑；导入方便，直接读取sql语句即可；逻辑备份恢复时间慢，占据空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引。
（4）根据备份整个数据还是变化数据
1） 全量备份 full backup
完全备份：每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长

2） 增量备份 incremental backup
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
3） 差异备份 differential backup
备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。
（5）备份策略，需要考虑因素如下
备份方式
备份实践
备份成本
锁时间
时长
性能开销
恢复成本
恢复时长
所能够容忍丢失的数据量

**Percona XtraBackup（简称PXB）**是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQl（Oracle）、Percona Server 和 MariaDB，并且全部开源

需要用PXB的工具

红色的这两个

二、innobackupex针对mysql数据库的备份环境部署
安装xtrabackup

[root@localhost ~]# tar xf percona-xtrabackup2.4.5-Linux-x86-_64.tar.gz -C /usr/src/

[root@localhost ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/

[root@localhost percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/

[root@localhost ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5

[root@localhost ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm

将该软件的解压产生的命令添加到环境变量中

Xtrabackup完全备份+binlog增量备份
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
–host 指定主机
–user 指定用户名
–password 指定密码
–port 指定端口
–databases 指定数据库
–incremental 创建增量备份
–incremental-basedir 指定包含完全备份的目录
–incremental-dir 指定包含增量备份的目录
–apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
–redo-only 不回滚未提交事务
–copy-back 恢复备份目录
[root@master backups]# innobackupex --user=root --password=123123 /backups/　　#在master上进行全库备份#
语法解释说明：
#–user=root 指定备份用户
#–password=123456 指定备份用户密码
#–host　　指定主机
#/backups　　指定备份目录

修改属主属组
chown -R mysql.mysql /usr/local/mysql/data/

backup-my.cnf　　#备份用到的配置选项信息文件
xtrabackup_binlog_info　　#mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件

xtrabackup_checkpoints　　#备份的类型、状态和LSN状态信息文件
xtrabackup_logfile　　　　#备份的日志文件
（2）恢复
[root@localhost ~]# innobackupex --apply-log /backups/2019-11-28_17-13-01/ #合并数据，使数据文件处于一致性的状态

rm -rf /usr/local/mysql/data/*　　#删除原有的数据

#使用innobackupex进行增量备份
[root@master backups]# innobackupex --user=root --password=123456 --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/

模拟实例
模拟mysql故障，删除数据目录所有数据
[root@localhost ~]# rm -rf /usr/local/mysql/data/*

确认全备和增量存在
合并全备目录 确保数据一致性
innobackupex --apply-log --redo-only /backups/2020-09-29_21-55-05/
将增量合并到全备中
innobackupex --apply-log --redo-only /backups/2020-09-29_21-55-05/ --incremental-dir=/backups/2020-09-29_22-11-47
恢复
innobackupex --copy-back /backups/2020-09-29_21-55-05/
修改属主属组
ll /usr/local/mysql/data/
chown -R mysql.mysql /usr/local/mysql/data/

mysql -uroot -p -e "show databases;"　　#查看数据是否恢复