MySQL运维实战备份

备份

备份原因：容易丢、误删
备份目标：数据的一致性、服务的可用性
备份技术

1. 物理备份（冷备份）
   直接复制数据库文件，使用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
   Tar，cp，scp 解压 拷贝 远程拷贝
   拷贝数据，
   优点：快、
   缺点:服务停止
2. 逻辑备份（热备份）
   备份的是建表、建库、插入等操作所执行的SQL语句(DDL、DML、DCL)，适用于中小型数据库
   Mysqldump，mydumper，
   效率相对较低

• 备份种类
  完全备份
  增量备份：连续恢复
  特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长。
  
  差异备份 跳跃恢复
  特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后 一次的差异版本(包含所有的差异)，恢复速度介于完整备份和增量备份之间。
  
  

pertcona-xtrabackup

现在主流开源备份软件：Percona-xtrabackup

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

这里我选择了centos7版本

• 安装方式
  安装percona可能会有冲突所以需要MySQL包

MySQL官方源
yum install -y https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
yum工具：
yum install -y yum-utils
查看MySQL可用版本：
yum repolist all | grep mysql
Yum –y install mysql-community-libs-compat
安装percona包
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum安装 percona-xtrabackup
yum -y install percona-xtrabackup-24.x86_64
本地RPM ：percona-xtrabackup24.rpm.zip
查询安装结果
Rpm –ql percona-xtraback-24

完整备份

1. 连接数据库，备份

Innobackupex  --user=root –password=‘ZhongGuo@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-02/xtrabackup_binlog_info
注意：查看二进制日志是否开启
开启二进制日志
Vim /etc/my.cnf
[mysql]
Server-id=1
Log_bin

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  restart mysqld   重启服务
Mysql –uroot –p’ZhongGuo@123’
Select * from test.t1; 查询是否一致

增量备份

1.准备工作

创建test库：create  database  test；
使用text库：use  test；
创建t1表：create table t1（id int）；
插入数据：insert  into t1  values（1）；
查看表内容：select * from t1;
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

完整备份：周一

Date  00910000
Innobackupex  --user=root  --password=‘ZhongGuo@123’  /xtrabackup
Ll  /xtrabackup/    2017-09-01_00-00-04  查看备份  数据库记录 ‘ 1 ’

增量备份：周二

Date 09020000
Mysql –uroot –p‘ZhongGuo@123’ –e ‘insert  into test.t1 values(2);
不进数据库执行插入数据
Innobackupex  --user=root  --password=‘ZhongGuo@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  基于周一的备份生成一个周二的备份
以下以此类推

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  mysql   

Mysqldump与binlog

• 特点：
  自动记录日志position位置 （show master status）
  可用性、一致性 --锁表机制
• 语法
  Mysqldump -h 服务器 -u用户 数据库名 >备份文件.Sql
• 库的范围
  -A，-all-databases 所有库
  -B，–databases +库名 多个数据库
  –single-transaction lnnodb 一致性 服务可用性
  –master-data=1/2 该选项将会记录binlog的日志位置与文件名并追加到文件中，活态添加注释
• 高级选项
  –opt 同时启动各种高级选项
  -R ，–flush-logs 备份之前刷新日志，截断日志，备份之后新binlog
  –triggers 备份触发器
  帮助：mysqldump –help

1. 实战运行

准备
设置两套root密码
123 配置到当前数据库中
1234 备用
创建库：create databases test；
创建表：create table test.t1（id int）;
插入数据：insert into test.t1 values（1）；（2）（3）

• 执行备份：

Mkdir  /backup/  创建backup文件夹
mysqldump –p’123’ 
--all-databases   所有库
--single-transaction  保持数据可用性不停机备份
--master-data=2    二进制日志位置
--fiush-logs  截断日志，做日志轮转
>/backup/`date+%F-%H` -mysql-all.sql

• 查看备份

Ls –lh  /backup/
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；
最后重启服务

2. 恢复

备份二进制日志文件：cp  /var/lib/mysql/*bin* ~
停止数据库：systemctl  stop  mysqld
清除环境：rm  -rf  /var/lib/mysql/*
启动数据库：systemctl  start  mysqld
Grep ‘password’  /var/log/mysql.log
找到密码 使用密码1234
Mysqladmin  -uroot  -p‘Kyzw;u2dq<mh’  password ‘1234’
Mysql恢复数据
Mysql –p‘1234’  -e ‘flush  privileges’
登录验证恢复结果
观察二进制：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 –uroot  -p’123’
后面几个日志   跟多少日志名字
观察数据是否完全恢复
误删
如果需要保留test库
Mysqlbinlog  localhost-bin.00002 >1.txt
Mysqlbinlog start    MySQL日志起始位置找到数据
删除 1.txt中不需要的at
再查看数据库中是否有数据