首页 > 数据库 >数据库备份

数据库备份

时间：2024-08-18 12:37:42浏览次数：11  
标签：-- 备份 mysql xtrabackup sql root 数据库

数据库备份

通常数据库备份 database 数据⽂件 binlog ⽇志⽂件 my.cnf 配置⽂件,所有的数据库的备份数据都应放在⾮数据

库本地, ⽽且建议备份多份。

仅备份是没有任何意义, 需要在测试环境中做⽇常恢复演练, 测试备份的可⽤性, 恢复较⽐备份更加的重要。

备份: 能够有效防⽌设备故障以及⼈为误操作带来的数据丢失, 例如:将数据⽂件保存在远端。

冗余: 数据有多分冗余, 但不等于备份, 只能防⽌机械故障丢失的数据, 例如: 主备模式、数据库集群。

数据库备份必须考虑因素

1.数据的⼀致性

2.服务的可⽤性

数据库备份⽅式

1.逻辑备份: 备份 DDL DML DCL 语句, 适⽤于中⼩型数据库, 效率相对低下。 mysqldump、mydumper

2.物理备份: 直接复制数据库⽂件, 适⽤于⼤型数据库环境, 效率相对较⾼。 xtrabackup、inbackup、cp、

tar、lvm snapshot

在选择备份⽅式时，需要考虑数据库的规模、备份和恢复的效率、备份数据的⼀致性等因素。同时，为了保证备份

数据的安全性，备份数据应该保存在⾮本地的地⽅，并且建议备份多份。

数据库备份模式

完全备份

增量备份

差异备份

冷备份与热备份

冷备份

关闭数据库服务，完整拷⻉数据⽂件

热备份

在不影响数据库读写服务的情况下备份数据库

备份周期

考虑因素：

数据库⼤⼩(决定备份时间)

恢复速度要求(快速or慢速)

备份⽅式(全量or增量)

企业备份机制

如果你的数据库⼩于20G 每天公司0点进⾏全量备份 逻辑备份 物理备份可以的

如果数据库⼤于100G，周⼀晚上0点进⾏物理全备份，周⼆ 增量备份,周三 以此类推，

逻辑备份与恢复

数据库完整恢复流程

1.停⽌数据库

2.删除破损数据库

3.重新初始化数据库

4.重置密码,否则⽆法恢复

5.恢复数据[新密码]

6.刷新授权[备份时密码

mysqldump -uroot -p'zgw123' -h 192.168.107.133 \

--all-databases \

c



--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

image-20240817184713348

建议在恢复备份时暂停 binlog ⽇志记录

//1.关闭binlog记录, 不然容易恢复增量失败

[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql

all.sql

//2.在mysql控制台临时修改不记录binlog⽇志

mysql> set sql_log_bin=0;

mysql> source /backup/mysql/2023-05-2816-mysql-all.sql

//3. binlog打开

mysql> set sql_log_bin=1;

1.2****数据库增量备份与恢复

实战案例****2: 数据库完整备份+数据库增量备份

//备份之前

mysql> set sql_log_bin=0;

mysql> drop database wing;

mysql> set sql_log_bin=1;

mysql> create database wing;

mysql> create table wing.t1 (id int, name varchar(20));

mysql> insert into wing.t1 values (1,"wing1");

mysql> insert into wing.t1 values (2,"wing2");

mysql> select * from wing.t1;

+------+------+

| id | name |

+------+------+

| 1 | wing1 |

| 2 | wing2 |

+------+------+

2 rows in set (0.00 sec)

//基于当前状态备份

[root@sql ~]# mysqldump -uroot -p'Wing@123' \

--all-databases \

--single-transaction \

--master-data=1 \

--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

//模拟数据插⼊操作

mysql> insert into wing.t1 values (3,"wing3");

mysql> insert into wing.t1 values (5,"tt");

mysql> select * from wing.t1;

+------+------+

| id | name |

+------+------+

| 1 | wing1 |

| 2 | wing2 |

| 3 | wing3 |

| 5 | tt |

+------+------+

4 rows in set (0.00 sec)

//模拟数据库故障

[root@sql ~]# /etc/init.d/mysqld stop

[root@sql ~]# rm -rf /sort/mysql

恢复全备数据

//关闭binlog记录, 不然容易恢复增量失败

[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2716-mysql

all.sql

[root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2716-mysql-all.sql

[root@sql mysql]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;"

+------+------+

| id | name |

+------+------+

| 1 | wing1 |

| 2 | wing2 |

+------+------+

恢复增量数据

//查看备份后binlog起始位置点

[root@sql ~]# sed -n '22p' /backup/mysql/2023-05-2816-mysql-all.sql

CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000014', MASTER_LOG_POS=154;

//查找结束位置点, 进⾏恢复

[root@sql bin]# mysqlbinlog --start-position=154 sql_106_70.000014|mysql -uroot -

p'Wing@123'

//验证增量恢复数据

[root@sql bin]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;"

+------+-------+

| id | name |

+------+-------+

| 1 | wing1 |

| 2 | wing2 |

| 3 | wing3 |

| 5 | tt |

+------+-------+

image-20240817201717153

image-20240817201728619

2.MySQL****物理备份与恢复

2.1 xtrabackup 物理备份

特点：

开源，在线备份InnoDB表

⽀持限速备份，避免对业务造成影响

⽀持流备

⽀持增量备份

⽀持备份⽂件压缩与加密

⽀持并⾏备份与恢复，速度快

基于InnoDB的crash-recovery功能

备份期间允许⽤户读写，写请求产⽣redo⽇志

从磁盘上拷⻉数据⽂件

从InnoDB redo log file实时拷⻉⾛备份期间产⽣的所有redo⽇志

恢复的时候 数据⽂件 + redo⽇志 = ⼀致性数据

innobackupex --defaults-file=/etc/my.cnf -S /tmp/mysql.sock --user=root --password=zgw /xtrabackup/

image-20240817222039883

# innobackupex --username=root --password=zgw123 -S /tmp/mysql.sock /xtrabackup/

*/5 * * * * /usr/sbin/ntpdate -u ntp1.aliyun.com

innobackupex --user=root --password='zgw123' -S /tmp/mysql.sock \--incremental /xtrabackup/ \

image-20240817224526967

innobackupex --username=root --password=zgw123 -S /tmp/mysql.sock /xtrabackup/

innobackupex --apply-log --redo-only /xtrabackup/2024-08-18_08-20-55 --incremental-dir=/xtrabackup/2023-05-29_00-25-52

image-20240818091709778

数据库差异备份与恢复

3.差异备份恢复流程

//1.停⽌数据库

[root@sql ~]# /etc/init.d/mysqld stop

//2.清理环境

[root@sql ~]# rm -rf /soft/mysql/data/*

//3.重演回滚, 回滚全备

[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/

//4.重演回滚, 将差异备份应⽤⾄完整备份上

[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/ \

--incremental-dir=/xtrabackup/2023-05-31_00-00-30/

//5.应⽤数据

[root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-52-14/

//6.修改权限

[root@sql ~]# chown -R mysql.mysql /soft/mysql/

//7.启动数据库

[root@sql ~]# /etc/init.d/mysqld start

1.完整备份

innobackupex --user=root --password='zgw123' -S /tmp/mysql.sock/xtrabackup/

[root@zgw 2024-08-18_10-25-07]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 23607770
last_lsn = 23607779
compact = 0
recover_binlog_info = 0
flushed_lsn = 23607779
[root@zgw 2024-08-18_10-25-07]# backup_type = full-backuped

innobackupex --user=root --password='zgw123' -S /tmp/mysql.sock --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-08-18_10-25-07

2、差异备份:周⼀ -> 周三



//修改时间为周⼀, 新增数据
[root@sql ~]# date -s '2023-05-29'
mysql> insert into wingdb.t2 values(1);
//差异备份(以完整备份为准2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
//修改时间为周⼆, 新增数据
[root@sql ~]# date -s '2023-05-30'
mysql> insert into wingdb.t2 values(2);
//差异备份(以完整备份为准2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
//修改时间为周三, 新增数据
[root@sql ~]# date -s '2023-05-31'
mysql> insert into wingdb.t2 values(3);
//差异备份, (以完整备份为准2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/

4.模拟备份后⼀次错误操作

mysql> create database WingSred;

mysql> drop database WingSred;

image-20240818104343831

6.binlog恢复删除掉的WingSred;库


[root@sql ~]# cat /xtrabackup/2023-05-31_00-00-25/xtrabackup_binlog_info
sql_106_70.000018 1676
//导出对应的sql, 找到drop语句位置
[root@sql ~]# mysqlbinlog --start-position=1676 \
--base64-output="decode-rows" \
-v sql_106_70.000019 sql_106_70.000018 >db.sql
//针对起始点与结束点位置恢复
[root@sql bin]# mysqlbinlog --start-position=1676 \
--stop-position=1847 sql_106_70.000018 |mysql -uroot -p'Wing@123'

image-20240818110117152


[root@zgw bin]# mysqlbinlog --start-position=1676 --base64-output=DECODE-ROWS /log/mysql/bin/sql_107_133.000003 > db.sql
[root@zgw bin]# grep 'DROP' db.sql
[root@zgw bin]# vim db.sql
[root@zgw bin]# mysqlbinlog --start-position=1676 \

> --stop-position=1847 sql_107_133.000003  |mysql -uroot -p'zgw123'
> mysql: [Warning] Using a password on the command line interface can be insecure.

image-20240818110254633

简单命令进⾏物理备份

//1.停⽌数据库

[root@sql ~]#/etc/init.d/mysqld stop

//2.清理环境

[root@sql ~]# rm -rf /soft/mysql

//3.导⼊备份数据

[root@sql ~]# tar -xf /backup/2023-05-31-mysql-all.tar -C /soft/

//4.权限

[root@sql ~]# chown mysql.mysql -R /soft/mysql

//4.启动数据库

[root@sql ~]#/etc/init.d/mysqld start

//5.binlog 恢复

⽣产备份思路与实战

逻辑备份

保证⼀致性，服务可⽤性

适合少量的数据1G-10G

只能全备->指定某⼀个数据库备份

效率不是很⾼, 将所有的数据转成sql语句

ddl dml dcl

物理备份xtrabackup

保证⼀致性，服务可⽤性

适合⼤量的数据20G 100G 500G

⽀持增量、差异、全备

备份怎么做

物理备份 周天全备 周⼀到周六增量

/xtrabackup/1⽉/1周/xxx

/xtrabackup/1⽉/2周/xx

/xtrabackup/1⽉/3周

/xtrabackup/1⽉/4周

/xtrabackup/2⽉/1周

注意：

binlog⽇志⾮常的重要

备份时，不要记录binlog⽇志

备份不单单只是数据⽂件，还有binlog、my.cnf

标签：--,备份,mysql,xtrabackup,sql,root,数据库
From： https://www.cnblogs.com/zgw03/p/18365498

相关文章

  • 一款免费、简单、直观的数据库设计工具和 SQL 生成器，在浏览器中直接使用（附源码）
    前言在软件开发过程中，数据库设计是一个关键步骤，它直接影响到应用的性能和可维护性。然而，传统的数据库设计工具往往存在一些痛点，比如操作复杂、study曲线陡峭、缺乏直观的图形界面等。这些问题不仅拖慢了开发速度，也增加了设计的难度。为了解决这些问题，一款简单、直观且功能强......
  • 图数据库在社交网络分析中的应用
    图数据库：解锁社交网络分析的新维度在这个信息爆炸的时代，社交网络已经成为我们日常生活中不可或缺的一部分。从微信到微博，从LinkedIn到小红书，这些平台连接着数十亿用户，每时每刻都在产生海量的数据。但是，你有没有想过，这些复杂的社交关系是如何被存储和分析的呢？今天，让我们一......
  • 数据库的管理
    通过TFDSQLiteSecurity可以对数据库进行加密处理：withsqlSecuritydobeginDatabase:='x:\path_to_database\db.sdb';Password:='p@ssword';ToPassword:='p@ssword_changed';casergSecActions.ItemIndexof//是一个radiogroup......
  • PHP—MySQL（PHP连接数据库）
    目录【学习目标、重难点知识】【学习目标】【重难点知识】二、PHP和MySQL的合作方式三、PHP连接数据库3.1、MySQLi拓展​编辑3.2、建立与数据库的连接（4）创建SQL语句（5）获取结果（6）获取查询结果行数（7）增删改（8）数据可视化。【学习目标、重难点知识】【学习目标】P......
  • 备份docker
    1、备份images└─$sudodockerimagesREPOSITORYTAGIMAGEIDCREATEDSIZEnemov29c06d79c70773monthsago1.62GBtophant/arllatest826db31cea994monthsago......
  • rsync备份【基于客户端与服务端】
    一、需求1、客户端客户端提前准备存放到的备份目录，目录规则如下：/backup/nfs_IP+年/月/日客户端在本地打包备份（将etc目录中所有的普通文件打包）拷贝到目标目录/backup/nfs_IP+年/月/日客户端最后将备份的数据进行推送到备份服务器中客户端每天凌晨1点定时执行该脚本客户端服......
  • 极速查询：索引在数据库性能优化中的制胜之道
    标题：极速查询：索引在数据库性能优化中的制胜之道在数据库的浩瀚世界中，查询性能是每个开发者和数据库管理员追求的圣杯。索引，作为提升查询效率的利器，却常被误认为是高级技巧。本文将揭开索引的神秘面纱，深入探讨如何通过索引优化来实现查询性能的飞跃。一、索引的魔力：基础概......
  • JSP基于JAVAWEB医院挂号系统jep98（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表系统功能：用户,医生,护士,科室信息,预约时间段,出诊信息,预约挂号,门诊病历,医生打卡,护士打卡,就诊评价技术要求:开发语言：JSP前端使用:HTML5，CSS，JSP动态网页技......
  • JSP基于JavaWeb的CRM系统684y6程序+源码+数据库+调试部署+开发环境
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表系统功能：公告信息,部门,职位,用户,客户分组,客户信息,拜访记录,商机信息,订单信息技术要求:开发语言：JSP前端使用:HTML5，CSS，JSP动态网页技术后端使用SpringBoo......
  • JSP惠民一网通nwzyz--（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表系统功能：户主,新生入证明,迁入户籍,死亡证明,迁出户籍,本地人员流动,外来人员流动,民生业务,评分信息,应急电话开题报告内容一、项目背景随着信息化技术的飞速......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99