开源数据库MySQL DBA运维实战 第5章 备份
1、关于备份:丢,删
2、备份的目标:
1).数据的一致性
2).服务的可用性
3、备份技术:
物理备份 ---冷备份
直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制
Tar,cp,scp
拷贝数据,优点快,缺点服务停止
逻辑备份 ---热备份
备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL),适用于中小型数据库。
Mysqldump,mysqldumper
效率相对较低
4、备份种类
完全备份
增量备份
连续回复
特点:因每次仅备份自上一次备份(注意是上一次,不是第一次)以来有变化的文件,所 以备份体积小,备份速度快,但是恢复的时候,需要按备份时间顺序,逐个备份版本进行恢复,恢复时间长。
差异备份
跳跃恢复
特点:占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后 一次的差异版本(包含所有的差异),恢复速度介于完整备份和增量备份之间。
一、实战案例 ---percona-xtrabackup
Logo
简介:
它是开源免费的支持MySQL 数据库热备份的软件,它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份;
为mysql做增量备份;在mysql服务器之间做在线表迁移;使创建replication更加容易;备份mysql而不增加服务器的负载。
percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询,还发布了mysql的分支版本--percona Server。并围绕
percona Server还发布了一系列的mysql工具。
获得的软件包-----官方站点:https://www.percona.com/
1、安装YUM仓库
安装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
禁用80:yum-config-manager --disable mysql80-community
启用57: yum-config-manager --enable mysql57-community
yum install mysql-community-libs-compat -y
2、YUM安装percona-xtrabackup
yum -y install percona-xtrabackup-24.x86_6
3、查询安装结果
rpm -ql percona-xtrabackup-24
4、完全备份流程
innobackupex --user=root --password='[email protected]' /xtrabackup/full ---- 连接数据库,开始备份
ls /xtrabackup/full/ ---查看备份目录。数据库,配置文件,日志文件
ls /xtrabackup/full/2017-08-01_00-00-02/
cat /xtrabackup/full/2017-08-01_00-00-18/xtrabackup_binlog_info ----观看二进制日志位置
4-1、完全恢复流程
停止数据库
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 start mysqld
mysql -uroot -p'[email protected]'
5-1、增量备份流程
1)准备工作
create database testdb;
use testdb;
create table test(id int);
insert into test values (1);
- 完整备份:周一
rm -rf /xtrabackup/* ---清理需备份文件下的文件
date 09010000 -----时间可以设置的靠后一些
innobackupex --user=root --password='[email protected]' /xtrabackup
ll /xtrabackup/ ---查看备份的文件
- 增量备份:周三
date 09020000 ---更新时间
mysql -uroot -p'[email protected]' -e 'insert into testdb.test values (2)' ---插入数据
innobackupex --user=root --password='[email protected]'
--incremental /xtrabackup/
--incremental-basedir=/xtrabackup/2020-09-01_00-00-04 -----basedir基于周一的备份。会生成一个今天的。
ls /xtrabackup/ ---查看备份的文件
4)增量备份:周三
date 09030000 ---更新时间
mysql -uroot -p'[email protected]' -e 'insert into testdb.test values (3)'
innobackupex --user=root --password='[email protected]'
--incremental /xtrabackup/
--incremental-basedir=/xtrabackup/2020-09-02_00-00-26 ---basedir基于周2的备份。
会生成一个今天的。
以此类推,周四、周五、周六、周日
5-2 增量恢复流程
停止数据库 --- 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
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld
周三:
innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-00-04
--incremental-dir=/xtrabackup/2020-09-03_00-00-27
恢复:
innobackupex --copy-back /xtrabackup/2017-09-01_00-00-04
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld
二、实战案例2 -----mysqldump + binlog
优势:
1)自动记录日志position位置 ---show master status\G;
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 #备份触发器
- 备份实战
1)执行备份
[[email protected] ~]# mysqldump -p'[email protected]' \
--all-databases --single-transaction \
--master-data=2 \
--flush-logs \
> /backup/`date +%F-%H`-mysql-all.sql -----master-data=2 注释掉日志记录
2)观察备份细节
vim /backup/2020-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行
2.恢复实战
1)备份二进制日志文
cp /var/lib/mysql/*bin* ~
2)停止数据库 ----systemctl stop mysql
3)清理环境 ----rm -rf /var/lib/mysql/*
4)启动数据库 --systemctl start mysqld
5) grep 'password' /var/log/mysqld.log
6) mysql恢复数据
mysql -p'[email protected]' < /backup/2020-12-08-04-mysql-all.sql
mysql -p'[email protected]' -e 'flush privileges' -请使用备份时密码
7)二进制日志恢复
mysqlbinlog localhost-bin.000002 localhost-bin.000003
--start-position=154 | mysql -p'[email protected]'