当前位置: 澳门新濠3559 > 数据库 > 正文

我们按时定点来备份数据,在开发或者测试环境

时间:2019-12-09 01:05来源:数据库
二、基于LVM的备份还原 1、分离数据和binlog到各自的逻辑卷 2、锁定所有表 MariaDB [(none)] flush tables with read lock; 3、记录二进制日记文件及事件位置 MariaDB [(none)] flush logs; #刷新日记mysql -

二、基于LVM的备份还原

1、分离数据和binlog到各自的逻辑卷

图片 1

2、锁定所有表

MariaDB [(none)]> flush tables with read lock;

3、记录二进制日记文件及事件位置

MariaDB [(none)]> flush logs;     #刷新日记
mysql -uroot -p -e 'show master logs' > /app/pos.log    #将日记位置记录到文件

4、创建快照

lvcreate -n mysqldata-snapshot -s -p r -L 3G /dev/vg0/mysqldata

图片 2

5、释放锁,修改部分数据

MariaDB [(none)]> unlock tables;
MariaDB [(none)]> create database lvsceshi;

图片 3

6、挂载快照卷,执行数据备份

mkdir /mnt/snap
mount -o nouuid,norecovery /dev/vg0/mysqldata-snapshot /mnt/snap
mkdir /backups
cp -a /mnt/snap/* /backups/

7、备份完成后,删除快照卷

umount /mnt/snap/
lvremove /dev/vg0/mysqldata-snapshot

图片 4

8、破坏数据库

systemctl stop mysqld
rm -rf /data/mysqldata/*

9、还原

cp -a /backups/* /data/mysqldata/
systemctl start mysqld

10、binlong还原到最新状态

MariaDB [(none)]>flush tables with read lock;    #锁定所有表
cp -a /data/binlongs/mysql-bin.00000{4..6} /app/
cd /app/
mysqlbinlog --start-position=385 mysql-bin.000004 > bin.sql
mysqlbinlog mysql-bin.000005 >> bin.sql
mysqlbinlog mysql-bin.000006 >> bin.sql
MariaDB [(none)]> set sql_log_bin=0;      #临时关闭二进制日记
MariaDB [(none)]> unlock tables;         #解锁
MariaDB [(none)]> source /app/bin.sql     #还原
MariaDB [(none)]> set sql_log_bin=1;      #开启二进制日记

图片 5

图片 6

图片 7

图片 8

 

前言

对于我们运维来说,在mysql数据库领域,别的不说,最起码要会两大技能!

第一大技能:备份与还原

第二大技能:主从异步

关于这两大技能我们先来说说第一个

 

数据库存储引擎:InnoDB

验证

1、删除的表看看有了没

图片 9

2、在表删除后其他的表改的记录还在不

图片 10

OK

还原成功

欢迎补充--

简单测试了一下,应该可以达到目的,没有做完整的测试,原理很简单,双层while循环,就是通过循环每一个数据库对应的物理文件,动态生成一个创建数据库快照的语句
此种方式仅适应于开发或者测试环境数据库的轻量级备份,不适应于生产环境。

一、恢复误删除的数据库到最新状态

1、备份数据库

mysqldump -A -F -uroot -p --master-data=2 --single-transaction > /backups/all-`date +%F_%T`.sql

图片 11

图片 12

2、对数据库误删除

MariaDB [reset_gq]> drop database reset_gq;

图片 13

3、对数据库添加新数据

MariaDB [(none)]> create database kuc ;
MariaDB [(none)]> use kuc;
MariaDB [kuc]> create table cun_dg(id int);

图片 14

4、发现问题对数据库进行恢复

MariaDB [(none)]> flush tables with read lock;     #全局锁,数据库只读
MariaDB [(none)]> flush logs;       #刷新日志

图片 15

cp /www/server/data/mysql-bin.000015 /backups/     #拷贝一份修改
mysqlbinlog --start-position=366 mysql-bin.000015 > bin.sql

我们按时定点来备份数据,在开发或者测试环境的数据库中。修改bin.sql,找到误操作命令注释或删除

 图片 16

MariaDB [(none)]> set sql_log_bin=0;    #暂停二进制日记
MariaDB [(none)]> unlock tables;          #解锁

cp /backups/all-2018-02-27_17:50:26.sql /backups/mysql.sql   #改名
MariaDB [(none)]> source /backups/mysql.sql;    #恢复数据库到备份状态
MariaDB [reset_gq]> source /backups/bin.sql;     #恢复数据库到最新状态
MariaDB [kuc]> set sql_log_bin=1;      #启动二进制日记

5、查询恢复是否完整

图片 17

 

1、完全备份

mysqldump -pcentos -A -F --master-data=2 --single-transaction |gzip > /data/all-`date +%F`.sql.gz

备注:

-A:备份全部数据库

-F:切换二进制日志

--master-data=2:记录了基于二进制那个位置备份的,这个点之后是新数据,备份结束点,主从改为1

--single-transaction:保证数据的一致性

通过定时对开发环境的数据库打快照,一旦出现误删数据的情况,可以根据最近的快照中的数据还原误删的数据,同时可以根据实际情况,删除创建的已过期的快照。

启用binlong

1、完全备份

mysqldump -pcentos -A -F --master-data=2 --single-transaction |gzip > /data/all-`date +%F`.sql.gz

为了切实地解决这个问题,同时又能够以最小的代价来实现,可以定时给测试库打快照的这种轻量级的方式来解决误删除数据的问题。
一是快照占用的空间并不大,
二是可以通过轻量级的方式去还原单个表的数据
花了点时间,写了个全实例下用户数据库的生成快照操作,可以安排一个定时任务来完成定时对数据库生成快照的方式来做一个轻量级的备份。
因此就可以方便地解决执行update或者delete操作的时候“忘了加where条件”误删数据或者表的情况。

8、还原增量备份

1)合并00004以及之后的二进制日志文件

mysqlbinlog  --start-position=385 mysql-bin.000004 > /data/binlogs.sql
mysqlbinlog   mysql-bin.000005 >> /data/binlogs.sql
mysqlbinlog   mysql-bin.000006 >> /data/binlogs.sql

2)从合并的二进制日志文件中找到误操作的指令删除或注释

图片 18

3)scp传送到57目标主机还原

mysql <binlogs.sql

。。。

图片 19

1、scp传送

1)传送完全备份文件到目标主机

scp all-2018-08-03.sql 192.168.43.57:

2)传送上面图中00005文件以及之后的二进制文件到目标主机

scp mysql-bin.000005 192.168.43.57:

这是开发或者测试环境的一个痛点,相信多数人都经历过,
当面对开发人员过来说“我刚才delete表的时候忘了加where条件,结果全删了,能不能还原一下?”这个问题的时候,
实则有点尴尬,只能告知无法还原,sqlserver不支持闪回功能,又因为测试数据库没有备份,确实无能为力。
此时,开发人员往往会报以鄙视+无奈的眼神,作为冒牌DBA,确实难以相助。

备份与还原

备份:我们按时定点来备份数据,当下数据最值钱,所以我们要确保数据的安全。

平常我们都是7天一大备,三天两头一小备,也就是说,一周来一个完全备份,1、2、天来一个增量或差异备份。

确保那天服务器宕机或误操作,能恢复过来。

还原:当服务器负重过量会导致宕机,或有时候我们误操作,删除了某张重要的数据表等等,这时候就要用到我们之前备份的数据来恢复。

简单说下备份的类型,

冷备:读写操作均不可进行

温备:读操作可执行;但写操作不可执行

热备:读写操作均可执行

MyISAM:温备,不支持热备
InnoDB: 都支持

简单说下常用的备份工具,

LVM的快照:先加锁,做快照后解锁,几乎热备;借助文件系统工具进行备份

mysqldump:逻辑备份工具,适用所有存储引擎,温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份

xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份

备注:以上除了xtrabackup,都是基于完整备份包以及二进制日志来恢复数据库的,二进制日志记录了增删改语句的操作指令,拿过来重放,

 

6、干净系统还原

1)停止服务

systemctl stop mairadb

2)删除所有mysql数据库数据

rm -rf /var/lib/mysql/

3)确保用户不能访问服务器 配置文件加上

skip-networking

4)启动服务

 

 

2、解压并查看

gzip -d all-2018-08-03.sql.gz
less all-2018-08-03.sql

图片 20

备注:记住这个文件以及数字,一会还原的时候要用

--生成全实例下的数据库快照
create proc [dbo].[CreateSnapshotForBackUp]
(
    --数据库快照文件位置
    @p_FilePath varchar(500),
    --保留最近N个小时之内创建的快照,单位为小时
    @p_RetainTime int
)
as
begin
    set nocount on;

    declare @strSql            varchar(2000)
    declare @strDatetime    varchar(20)
    declare @strDBFileName    varchar(200)

    set @strDatetime =  replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')


    if object_id('tempdb..#databaseName') is not null
    begin 
        drop table #databaseName
    end

    select name 
    into #databaseName  
    from  sys.databases where database_id>6 and source_database_id is null 


    declare @databaseName varchar(200);
    declare @databaseCnt int;
    select @databaseCnt = count(1) from #databaseName;

    while @databaseCnt>0
    begin

        select top 1 @databaseName = name from #databaseName

        if object_id('tempdb..#dbFiles') is not null
        begin 
            drop table #dbFiles
        end

        select concat('(','name = ' , name , ',FileName = ''',@p_FilePath,name,'_',@strDatetime,'.ss'' )' ) as strFileName
        into #dbFiles
        from sys.sysaltfiles 
        where  dbid=db_id(@databaseName) and status = 2

        DECLARE @dbFileCnt int = 0;
        SELECT @dbFileCnt = COUNT(1) FROM  #dbFiles
        while @dbFileCnt>0
        begin
            select top 1 @strDBFileName = strFileName from #dbFiles;
            set @strSql=CONCAT(@strSql,',',@strDBFileName,char(10));
            delete top (1) from #dbFiles;
            select @dbFileCnt = count(1) from #dbFiles;
        end

        set @strSql=stuff(@strSql,1,1,'')

        set @strSql = CONCAT('create database ' ,@databaseName,'_',@strDatetime, char(10),
                             ' on ' , char(10), @strSql
                             ,'as snapshot of ' , @databaseName)


        begin try
            print @strSql
            exec(@strSql)
            select @databaseName+'snapshot create successful:'+@databaseName+@strDatetime
            set @strSql = ''
        end try
        begin catch
            select 'snapshot create fail'
            throw
        end catch

        delete top (1) from #databaseName
        select @databaseCnt = count(1)  from #databaseName
    end


    --删除过期的数据库快照
    begin try
        if object_id('tempdb..#snapshotname') is not null
            drop table #snapshotname

        select name 
        into #snapshotname 
        from sys.databases 
        where source_database_id is not null and create_date<dateadd(hh,-@p_RetainTime,getdate());

        declare @cnt int = 0;
        declare @strDBName varchar(200) = ''
        declare @strDropDatabase varchar(max) =  ''
        select @cnt = count(1) from #snapshotname
        while @cnt>0
        begin
            select top 1 @strDBName = name from #snapshotname;
            set @strDropDatabase = 'drop database ' +@strDBName;
            print @strDropDatabase
            exec (@strDropDatabase);
            delete top (1) from #snapshotname;
            select @cnt = count(1) from #snapshotname
        end

    end try
    begin catch
        select N'snapshot delete fail'
        throw
    end catch

end

5、发现问题并及时禁止用户写权限(只能读)

flush tables with read lock;

备注:现在只能root用户可以读写其他用户是不能修改数据的

图片 21

。。。

在开发或者测试环境的数据库中,经常会发现有开发或者测试人员误删除表或者数据的情况,
对于开发或者测试库,一般都没有安排定时的备份任务去备份数据库,
一方面是由于存储资源有限,不太可能给开发或者测试环境准备大量的存储空间,
二是必要性不是很强,开发或者测试库的数据库对象变化太多,通过还原备份的方式又有可能冲掉其最近新建的数据库对象。
但是不得不面对的问题就是个别人在执行update或者delete操作的时候“忘了加where条件”这种事情的发生。

2、模拟误删除表前的修改

insert students (name,age)values('gaoda1hao',20);

。。。

备注:这期间加了好多数据,可能二进制文件已经满了,进行了切换

 

 

实话讲,已经完全忘记了T-SQL中游标的语法了,通过while循环临时表的方式,也可以达到游标循环的效果,并且这种语法逻辑结构上更清晰简便,根本不会忘记,呵呵。

3、修改students表(新数据和日志)

1)没修改前

图片 22

2)加记录

insert students (name,age)values('gaoda1hao',20);

图片 23

3)查看现在的二进制变化

图片 24

4)假设现在这台mysql服务器宕机崩溃了,下面开始还原

。。。

 

备份阶段

 

2、57主机来还原

注意:还原的时候要禁止除了你之外的所有用户的访问。

停止mysql服务,在my.cnf配置文件中可以加上上面说到一项

1)开启服务前提下并完全备份数据包

mysql < all-2018-08-03.sql

备注:现在只还原到了,备份时的状态,下面再次还原备份后到宕机这段时候发生的操作

2)导入二进制日志

mysqlbinlog  --start-position=385 /root/mysql-bin.000005 > /app/binlog.sql

备注:把00005中从385开始的指令语句导入到一个文件中

mysql < /app/binlog.sql

备注:导入二进制记录的指令

。。。

 

4、模拟误删除数据表

drop table students;

备注:删除后还么有发现,接着增加其他表的数据

insert teachers (name,age)values('gaoda3',30);

建议:建议二进制日志和数据文件分开存放

1、关闭网络链接,只监听本地端口访问,(在备份还原的时候用)

skip-networking=1

2、禁止主机名解析,ip地址访问的时候,禁止反向解析(建议加上)

skip_name_resolve = on

3、每个表单独使用一个表空间存储表的数据和索引(建议加上)

innodb_file_per_table = on

4、开启并指定二进制文件存放位置

log_bin=/...

。。。

验证

1、看表存在不

图片 25

2、看表数据正不正确

图片 26

OK 还原成功

 

实战

3、模拟二进制文件的切换

flush logs;
#

接着增加几条记录

insert students (name,age)values('gaoda2hao',22);

7、还原完全备份

1)解压完全备份包

gzip -d all-2018-08-03.sql.gz

2)查看解压的文件确定位置

less all-2018-08-03.sql

图片 27

3)把完整备份传送到57目标主机还原

mysql <all-2018-08-03.sql

准备

1、准备两台主机,我用.17充当mysql主服务器,.57来还原用。

2、17主mysql数据库有以下几个表

图片 28

3、目录

/data/mysq/:下存放数据库数据

/app/logs/:下存放二进制日志

。。。

下面再来演示一个案例-误删除表的恢复

比如我们在某天的12点中做了完全备份,在下一次做完全备份中间有一天执行了删除某张表,删除后等了几个小时才发现表删除了,

在这种场景下,来恢复数据,下面来模拟下。

模拟顺序:完全备份--用户对表的数据修改--删除某张表--未发现用户继续对其他表操作--当访问被删除的表时发现--

准备工作和上面一样,下面进入正题

前几步和前面一样

还原阶段

上面规划的是用57这台主机来还原,首先这台主机必须是干净的mysql数据库

编辑:数据库 本文来源:我们按时定点来备份数据,在开发或者测试环境

关键词: