mysql备份与恢复-innobackupex

系统环境

主机名   IP

node1    192.168.231.130

node2    192.168.231.131


MariaDB版本

# mysql --version
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作

测试：
要注意的要点：
能容忍最多丢失多少数据
恢复数据需要在多长事件内完成
需要恢复那些数据
（1）做还原测试，用于测试备份的可用性
（2）做还原演练

备份时需要考虑的因素：
    持锁多久
    备份过程的时长
    备份负载
    恢复过程的时长

备份什么？
    数据
    二进制日志、InnoDB事务日志
    代码（存储过程、存储函数、触发器、事件调度器）
    服务器的配置文件

设计备份方案：
数据集：完全+增量
备份手段：物理，逻辑

备份工具：
mysqldump:逻辑备份工具，适用所有存储引擎，温备；完全备份、部分备份；对INnoDB存储引擎支持热备
cp,tar等复制归档工具：物理备份工具，使用所有存储引擎；冷备：完全备份，部分备份
lvm2的快照：几乎热备；仅适用于MyISAM存储引擎
mysqlhotcopy:几乎冷备；仅使用于MyISAM存储引擎

备份工具的选择：
    mysqldump+复制binlog:
    mysqldump:完全备份：
                      复制binlog中指定时间范围的event:增量备份
    lvm2快照+复制binlog:
    lvm2快照：使用cp或tar等做物理备份；完全备份
                      复制binlog中指定时间范围的event:增量备份
    xtrabackup:
                     支持对InnoDB做热备（物理备份）的工具
                     完全备份、增量备份

备份类型

完全备份，部分备份，增量备份，差异备份

完全备份：整个数据集
部分备份：只备份数据子集
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据
差异备份：仅备份最近一次完全备份以来变化的数据

热备份、温备份、冷备份

    热备：读写操作均可执行
    温备：读操作可执行；但写操作不执行
    冷备：读写操作均不可进行
    MyISAM:温备，不能热备
    InnoDB:热备

物理备份、逻辑备份
 

    物理备份：直接复制数据文件进行备份
    逻辑备份：从数据库中“导出”数据另存而进行备份
    逻辑备份：与存储引擎无关

 

innobackupex 的备份与恢复

命令介绍

--user,--password：mysql的用户名和密码
innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

--no-timestamp：带上参数就不会生成一个时间戳文件夹，默认生成时间戳文件
innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/ --no-timestamp
innobackupex --user=user --password=passwd --no-timestamp ./2016/09-29/`date +%H-%M`.dbname

--defaults-file：指定mysql的配置文件，不带该参数也行
nnobackupex --defaults-file=/tmp/other-my.cnf --user=DBUSER --password=DBUSERPASS /
path/to/BACKUP-DIR/

--apply-log：应用日志到数据库文件，进行恢复前准备工作
innobackupex --apply-log /path/to/BACKUP-DIR

--use-memory：增加备份速度，如果有多的RAM可以增加内存来处理速度，默认100M 
innobackupex --apply-log --use-memory=4G /path/to/BACKUP-DIR

--incremental:进行增量备份，后边跟备份目录；--incremental-basedir：基于上一次备份目录
innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1

--redo-only：指定该参数，不进行回滚操作
innobackupex --apply-log --redo-only BASE-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

--copy-back：恢复数据文件到数据库的datadir目录
innobackupex --copy-back BASE-DIR

注意：innobackupex命令执行后出现“completed OK!”，方为正确。

全量备份

创建备份后，数据尚未准备好还原。可能有要撤消的未提交事务或要重播日志中的事务。
 全量备份：把node1节点的数据备份，在node2节点进行还原（要清除node2中my.cnf中datadir目录中的数据）

备份node1数据库数据，在node2恢复
[root@node1 ~]# mysql
MariaDB [mydb]> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | wang     |
|  2 | zhangsan |
|  3 | li       |
|  4 | dsf      |
|  5 | wangsan  |
+----+----------+
#在node1上创建backups以备用数据
[root@node1 ~]# mkdir /backups
[root@node1 ~]# innobackupex --user=root /backups
[root@node1 ~]# scp -r /backups/2019-03-02_19-58-51 root@192.168.231.131:/root
[root@node2 ~]# mkdir /backups
[root@node2 ~]# mv 2019-03-02_19-58-51 /backups
[root@node2 ~]# innobackupex --apply-log /backups/2019-03-02_19-58-51
[root@node2 ~]# systemctl stop mariadb.service
[root@node2 ~]# innobackupex --copy-back /backups/2019-03-02_19-58-51
#数据库目录在datadir=/mydata/mysql，切换到目录，修改文件属主，属组为mysql,否则mariadb启动会失败
[root@node2 mysql]# chown -R mysql.mysql ./*
[root@node2 ~]# systemctl restart mariadb.service 
[root@node2 mysql]# mysql
MariaDB [mydb]> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | wang     |
|  2 | zhangsan |
|  3 | li       |
|  4 | dsf      |
|  5 | wangsan  |
+----+----------+

增量备份

增量备份在上一次全量备份的基础上进行备份

[root@node1 ~]# mysql
MariaDB [mydb]> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | wang     |
|  2 | zhangsan |
|  3 | li       |
|  4 | dsf      |
|  5 | wangsan  |
+----+----------+

#先全量备份
[root@node1 ~]# innobackupex --user=root /backups
[root@node1 2019-03-02_20-49-08]# pwd
/backups/2019-03-02_20-49-08
[root@node1 2019-03-02_20-49-08]# cat xtrabackup_checkpoints
backup_type = full-backuped    #全量备份
from_lsn = 0
to_lsn = 1607977
last_lsn = 1607977
compact = 0
recover_binlog_info = 0
[root@node1 ~]# mysql
MariaDB [mydb]> insert into student values(6,'xiaoming'),(7,'xiaoli');
MariaDB [mydb]> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | wang     |
|  2 | zhangsan |
|  3 | li       |
|  4 | dsf      |
|  5 | wangsan  |
|  6 | xiaoming |
|  7 | xiaoli   |
+----+----------+
#基于/backups/2019-03-02_20-49-08做增量备份
[root@node1 ~]# innobackupex --incremental /backups/ --incremental-basedir=/backups/2019-03-02_20-49-08
[root@node1 ~]# cat /backups/2019-03-02_20-56-41/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1607977
to_lsn = 1609154
last_lsn = 1609154
compact = 0
recover_binlog_info = 0
#进行恢复前准备工作，将目录中的数据文件应用到数据文件中
[root@node1 ~]# innobackupex --apply-log --redo-only /backups/2019-03-02_20-49-08
#基于增量文件，进行恢复前准备工作，将目录中的数据文件应用到数据文件中
[root@node1 ~]# innobackupex --apply-log --redo-only /backups/2019-03-02_20-49-08 --incremental-dir=/backups/2019-03-02_20-56-41
[root@node1 2019-03-02_20-49-08]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1609154
last_lsn = 1609154
compact = 0
recover_binlog_info = 0
[root@node1 ~]# systemctl stop mariadb.service
#把数据库的datadir目录中的数据删除，以便测试
[root@node1 ~]# rm -rf /mydata/mysql/*
#将数据拷贝回数据库目录
[root@node1 ~]# innobackupex --copy-back /backups/2019-03-02_20-49-08
[root@node1 mysql]# pwd
/mydata/mysql
#修改属主、属组为mysql
[root@node1 mysql]# chown -R mysql.mysql ./*
[root@node1 ~]# systemctl start mariadb
#恢复成功
[root@node1 ~]# mysql
MariaDB [mydb]> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | wang     |
|  2 | zhangsan |
|  3 | li       |
|  4 | dsf      |
|  5 | wangsan  |
|  6 | xiaoming |
|  7 | xiaoli   |
+----+----------+