开源数据库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;