在Centos中的mysql的备份与恢复

于 2024-08-29 23:37:04 发布
阅读量83 收藏 3
点赞数 2
分类专栏： mysql 文章标签： mysql mysql备份
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45024631/article/details/141685683
版权

1.物理备份

  • 冷备份：关闭数据库时进行
  • 热备份：数据库运行时进行，依赖于数据库日志文件
  • 温备份：数据库不可写入但可读的状态下进行

2.逻辑备份

  • 对数据库的表或者对象进行备份

3.备份策略

  • 完全备份：每次都备份完整的数据库
  • 差异备份：只备份上一次完全备份之后的更新数据
  • 增量备份：每次备份只备份上一次完全备份或增量备份之后的更新数据

4.备份方法和工具

  • 物理冷备份：备份时数据库关闭，直接打包数据库文件；备份速度块，恢复时也简单
  • 备份工具：mysqldump或者mysqlhoocopy
    mysqldump备份速度慢(如果数据库超过100G，不建议使用)
  • 使用二进制日志进行增量备份
  • 使用第三方工具：XtraBackup

一、物理备份

使用物理冷备份
systemctl stop mysqld  #先关闭主机的mysql服务
mkdir mysqlbackup     #创建一个文件夹之后将数据库压缩包放入其中
tar -zcvf /usr/local/src/mysqlbackup/mysql_full_$(date -d "-1 day" +%Y%m%d).tar.gz /var/lib/mysql/data/  #备份 /var/lib/mysql/data下的所有

scp /usr/local/src/mysqlbackup/mysql_full_20240828.tar.gz 192.168.222.213:/usr/local/src/mysql/  #使用scp命令将数据库拷贝到要备份的主机上

之后在备份主机上进行压缩

systemctl stop mysqld  #先停止mysql服务
tar -xf mysql_full_20240828.tar.gz #解压 
mv /data/ /var/lib/mysql/ #再将压缩后的数据库复制到目标目录下即可
使用工具mysqldump进行冷备份

可以将指定的库、表导出为SQL脚本

1.完整备份一个或者多个完整的数据库（包含其中的表）
  mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
  mysqldump -u root -ppassword  --databases exam_system > /usr/local/src/mysqlbackup/exam_system.sql
  遇到的问题：mysqldump: [Warning] Using a password on the command line interface can be insecure
  解决办法：在my.cnf中添加
  [mysqldump]
  user=root
  password=password
  然后不需要使用账号密码即可进行备份：
  mysqldump  --databases exam_system > /usr/local/src/mysqlbackup/exam_system.sql

如果不加 --databases ,只备份库中的表，不会备份库

2.备份整个数据库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
mysqldump --all-databases > /usr/local/src/mysqlbackup/all.sql

3.备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
mysqldump  exam_system answer question > /usr/local/src/mysqlbackup/answerAndquestion.sql
备份成sql语句后进行恢复
1.通过source命令恢复
  登录备份主机的mysql
  mysql -uroot -p
  执行命令：mysql>  source /usr/local/src/mysql/exam_system.sql;
2.通过mysql命令恢复
  mysql -u 用户名 -p[密码] < 库备份脚本的路径
  mysql -uroot -ppassword < /usr/local/src/mysql/exam_system.sql
当备份的sql中只包含表的备份，而不是库的备份时，恢复的时候需要指定数据库，并且目标库必须存在
mysql -uroot -ppassword exam_system < /usr/local/src/mysqlbackup/answerAndquestion.sql

二、增量备份（需要用到mysql的二进制日志）

1.开启二进制日志功能：
	vim /etc/my.cnf
	[mysqld]
	log-bin=mysql-bin
	binlog_format = MIXED				
	#可选，指定二进制日志(binlog)的记录格式为 MIXED
	server-id = 1
	
	#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
通过刷新二进制日志文件实现增量备份（和上面同理，在my.cnf中添加 [mysqladmin] 的账号密码在此处就不需要使用账号密码了）
	mysqladmin  flush-logs
	查看二进制文件内容 
	mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件（绝对路径）
	mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/lib/mysql/binlog.000001

2.进行增量备份操作


一般恢复手段
  2.1.先对数据库exam_system中的answer表做完全备份
 	mysqldump exam_system answer > /usr/local/src/mysqlbackup/answer.sql
  2.2.在answer中添加新数据
  2.3.再刷新二进制日志文件
 	mysqladmin flush-logs
  2.4.新增数据

在这里插入图片描述

  2.5.再次刷新二进制日志文件
  2.6.进行备份，备份的是倒数第二个二进制日志文件，不是最新的那个
	mv -f /var/lib/mysql/binlog.000012 /usr/local/src/mysqlbackup/binlog.000012_backup
  2.7.将其拷贝到目的主机上
	scp /usr/local/src/mysqlbackup/binlog.000012_backup 192.168.222.213:/var/lib/mysql
  2.8.通过管道符导入的方式进行增量备份
 	mysqlbinlog --no-defaults  /usr/local/src/mysqlbackup/binlog.000012_backup  | mysql -uroot -ppassword



断点恢复手段
  2.1.删除一条id=7的数据
  2.2 #查看二进制文件 
	mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/src/mysqlbackup/binlog.000012_backup
	
	  查询结果
	  # The proper term is pseudo_replica_mode, but we use this compatibility alias
	# to make the statement usable on server versions 8.0.24 and older.
	/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
	/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
	DELIMITER /*!*/;
	# at 4
	#240829 22:20:58 server id 1  end_log_pos 126 CRC32 0x5a25dbb2 	Start: binlog v 4, server v 8.0.39 created 240829 22:20:58
	# at 126
	#240829 22:20:58 server id 1  end_log_pos 157 CRC32 0xb00ab440 	Previous-GTIDs
	# [empty]
	# at 157
	#240829 22:21:43 server id 1  end_log_pos 236 CRC32 0x11b51759 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes	original_committed_timestamp=1724941303404462	immediate_commit_timestamp=1724941303404462   transaction_length=330
	/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
	# original_commit_timestamp=1724941303404462 (2024-08-29 22:21:43.404462 CST)
	# immediate_commit_timestamp=1724941303404462 (2024-08-29 22:21:43.404462 CST)
	/*!80001 SET @@session.original_commit_timestamp=1724941303404462*//*!*/;
	/*!80014 SET @@session.original_server_version=80039*//*!*/;
	/*!80014 SET @@session.immediate_server_version=80039*//*!*/;
	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
	# at 236
	#240829 22:21:43 server id 1  end_log_pos 318 CRC32 0xc8f08fde 	Query	thread_id=17	exec_time=0	error_code=0
	SET TIMESTAMP=1724941303/*!*/;
	SET @@session.pseudo_thread_id=17/*!*/;
	SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
	SET @@session.sql_mode=1168113696/*!*/;
	SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
	/*!\C utf8mb4 *//*!*/;
	SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
	SET @@session.lc_time_names=0/*!*/;
	SET @@session.collation_database=DEFAULT/*!*/;
	/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
	BEGIN
	/*!*/;
	# at 318
	#240829 22:21:43 server id 1  end_log_pos 392 CRC32 0x792677a7 	Table_map: `exam_system`.`answer` mapped to number 148
	# has_generated_invisible_primary_key=0
	# at 392
	#240829 22:21:43 server id 1  end_log_pos 456 CRC32 0x59ebb7ee 	Write_rows: table id 148 flags: STMT_END_F
	### INSERT INTO `exam_system`.`answer`
	### SET
	###   @1=6
	###   @2='因数'
	###   @3=NULL
	###   @4='因数'
	###   @5=2
	###   @6=NULL
	# at 456
	#240829 22:21:43 server id 1  end_log_pos 487 CRC32 0xe2a746e6 	Xid = 1415
	COMMIT/*!*/;
	# at 487
	#240829 22:22:03 server id 1  end_log_pos 566 CRC32 0x80070a3c 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1724941323438391	immediate_commit_timestamp=1724941323438391   transaction_length=348
	/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
	# original_commit_timestamp=1724941323438391 (2024-08-29 22:22:03.438391 CST)
	# immediate_commit_timestamp=1724941323438391 (2024-08-29 22:22:03.438391 CST)
	/*!80001 SET @@session.original_commit_timestamp=1724941323438391*//*!*/;
	/*!80014 SET @@session.original_server_version=80039*//*!*/;
	/*!80014 SET @@session.immediate_server_version=80039*//*!*/;
	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
	# at 566
	#240829 22:22:03 server id 1  end_log_pos 648 CRC32 0xd97b61d5 	Query	thread_id=17	exec_time=0	error_code=0
	SET TIMESTAMP=1724941323/*!*/;
	BEGIN
	/*!*/;
	# at 648
	#240829 22:22:03 server id 1  end_log_pos 722 CRC32 0x066adb4a 	Table_map: `exam_system`.`answer` mapped to number 148
	# has_generated_invisible_primary_key=0
	# at 722
	#240829 22:22:03 server id 1  end_log_pos 804 CRC32 0x23e50863 	Write_rows: table id 148 flags: STMT_END_F
	### INSERT INTO `exam_system`.`answer`
	### SET
	###   @1=7
	###   @2='多音字，少银子'
	###   @3=NULL
	###   @4='少银子'
	###   @5=1
	###   @6=NULL
	# at 804
	#240829 22:22:03 server id 1  end_log_pos 835 CRC32 0xeb4d3e35 	Xid = 1418
	COMMIT/*!*/;
	# at 835
	#240829 22:22:07 server id 1  end_log_pos 879 CRC32 0x29dc8064 	Rotate to binlog.000015  pos: 4
	SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
	DELIMITER ;
	# End of log file
	/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
	/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

  2.3.基于位置恢复，恢复id=7的数据
   mysqlbinlog --no-defaults --start-position='648'  /var/lib/mysql/binlog.000014 | mysql -uroot -ppassword
   或者
   mysqlbinlog --no-defaults --start-position='648' --stop-position='835' /var/lib/mysql/binlog.000014 | mysql -uroot -ppassword

  2.4.基于时间恢复
  mysqlbinlog --no-defaults --start-datetime='2024-08-29 22:22:03' /var/lib/mysql/binlog.000014 | mysql -uroot -ppassword

MySQL 的日志默认保存位置为 /usr/local/mysql/data
vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
#指定日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log					


##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin				
#也可以 log_bin=mysql-bin

##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5												
#设置超过5秒执行的语句被记录，缺省时为10秒


mysql -u root -p
#查看通用查询日志是否开启
show variables like 'general%';									

#查看二进制日志是否开启
show variables like 'log_bin%';									
#查看二进制日志的格式
show variables like '%binlog_format%';
#查看慢查询日功能是否开启
show variables like '%slow%';									
#查看慢查询时间设置
show variables like 'long_query_time';							

#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;				




[root@localhost mysql]# cat backupmysqlbinlog.sh 
#!/bin/bash
#增量备份mysql数据库
TODAY=$(date +%Y%m%d)
YESTERDAY=$(date -d '-1 day' +%Y%m%d)
DATA_HOME=/var/lib/mysql/
USERNAME=root
PASSWORD=password

##备份二进制日志索引文件 binlog.index
/usr/bin/cp -f $DATA_HOME/binlog.index /opt/backup/binlog-$TODAY.index

##判断是否存在前一天的日志索引文件，如果存在则通过反向查询获取今天生成的二进制日志文件
if [ -f /opt/backup/binlog-$YESTERDAY.index ]
then
  COUNT=$(cat /opt/backup/binlog-$TODAY.index | grep -v `/opt/backup/binlog-$YESTERDAY.index` | awk -F '/' '{print $2}')
else
  COUNT=$(cat /opt/backup/binlog-$TODAY.index | awk -F '/' '{print $2}')
fi

#刷新二进制日志文件
mysqldump -u"$USERNAME" -p"$PASSWORD" flush-logs &> /dev/null
#使用循环备份当天的二进制日志
for BINLOG in $COUNT
do
  /usr/bin/mv -f "$DATA_HOME/$BINLOG" "/opt/backup/$BINLOG-$TODAY"
done

https://blog.csdn.net/m0_71593537/article/details/133240696原文链接

卑微的码蚁
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
centos7 mysql dump还原_CentOS 7.6使用mysqldump备份恢复MariaDB
weixin_35678674的博客
02-05 750
一、mysqldump简介：mysqldump是MariaDB自带的客户端逻辑备份工具，通过mysql协议连接至mysqld，将需要备份的数据查询出来，并转换成对应的insert语句，当需要恢复这些数据时，只要执行这些insert语句，适用于数据量较小的场景。逻辑备份是指备份SQL语句，在恢复时执行备份SQL，从而实现数据库数据的重现。备份完成后生成的SQL文件可以使用文本编辑工具直接修改。二、m...
CentOS中mysql数据库mysqldump备份与恢复
liuwei_q的博客
06-18 1709
CentOS中mysql数据库的备份与导入 1、备份命令 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql 例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql 2、备份压缩 导出的数据有可能比较大，不好备份到远程，这时候就需要进行压缩 格式：mysqldump -h主机名 -P端口 -
centos7中MySQL备份还原策略
weixin_53308294的博客
07-23 1640
可能是因为日志文件冲突：如果在删除log_bin配置项后，MySQL已经生成了一些binlog日志文件，再重新添加log_bin配置项时，可能会导致日志文件冲突。注：mysqlbinlog mysql-bin.000002 --start-position=340 --stop-position=490 -r pos1.sql 是基于节点。server_id=158 ----》server_id一般是IP的最后一段。character-set-server：指定MySQL服务器的默认字符集。
在CentOS中的MySQL备份与恢复
weixin_45950635的博客
10-05 463
MySQL备份与恢复 1.掌握数据库备份类型与方法 2.掌握MySQL完全备份恢复操作 3.掌握MySQL增量备份恢复操作 简介 随着自动化办公与电子商务的不断发展，企业对于信息系统的依赖性越来越高，而数据库在信息系统中担任着非常重要的角色，尤其一些对数据可靠性要求非常高行业，如银行，证券，电信等，如果发生意外宕机或数据丢失，其损失是非常严重的。为此数据库管理员必须针对具体的业务要求制定详细的数据库备份与灾难恢复的策略，并通过模拟故障对每种可能的情况进行严格的测试，从而保证数据的可靠性。 上一章我们已经学
centos系统mysql数据库差异备份与恢复
weixin_67353555的博客
07-19 1249
在恢复之前，你需要准备备份数据。跟增量备份不同的是，不需要准备所有的备份，只需要准备第一次的全备份和最后一次差异备份即可。注意：在实际操作中，请不要直接在命令行中包含密码，而是通过配置文件或环境变量来管理密码。显示completed OK！就成功了，若是报错可以点击以下链接博客查询。首先，你需要为 MySQL 数据库创建一个全备份、这是差异备份的基础。在进行了全备份之后，你可以根据需要创建多个差异备份。如果数据库中已有默认以外的数据，直接跳过。差异备份只包含全备份以来发生更改的数据。
centos 7 mysql备份,Centos7Mysql完全备份与恢复
weixin_42533910的博客
04-01 1094
Centos7Mysql完全备份与恢复Centos7Mysql完全备份与恢复Centos7完全备份与恢复物理冷备份与恢复mysqldump备份与恢复实验材料一台Centos7已安装Mysql数据库，并开启3306端口物理冷备份与恢复1.关闭mysql数据库[[emailprotected] ~]# systemctl stop mysqld(关闭mysqld服务)[[emailprotecte...
CentOS7 MySQL 增量备份与恢复
2401_83916355的博客
04-01 962
针对以上面试题，小编已经把面试题+答案整理好了。
CentOS7下 MySQL定时自动备份的实现方法
09-08
在这个配置文件中，分别定义了`mysqldump`和`mysql`两个部分的参数，分别对应于备份和恢复操作时的连接信息。 在赋予脚本执行权限后，你可以手动运行一次`mysql_backup.sh`来测试备份过程是否成功。执行脚本后，...
centos中mysql备份数据库脚本分享
09-10
在CentOS系统中，对MySQL数据库进行备份是维护数据安全的重要环节。这篇脚本分享提供了一个自动化的解决方案，适用于那些需要定期备份数据库的网站管理员。下面我们将深入探讨这个脚本的工作原理及其涉及的关键知识...
linux centos MySql备份和恢复方案.zip
08-14
本资料包"linux centos MySql备份和恢复方案.zip"显然是为了帮助用户理解和实施在Linux CentOS环境中MySQL的备份与恢复策略。 首先，我们来谈谈MySQL备份的重要性。数据是任何业务的生命线，定期备份可以防止意外...
mysql-day02
m0_64370841的博客
08-29 139
【代码】mysql-day02。
华为云 GaussDB 数据库和 MySQL 数据库的区别
2401_86488984的博客
08-28 554
深入搜索。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 359
MySQL——子查询（5）带比较运算符的子查询
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 154
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
阿里龙晰系统上将yum安装的myql_8.0.36升级到mysql_8.4.0的过程
老杜的博客
08-29 263
本章内容介绍了在阿里龙晰系统上修复 Oracle MySQL 安全漏洞CVE-2024-21176、CVE-2024-21177、CVE-2024-21179的过程。将Mysql从8.0.36升级到8.4.0
MySQL基本查询语言
最新发布
st03zy的博客
08-29 477
ifnull(colName,value): 如果colName对应的值不为空，就使用本身的值，如果为null,使用value.3. 在分组查询时，只有分组字段可以写在select子句中,其他不是分组的字段，不应该写在select子句中，无意义。有的时候，需要分组统计一些，最大值，最小值，平均值，和，总数之类的这样的信息，此时需要分组查询。reg:查询工资大于1600并且小于2500的所有员工的编号，姓名，职位，工资。# 案例：查询每个部门中的每种职位的最高工资，最低工资，工资之和。
MySQL做题笔记
qq_44736340的博客
08-25 507
sql语法
前后端分离项目实战-通用管理系统搭建（前端Vue3+ElementPlus，后端Springboot+Mysql+Redis）第七篇：菜单和路由动态绑定
分享式获得也是一种学习的态度
08-26 733
每个人都有惰性，但不断学习是好好生活的根本，共勉！绿竹入幽径，青萝拂行衣。——《下终南山过斛斯山人宿置酒》
【MySQL数据库管理问答题】第1章 MySQL 简介
Songyaxuan075118的博客
08-24 442
数据库的性能监控、备份和恢复策略、安全管理、以及故障排除方法 使用 MySQL 作为核心业务支撑平台 MySQL 8.0 版本 了解 MySQL 社区动态和技术演进的资源和渠道
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

卑微的码蚁

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值