微信扫一扫登录网站如何做,京东做代码的网站,市场营销与网络营销,开源网站建设工具目录
一、在数据库表中#xff0c;分三次录入学生考试成绩
1.1先创建库#xff0c;创建表#xff0c;完成三次数据的录入
1.2首次录入成绩后#xff0c;做该表的完全备份
1.3第二次插入后 做增量备份
1.4第三次插入后 做增量备份
二、模拟数据丢失#xff0c;并使用…目录
一、在数据库表中分三次录入学生考试成绩
1.1先创建库创建表完成三次数据的录入
1.2首次录入成绩后做该表的完全备份
1.3第二次插入后 做增量备份
1.4第三次插入后 做增量备份
二、模拟数据丢失并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩
2.1用完全备份恢复获取一班的人的成绩
2.2陈铭的成绩恢复
方法一
方法二
2.3付杰的成绩恢复
三、数据库上云迁移的方案
1.1 方案一使用脱机冷备份
1.2 方案二热迁移
四、脚本增量备份
五、温故而知新 某学校进行期中考试要求录入各班考试成绩
为保证数据的可靠性录入学生成绩需要做数据库备份
为了测试备份数据是否可用模拟数据丢失故障进行数据还原需求描述 需求描述
在数据库表中分三次录入学生考试成绩
首次录入成绩后做该表的完全备份后两次成绩的录入之后分别作增量备份
模拟数据丢失并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩 一、在数据库表中分三次录入学生考试成绩 1.1先创建库创建表完成三次数据的录入 create table class(name varchar(10) unique key,class varchar(4),id int primary key,chinese int,math int,English int,sci int); insert into class values(张三,一班,20170822,110,105,92,235);
insert into class values(李四,一班,20170820,95,115,110,260);
insert into class values(王五,一班,20170818,95,103,108,270);
insert into class values(赵六,一班,20170816,100,109,112,265);1.2首次录入成绩后做该表的完全备份 cd /opt
mkdir backup
mysqldump -uroot -p123123 nanjing class /opt/backup/nanjing_class.sql
ls /opt/backup/
nanjing_class.sql 这是MySQL自带的备份和恢复工具支持逻辑热备份通过SQL语句的形式进行保存
[rootlocalhost opt]#cat /opt/backup/nanjing_class.sql
-- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86_64)
--
-- Host: localhost Database: nanjing
-- ------------------------------------------------------
-- Server version 5.7.17/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET OLD_TIME_ZONETIME_ZONE */;
/*!40103 SET TIME_ZONE00:00 */;
/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;
/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;
/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;
/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;--
-- Table structure for table class
--DROP TABLE IF EXISTS class;
/*!40101 SET saved_cs_client character_set_client */;
/*!40101 SET character_set_client utf8 */;
CREATE TABLE class (name varchar(10) DEFAULT NULL,class varchar(4) DEFAULT NULL,id int(11) NOT NULL,chinese int(11) DEFAULT NULL,math int(11) DEFAULT NULL,English int(11) DEFAULT NULL,sci int(11) DEFAULT NULL,PRIMARY KEY (id),UNIQUE KEY name (name)
) ENGINEInnoDB DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table class
--LOCK TABLES class WRITE;
/*!40000 ALTER TABLE class DISABLE KEYS */;
INSERT INTO class VALUES (赵六,一班,20170816,100,109,112,265),(王五,一班,20170818,95,103,108,270),(李四,一班,20170820,95,115,110,260),(张三,一班,20170822,110,105,92,235);
/*!40000 ALTER TABLE class ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2024-03-30 23:52:25 1.3第二次插入后 做增量备份 insert into class values(李宁,二班,20170824,92,98,105,235);
insert into class values(陈铭,二班,20170826,111,107,96,204); 开启二进制日志 log-error/usr/local/mysql/data/mysql_error.log
general_logON
general_log_file/usr/local/mysql/data/mysql_general.log
log-binmysql-bin
slow_query_logON
slow_query_log_file/usr/local/mysql/data/mysql_slow_query.log
long_query_time5
binlog_format MIXED mysqladmin -uroot -p123123 flush-logs
mv mysql-bin.000001 /opt/backup/mysql-bin.000001.$(date %F_%T)
[rootlocalhost /opt/backup]#mysqlbinlog --no-defaults --base64-outputdecode-rows -v 指定二进制日志备份文件 为什么空的是由于没有在插入数据前开启二进制日志所以切记切记一定要在开启二进制日志后才可以插入数据不然二进制日志识别不到哦 开启二进制日志后先把之前错误的数据删除然后我们在重复新插入数据 查看二进制日志
[rootlocalhost data]#mysqlbinlog --no-defaults --base64-outputdecode-rows -v /usr/local/mysql/data/mysql-bin.000002
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#240331 10:57:36 server id 1 end_log_pos 123 CRC32 0x68d67970 Start: binlog v 4, server v 5.7.17-log created 240331 10:57:36
# Warning: this binlog is either in use or was not closed properly.
# at 123
#240331 10:57:36 server id 1 end_log_pos 154 CRC32 0x53f83afb Previous-GTIDs
# [empty]
# at 154
#240331 11:35:29 server id 1 end_log_pos 219 CRC32 0x8e1dc01f Anonymous_GTID last_committed0 sequence_number1
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 219
#240331 11:35:29 server id 1 end_log_pos 304 CRC32 0xffd43c1e Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856129/*!*/;
SET session.pseudo_thread_id4/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;
SET session.sql_mode1437073414/*!*/;
SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;
/*!\C utf8 *//*!*/;
SET session.character_set_client33,session.collation_connection33,session.collation_server33/*!*/;
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
BEGIN
/*!*/;
# at 304
#240331 11:35:29 server id 1 end_log_pos 421 CRC32 0x31a6b5eb Query thread_id4 exec_time0 error_code0
use nanjing/*!*/;
SET TIMESTAMP1711856129/*!*/;
delete from class where name李宁
/*!*/;
# at 421
#240331 11:35:29 server id 1 end_log_pos 452 CRC32 0x57775191 Xid 8
COMMIT/*!*/;
# at 452
#240331 11:37:44 server id 1 end_log_pos 517 CRC32 0xb2bce275 Anonymous_GTID last_committed1 sequence_number2
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 517
#240331 11:37:44 server id 1 end_log_pos 602 CRC32 0x826c35bf Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856264/*!*/;
BEGIN
/*!*/;
# at 602
#240331 11:37:44 server id 1 end_log_pos 719 CRC32 0x4d30d3d9 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856264/*!*/;
delete from class where name李宁
/*!*/;
# at 719
#240331 11:37:44 server id 1 end_log_pos 805 CRC32 0x0effbaa6 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856264/*!*/;
COMMIT
/*!*/;
# at 805
#240331 11:38:09 server id 1 end_log_pos 870 CRC32 0x531b5239 Anonymous_GTID last_committed2 sequence_number3
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 870
#240331 11:38:09 server id 1 end_log_pos 955 CRC32 0x7e377af7 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856289/*!*/;
BEGIN
/*!*/;
# at 955
#240331 11:38:09 server id 1 end_log_pos 1072 CRC32 0x7f128ff4 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856289/*!*/;
delete from class where name陈铭
/*!*/;
# at 1072
#240331 11:38:09 server id 1 end_log_pos 1103 CRC32 0xe6e93731 Xid 10
COMMIT/*!*/;
# at 1103
#240331 11:38:34 server id 1 end_log_pos 1168 CRC32 0xd340a8d1 Anonymous_GTID last_committed3 sequence_number4
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 1168
#240331 11:38:34 server id 1 end_log_pos 1253 CRC32 0xb2d9ce6b Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856314/*!*/;
BEGIN
/*!*/;
# at 1253
#240331 11:38:34 server id 1 end_log_pos 1399 CRC32 0x62bc5441 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856314/*!*/;
insert into class values(李宁,二班,20170824,92,98,105,235)
/*!*/;
# at 1399
#240331 11:38:34 server id 1 end_log_pos 1430 CRC32 0xd85c6c10 Xid 12
COMMIT/*!*/;
# at 1430
#240331 11:38:39 server id 1 end_log_pos 1495 CRC32 0x0674a180 Anonymous_GTID last_committed4 sequence_number5
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 1495
#240331 11:38:39 server id 1 end_log_pos 1580 CRC32 0xed5f16ee Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856319/*!*/;
BEGIN
/*!*/;
# at 1580
#240331 11:38:39 server id 1 end_log_pos 1727 CRC32 0xd5dc66ea Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856319/*!*/;
insert into class values(陈铭,二班,20170826,111,107,96,204)
/*!*/;
# at 1727
#240331 11:38:39 server id 1 end_log_pos 1758 CRC32 0xd4119983 Xid 13
COMMIT/*!*/;
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/
接下来开始做增量备份 [rootlocalhost backup]#mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/backup/mysql-bin.000002.2024-03-31_11:58:58 [rootlocalhost backup]#mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/backup/mysql-bin.000002.2024-03-31_11:58:58
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#240331 10:57:36 server id 1 end_log_pos 123 CRC32 0x68d67970 Start: binlog v 4, server v 5.7.17-log created 240331 10:57:36
# at 123
#240331 10:57:36 server id 1 end_log_pos 154 CRC32 0x53f83afb Previous-GTIDs
# [empty]
# at 154
#240331 11:35:29 server id 1 end_log_pos 219 CRC32 0x8e1dc01f Anonymous_GTID last_committed0 sequence_number1
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 219
#240331 11:35:29 server id 1 end_log_pos 304 CRC32 0xffd43c1e Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856129/*!*/;
SET session.pseudo_thread_id4/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;
SET session.sql_mode1437073414/*!*/;
SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;
/*!\C utf8 *//*!*/;
SET session.character_set_client33,session.collation_connection33,session.collation_server33/*!*/;
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
BEGIN
/*!*/;
# at 304
#240331 11:35:29 server id 1 end_log_pos 421 CRC32 0x31a6b5eb Query thread_id4 exec_time0 error_code0
use nanjing/*!*/;
SET TIMESTAMP1711856129/*!*/;
delete from class where name李宁
/*!*/;
# at 421
#240331 11:35:29 server id 1 end_log_pos 452 CRC32 0x57775191 Xid 8
COMMIT/*!*/;
# at 452
#240331 11:37:44 server id 1 end_log_pos 517 CRC32 0xb2bce275 Anonymous_GTID last_committed1 sequence_number2
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 517
#240331 11:37:44 server id 1 end_log_pos 602 CRC32 0x826c35bf Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856264/*!*/;
BEGIN
/*!*/;
# at 602
#240331 11:37:44 server id 1 end_log_pos 719 CRC32 0x4d30d3d9 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856264/*!*/;
delete from class where name李宁
/*!*/;
# at 719
#240331 11:37:44 server id 1 end_log_pos 805 CRC32 0x0effbaa6 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856264/*!*/;
COMMIT
/*!*/;
# at 805
#240331 11:38:09 server id 1 end_log_pos 870 CRC32 0x531b5239 Anonymous_GTID last_committed2 sequence_number3
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 870
#240331 11:38:09 server id 1 end_log_pos 955 CRC32 0x7e377af7 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856289/*!*/;
BEGIN
/*!*/;
# at 955
#240331 11:38:09 server id 1 end_log_pos 1072 CRC32 0x7f128ff4 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856289/*!*/;
delete from class where name陈铭
/*!*/;
# at 1072
#240331 11:38:09 server id 1 end_log_pos 1103 CRC32 0xe6e93731 Xid 10
COMMIT/*!*/;
# at 1103
#240331 11:38:34 server id 1 end_log_pos 1168 CRC32 0xd340a8d1 Anonymous_GTID last_committed3 sequence_number4
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 1168
#240331 11:38:34 server id 1 end_log_pos 1253 CRC32 0xb2d9ce6b Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856314/*!*/;
BEGIN
/*!*/;
# at 1253
#240331 11:38:34 server id 1 end_log_pos 1399 CRC32 0x62bc5441 Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856314/*!*/;
insert into class values(李宁,二班,20170824,92,98,105,235)
/*!*/;
# at 1399
#240331 11:38:34 server id 1 end_log_pos 1430 CRC32 0xd85c6c10 Xid 12
COMMIT/*!*/;
# at 1430
#240331 11:38:39 server id 1 end_log_pos 1495 CRC32 0x0674a180 Anonymous_GTID last_committed4 sequence_number5
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 1495
#240331 11:38:39 server id 1 end_log_pos 1580 CRC32 0xed5f16ee Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856319/*!*/;
BEGIN
/*!*/;
# at 1580
#240331 11:38:39 server id 1 end_log_pos 1727 CRC32 0xd5dc66ea Query thread_id4 exec_time0 error_code0
SET TIMESTAMP1711856319/*!*/;
insert into class values(陈铭,二班,20170826,111,107,96,204)
/*!*/;
# at 1727
#240331 11:38:39 server id 1 end_log_pos 1758 CRC32 0xd4119983 Xid 13
COMMIT/*!*/;
# at 1758
#240331 11:58:28 server id 1 end_log_pos 1805 CRC32 0xb246b44f Rotate to mysql-bin.000003 pos: 4
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;
[rootlocalhost backup]# 1.4第三次插入后 做增量备份 insert into class values(付杰,二班,20170828,115,118,116,268);
insert into class values(郭尚,二班,20170830,111,99,80,259); 二、模拟数据丢失并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩
模拟整个数据全部丢失然后分析需要恢复一班的成绩那么就是将完全备份进行恢复需要恢复陈铭和付杰的成绩那么就是分别对第2和第3次的增量数据做断点恢复 2.1用完全备份恢复获取一班的人的成绩 数据恢复
mysql -uroot -p123123 nanjing /opt/backup/nanjing_class.sql
注意路径哦 2.2陈铭的成绩恢复
mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/backup/mysql-bin.000002.2024-03-31_11:58:58
查看断点信息 方法一 mysqlbinlog --no-defaults --start-position1580 /opt/backup/mysql-bin.000002.2024-03-31_11:58:58|mysql -uroot -p123123 方法二 mysqlbinlog --no-defaults --start-position1580 --stop-position1758 /opt/backup/mysql-bin.000002.2024-03-31_11:58:58|mysql -uroot -p123123 2.3付杰的成绩恢复
mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/backup/mysql-bin.000003.2024-03-31_12:13:22 数据恢复
mysqlbinlog --no-defaults --start-datetime2024-03-31 12:07:14 --stop-datetime2024-03-31 12:07:58 /opt/backup/mysql-bin.000003.2024-03-31_12:13:22|mysql -uroot -p123123 三、数据库上云迁移的方案
1.1 方案一使用脱机冷备份 冷迁移----物理冷备 首先需要关闭数据库服务 然后用tar对数据库进行打包 再将tar包传给另一台主机 然后将其解压再恢复 1.2 方案二热迁移 热迁移---阿里云自带的热迁移工具DTS数据传输服务 四、脚本增量备份
#!/bin/bash
##增量备份脚本
DIRLOGS/var/log/mysql
LOGS_HOME/usr/local/mysql/data
TODAY$(data %F)
YESTERDAY$(date -d -1 day %F)
USERroot
PASSWORDabc123#先判断收集备份文件的目录是否存在不存在则创建目录
[ -d $DIRLOGS ]||mkdir -p $DIRLOGS##备份二进制日志的索引文件到指定目录并加入时间标记
\cp $LOGS_HOME/mysql_bin.index $DIRLOGS/mysql_bin.index.$TODAY#先判断昨天的二进制日志的索引文件是否存在存在则作为过滤条件过滤出今天需要备份的二进制日志文件名称
if [ -f $DIRLOGS/mysql_bin.index.$YESTERDAY ];thenBINGLOG$(cat $DIRLOGS/mysql_bin.index.$TODAY|grep -v $(cat $DIRLOGS/mysql_bin.index.$YESTERDAY)|awk -F/ {print $2})elseBINGLOG$(cat $DIRLOGS/mysql_bin.index.$TODAY|awk -F/ {print $2})
fi##刷新生成新的二进制日志文件便于收集最新的二进制日志
mysqladmin -u$USER -p$PASSWORD flush-logs /dev/null##使用for循环对所有的新产生的binlog文件进行备份
for i in $BINGLOG
do
mv $LOGS_HOME/$i $DIRLOGS/$i.$TODAY
done 五、温故而知新
备份方法
物理备份直接对数据库的物理文件数据文件、日志文件等进行备份
逻辑备份对数据库的库和表对象以SQL语言的形式导出进行备份备份策略
完全备份每次备份都备份完整的库或者表数据
差异备份只备份上一次完全备份后的更新数据
增量备份每次备份只备份上一次完全备份或增量备份后的更新数据数据库上云迁移 冷迁移 物理冷备 先关闭数据库再打包备份恢复热迁移 阿里云 DTS完全备份
物理冷备先关闭数据库使用tar命令打包备份数据库的数据目录和文件 mysql/data/mysqldump逻辑热备
mysqldump -uXXX -pXXX --databases 库1 [库2 ....] XXX.sql 备份一个或多个库及库中所有的表数据
mysqldump -uXXX -pXXX --all-databases XXX.sql 备份所有的库
mysqldump -uXXX -pXXX 库名 XXX.sql 备份指定库中的所有的表数据不包含库对象
mysqldump -uXXX -pXXX 库名 表1 [表2 ....] XXX.sql 备份指定库中的一个或多个表数据不包含库对象xtrabackup物理热备完全恢复
先登录到数据库再执行 source XXX.sql 如果sql文件是只备份了表数据的文件需要先创建库并 use 切换库后再执行mysql -uXXX -pXXX XXX.sql cat XXX.sql | mysql -uXXX -pXXX 恢复库
mysql -uXXX -pXXX 库名 XXX.sql cat XXX.sql | mysql -uXXX -pXXX 库名 恢复表增量备份
通过刷新二进制日志间接实现增量备份
mysqladmin -uXXX -pXXX flush-logs查看二进制日志内容
mysqlbinlog --no-defaults --base64-outputdecode-rows -v BINLOG使用二进制日志增量恢复
mysqlbinlog --no-defaults BINLOG | mysql -uXXX -pXXX断点恢复
基于位置点恢复
mysqlbinlog --no-defaults --start-position开始位置点 --stop-position结束位置点 BINLOG | mysql -uXXX -pXXX基于时间点恢复
mysqlbinlog --no-defaults --start-datetimeYYYY-mm-dd HH:MM:SS --stop-positionYYYY-mm-dd HH:MM:SS BINLOG | mysql -uXXX -pXXX如果需要恢复到某条sql语句之前的所有数据就stop在这个语句的位置点或时间点之前
如果需要恢复某条sql语句及其之后的所有数据就从这个语句的位置点或时间点开始start
