【mysql备份】

最新推荐文章于 2024-06-11 21:29:27 发布

黑乎乎的小升

最新推荐文章于 2024-06-11 21:29:27 发布

阅读量109

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_55084041/article/details/130886410

版权

mysql备份

备份数据库

备份类型

完全备份，部分备份
- 完全备份：整个数据集
- 部分备份：只备份数据子集，如部分库或表

2. 完全备份+增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂，存在依赖性，还原需先还原最近的一次完全备份再递进式还原其他增量备份，最后一段可用二进制文件还原，1->2、2->3、3->4
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单，存在包含关系，1->2、1->3、1->4
增量和差异备份的基础和前提是完全备份

冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行，能读不能写，相当于加读锁
热备：读、写操作均可执行，数据库正常工作

MyISAM：温备，不支持热备

InnoDB：都支持

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：通过mysql协议从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份注意要点

能容忍最多丢失多少数据
备份产生的负载
备份过程的时长
温备的持锁多久
恢复数据需要在多长时间内完成
需要备份和恢复哪些数据

还原要点

做还原测试，用于测试备份的可用性
还原演练，写成规范的技术文档

mysqldum备份工具

支持热备份的工具，不支持增量备份

mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] –B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] –A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

#仅备份数据库hellodb里的students表，备份到/backup/student.sql中，目录需提前存在，该方法无法直接备份数据库
[root@centos8 ~]# cd /data
[root@centos8 ~]# mkdir /backup
[root@centos8 ~]# mysqldump -uroot -p123456 hellodb students > /backup/student.sql
[root@centos8 ~]# mysql -uroot -p123456 hellodb       #登录到数据库中
mysql> source /backup/student.sql                     #还原students表
mysql> show tables;                                   #查看

#可备份数据库，可多个-B
[root@centos8 ~]# mysqldump -uroot -p123456  -B hellodb hellodb2  > /backup/hellodb.sql   
#-B备份数据库，会同样备份数据库的相关属性，可多个，空格隔开，存在一个文件即可
[root@centos8 ~]# mysql -uroot -p123456    
mysql> source /backup/hellodb.sql
mysql> show databases;

#备份数据库
[root@centos8 ~]# mysqldump -uroot -p123456 -A > /backup/all.sql

常用选项

-A, --all-databases              #备份所有数据库
-B, --databases db_name…         #指定备份的数据库
-E, --events：                   #备份相关的所有event scheduler，计划任务，事件
-R, --routines：                 #备份所有存储过程和自定义函数
--triggers：                     #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8     #指定字符集，需和存放的数据的字符集相同

--master-data[=#]：              #mysql8.0.26后更改为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：生成被注释的#CHANGE MASTER TO语句，适合于单机使用，记录的是备份数据库时的二进制日志标记位置，用于还原数据时使用
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs                 #会自动刷新日志，重新记录，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact                        #去掉注释，适合调试，生产不使用
-d, --no-data                    #只备份表结构,不备份数据
-t, --no-create-info             #只备份数据,不备份表结构
-n,--no-create-db                #不备份create database，可被-A或-B覆盖
--flush-privileges               #备份mysql或相关时需要使用，刷新权限
-f, --force                      #忽略SQL错误，继续执行吗，强制执行
--hex-blob                       #使用十六进制符号转储二进制列，避免乱码
-q, --quick                      #不缓存查询，直接输出，加快备份速度，8.0后已经有缓存功能因此不需使用了

MYISAM特有

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

mysqldump的InnoDB存储引擎

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

InnoDB建议备份策略

mysqldump –uroot -p123456 –A –F –E –R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

简单mysqldump备份

单数据库备份

模拟每天半夜两点半完全备份，早上十点误删除表students，10:10发现误删除操作，将数据还原到10:10分且恢复误删除的表students

先恢复2:30完全备份，在二进制日志文件中删掉误删除操作，再利用二进制日志恢复到10:10分
注意二进制日志文件的起始点位置

#检查二进制日志功能是否开启，需先开启
mysql> select @@log_bin;
mysql> select @@sql_log_bin;

#完全备份，并记录备份的二进制位置
mysqldump  -uroot -p123456 -A -F --default-character-set=utf8  --single-transaction --master-data=2 | gzip > /data/fyll_`date +%F`.sql.gz

#2:30到10：00之间的数据更新
insert students (name,age,gender)value('rose',20,'M');
insert students (name,age,gender)value('wei',22,'M');

#误删除students表
drop table students;

#后续其它表继续更新
mysql> use hellodb;
mysql> insert teachers (name,age,gender)values('wang',30,'M');
Query OK, 1 row affected (0.002 sec)
mysql> insert teachers (name,age,gender)values('wei',28,'M');
Query OK, 1 row affected (0.002 sec)

#10：10发现表删除，进行还原
#停止数据库访问

#从完全备份中，找到二进制位置
[root@centos8 ~]#grep '\-\- CHANGE MASTER TO' /data/full_2023-05-15.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=156;

#备份从完全备份后的二进制日志
[root@centos8 ~]#mysqlbinlog --start-position=156 /var/lib/mysql/mariadb-bin.000002 > /data/inc.sql
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/binlog.000003 >> /data/inc.sql
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/binlog.000004 >> /data/inc.sql


#找到误删除的语句，从备份中删除此语句
[root@centos8 ~]#vim /data/inc.sql
#DROP TABLE `students` /* generated by server */
#如果文件过大，可以使用sed实现
[root@centos8 ~]#sed -n '/DROP TABLE `students`/p' /data/inc.sql
[root@centos8 ~]#sed -i '/^DROP TABLE `students`/d' /data/inc.sql  #准确查找准确删除

#利用完全备份和修改过的二进制日志进行还原，先还原完全备份的再还原二进制日志，注意次序
[root@centos8 ~]#mysql -uroot -p
MariaDB [hellodb]> set sql_log_bin=0;
MariaDB [hellodb]> source /data/full_2023-05-15.sql;
MariaDB [hellodb]> source /backup/inc.sql
MariaDB [hellodb]> set sql_log_bin=1;

分库备份

[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db | gzip > /data/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema|sys)$'|while read db;do mysqldump -B $db | gzip > /data/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema|sys)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' |bash

[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' |bash

[root@centos8 ~]#cat backup_db.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456

[ -d "$DIR" ] || mkdir $DIR

for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ev 
"^Database|information_schema|performance_schema|sys$"`;do
 mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done

Xtrabackup备份工具

增量备份及还原

1 备份过程
1）完全备份：
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
2）第一次修改数据
insert students (name,age,gender)value('rose',20,'M');
3）第一次增量备份，指定基于完全备份文件--incremental-basedir，增量备份要单独存放在一个目录中inc1
[root@centos8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4）第二次修改数据
insert students (name,age,gender)value('wei',22,'M');
5）第二次增量，基于第一次增量备份inc1，存放于inc2
[root@centos8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1


6）[root@centos8 ~]#scp -r /backup/ 10.0.0.18：
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}

2还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚撤销未完成的事务
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份，
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

4）整理拷贝到数据库目录，注意数据库目录必须为空，MySQL服务不能启动，/var/lib/mysql路径下需要为空
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：[root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql
6）启动服务：[root@centos8 ~]#service start mysqld

编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。

[root@firewall ~]#vim backup.sh
#!/bin/bash
DIR=/backup
TIME=`date +%F`
TIMEA=`date +%F_%H-%M-%S`
pass=123456
B=hellodb
mkdir ${DIR}_mysql/${TIME}
 
mysqldump -uroot $B students | gzip > ${DIR}_mysql/${TIME}/${B}.students_${TIMEA}.sql.gz
 
chmod +x backup.sh
[root@firewall ~]#crontab -e
 
30 2 * * * /root/backup.sh
 
 
[root@firewall ~]#ll /backup_mysql/
-rw-r--r-- 1 root root 1114 May 26 12:59 hellodb.students_2023-05-26_12-59-54.sql.gz

编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。

[root@firewall backup]#crontab -e
 
0 14 * * 1,5   /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
0 15 * * 2     /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
0 15 * * 3     /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
0 15 * * 4     /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc3 --incremental-basedir=/backup/inc2

在这里插入图片描述

黑乎乎的小升

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【mysql备份】

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂，存在依赖性，还原需先还原最近的一次完全备份再递进式还原其他增量备份，最后一段可用二进制文件还原，1->2、2->3、3->4。模拟每天半夜两点半完全备份，早上十点误删除表students，10:10发现误删除操作，将数据还原到10:10分且恢复误删除的表students。差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单，存在包含关系，1->2、1->3、1->4。热备：读、写操作均可执行，
复制链接

扫一扫