mysql备份

关于备份

为保持数据的一致性与服务的可用性所以我们需要用到备份
以防止数据的丢失或者误操作
备份分为冷备份与热备份

物理备份/冷备份
直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MYSQL
常用方法为tar（压缩）cp（复制）scp（远程文件拷贝）

逻辑备份/热备份
备份的是建表，建库，插入等操作所执行SQL语句（DDL DML DCL）。适用于中小型数据库
常用方法为 mysqldump,musumper.percona-xtrabackuo(热备软件)

备份种类

完全备份：把数据库内的文件一次性全部备份
增量备份：仅备份自上一次备份以来有变化的文件。备份体积小，速度快但恢复的时候需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长
差异备份：占用空间比增量备份大，比完整备份小。恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间

使用percona-xtrabackup备份与恢复

软件安装
需在官网获得软件包 https://www,percona.com/

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 -y mysql-community-libs-compat

安装YUM仓库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm(过程会很慢，等待时间较长。有条件的话可以使用云服务器把站点设置在国外下载以后再传到本地)

YUM安装percona-xtrabackup
yum -y instart percona-xtrabackup-24.x86_64

查询安装结果
rpm -ql percona-xtrabackup-24

完全备份流程
备份
innobackupex --user=root --password=‘QianFeng@123’ /xtrabackup/full （请注意备份结果是否完成）
查看备份目录
ls /xtrabackup/full (查看备份目录，数据库，配置文件，日志文件)
ls /xtrabackuo/full/2020-08-28_00-00-01
观看二进制日志位置
cat /xtrabackup/full/2020-08-28_00-00-01/xtrabackup_binlog_info

完全恢复流程
停止数据库
systemctl stop mysqld
清理环境（模拟损坏）
rm -rf /var/lib/mysql/*
rm -rf /var/log/mysqld.log
生成回滚日志
innobackupex --apply-log/xtrabackup/full/2020-08-28_00-00-01
恢复文件
innobackupex --copy-back/xtrabackup/full/2020-08-28_00-00-01
登录验证
ls /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld
mysql -uroot -p’数据库密码’

增量备份流程
​提前准备一些库或者表里面插入内容
create database testdb;
use testdb;
create table test(id int)
insert into test values(1);
完整备份：周一
更改系统时间 尽量往后设置，不然会报错
date 09010000
innobackupex --user=root --password=‘密码’ /xtrabackup
增量备份：周二
更新时间
date 09020000
把提前准备好的testdb库test表里插入一些内容
mysql -uroot -p’密码’ -e ‘inset into testdb.test values(2)’
besedir基于周一的备份会生成一个今天的备份文件
innobackupex --user=root --password=‘密码’
–incremetal /xtrabackup
–incremental-basedir=/xtrabackup/2020-09-01_00-00-04
查看备份目录
ls /xtrabackup/
增量备份：周三
在表里再插入一些内容
mysql -uroot -p’密码’ -e insert into testdb.test values(3);
besedir基于周二的备份会生成一个今天的备份文件
iniobackupex --user=root --password=‘密码’
–incremental /xtrabackup/
–incremental-basedir=/xtrabackup/2020-09-02_00-00-26
查看备份目录
ls /xtrabackup/
增量备份周四。。周五。。周六以此类推

增量恢复流程
​停止数据库
systemctl stop mysqld
清理环境:只为模拟数据损坏或者丢失
rm -rf /var/lib/mysql/*
周一
innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-00-04（回滚合并）
周二
innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-00-04
–incremental-dir=/xtrabackup/2020-09-02_00-00-26
恢复到周二
innobackupex --copy-back /xtrabackup/2020-09-01_00-00-06
给恢复过的文件授权属主属组均为mysql
chown -R mysql.mysql /var/lib/mysql
启动数据库
systemctl start mysqld
若想恢复到周三以后，以周三为例
停止数据库
systemctl stop mysqld
回滚合并
innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-00-04
–incremental-dir=/xtrabackup/2020-09-03_00-00-27
清理环境
rm -rf /var/lib/mysql/*
恢复备份
innobakupex --copy-back /xtrabackup/2017-09-01_00-00-04
给恢复过的文件授权属主属组均为mysql
chown -R mysql.mysql /var/lib/mysql
启动数据库
systemctl start mysqld
查看恢复后的数据
mysql -uroot -p’密码’ -e’select * from testdb.test’

使用 mysqldump +binlog 备份与恢复

优势：
自动记录日志position位置（show master status\G;)
可用性，一致性。（锁表机制）

语法：
​mysqldump -h 服务器 -u用户名 -p‘密码’ 数据库名 > 备份文件.sql
参数说明：库的范围
-A —all-databases //所有库
school //数据库名
school stu_info t1 //是指school数据库的表stu_info、t1
-B —databases bbs test mysql多个数据库
–single-transaction //一致性，服务可用性
–master-data=1|2 //该选项将会记录binlog日志位置与文件名并追加到文件中或添加注释
高级选项：
–opt //同时启动各种高级选项
-R --routines //备份存储过程和存储函数
-F --flush-logs //备份之前刷新日志，截断日志。备份之后新binlog
–triggers //备份触发器

备份
参照前面部分准备库以实验使用
执行备份
mysqldump -p’密码’
–all-databases --single-transaction
–master-data=2 \ (注释掉日志记录)
–flush-logs
。> /backup/‘date+%F-%H’ -mysql-all.sql (格式原因本行开头增加一个符号 实际操作不要加入句号)
观察备份细节
vim /backup/2020-09-01-14-mysql-all.sql
观察各种锁机制，用来保证数据一致性
LOCK TABLES ‘user’ WRITE
二进制日志截断位置，第22行
22 CHANGE MASTER TO MASTER_LOG_FILE=‘localhost-bin.000004’,
MASTER_LOG_POS=154;
备份后的数据变更行为（记清自己前后插入的内容）
在刚才准备的库中插入数据创新库。
切断二进制日志（重启数据库）
在库中插入数据并删掉某个库

恢复
备份二进制日志文件
cp /var/lib/mysql/bin ~
停止数据库
systemctl stop mysqld
清理环境
rm -rf /var/lib/mysql/*
启动数据库
systemctl start mysqld
找到临时密码（刚才把数据清掉，默认变成了刚建库的样子）
grep ‘password’ /var/log/mysqld.log
恢复数据
mysql -p’密码’< /backup/2020-09-01-14-mysql-all.sql
刷新数据库
mysql -p’密码’-e ‘flush privileges’ （临时密码在恢复数据时就已失效，使用的是原来库的密码，新库密码被覆盖）

二进制日志恢复
观察二进制截取记录，每次在输入库中的输入都可以在这里显示
vim /backup/2020-09-01-14-mysql-all.ql
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’密码’
如何保留刚才删掉的库
把后面日志内容都合并到文件中方便查看
mysqlbinlog localhost-bin.000004 …000005 …000006…>1.txt
找到不需要的at（比如刚才删除某个库的操作）
删除以后把剩下的内容交给mysql读取即可完成恢复
cat 1.txt | mysql -p’密码’

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

每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。
方法1
mysql> set_sql_log_bin=0
方法2
在备份文件中，加入关闭二进制日志。