开源数据库MYSQL DBA运维实战 第三章 备份

一、关于备份

1.备份原因

丢/删

2.备份目标

1.数据的一致性

2.服务的可用性

3.备份技术

3.1物理备份/冷备份

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

tar,cp,scp

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

3.2逻辑备份/热备份

备份的是建表，建库，插入等操作所执行SQL语句（DDL,DML,DCL）,

mysqldump,mydumper

效率相对较低

4.备份方式

4.1完全备份

4.2增量备份

4.3差异备份

二、实战案例1

percona-xtrabackup

简介

它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份;为mysql做增量备份:在mysql服务器之间做在线表迁移:使创建replication更加容易;备份mysql而不增加服务器的负载。
percona是一家老牌的mysql技术咨询公司。它不仅提供mysal的技术支持、培训、咨询，还发布了mysql的分支版本--percona Server。并围绕perconaServer还发布了一系列的mysql工具。

获得软件包

官网站点：https://www.percona.com/

选择版本

安装

安装YUM仓库

安装percona需要的mysql包

安装precona的yum仓库

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

YUM安装percona-xtrabackup

yum install -y percona-xtrabackup-24.x86_64

查询安装结果

yum list | grep percona-xtrabackup

1.完全备份流程

innobackupex --user=root --password='Root123@' /xtrabackup/full

ls /xtrabackup/full 查看备份目录，数据库，配置文件，日志文件

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

观察二进制日志位置

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

2.完全恢复流程

停止数据库

systemctl stop mysql

清理环境（模拟损坏）

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'Root123@'

3.增量备份流程

准备工作

完整备份：周一

rm -rf /xtrabackup/*

date 09010000

innobackupex --user=root --password='Root123@' /xtrabackup

ll /xtrabackup/ 2017-09-01 00-00-04

请问数据库中的增量是多少？ 1

增量备份：周二

date 09020000 更新时间

mysql -uroot -p'Root123@' -e 'insert into testdb.test value(2)'

innobackupex --user=root --password='Root123@' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2017-09-01 00-00-04

basedir基于周一的备份，会生成一个今天的

ls /xtrabackup/

增量备份：周三

date 09030000 更新时间

mysql -uroot -p'Root123@' -e 'insert into testdb.test value(3)'

innobackupex --user=root --password='Root123@' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2017-09-02 00-00-04

basedir基于周2的备份，会生成一个今天的

ls /xtrabackup/

周四。。周五。。周六

4.增量恢复流程

停止数据库

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-04

恢复

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 mysq.mysql /var/lib/mysql
systemctl start mysqld
mysql -uroot -p'Root123@' -e 'select * from testdb.test'

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

5.差异备份流程

rm -rf /xtrabackup/*

1.完整备份

周一

date 10010000

create database testdb;

use testdb;

create table test(id int);

insert into test2 values(1);

select * from test2;

innobackupex --user=root --password='Root123@' /xtrabackup

ls /xtrabackup 看是否备份成功

2.差异备份

周二

date 10020000

mysql -uroot -p'Root123@' -e 'insert into testdb.test2 value(2)'

innobackupex --user=root --password='Root123@' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2017-10-01 00-01-12

周三

date 10030000

mysql -uroot -p'Root123@' -e 'insert into testdb.test2 value(3)'

innobackupex --user=root --password='Root123@' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2017-10-01 00-01-12

周四

date 10040000

mysql -uroot -p'Root123@' -e 'insert into testdb.test2 value(4)'

innobackupex --user=root --password='Root123@' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2017-10-01 00-01-12

6.差异恢复流程

停止数据库

systemctl stop mysqld

清理环境

rm -rf /var/lib/mysql/*

重演回滚redo log（周一，某此差异）------->恢复数据

innobackupex --apply-log --redo-only /xtrabackup/2017-10-01_00-01-12

innobackupex --apply-log --redo-only /xtrabackup/2017-10-01_00-01-12

--incremental-dir=/xtrabackup/2017-10-04_00-00-29

innobackupex --copy-back /xtrabackup/2017-10-01_00-01-12

chown -R mysql.mysql /var/lib/mysql 修改权限

systemctl start mysqld 启动mysqld

通过binlog增量恢复（略）

三、实战案例2

mysqldump+binlog

1.优势

1.自动记录日子position位置

2.可用性，一致性

2.语法

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql -h不写是对本机

-single-transaction innodb 一致性，服务可用性，不关机的备份

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

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

帮助

mysqldump --help

3.备份实战

3.1准备两套root密码

密码1：Root123@ 配置到当前数据库中

密码2：Root1234@ 备用

3.2准备库1

create database testdb1;

use testdb;

create table t1(id int);

insert into t1 values(1)，（2），（3）;

select * from t1;

3.3执行备份

mysqldump -p'Root123@' --all-databases --single-transaction --master-data=2 --flush-logs > /backup/date +%F-%H -mysql-all.sql

3.4观察备份细节

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

LOCAK TABLES user WRITE mysqldump的锁机制，来保证数据一致性

22行 CHANGE MASTER TO MASTER LOG FILE=ocalhost-bin.000004MASTER LOG POS=154:

3.5备份后的，数据变更行为

4.恢复实战

4.1备份二进制日志文件

cp /var/lib/mysql/bin ~

4.2停止数据库

systemctl stop mysqld

4.3清理环境

rm -rf /var/lib/mysql/*

4.4启动数据库

systemctl start mysqld

grep 'password' /var/log/mysqld.log 找密码，再改一下密码，使用密码2

mysqladmin -uroot -p'd-tlbwIgP3e2' password "Root1234@" password后面有空格

4.5mysql恢复数据

mysql -p'Root1234@' < /backup/2016-11-25-14-mysql-all.sql

mysql -p'Root1234@' -e 'flush privileges' 一重启之后变成了密码1

登录并验证数据恢复结果，请思考恢复的数据库有几个？ 1个

4.6二进制日志恢复

观察二进制截取记录

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

第22行 CHANGE MASTER TO MASTER LOG FILE=ocalhost-bin.000004MASTER LOG POS=154:

mysqlbinlog localhost-bin.000002 localhost-bin000003-start-position=154mysql-p'Root123@'

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

4.7观察数据

恢复完整

4.8请思考误删除的问题.

如何保留误删的testdb3库

[root@localhost -]#mysglbinlog localhost-bin.00004.....005.....006...... > 1.txt
删除1.txt中不需要的at(比如test3库是误操作)
cat 1.txt I mysgl -p'Root123@'

4.9关于数据恢复时的多余日志

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

四、实战案例3---记录的导出和导入

1.记录导出

注意目录权限

vim /etc/my.cnf

secure-file-priv=/backup mysql不信任该目录（要重启mysql程序）

chown mysql.mysql /backup mysql用户没有权限

SELECT...INTO OUTFILE 导出文本文件

mysql> SELECT * FROM testdb1.t1 INTO OUTFILE '/backup/testdb1.t1.txt';

mysql命令导出文本文件

mysql-uroot -p'QianFeng@123' -e 'select * from testdb1.t1' > /backup/testdb1.t2.txt
mysql-u root -p'QianFeng@123' --xml-e 'select * from testdb1.t1' > /backup/testdb1.t3.txt

mysql-u root -p'QianFeng@123' --html -e 'select * from testdb1.t1' > /backup/index.html

2.LOAD DATA INFILE导入文本文件.

注意目录权限

vim /etc/my.cnf

secure-file-priv=/backup mysql不信任该目录（要重启mysql程序）

chown mysql.mysql /backup mysql用户没有权限

删除表的内容，标的到处和导入只备份表记录，不会备份表结构，因此需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据

delete from testdb1.t1

LOAD DATA INFILE '/backup/testdb1.t1.txt' INTO TABLE testdb1.t1;

select * from testdb1.t1;