mysql 备份 开源_云计算-开源数据库-备份

关于备份：

备份原因；怕丢，怕被误删。

备份目标：数据的一致性，服务的可用性。

备份技术：物理备份/冷备份

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

tar,cp,scp

拷贝数据， 优点快，缺点服务停止。

逻辑备份/热备份

备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL)，适用于中小型数据库。

mysqldump,mydumper

效率相对较低

备份种类： 完全备份

增量备份

连续回复

特点：因每次仅备份自上一次备份(注意是上一次，不是第一次)以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长。

差异备份

跳跃恢复

特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后 一次的差异版本(包含所有的差异)，恢复速度介于完整备份和增量备份之间。

percona-xtrabackup

官方下载软件包

安装：

安装yum仓库。安装percona需要的mysql包

mysql官方源

yum工具

yum install -y yum-utils

查看mysql可用版本

yum repolist all | grep mysql

禁用80

yum-config-manager --disable mysql80-community

启用57

yum-config-manager --enable mysql57-community

yum install mysql-community-libs-compat -y

YUM安装percona-xtrabackup

yum -y install percona-xtrabackup-24.x86_64

安装percona

本地RPM

查询安装结果

rpm -ql percona-xtrabackup-24

准备备份：1-1 完全备份流程

innobackupex --user=root --password='QianFeng@123' /xtrabackup/full

连接数据库，开始备份。

请注意备份结果是否完成

xtrabackup: Transaction log of lsn (74182721) to (74182730) was copied.

171127 15:04:12 completed OK!

ls /xtrabackup/full/

查看备份目录。数据库，配置文件，日志文件

ls /xtrabackup/full/2017-08-01_00-00-02/

观看二进制日志位置

cat /xtrabackup/full/2017-08-01_00-00-18/xtrabackup_binlog_info

1-2 完全恢复流程

停止数据库

systemctl stop mysqld

清理环境(模拟损坏)

rm -rf /var/lib/mysql/*

rm -rf /var/log/mysqld.log

生成回滚日志

innobackupex --apply-log /xtrabackup/full/2017-08-01_00-00-18/

指定备份点

恢复文件

innobackupex --copy-back /xtrabackup/full/2017-08-01_00-00-18/

登陆验证

ls /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql

systemctl start mysqld

mysql -uroot -p'QianFeng@123'

2-1 增量备份流程

准备工作

使用指定的数据库

create database testdb;

use testdb;

create table test(id int);

insert into test values (1);

select * from test;

mysql> select * from test;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

完整备份：周一

rm -rf /xtrabackup/*

date 09010000

时间建议靠后设置。

innobackupex --user=root --password='QianFeng@123' /xtrabackup

ll /xtrabackup/

2017-09-01_00-00-04

请问数据库中的记录是多少？

1

增量备份：周二

date 09020000

更新时间

mysql -uroot -p'QianFeng@123' -e 'insert into testdb.test values (2)'

[root@localhost ~]# mysql -uroot -p'QianFeng@123' -e 'select * from testdb.test'

innobackupex --user=root --password='QianFeng@123'

--incremental /xtrabackup/

--incremental-basedir=/xtrabackup/2017-09-01_00-00-04

basedir基于周一的备份。

会生成一个今天的。

ls /xtrabackup/

2017-09-01_00-00-04

2017-09-02_00-00-58

增量备份：周三

date 09030000

mysql -uroot -p'QianFeng@123' -e 'insert into testdb.test values (3)'

innobackupex --user=root --password='QianFeng@123'

--incremental /xtrabackup/

--incremental-basedir=/xtrabackup/2017-09-02_00-00-26

basedir基于周2的备份。

会生成一个今天的。

ls /xtrabackup/

2017-09-01_00-00-04

2017-09-02_00-00-58

2017-09-03_00-00-36

周四。。周五。。周六

2-2 增量恢复流程

停止数据库

systemctl stop mysqld

清理环境

rm -rf /var/lib/mysql/*

周一

innobackupex --apply-log --redo-only /xtrabackup/2017-09-01_00-00-04

回滚合并

周二

innobackupex --apply-log --redo-only /xtrabackup/2017-09-01_00-00-04

--incremental-dir=/xtrabackup/2017-09-02_00-00-26

恢复

innobackupex --copy-back /xtrabackup/2017-09-01_00-00-06

chown -R mysql.mysql /var/lib/mysql

systemctl start mysqld

登陆查询记录。请思考记录是第几天的。

想回复3，怎么办

systemctl stop mysqld

innobackupex --apply-log --redo-only /xtrabackup/2017-09-01_00-00-04

--incremental-dir=/xtrabackup/2017-09-03_00-00-27

# rm -rf /var/lib/mysql/*

innobackupex --copy-back /xtrabackup/2017-09-01_00-00-04

chown -R mysql.mysql /var/lib/mysql

systemctl start mysqld

mysql -uroot -p'QianFeng@123' -e 'select * from testdb.test'

请思考，如何一次性把数据，周一，周二，周三恢复出来？

mysqldump + binlog

优势

1 自动记录日志position位置。

(show master status\G;)

2 可用性，一致性

锁表机制。

语法

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

参数说明

库的范围

-A, --all-databases所有库

school数据库名

school stu_info t1是指school数据库的表stu_info、t1

-B, --databases bbs test mysql多个数据库

--single-transaction #InnoDB 一致性 服务可用性

--master-data=1|2#该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释

高级选项

--opt #同时启动各种高级选项

-R, --routines #备份存储过程和存储函数

-F, --flush-logs#备份之前刷新日志，截断日志。备份之后新binlog。

--triggers#备份触发器

帮助

mysqldump --help

备份实战

请准备两套root密码

密码1

QianFeng@123

配置到当前数据库中。

密码2

QianFeng@1234

备用

准备库1

注意

请清理掉之前的test库。实验后半部分，需要继续创建test库，避免混淆。

testdb1.t1

mysql> select * from testdb1.t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

执行备份

[root@localhost ~]# mysqldump -p'QianFeng@123' \

--all-databases --single-transaction \

--master-data=2 \

--flush-logs \

> /backup/`date +%F-%H`-mysql-all.sql

master-data=2 注释掉日志记录

观察备份细节

vim /backup/2016-11-25-14-mysql-all.sql

LOCK TABLES `user` WRITE;

观察各种锁机制，用来保证数据一致性

22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154;

二进制日志截断位置。第22行

业务正常推进...

备份后的，数据变更行为

在testdb2.t2 中插入数据1，2,3；

创testdb3库

切断二进制日志(重启数据库)

在testdb2.t2 中插入数据4；

删testdb3库

在testdb2.t2 中插入数据5；

mysql> select * from testdb2.t2;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| testdb1 |

| testdb2 |

+--------------------+

12 rows in set (0.00 sec)

mysql>

恢复实战

1 备份二进制日志文件

cp /var/lib/mysql/*bin* ~

2. 停止数据库

systemctl stop mysqld

3. 清理环境

rm -rf /var/lib/mysql/*

4.启动数据库

systemctl start mysqld

grep 'password' /var/log/mysqld.log

找密码，再改一下密码。请使用密码2

Kyzw;u2dq

mysqladmin -uroot -p'Kyzw;u2dq

注意

语法要求password后面有空格。

5.mysql恢复数据

mysql -p'QianFeng@1234' < /backup/2016-12-08-04-mysql-all.sql

mysql -p'QianFeng@1234' -e 'flush privileges'

请使用备份时密码

登陆并验证数据恢复结果。请思考恢复的数据库有几个？

1个

6.二进制日志恢复

观察二进制截取记录

vim /backup/2016-11-25-14-mysql-all.sql

CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010', MASTER_LOG_POS=154;

mysqlbinlog localhost-bin.000002 localhost-bin.000003

--start-position=154 | mysql -p'QianFeng@123'

注意后续有多少日志，要跟多少日志名字。

7.观察数据

请思考数据是否恢复完整。

是完整的。

8.请思考误删除的问题

如何保留testdb3库

[root@localhost ~]#mysqlbinlog localhost-bin.00004 ......005.....006...... > 1.txt

删除1.txt中不需要的at(比如test3库是误操作)

cat 1.txt | mysql -p'QianFeng@123'

课后题：是否可以通过mysqlbinlog start 和stop 命令解决。

9.关于数据恢复时的多余日志。

方法一

原因

每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。

mysql> set sql_log_bin =0

再mysql> source /backup/*.sql

观察

二进制日志并没有发生变化。

方法2

在备份文件中，加入关闭二进制日志。