Linux 第71天 mariadb backup and recovery

时间: 20181013

        欢迎访问我的博客: www.winthcloud.top


目录

备份和恢复

冷备实验

lvm快照备份实验(温备)

逻辑备份工具：mysqldump, mydumper, phpMyAdmin

总结



备份和恢复

为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、******、误操作测试等数据丢失场景

备份注意要点

能容忍最多丢失多少数据

恢复数据需要在多长时间内完成

需要恢复哪些数据

还原要点

做还原测试，用于测试备份的可用性

还原演练

备份类型：

完全备份：整个数据集

部分备份：只备份数据子集，如部分库或表

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，

备份较快，还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘


冷、温、热备份

冷备：读写操作均不可进行

温备：读操作可执行；但写操作不可执行

热备：读写操作均可执行

MyISAM：温备，不支持热备

InnoDB：都支持

物理和逻辑备份(重点)

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快

逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，

速度慢，可能丢失精度


备份时需要考虑的因素

温备的持锁多久

备份产生的负载

备份过程的时长

恢复过程的时长

备份什么

数据

二进制日志、InnoDB的事务日志

程序代码（存储过程、函数、触发器、事件调度器）

服务器的配置文件



备份工具

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；

只支持冷备；完全和部分备份

LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份


mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；

对InnoDB存储引擎支持热备，结合binlog的增量备份

xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)

的工具，支持完全备份、增量备份


MariaDB Backup： 从MariaDB 10.1.26开始集成，

基于Percona XtraBackup 2.3.8实现


mysqlbackup：热备份， MySQL Enterprise Edition组件(收费)


mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，

使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库



冷备实验

即将mariadb服务停掉然后直接将其数据文件全部备份

datadir下的内容，二进制日志，事务日志等，全部备份，然后再另外一台机器还原



lvm快照备份实验(温备)

想要使用lvm快照实现备份还原，首先数据库文件必须是在lvm逻辑卷上存放，其次就是

卷组中有空余的空间用来创建快照卷，此种方法备份时有短暂的时间锁表用来创建快照

创建完成后即可将其释放(如果用脚本几乎就是热备) 以下为具体步骤:


1. 锁表，创建数据库分区快照 (锁表之前模拟有事务未提交完成)

mysql -uroot -pcentos -e 'FLUSH TABLES WITH READ LOCK;'

lvcreate -L 1G -s -p r  -n snap_data /dev/skyVG/data

2. 记录当前binlog位置，解锁

mysql -uroot -pcentos -e 'FLUSH LOGS;'

mysql -uroot -pcentos -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE

mysql -uroot -pcentos -e 'UNLOCK TABLES;'


3. 备份数据库内容并模拟插入新数据(如果是还原至其它的服务器注意要备份配置文件)

mount -o nouuid,norecovery /dev/mapper/skyVG-snap_data /mnt

tar -Jcf /root/mysql.back.xz /mnt/mysql

模拟向插入新数据(此处不写具体命令了，自行插入)


4. 还原所备份的数据(还原时关闭记录二进制日志记录)

因为是全部还原所以需要将数据库的数据文件全部删除

(切记不要删除二进制日志,还原后需要用此日志还原至最新状态)

systemctl stop mariadb

rm -rf /data/mysql/*

cd /data/mysql

tar -xf /root/msyq.back.xz

mv mnt/mysql/* ./

rm -rf mnt


service mysqld start


5. 使用binlog还原至最新(建议此时关闭二进制日志记录)

查看之前所备份的那个位置记录,然后将此位置文件之后的所有binlog文件重定向至

一个文件里,我这里的位置记录是binlog.000004 379

cat mysql.posi

mysql -uroot -pcentos -e 'SET GLOBAL sql_log_gin=0'

mysqlbinlog --start-position=379 /data/mariadblog/binlog.000004>rect.sql


如在此之后还有其它binlog将其追加至同一个文件中(有几个加几个)

mysqlbinlog --start-position=379 /data/mariadblog/binlog.000005>>rect.sql


将此文件同步至数据库中

mysql -uroot -pcentos < rect.sql

mysql -uroot -pcentos -e 'SET GLOBAL sql_log_gin=1'

此时数据库中已同步至最新的状态(可以查看即使当时有事务未完成提交也不影响恢复

后丢失数据)



逻辑备份工具：mysqldump, mydumper, phpMyAdmin

此种备份与lvm不同之处在于这个备份是在逻辑上的备份，即不是基于底层硬盘块的备份，而是

基于sql语句进行的备份，原理是mysqldump命令可以将数据库里要备份的数据库做查询并将数

据库的所有配置和表格的数据转换为sql语句，还原时则是重新将一个空库中插入所备份的sql

语句，可能会出现丢失精度，这里使用mysqldump命令做讲解

mysqldump工具: 客户端命令，通过mysql协议连接至mysql服务器进行备份

mysqldump参考: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump常见选项：

-A, --all-databases 备份所有数据库，含create database

-B, --databases db_name… 指定备份的数据库，包括create database语句

-E, --events: 备份相关的所有event scheduler

-R, --routines: 备份所有存储过程和自定义函数

--triggers: 备份表相关触发器，默认启用,用--skip-triggers, 不备份触发器

--default-character-set=utf8 指定字符集

--master-data[=#]: 此选项须启用二进制日志

1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#，

默认为1

2:记录为注释的CHANGE MASTER TO语句 此选项会自动关闭--lock-tables功能

自动打开-x|--lock-all-tables功能(除非开启--single-transaction)


-F, --flush-logs: 备份前滚动日志，锁定表完成后，执行flush logs命令,

生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。

建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，

--master-data 一起使用实现，此时只刷新一次日志

--compact: 去掉注释，适合调试，生产不使用

-d, --no-data 只备份表结构

-t, --no-create-info 只备份数据,不备份create table

-n,--no-create-db 不备份create database，可被-A或-B覆盖

--flush-privileges 备份mysql或相关时需要使用

-f, --force 忽略SQL错误，继续执行

--hex-blob 使用十六进制符号转储二进制列(例如，“abc”变为0x616263)

受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT

-q, --quick 不缓存查询，直接输出，加快备份速度


MyISAM备份选项：

支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

锁定方法如下：

-x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加

--single-transaction或--lock-tables选项会关闭此选项功能

注意：数据量大时，可能会导致长时间无法并发访问数据库

-l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，

默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成

数据不一致

注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用


InnoDB备份选项： 支持热备，可用温备但不建议用

--single-transaction

此选项Innodb中推荐使用，不适用MyISAM，

此选项会开始备份前，先执行START TRANSACTION指令开启事务

此选项通过在单个事务中转储所有表来创建一致的快照。 

仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）;

转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转

储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：

ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE

此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥

备份大型表时，建议将--single-transaction选项和--quick结合一起使用



InnoDB建议备份策略(下方为一条命令)

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 

--flush-privileges --triggers --default-character-set=utf8 --hex-blob

> $BACKUP/fullbak_$BACKUP_TIME.sql


MyISAM建议备份策略(下方为一条命令)

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges 

--triggers --default-character-set=utf8 --hex-blob 

>$BACKUP/fullbak_$BACKUP_TIME.sql



mysqldump备份示例(建议还原时关闭bin_log日志功能)

1. 先备份数据库(下方为一条命令)

mysqldump -A -E -R --master-data=2 --single-transaction -uroot

-pcentos  > all.sql

2. 模拟备份数据库后新插入数据

这里不写具体命令了，自行创造，原理和上一个类似

3. 还原所备份的数据

还原第一步先要配置好一个mysql使变成运行状态

因为所备份的数据都是sql语句，直接执行命令导入即可

mysql -uroot -pcentos -e 'SET GLOBAL sql_log_gin=0'

mysql -uroot -pcentos < all.sql

4. 根据binlog日志还原数据库

因为all.sql里有写备份时所备份的位置即从那个位置之后的binlog都添加至一个文件

即可实现不愿

mysqlbinlog --start-position=245 mariadb.bin.log.000005 > rec.sql

mysqlbinlog --start-position=245 mariadb.bin.log.000006 >> rec.sql

mysql -uroot -pcentos < rec.sql

mysql -uroot -pcentos -e 'SET GLOBAL sql_log_gin=1'


总结

1.使用lvm快照备份恢复，即使有事务当时未提交，恢复时也不会丢失数据，原理是因为事务

在未提交时只是在事务日志里有记录，但并未写入binlog日志文件中，即未对表做修改。

当做备份时，其实事务里的内容由于未提交所以还原时其实是被丢弃了，但由于解锁后

事务又提交了，即产生了二进制日志，所以用二进制日志即可将之前被丢弃的事务再次

恢复至最新的状态

2.单台机器虽然有备份但是还原需要时间会影响线上业务

3.mysqldump备份在一定基础上可以接受跨版本还原数据库(测试)