第1章 MySQL备份介绍
1.MySQL备份方式
逻辑备份 mysqldump 备份的是SQL语句
物理备份 Xtrabackup 直接备份数据目录的文件
2.逻辑备份和物理备份区别
mysqldump:
备份出来的文件可以直接查看
如果数据量较大,恢复速度较慢
只能全备
100G以内
Xtrabackup:
直接备份的物理文件,备份速度和恢复速度都很快
备份出来的文件不可阅读
支持全备和增量备份
超过100G
第2章 mysqldump工具使用
1.介绍
数据逻辑备份工具
MySQL 自带的客户端命令
可以实现远程和本地备份
2.参数
#连接参数
-u 用户名
-p 密码
-S 本地socket文件路径
#socket文件解释
1.socket文件如果配置里没有设置,则默认为/tmp/mysql.sock
2.mysql客户端连接的时候,如果不指定,默认也是找/tmp/mysql.sock
3.mysql -uroot -p123如果不接IP,就认为你是链接本地的mysql
4.配置解释:当你使用mysql自己的客户端连接的时候,读取以下标签内的配置
[mysql]
socket=/tmp/mysql.sock
#备份参数
-A 导出所有的库
-B 导出单库或多库
--master-data=2 标记全备的时候位于binlog哪个位置,=2这一行是个注释,固定为22行
--single-transaction 对于InnoDB表,通过快照备份表数据,不锁表备份,可以理解为热备
-R -E --triggers 备份特殊对象使用
第3章 备份命令
1.全备命令
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers > /backup/full_$(date +%F).sql
2.备份单个库或多个库
mysqldump -uroot -p123 -B 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
mysqldump -uroot -p123 -B 库名 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
3.备份单个表或多个表
mysqldump -uroot -p123 库名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
mysqldump -uroot -p123 库名 表名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
4.远程备份
mysqldump -uroot -p123 -h10.0.0.51 -A --master-data=2 --single-transaction -R -E --triggers > /backup/full_$(date +%F).sql
第4章 分库分表备份
1.思路
双层for循环
第一层for循环所有的库
第二层for循环库下所有的表
for ku in $(查询库的语句)
do
for biao in $(查询库里的表)
do
mysqldump -uroot -p123 -h127.0.0.1 $ku $biao --master-data=2 --single-transaction -R -E --triggers > /backup/$ku/$biao.sql
done
done
2.脚本
#!/bin/bash
for ku in $(mysql -N -e 'show databases;'|egrep -v 'mysql|schema|sys')
do
mkdir /backup/$(date +%F)/$ku/ -p
for biao in $(mysql -N -e "show tables from $ku;")
do
mysqldump -h127.0.0.1 $ku $biao --master-data=2 --single-transaction -R -E --triggers > /backup/$(date +%F)/$ku/$biao.sql
done
done
3.注意
#! /bin/bash
ku=$(mysql -N -uroot -p123 -e "show databases;" |egrep -v "information_schema|mysql|performance_schema|sys|logs|mysql3306|zahocheng")
for kuming in ${ku}
do
biao=$(mysql -N -uroot -p123 -e "show tables from ${kuming};")
mkdir /backup/${kuming} -p
for biaoming in ${biao}
do
mysqldump -uroot -p123 ${kuming} ${biaoming} --master-data=2 --single-transaction -R -E --triggers > /backup/${kuming}/${biaoming}.sql
done
done
可以把账号密码写进配置文件里,这样就不会出现命令警告了
vim /etc/my.cnf
[client]
user=root
password=123
第5章 Xtrabackup
1.Xtrabackup介绍
percona公司研发
xtrabackup --> C C++
innobackupex --> perl语言
8.0之前，2.4.x
8.0之后，8.0
物理备份工具，类似于cp文件。
支持：全备和增量备份
2.Xtrabackup安装
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
第6章 Xtrabackup全备数据
1.全量备份命令
#添加socket配置
[client]
user=root
password=123
socket=/tmp/mysql.sock
#全备命令
innobackupex -uroot -p123 -S /tmp/mysql.sock /backup/
#自定义目录名备份
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/full_$(date +%F)
2.相关文件
xtrabackup_binlog_info: 备份时位于binlog位置点
xtrabackup_checkpoints: 备份期间的一些校验信息
--------------------------------------------------
backup_type = full-backuped 全备
from_lsn = 0 增量备份需要关心
to_lsn = 2704642 最近的内存数据落地到磁盘上的LSN号
last_lsn = 2704651 xtrabackup_logfile
--------------------------------------------------
xtrabackup_info: 备份命令的一些信息
xtrabackup_logfile: 备份期间变化的数据,还没有写入到磁盘里
3.恢复命令
第1步: 准备数据
innobackupex --apply-log /backup/2022-01-13_14-51-59
第2步: 恢复数据-任选一个方法即可
方法1): 使用命令复制全备文件到数据目录
innobackupex --copy-back /backup/2022-01-13_14-51-59/
innobackupex --move-back /backup/2022-01-13_14-51-59/
方法2): 修改配置文件,数据目录指向备份文件目录
systemctl start mysqld
方法3): 创建备份目录的软链接到数据目录
mkdir /backup/2022-01-13_14-51-59/logs
touch /backup/2022-01-13_14-51-59/logs/mysql.err
chown -R mysql:mysql /backup/2022-01-13_14-51-59/
ln -s /backup/2022-01-13_14-51-59/ /data/mysql_3306
第3步: 小坑-恢复数据后记得更改权限
1)备份恢复的时候不会把日志目录一起备份，比如错误日志和Binlog日志，恢复完成后需要手动创建
2)恢复后数据目录的用户权限都是root，需要手动更改权限
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld
第7章 Xtrabackup增量备份数据
1.介绍
自带的功能。
每次增量一般是将最近一次备份作为参照物。
自动读取参照物cat xtrabackup_checkpoints中to_lsn值，与当前CKPT的LSN对比，备份变化过page。
备份期间新的数据变化，通过redo自动备份。
恢复数据时，需要把所有需要的增量合并到FULL中。无法通过增量单独恢复数据，依赖与全备。
2.实验步骤
FULL(周日)+inc1(周一)+inc2(周二)+inc3(周三)
第1步: 实验数据准备
create database xbk charset utf8mb4;
show databases；
use xbk
create table full (id int);
show tables；
insert into full values(1),(2),(3);
select * from full；查询表数据
第2步: 模拟周日 23:00 全备
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/full_$(date +%F)
--no-timestamp 不使用系统日期做备份目录的子目录名
第3步: 模拟周一白天数据变化
use xbk
create table inc1 (id int);
insert into inc1 values(1),(2),(3);
第4步: 第一次增量备份
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/full_2022-01-13 /backup/inc1_$(date +%F)
--incremental 增量模式
--incremental-basedir 增量基础目录
第5步: 模拟周二白天数据变化
use xbk
create table inc2 (id int);
insert into inc2 values(1),(2),(3);
第6步: 第二次增量备份
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1_2022-01-13 /backup/inc2_$(date +%F)
第7步: 模拟周三白天数据变化
use xbk
create table inc3 (id int);
insert into inc3 values(1),(2),(3);
第8步: 第三次增量备份
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc2_2022-01-13 /backup/inc3_$(date +%F)
第9步: 模拟周四白天数据变化
use xbk
create table inc4(id int);
insert into inc4 values(1),(2),(3);
commit;
第10步: 周四下午出现数据损坏。如何恢复到误删除之前。
先导出binlog日志
先查看binlog日志
找出当前使用哪个binlog日志的位置
pkill mysqld
binlog日志导出到/tmp目录
rm -rf /data/mysql_3306/*
第11步: 截取binlog位置点
全备-增量1-增量2-增量3-binlog
备份的数据:
全备-增量1-增量2-增量3
binlog起始和终止点
起始点:
cat /backup/inc3_2022-01-13/xtrabackup_binlog_info
mysql-bin.000001 21105729
起始点
[root@db-51 /backup/inc3_2022-01-13]# cat xtrabackup_binlog_info
mysql-bin.000003 2510
终止点:
at 21106154
截取命令:
mysqlbinlog --start-position=21105729 --stop-position=21106154 /opt/logs/mysql-bin.000001 > /tmp/backup.sql
第12步: 处理全备,将临时文件整合到磁盘里
innobackupex --apply-log --redo-only /backup/full_2022-01-13/
第13步: inc1合并到full中，并且prepare
innobackupex --apply-log --redo-only --incremental-dir=inc1_2022-01-13 full_2022-01-13
cd 到 /backup目录
检验合并结果:
合并与没有合并的情况对比
cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc1_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
第14步: inc2合并到full中，并且prepare
innobackupex --apply-log --redo-only --incremental-dir=inc2_2022-01-13 full_2022-01-13
检验合并结果:
cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc2_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
第15步: inc3合并到full中，并且prepare
innobackupex --apply-log --redo-only --incremental-dir=inc3_2022-01-13 full_2022-01-13
检验合并结果:
cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc3_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
第16步:将合并后全备再次prepare
innobackupex --apply-log /backup/full_2022-01-13
第17步:恢复数据
innobackupex --copy-back /backup/full_2022-01-13
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld
检查数据
mysql -uroot -p123 < /backup/backup.sql
导入inc4
终点 2935
起点
终点
[root@db-51 /backup/inc3_2022-01-13]# cat xtrabackup_binlog_info
mysql-bin.000003 2510
mysqlbinlog --start-position=2510 --stop-position=2935 mysql-bin.000003 > /tmp/backup.sql
导入inc4数据
第18步:检查数据
t100w
xbk inc1 inc2 inc3 inc4
第19步:立刻做一次全备
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/full_OK_$(date +%F)
