MySQL数据库备份

本文主要是介绍MySQL数据库备份，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

备份原因：数据丢了或者被删了
备份目标：数据的一致性和服务的可用性
备份技术：物理备份/冷备份——直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的Mysql  ，
                                                    拷贝数据，优点快，缺点需要停止服务
                逻辑备份/热备份——备份的是建表、建库、插入等操作所执行的SQL语句（DDL、DML、DCL），适用于中小型数据库
                                                效率比较低

备份方式：
a.完全备份
b.增量备份——
c.差异备份

实战案例1——percona-xtrabackup

开源的免费的MySQL数据库热备份软件
获得软件包：www.percona.com
安装percona需要的MySQL包

安装MySQL官方源 yum -y install https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
yum工具：yum -y install 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

安装percona

yum -y install  https://repo.percona.com/yum/percona-release-latest.noarch.rpm                    //安装软件官方源
yum  makecache             //刷新yum仓库缓存
yum list | grep percona-xtrabackup
yum -y install  percona-xtrabackup-24.x86_64        //安装percona
yum list | grep  percona-xtrabackup             //查询安装结果

完整备份流程

innobackupex --user=root --password='Hzr@5201314' /xtrabackup/full           //备份数据命令
ls /xtrabackup/full        //查看备份目录。数据库，配置文件，日志文件
ls /xtrabackup/full/2021-10-27_20-51-58
cat /xtrabackup/full/2021-10-27_20-59-27/xtrabackup_binlog_info               //查看备份的二进制文件

完全恢复流程

停止数据库 ：  systemctl  stop  mysqld
清理环境（模拟损坏）： rm -rf /var/lib/mysql/*          //清理数据库文件
                     rm -rf /var/log/mysqld.log       //清理数据库日志文件
生成回滚日志：innobackupex --apply-log /xtrabackup/full/2021-10-27_20-59-27/             //指定备份点
恢复文件： innobackupex --copy-back /xtrabackup/full/2021-10-27_20-59-27/          //恢复文件
登录验证
ls /var/lib/mysql/
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld

增量备份流程

第一天数据备份：innobackupex --user=root --password='Hzr@5201314' /xtrabackup/full           //首先要做一份完整备份
date 10290000           //模拟时间更新
进入数据库写入数据     mysql -uroot -p'密码'  -e 'insert into 数据库.表名 values（）；'
第二天数据备份：innobackupex --user=root --password='Hzr@5201314' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2021-10-28_13-23-02   //basedir会基于上一天的备份，再生成一个今天的文件
第三天数据备份：innobackupex --user=root --password='Hzr@5201314' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2021-10-29_00-28-51  //--incremental-basedir字段是基于第二天的文件目录

增量恢复流程

systemctl stop mysqld           //停止数据库
rm -rf /var/lib/mysql/*         //清理环境，模拟数据库损坏
第一天数据：innobackupex --apply-log --redo-only  /xtrabackup/2021-10-28_13-23-02            //回滚合并，目录为第一次完整备份的目录
第二天数据：innobackupex --apply-log --redo-only /xtrabackup/2021-10-28_13-23-02 --incremental-dir=/xtrabackup/2021-10-29_00-08-51   //--incremental-dir 选项是指后边做的增量备份目录
恢复：innobackupex --copy-back /xtrabackup/2021-10-28_13-23-02
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld

登录查询数据库，现在数据库的数据记录应该是只有第二天的数据，第三天数据并没有恢复，想还原到三天的数据怎么办?

systemctl stop mysqld
innobackupex --apply-log --redo-only /xtrabackup/2021-10-28_13-23-02 --incremental-dir=/xtrabackup/2021-10-30_00-05-10     //生成第三天的回滚日志
rm -rf /var/lib/mysql/*                 //这个步骤是为了删除之前恢复的前两天的备份，要不然回复第三天会报错
innobackupex --copy-back /xtrabackup/2021-10-28_13-23-02

差异备份流程

rm -rf /xtrabackup/* //删除之前做的备份 
date 10010000 //更改系统时间，作为实验的时间 
innobackupex --user=root --password='Hzr@5201314' /xtrabackup/ //先在周一做一份完整备份 
date 10020000 //更改系统时间到第二天 
innobackupex --user=root --password='Hzr@5201314' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2021-10-01_00-01-22 //周二基于周一做增量备份 
date 10030000 
innobackupex --user=root --password='Hzr@5201314' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2021-10-01_00-01-22 //周三也是基于周一做增量备份

差异恢复流程

systemctl stop mysqld           //停止数据库
rm -rf /var/lib/mysql/*         //清理环境
innobackupex --apply-log --redo-only  /xtrabackup/2021-10-01_00-01-22            //回滚合并，目录为第一次完整备份的目录
如果是要恢复到哪一天的数据，直接在下边的命令中把--incremental-dir指定到这天的目录即可,下边是还原到周四的数据库状态
innobackupex --apply-log --redo-only /xtrabackup/2021-10-01_00-01-22  -incremental-dir=/xtrabackup/2021-10-04_00-01-22
innobackupex --copy-back /xtrabackup/2021-10-01_00-01-22  
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld

总结：增量备份与完整备份相同的地方是再第一天都要做完整备份，不一样的地方是增量备份是基于前一天做的备份来进行备份；而差异备份是每一天都基于第一天的完整备份来进行备份；

实战案例2——mysqldump+binlog

优势：1.自动记录日志position位置

2.可用性、一致性

语法：mysqldump -h 服务器  -u用户名 -p密码 数据库名 > 备份文件.sql
参数说明：
--single-transaction  // innoDB一致性  服务可用性

备份实战

第一步准备数据库

第二步执行备份

mysqldump -uroot -p'Hzr@5201314' --all-databases --single-transaction  --master-data=2 --flush-logs  > /backup/`date +%F-%H`mysql-all.sql   //master-data=2 是注释掉日志记录； `date +%F-%H`反引号括住的是一条命令，优先执行，目的是为了每次生成相应日期的备份文件

第三步观察备份细节

vim /backup/2016-11-25-14-mysql-all.sql

第四步 备份完后发生数据变更

恢复实战

1.第一步备份二进制日志文件

cp /var/lib/mysql/*bin* ~

2.停止数据库

systemctl stop mysqld

3.清理环境

rm -rf /var/lib/mysql/*

4.启动数据库

systemctl start mysqld

5.mysql恢复数据

mysql -uroot -p'Hzr@5201314'  < /backup/2021-10-31-14mysql-all.sql 
数据库密码要保持一致
mysql -uroot -p'Hzr@5201314' -e 'flush privileges'

二进制日志恢复

观察二进制截取记录
vim /backup/2021-10-31-14mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='houzhengrong-bin.000003', MASTER_LOG_POS=154;

mysqlbinlog houzhengrong-bin.000002 houzhengrong-bin.000003 --start-position=154 | mysql -uroot -p'Hzr@5201314'

登录数据库观察数据是否恢复完整

如何保留人为删除的数据库数据

 mysqlbinlog houzhengrong-bin.000001 houzhengrong-bin.000002 houzhengrong-bin.000003 > 1.txt
 删除1.txt中不需要的at
 cat 1.txt | mysql -uroot -p'Hzr@5201314'

如何处理数据恢复时的多余日志

方法一：

mysql -uroot -p''
set sql_log_bin=0
source /backup/*.sql
二进制文件并没有发生改变

方法二：

在配置文件中，加入关闭二进制日志

实战案例3——记录的导出和导入

记录导出

注意目录权限
vim /etc/my.cnf
secure-file-priv=/backup        //mysql不信任该目录，需要添加信任

chown mysql.mysql /backup       //mysql用户没有权限
systemctl restart mysqld

登录MySQL数据库
select .........   into  outfile '/backup/目标文件名'

MySQL命令导出文本文件
mysql -uroot -p'Hzr@5201314' -e 'select * from haha.xinxi' > /backup/haha.xinxi.txt
mysql -uroot -p'Hzr@5201314' --xml -e 'select * from haha.xinxi' > /backup/haha.xinxi1.txt
mysql -uroot -p'Hzr@5201314' --html -e 'select * from haha.xinxi' > /backup/haha.xinxi2.html

导入文本文件 LOAD DATA INFILE

注意目录权限
vim /etc/my.cnf
secure-file-priv=/backup        //mysql不信任该目录，需要添加信任

chown mysql.mysql /backup       //mysql用户没有权限
systemctl restart mysqld

delete from haha.xinxi       //删除表的内容
表的导入和导出只备份表记录，不会备份表结构，因此需要使用mysqldump备份表结构，恢复时先恢复表结构，再导入数据
load data infile '/backup/haha.xinxi.txt' into table haha.xinxi;

这篇关于MySQL数据库备份的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！