3种mysql备份恢复方案优劣对比( 二 ) _mysql

备份的方案有哪些？
方案名称适用场景mysqldump + binlog全量备份 + 增量备份混合方案xtrabackupInnoDB 支持热备，支持全量备份/增量备份，MyISAM 支持温备，只支持全量备份lvm + binlog热备，物理备份
实践
前期准备

创建一个数据库
执行以下 SQL，准备好我们的基础数据

-- ------------------------------ 创建一个表-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ------------------------------ 插入基础数据-- ----------------------------INSERT INTO `user` VALUES ('1', '123');INSERT INTO `user` VALUES ('2', '456');?? 使用 mysqldump+binlog 备份
mysqldump 其实是一个 mysql 的一个命令行。binlog 是一个二进制格式的文件,用于记录用户对数据库更新的 SQL 语句信息,例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，对查询等操作并不会记录。
场景模拟

在基础数据下，先做一个全量备份
模拟新增数据操作，增加新数据
然后使用 binlog 做一个增量备份
模拟数据库误操作，将数据表删除
关闭二进制日志，然后恢复全量备份，备份完后开启二进制日志
通过增量备份恢复数据
检查恢复情况

根据场景模拟开始之前，我们需要确认 mysqldump 是否开启。在 SQL 命令行模式下检查是否开启：
// Off 关闭；On 开启show variables like 'log_bin';如果没开启，我们打开并编辑 /etc/my.cnf
log-bin=/root/mysql/bin-log/bin-log-fileexpire-logs-days = 14max-binlog-size = 500Mserver-id = 1保存后重启，再次检查是否开启
第一步
检查目前的 binlog 备份状态，便于
mysql -e 'SHOW MASTER STATUS'结果
+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000000 | 45 | | |+------------------+----------+--------------+------------------+Position 代表着已经被备份数据的位置，我们需要记住便于接下来从这个位置恢复。
使用 mysqldump 进行全量备份
mysqldump --all-databases --lock-all-tables > user_backerup.sql第二步
模拟前端新增操作，代表着目前的数据已经发生了变化
INSERT INTO `user` VALUES ('3', '456');第三步
我们再次查看目前的增量备份文件是多少
show master status假设结果是
+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000000 | 80 | | |+------------------+----------+--------------+------------------+使用 binlog 进行增量备份，在 sql 命令执行 flush logs 后，会在你之前设的 logbin 文件夹下多一份文件 mysql-bin.000001，那么这份就是增量备份。
第四步
我们可以数据库误操作，例如说不小心删了表，或者删除了一些表数据。我这里通过删表作为误操作
drop table user;再检查是否真的删除了
show tables;第五步
因为现在我们已经误操作了，我们需要进行全量备份，然后再增量备份。
关闭二进制日志
SET sql_log_bin=OFF;然后执行全量备份文件
mysql -uroot -p user < user_backerup.sql执行完后再次开启二进制日志
SET sql_log_bin=ON;第六步
这时候，我们应该想到了，还差增量备份的数据。就能返回到了误操作的前面。
所以我们使用 mysqlbinlog 命令执行增量备份文件
mysqlbinlog --start-position=45 --stop-position=80 mysql-bin.000001 | mysql user第七步
接下来就是检查的情况了
【3种mysql备份恢复方案优劣对比】show tables;?? 使用 xtrabackup 备份
xtrabackup 是一款开源的免费数据库热备份软件，实现非阻塞备份 InnoDB 引擎数据库，但是对于 MyISAM 还是需要加表锁备份。
下面是 xtrabackup 的优点