MySQL数据库--集群

开源数据库MySQL DBA运维实战-备份
一.关于备份
1.备份原因
2.备份目标
3.备份技术
4.备份方式
二.实战案例1
percona-xtrabackup
1.简介
2.获取安装包
3.安装
4.完全备份流程
5.完全恢复流程
6.增量备份流程
7.增量恢复流程
三.实战案例2
mysqldump + binlog
1.优势
2.语法
3.备份实战
4.恢复实战
一.关于备份
1.备份原因
丢失
删除
2.备份目标
1.数据的一致性
2.服务的可用性
3.备份技术
物理备份/冷备份
1.直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
2.tar,cp,scp
3.拷贝数据， 优点快，缺点服务停止。
逻辑备份/热备份
1.备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
2.mysqldump,mydumper
3.效率相对较低
4.备份方式
完全备份

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

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

二.实战案例1
percona-xtrabackup
1.简介

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

3.安装

安装YUM仓库
#安装percona需要的mysql包
#mysql官方源

[root@localhost ~]# yum install -y  https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

#yum工具

[root@localhost ~]# yum install -y yum-utils

#查看mysql可用版本

[root@localhost ~]# yum repolist all | grep mysql

#禁用80版本

[root@localhost ~]# yum-config-manager --disable mysql80-community

#开启57版本

[root@localhost ~]# yum-config-manager --enable mysql57-community

[root@localhost ~]# yum  install mysql-community-libs-compat -y

[root@localhost ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

YUM安装percona-xtrabackup
[root@localhost ~]# yum -y install percona-xtrabackup-80.x86_64
推荐本地rpm安装
#上传rpm压缩包，解压，直接在解压目录里面安装

[root@localhost ~]# [root@localhost ~]# yum install percona-xtrabackup24.rpm/*

查询安装结果

[root@localhost ~]# rpm -ql percona-xtrabackup-24

4.完全备份流程

#连接数据库，开始备份

[root@localhost ~]# innobackupex --user=root --password='Q@123' /xtrabackup/full
#注意completed OK！完成标志

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

[root@localhost ~]# ls /xtrabackup/full/
[root@localhost ~]# ls /xtrabackup/full/2020-08-28_20-00-00/

#观看二进制日志位置

[root@localhost ~]# cat /xtrabackup/full/2020-08-28_20-00-00/xtrabackup_binlog_info 

5.完全恢复流程

#停止数据库（实际环境下正常情况不能停止）

[root@localhost ~]# systemctl stop mysqld

#清理环境（模拟损坏）

[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# rm -rf /var/log/mysqld.log 

#生成回滚日志

[root@localhost ~]# innobackupex --apply-log /xtrabackup/full/2020-08-28_20-00-00/

#恢复文件

[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2020-08-28_20-00-00/

#登录验证
[root@localhost ~]# ls /var/lib/mysql
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p'Q@123' 

6.增量备份流程

#使用指定数据库
#准备工作

mysql> create database testdb;
mysql> use testdb;
mysql> create table test(id int);
mysql> insert into test values (1);
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

#完整备份-周一

[root@localhost ~]# rm -rf /xtrabackup/*   #清空上次实验备份的数据
[root@localhost ~]# date 09010000   #时间建议靠后设置。
[root@localhost ~]# innobackupex --user=root --password='Q@123' /xtrabackup
[root@localhost ~]# ll /xtrabackup/
2020-09-01_00-03-04

#增量备份-周二

[root@localhost ~]# date 09020000   #更新时间
[root@localhost ~]# mysql -uroot -p'Q@123' -e  'insert into testdb.test values (2)'
[root@localhost ~]# mysql -uroot -p'Q@123' -e 'select * from testdb.test'

[root@localhost ~]# innobackupex --user=root --password='Q@123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2020-09-01_00-03-04
#basedir基于周一的备份，会生成一个今天的。

[root@localhost ~]# ls  /xtrabackup/
2020-09-01_00-03-04  
2020-09-02_00-02-58

#增量备份-周三

[root@localhost ~]# date 09030000
[root@localhost ~]# mysql -uroot -p'Q@123' -e  'insert into testdb.test values (3)'

[root@localhost ~]# innobackupex --user=root --password='Q@123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2020-09-02_00-02-58
#basedir基于周2的备份。会生成一个今天的。

[root@localhost ~]# ls  /xtrabackup/
2020-09-01_00-03-04  
2020-09-02_00-02-58
2020-09-03_00-03-36

#周四--周五--周六，以此类推实验

7.增量恢复流程

#停止数据库

[root@localhost ~]#  systemctl stop mysqld

#清理环境

[root@localhost ~]# rm -rf /var/lib/mysql/*

#周一备份回滚合并日志

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-03-04

#周二备份以只读回滚合并到周一备份回滚合并日志

[root@localhost ~]#  innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-03-04 --incremental-dir=/xtrabackup/2020-09-02_00-02-58

#周三备份以只读回滚合并到周一备份回滚合并日志

[root@localhost ~]#  innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-03-04 --incremental-dir=/xtrabackup/2020-09-03_00-03-36

#恢复数据
[root@localhost ~]# innobackupex --copy-back /xtrabackup/2020-09-01_00-03-04

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql   
#恢复时该目录属主属组为root，要改为mysql不然mysql程序打不开

[root@localhost ~]# systemctl start mysqld

三.实战案例2
mysqldump + binlog
1.优势
1 自动记录日志position位置。（show master status\G;）
2 可用性，一致性，锁表机制。
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

3.备份实战

#准备库1

mysql> create database testdb1;
mysql> create table testdb1.t1 (id int);
mysql> insert into testdb1.t1 values (1);
mysql> insert into testdb1.t1 values (2);
mysql> insert into testdb1.t1 values (3);
mysql> select * from testdb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

#执行备份

[root@localhost ~]# mkdir /backup
[root@localhost ~]# mysqldump -p'Q@123' \   #\表示使回车执行失效，用于换行
--all-databases --single-transaction \   #一致性
--master-data=2 \   #注释掉日志记录
--flush-logs \
> /backup/`date +%F-%H`-mysql-all.sql

#观察备份细节

[root@localhost ~]# vim /backup/2020-08-29-09-msql-all.sql

LOCK TABLES `user` WRITE;   #观察各种锁表机制，用来保证数据一致性
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000005', MASTER_LOG_POS=154;
#二进制日志截断位置，在第22行

#备份后的数据变更行为

##在testdb2.t2中插入数据1，2，3； 创建testdb3库； 重启数据库（切断二进制日志）

mysql> create database testdb2;
mysql> create table testdb2.t2 (id int);
mysql> insert into testdb2.t2 values (1);
mysql> insert into testdb2.t2 values (2);
mysql> insert into testdb2.t2 values (3);
mysql> select * from testdb2.t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> create database testdb3;
[root@localhost ~]# systemctl restart mysqld

##在testdb2.t2中插入数据4； 删除testdb3库； 在testdb2.t2中插入数据5；

mysql> insert into testdb2.t2 values (4);
mysql> drop database testdb3
mysql> show databases；
+--------------------+
| Database           |
+--------------------+
| testdb1            |
| testdb2            |
+--------------------+

4.恢复实战
#1.备份二进制日志文件

[root@localhost ~]# cp /var/lib/mysql/bin ~

#2.停止数据库

[root@localhost ~]# systemctl stop mysqld

#3.清理环境

[root@localhost ~]# rm -rf /var/lib/mysql/*

#4.启动数据库

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# grep 'password' /var/log/mysql.log 
#找到随机密码 jJfQEcyHa9-T 
[root@localhost ~]# mysqladmin -uroot -p'jJfQEcyHa9-T' password '新密码'

#5.mysql数据恢复

[root@localhost ~]# mysql -p'Q@1234' < /backup/2020-08-29-09-msql-all.sql
[root@localhost ~]# mysql -p'Q@1234' -e 'flush privileges'
#改命令一旦执行会把密码恢复到备份前的密码，新密码不能再使用，不记得原密码一般不执行
#登录并验证数据恢复结果

#6.二进制日志恢复

[root@localhost ~]# vim /backup/2020-08-29-09-msql-all.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000005', MASTER_LOG_POS=154;
#二进制日志截断位置，在第22行

[root@localhost ~]# mysqlbinlog localhost-bin.000005 localhost-bin,000006 --start-position=154 | mysql =p'Q@123'
#注意后续有多少日志，就跟多少日志名字

#7.登录数据库观察数据恢复

#8.如果之前testdb3库为误操作删除（完整备份恢复后操作）
#寻找二进制日志文件

[root@localhost ~]# mysqlbinlog  localhost-bin.000005 loclhost-bin.006 > 1.txt
#删除1.txt中不需要的at（比如testdb3库是误操作）
[root@localhost ~]# cat  1.txt | mysql -p'Q@123'
#也可以通过mysqlbinlog start 和stop 命令解决