mysql备份的重要性 mysql备份分为

转载

mob64ca13f2b62d 2023-10-14 08:03:54

文章标签 mysql备份的重要性 mysql 数据库数据 文章分类 MySQL 数据库

一般情况下, 我们需要备份的数据分为以下几种
数据
二进制日志, InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)服务器配置文件
备份的类型
按照备份时数据库的运行状态，可以分为三种
1. 冷备：停库、停服务来备份
即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
2. 温备：不停库、不停服务来备份，会(锁表)阻止用户的写入
即当数据库进行备份时，数据库的读操作可以执行, 但是不能执行写操作
3. 热备（建议）：不停库、不停服务来备份，也不会(锁表)阻止用户的写入
即当数据库进行备份时, 数据库的读写操作均不是受影响
MySQL中进行不同类型的备份还要考虑存储引擎是否支持
MyISAM InnoDB 热备 × 热备 √ 温备 √ 温备 √ 冷备 √ 冷备 √
按照备份的内容，可以分为两种
1. 物理备份：直接将底层物理文件备份
2. 逻辑备份：通过特定的工具从数据库中导出sql语句或者数据，可能会丢失数据精度
按照每次备份的数据量，可以分为三种
1. 全量备份/完全备份
2. 部分备份，而部分备份又分为：差异备份和增量备份两种
1、全量备份的数据恢复只需找出指定时间点的那一个备份文件即可，即只需要找到一个文件即可 2、差异备份的数据恢复需要先恢复第一次备份的结果，然后再恢复最近一次差异备份的结果，即需要找到两个文件 3、增量备份的数据恢复需要先恢复第一次备份的结果，然后再依次恢复每次增量备份，直到恢复到当前位置，即需要找到一条备份链综上，对比三种备份方案 1、占用空间：全量 > 差异 > 增量 2、恢复数据过程的复杂程度：增量 > 差异 > 全量

备份的工具

mysql备份的重要性 mysql备份分为_数据库

设计备份策略
备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略。
能够容忍丢失多少数据
恢复数据需要多长时间
需要恢复哪一些数据
三种备份策略及应用场景
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种：
直接cp，tar复制数据库文件(基本上可以忽略这一种了，“手动狗头”)
mysqldump+复制bin log
lvm快照+复制bin log
xtrabackup
以上的几种解决方案分别针对于不同的场景
如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
如果数据量还行, 可以使用第二种方式, 先使用 mysqldump对数据库进行完全备份, 然后定期备份binary log达到增量备份的效果
如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份binary log达到增量备份的效果
如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

备份实战

使用mysqldump备份 + bin log日志，模拟数据损坏恢复
mysqldump命令使用

语法:
mysqldump -h 服务器 -u 用户名 -p密码 选项与参数 > 备份文件.sql

选项与参数:
-A/--all-databases 					所有库
-B/--databases bbs db1 db2 			多个数据库
db1 								数据库名
db1 t1 t2 							db1数据库的表t1、t2
-F 									备份的同时刷新bin log
-R 									备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
--triggers 							备份触发器数据（现在都是开发写触发器）
-E/--events 						备份事件调度器
-d 									仅表结构
-t 									仅数据

--master-data=1 				
备份文件中 change master语句是没有注释的，默认为1
用于已经制作好了主从，现在想扩展一个从库的时候使用。
如此备份，扩展添加从库时导入备份文件后便不需要再加mater_pos了。
change matser to
master_host='10.0.0.111'
master_user='rep'
master_password=123
master_log_pos=120
master_log_file='master-bin.000001'

--master-data=2 
备份文件中change master语句是被注释的

--lock-all-tables, 提交请求锁定所有数据库中的所有表，以保证数据的一致性。
这是一个全局读锁，并且自动关闭--single-transaction和--lock-tables选项，他们是互斥的。
对于支持事务的表，例如InnoDB和BDB，推荐使用--single-transaction选项，因为它根本不需要锁定表。

--single-transaction: 快照备份 （搭配--master-data可以做到热备）
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。
本选项和--lock-tables选项是互斥的，不能同时存在，因为LOCK TABLES会使任何挂起的事务隐式提交。

# 完整语句
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

# 文件太大时可以压缩 gzip ，但是gzip不属于mysql独有的命令，可以利用管道
# --master-data=2 --single-transaction 这两个选项一起使用等于热备
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction | gzip >/tmp/full$(date +%F).sql.gz

# 导出时压缩了，导入时需要解压，可以使用zcat命令，很方便
zcat /tmp/full$(date +%F).sql.gz | mysql -uroot -p123

mysqldump做全量备份

1. 先打开binlog日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/service/mysql/mybinlog
binlog_format='row'		 	# (row,statement,mixed)
binlog_rows_query_log_events=on
max_binlog_size=100M

# 重启数据库
[root@db01 ~]# systemctl restart mysql

2. 登录数据库，插入测试数据
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)

mysql> use db01;
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

3. 在命令行执行命令，进行全量备份
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
Warning: Using a password on the command line interface can be insecure.

4. 在命令行执行命令，刷新bin log，便于日后查找(或者直接在上一条sql语句中加上-F参数也可以)
[root@db01 ~]# mysql -uroot -p123 -e "flush logs"

5. 登录数据库，再插入一些数据，模拟增量，这些数据写入了新的binlog
mysql> use db01;
Database changed

mysql> insert t1 values(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

模拟数据损坏恢复
bin log日志做增量备份
数据恢复：一定要先恢复全量备份，再恢复增量

模拟数据丢失
mysql> drop database db01;
Query OK, 1 row affected (0.01 sec)

# 查看事件详细信息,我们在做全量备份的时候刷新了bin log日志,所以我们查看的日志文件为mybinlog.000002
mysql> show binlog events in 'mybinlog.000002';
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002

导出增量备份日志的数据
# 注意:导出bin log时不要加选项--base64-output
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002 --start-position=192 --stop-position=338 > /tmp/1.sql

恢复数据
1. mysql数据导入时，临时关闭bin log，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

2. 先恢复全量
# 如果不是压缩包直接使用:source /tmp/full.sql
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.

3. 再恢复增量
mysql> source /tmp/1.sql;

4. 开启二进制日志，恢复数据的写操作也记入bin log文件
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

使用LVM备份 + bin log日志，模拟数据损坏恢复

1. 添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 无需重启
echo '- - -' > /sys/class/scsi_host/host0/scan
echo '- - -' > /sys/class/scsi_host/host1/scan
echo '- - -' > /sys/class/scsi_host/host2/scan

2. 创建逻辑卷
pvcreate /dev/sdb
vgcreate vg1 /dev/sdb
lvcreate -n lv1 -L 5G vg1

3. 格式化制作文件系统并挂载到指定目录下
mkfs.xfs /dev/mapper/vg1-lv1
# 我是用源码编译安装的mysql,指定数据库路径为/service
# 如果是yum安装的数据库,默认路径为/var/lib/mysql
mv /service/* /opt/
mount /dev/mapper/vg1-lv1 /service
mv /opt/* /service

4. 先打开binlog日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/service/mysql/mybinlog
binlog_format='row'		 	# (row,statement,mixed)
binlog_rows_query_log_events=on
max_binlog_size=100M

# 重启数据库
[root@db01 ~]# systemctl restart mysql

5. 往数据库内插入测试数据
create database db01;
use db01;
create table t1(id int);
insert t1 values(1),(2),(3);

创建LVM快照卷做全量备份

mysql> FLUSH TABLES WITH READ LOCK; 		 # 锁定所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# lvcreate -L 1G -s -n lv1_from_vg1_snap /dev/vg1/lv1  # 创建快照卷

# 刷新bin log日志，便于日后查找
[root@db01 ~]# mysql -uroot -p123 -e "flush logs"

mysql> UNLOCK TABLES; 			# 解锁所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# mkdir /snap1 		# 创建文件夹
[root@node1 lvm_data]# mount -o nouuid /dev/vg1/lv1_from_vg1_snap /snap1

[root@localhost snap1]# cd /snap1/
[root@localhost snap1]# tar cf /tmp/mysqlback.tar *

[root@localhost snap1]# umount -l /snap1/ 
[root@localhost snap1]# lvremove vg1/lv1_from_vg1_snap

模拟数据丢失恢复数据
bin log日志做增量备份
数据恢复：一定要先恢复全量备份，再恢复增量

模拟数据丢失
use db01;
create table t2(id int);
insert t2 values(1),(2),(3);
drop database db01;

# 查看事件详细信息,我们在做全量备份的时候刷新了bin log日志,所以我们查看的日志文件为mybinlog.000002
mysql> show binlog events in 'mybinlog.000002';
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002

导出增量备份日志的数据
# 注意:导出bin log时不要加选项--base64-output
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002 --start-position=120 --stop-position=468 > /tmp/1.sql

1. 先恢复全量
[root@db01 ~]# tar -xf /tmp/mysqlback.tar -C /service/

2. 再恢复增量
#  mysql数据导入时，临时关闭bin log，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;

mysql> source /tmp/1.sql;

#  开启二进制日志，恢复数据的写操作也记入bin log文件
mysql> set sql_log_bin=1;

使用Xtrabackup备份 + bin log日志，模拟数据损坏恢复
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
备份过程快速、可靠；
备份过程不会打断正在执行的事务；
能够基于压缩等功能节约磁盘空间和流量；
自动实现备份检验；
还原速度快；
使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间(.ibd文件), 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启。
安装版本选择
mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本。
mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本，xtrabackup8.0也只支持mysql8.0以上的版本。
比如，接触过一些金融行业，mysql版本还是多采用mysql5.7，当然oracle官方对于mysql 8.0的开发支持力度日益加大，新功能新特性迭代不止。生产环境采用mysql 8.0的版本比例会日益增加。

安装脚本

#!/usr/bin/env bash
cd /opt
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-release-latest.noarch.rpm -y
yum install percona-xtrabackup-24 -y

安装完后会生成命令
xtrabackup 以前使用该命令 innobackupex 现在使用该命令 innobackupex是xtrabackup的前端配置工具，使用innobackupex备份时,会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录。
Xtrabackup 备份方式（物理备份）
对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
备份时读取配置文件/etc/my.cnf
innobackupex命令默认会去找"/var/lib/mysql/mysql.sock"文件，
我之前的两种备份方式都是使用二进制安装的mysql(自定义的文件路径)，我这里就直接使用yum安装5.7版本的数据库了。
安装Mysql
Xtrabackup全量备份

# 创建备份目录
[root@db01 ~]# mkdir /backup

# 全备
[root@db01 ~]# innobackupex --user=root --password=Na123*** /backup/full

# 查看
[root@db01 ~]# cd /backup/
[root@db01 backup]# ls
full
[root@db01 backup]# cd full/
[root@db01 full]# ls
2021-07-21_20-31-12

# 去掉时间戳
[root@db01 full]# innobackupex --user=root --password=Na123*** --no-timestamp /backup/full
[root@db01 full]# ls
backup-my.cnf   mysql                   xtrabackup_checkpoints
egon_test       performance_schema      xtrabackup_info
ib_buffer_pool  sys                     xtrabackup_logfile
ibdata1         xtrabackup_binlog_info

补充：关于备份目录下新增的文件说明，可用cat命令查看 xtrabackup_checkpoints 存储系统版本号，
增备的时候会 用到xtrabackup_info 存储UUID，数据库是由自己的UUID的，如果相同，做主从会有问题 xtrabackup_logfile 就是redo

Xtrabackup增量备份

--incremental:开启增备功能
--incremental-basedir:上一次备份的路径

# 增备
[root@db01 ~]# innobackupex --user=root --password=Na123*** --no-timestamp --incremental --incremental-basedir=/backup/full /backup/xtra

# 查看
[root@db01 ~]# cd /backup/
[root@db01 backup]# ls
full  xtra

# 查看是否衔接
[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 5908495
last_lsn = 5908504
compact = 0
recover_binlog_info = 0
flushed_lsn = 5908504
[root@db01 backup]# cat xtra/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 5908495
to_lsn = 5908495
last_lsn = 5908504
compact = 0
recover_binlog_info = 0
flushed_lsn = 5908504

案例
数据备份

1. 下载xtrabackup软件
[root@db01 ~]# vim a.sh
#!/usr/bin/env bash
cd /opt
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-release-latest.noarch.rpm -y
yum install percona-xtrabackup-24 -y

[root@db01 ~]# bash a.sh

2. 开启bin log日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1                              
log-bin=/var/lib/mysql/mybinlog                 
binlog_format='row'                     
binlog_rows_query_log_events=on                  
max_binlog_size=100M

3. 重启mysql
[root@db01 ~]# systemctl restart mysqld

4. 插入数据
create database db01;
use db01;
create table t1(id int);
insert t1 values(1),(2),(3);

# 第一次全量备份
[root@db01 ~]# innobackupex --user=root --password=Na123*** --no-timestamp /backup/full

6. 周一继续插数据
use db01;
insert t1 values(4),(5),(6);

# 周一增量备份
[root@db01 ~]# innobackupex --user=root --password=Na123*** --no-timestamp --incremental --incremental-basedir=/backup/full /backup/xtra

8. 周二继续插数据
use db01;
insert t1 values(7),(8),(9);

# 周二增量备份
[root@db01 ~]# innobackupex --user=root --password=Na123*** --no-timestamp --incremental --incremental-basedir=/backup/xtra /backup/xtra1

9. 周三插入数据
use db01;
insert t1 values(10),(11),(12);

10. 然后数据库没了
drop database db01;

# 恢复思路
1. 停业务,挂维护页 
2. 查找可用备份并处理备份:full+inc1+inc2 
3. 找到binlog中: inc2 到 故障时间点的binlog 
4. 恢复全备+增量+binlog 
5. 验证数据 
6. 起业务,撤维护页

数据恢复

# 整理full
[root@db01 backup]# innobackupex --apply-log --use-memory=3G --redo-only /backup/full

# 合并周一的增量到full并整理
[root@db01 backup]# innobackupex --apply-log --use-memory=3G --redo-only --incremental-dir=/backup/xtra /backup/full

# 合并周二的增量到full并整理
[root@db01 backup]# innobackupex --apply-log --use-memory=3G --redo-only --incremental-dir=/backup/xtra1 /backup/full

# 整理full
[root@db01 backup]# innobackupex --apply-log --use-memory=3G /backup/full

# 查看周三的binlog_info
[root@db01 backup]# cat /backup/xtra1/xtrabackup_binlog_info
mybinlog.000001	1417

# 查看事件详细信息
mysql> show binlog events in 'mybinlog.000001';
[root@db01 ~]# mysqlbinlog /var/lib/mysql/mybinlog.000001

# 导出增量备份日志的数据,起始点选择上面的
[root@db01 backup]# mysqlbinlog /var/lib/mysql/mybinlog.000001 --start-position=1417 --stop-position=1734 > /backup/binlog.sql 

# 全量恢复
[root@db01 backup]# rm -rf /var/lib/mysql/*
[root@db01 backup]# \cp -a -f /backup/full/* /var/lib/mysql
[root@db01 backup]# chown -R mysql.mysql /var/lib/mysql
[root@db01 backup]# systemctl restart mysqld

# 增量恢复
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql;
mysql> set sql_log_bin=1;

自动备份脚本

使用mysqldump自动备份数据脚本

[root@db01 ~]# vim /mysql_back.sh
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123
if [ ! -d /backup ];then
mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u${user} -p${pass} --events -R --triggers --master-data=2 --single-transaction --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

- 手动测试
chmod a+x /mysql_back.sh
chattr +i /mysql_back.sh
bash /mysql_back.sh

配置计划任务
配置cron：
[root@db01 ~]# crontab -l
0 2 * * * /mysql_back.sh		# 每天晚上2点执行一次脚本

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。