Post Jobs

常见的错误及解决方案,文件组备份与还原

图片 10

今日是MySQL的第四篇,难度会稍微加大,加油!

一. 概述

  文件备份是指备份一个或多个文件或文件组中的所有数据。使用文件备份能够只还原损坏的文件,而不用还原数据库的其余部份,从而加快恢复速度。例如,如果数据库由位于不同磁盘上的若干文件组成,在其中一个磁盘发生故障时,只需还原这个故障磁盘上的文件,其它磁盘文件无须还原,这样缩短还原时间。

  在完整恢复模式下,一整套完整文件备份和涵盖所有文件备份的日志备份合起来,等同于一个完整数据库备份。

  1.1 文件备份具有如下优点:
    (1)可以迅速还原损坏的文件。
    (2)当超大型数据库在完整备份下变得难以管理时,文件备份增加了计划和媒体处理的灵活性。

  1.2 文件备份具有不足:
    (1)
与完整数据库备份相比,文件备份的主要缺点是管理较为复杂。如果某个损坏的文件未备份,那么媒体故障可能会导致无法恢复整个数据库。因此必须维护一组完整的文件备份,还必须维护一个或多个日志备份。
    (2)
维护和跟踪这些完整备份是一种耗时的任务,所需空间会超过完整数据库备份所需的空间。

图片 1

开始吧!

二. 文件备份策略  

   使用文件备份和日志备份还原数据库的操作可能比较复杂,因此最好先执行完整数据库备份,并在第一个文件备份开始之前,进行日志备份。下图在t0创建数据库后,立即执行完整数据库备份t1,创建第一个完整数据库备份后,便可以开始执行事务日志备份。事务日志备份按计划的间隔时间执行,文件备份以最适合数据库业务要求的间隔执行,下面是先备份主文件组A,再是辅助文件组B。在完整恢复模式下,恢复一个文件组备份,不但需要恢复文件组备份本身,还需要依次恢复从上一次完整数据库备份后到恢复的目标时间点为止的所有日志备份。如果日志备份数量多,可以考虑再给合差异文件备份,但这样备份计划更加难于管理。

图片 2

 

一、错误日志解析:

1、外键(foreign  key)

三.文件还原  

   当一个大数据库有若干个文件和文件组,如果损坏只是集中在其中一个文件或文件组上,sqlserver只要把坏掉的那个数据文件组重建,肯定可以节约时间。但是数据库的事务修改是会分布在各个数据文件上的,如果用备份只恢复其中一个文件,而其它文件不恢复,那么它们的状态一定会不一致,这样数据库是无法使用的,为了使新恢复的文件能够自动恢复备份以后做的修改,就需要借助事务日志。使用文件备份还原一个或多个受损文件的步骤如下:

  (1) 创建活动事务日志的尾日志备份。
对于离线文件还原,在文件还原之前必须始终先进行一次尾日志备份。对于在线文件还原,在文件还原之后必须始终先进行一次日志备份。因为日志文件一日损坏,文件还原则无法进行。

  (2) 从每个损坏的文件的最新文件备份还原相应文件。

  (3)
针对每个还原的文件,还原最近的差异文件备份(如果有,因为这样还原快)

  (4)
按顺序还原事务日志备份,从时间上最早备份的日志文件开始,到步骤1的尾日志结束。

(1) 【ERROR】1452:无法在外键的表插入参考主键没有的数据

  1.定义:让当前表字段的值在另一个表的范围内选择

四 . 数据初始化  

--第一步: 创建数据库
CREATE DATABASE [FileGroupTest]
go
USE [FileGroupTest]

--第二步:创建文件组
ALTER DATABASE [FileGroupTest] ADD FILEGROUP [FG_Test_Id_01]
ALTER DATABASE [FileGroupTest] ADD FILEGROUP [FG_Test_Id_02]

--第三步:创建文件添加到文件组
ALTER DATABASE [FileGroupTest] ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'D:\Data\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_01]

ALTER DATABASE [FileGroupTest] ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'D:\Data\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_02]

--第四步创建表存放在不同文件上
CREATE TABLE  Student(ID INT,Name varchar(50),[Address] varchar(100)) ON [FG_Test_Id_01]
CREATE TABLE  Teacher(ID INT,Name varchar(50),[Address] varchar(100)) ON [FG_Test_Id_02]
CREATE TABLE  School(ID INT,Name varchar(50),[Address] varchar(100)) ON [PRIMARY]

图片 3

图片 4

-- 养成好习惯先进行完整备份
backup database  [FileGroupTest] to BackupTestDevice

 

  2.语法:

五. 备份演示

-- 给二个表插入数据
insert into Student values(1,'张三','广东深圳')
insert into Teacher values(1,'李四','广东佛山')

-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 给二个表插入数据
insert into Student values(2,'张三2','广东深圳')
insert into Teacher values(2,'李四2','广东佛山')

-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 文件组FG_Test_Id_01备份
backup database [FileGroupTest] file='FG_TestUnique_Id_01_data' to BackupTestDevice

-- 给二个表插入数据
insert into Student values(3,'张三3','广东深圳')
insert into Teacher values(3,'李四3','广东佛山')
-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice
-- 给二个表插入数据
insert into Student values(4,'张三4','广东深圳')
insert into Teacher values(4,'李四4','广东佛山')
-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 文件组FG_Test_Id_02备份
backup database [FileGroupTest] file='FG_TestUnique_Id_02_data' to BackupTestDevice

-- 给主文件组表插入数据
insert into School values(1,'深圳大学','广东深圳南山')
-- 主文件组备份
backup database [FileGroupTest] file='FileGroupTest' to BackupTestDevice

  查看备份集如下图所示:type=F 代表文件组备份类型

图片 5

 

    foreign key(参考字段名)

六. 还原演示

--步骤1:假设文件FG_TestUnique_Id_01_data已损坏,数据库处于在线状态来还原该文件
restore database [FileGroupTest] file='FG_TestUnique_Id_01_data' 
from BackupTestDevice with file=33, norecovery 

  图片 6

--此时FileGroupTest库还能用,但FG_Test_Id_01文件组上的Student表现不能用,此时处于离线状态
select * from FileGroupTest.dbo.Student

  图片 7

--这两个表在不同文件组上,可以使用
select * from FileGroupTest.dbo.School
select * from FileGroupTest.dbo.Teacher

    图片 8
 BACKUP LOG 与 COPY_ONLY
选项将创建仅复制日志备份,该备份不会截断事务日志。
仅复制日志备份对日志链没有任何影响,因此其他日志备份的表现就像仅复制备份不存在一样。

--步骤2:进行新的日志备份,以确保捕获到该文件离线时的点
backup log  [FileGroupTest] to BackupTestDevice with copy_only

  图片 9

--步骤3: 在线还原日志备份
restore log [FileGroupTest] from BackupTestDevice with file=34,norecovery
restore log [FileGroupTest] from BackupTestDevice with file=35,norecovery
restore log [FileGroupTest] from BackupTestDevice with file=38,recovery

--离线的文件组FG_Test_Id_01处于在线状态,Student表可以使用,数据库恢复完成
select * from FileGroupTest.dbo.Student

  图片 10

1452:无法在外键的表插入或更新参考主键没有的数据。由于item_discovery.itemid字段(外键)参考了items.itemid字段(主键),当要在item_discovery表插数据时,如果items表的主键没有对应的数据,则无法插入,报1452错误。此时可以检查参考的表的主键是否有主库对应的数据,如果有,则插入参考的表相应的数据,再开启复制恢复SQL线程。

    references 主表(被参考字段名)

 

    on   delete  级联动作

(2) 【ERROR】1032:删除或更新数据,从库找不到记录

    on   update  级联动作 

 

  以上的语法连在一起看

 

  3.使用规则

 1032:删除或更新从库的数据,从库找不到记录。此时,主库的数据是比从库新的,可以采取从库添加相同的数据在开启复制恢复SQL线程。

    主表、从表字段数据类型要一致

 

    主表被参考字段:主键

(3) 【ERROR】1062:从库插入数据,发生唯一性冲突

  4.示例

 

    表1、缴费信息表(财务)

 

      id  姓名  班级  缴费金额

 1062:从库插入数据,发生唯一性冲突。此时从库已经有相同主键的数据,如果再插入相同主键值的数据则会报错。可以查看主库的改行数据与从库的要插入数据是否一致,如一致则跳过错误,恢复SQL线程,如不一致,则以主库为准,将从库的该行记录删除,再开启复制。

      1  唐伯虎  AD06  300

 

      2  点秋香  AD06  200

如果当前高可用架构为Master-Master,则以下均在从库的操作都必须set
sql_log_bin=0,避免从库执行的语句同步到主库(恢复时以主库的数据为准)。

      3  祝枝山  AD06  100

二、怎么解决问题:

    表2、学生信息表(班主任)

1.临时解决方案(业务运行期间不适宜使用数据对比和修复工具)

      id 姓名 缴费金额

 

      1 唐伯虎 300

【ERROR】1452:

      2 点秋香 260

 

*      3   xxx    xxx*

 

*      ………………..
*

 

*    创建表*

普通主从复制环境

*    表1(主表)*

 

      create table jftab(
      id int primary key,
      name varchar(15),
      class char(5),
      money int
      );

从库:

    插入数据

 

      insert into jftab values
      (1,”唐伯虎”,”AD06″,300),
      (2,”点秋香”,”AD06″,260),
      (3,”祝枝山”,”AD06″,250);

 

    表2(从表)     

主库:

      create table bjtab(
      stu_id int,
      name varchar(15),
      money int,
      foreign key(stu_id) references jftab(id)
      on delete cascade
      on update cascade
      );

查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert或者update的对应的主键值。

    插入数据

 

      insert into bjtab values

 

      (1,”唐伯虎”,300),(2,”点秋香”,260);

查询item_discovery的外键约束c_item_discovery_1参考的表items对应主键值的数据行。

*  5、删除外键*

 

*    alter  table  表名  drop  frreign  key  外键名;*

 

*    查看外键:show  create  table  表名;*

从库:

*  6、级联动作*

在items表插入主库查询出来的数据。

*    cascade*

 

*    数据级联删除、更新(参考字段)*

 

*    restrict(默认)*

基于GTID复制环境

*    从表有相关联记录,不允许主表操作*

与普通主从复制环境处理方式相同。

*    set  null*

 

*    主表删除、更新,从表相关联记录字段值为NULL*

【ERROR】1032:

*  7、已有表添加外键
*

 

*  alter  table  表名  add  for  foreeign  key(参考字段)  references 
主表(被参考字段)
*

 

*  on delete …*

 

*  on  update …*

发生1032可能是delete或者update时从库没有对应数据行,可以分两种情况处理:

2、表的复制

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图