`
lvjun106
  • 浏览: 437892 次
  • 性别: Icon_minigender_1
  • 来自: 芜湖
社区版块
存档分类
最新评论

mysql备份的三种方案(冷备、温备、热备)

 
阅读更多

一、备份的目的

做灾难恢复:对损坏的数据进行恢复和还原
需求改变:因需求改变而需要把数据还原到改变以前
测试:测试新功能是否可用

二、备份需要考虑的问题

可以容忍丢失多长时间的数据;
恢复数据要在多长时间内完; 
恢复的时候是否需要持续提供服务;
恢复的对象,是整个库,多个表,还是单个库,单个表。

三、备份的类型

1、根据是否需要数据库离线

冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;
温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;
热备(hot backup):备份的同时,业务不受影响。

注:

1、这种类型的备份,取决于业务的需求,而不是备份工具
2、MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具

2、根据要备份的数据集合的范围
完全备份:full backup,备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。
差异备份:differential backup 上次完全备份以来改变了的数据。
建议的恢复策略:
完全+增量+二进制日志
完全+差异+二进制日志

3、根据备份数据或文件

物理备份:直接备份数据文件

优点:

备份和恢复操作都比较简单,能够跨mysql的版本,
恢复速度快,属于文件系统级别的

建议:

不要假设备份一定可用,要测试
mysql>check tables;检测表是否可用
逻辑备份: 备份表中的数据和代码

优点:

恢复简单、
备份的结果为ASCII文件,可以编辑
与存储引擎无关
可以通过网络备份和恢复

缺点:

备份或恢复都需要mysql服务器进程参与
备份结果占据更多的空间,
浮点数可能会丢失精度
还原之后,缩影需要重建

四:备份的对象

1、 数据;
2、配置文件;
3、代码:存储过程、存储函数、触发器
4、os相关的配置文件
5、复制相关的配置
6、二进制日志

五、备份和恢复的实现

1、利用select into outfile实现数据的备份与还原
1.1把需要备份的数据备份出来

  1.  
    mysql> use hellodb;       //打开hellodb库
  2.  
    mysql> select * from students;   查看students的属性
  3.  
    mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt' ;   //将年龄大于三十的同学的信息备份出来

注意:

备份的目录路径必须让当前运行mysql服务器的用户mysql具有访问权限

备份完成之后需要把备份的文件从tmp目录复制走,要不就失去备份的目的了

回到tmp目录下查看刚才备份的文件

[root@www ~]# cd /tmp

[root@www tmp]# cat stud.txt

3Xie Yanke53M216

4Ding Dian32M44

6Shi Qing46M5\N

13Tian Boguang33M2\N

25Sun Dasheng100M\N\N

[root@www tmp]#

你会发现是个文本文件。所以不能直接导入数据库了。需要使用load data infile 恢复

回到mysql服务器端,删除年龄大于30的用户,模拟数据被破坏

  1.  
    mysql> delete from students where Age > 30;
  2.  
    mysql> load data infile '/tmp/stud.txt' into table students;

2、利用mysqldump工具对数据进行备份和还原

mysqldump 常用来做温备,所以我们首先需要对想备份的数据施加读锁,

2.1 施加读锁的方式:

1.直接在备份的时候添加选项

--lock-all-tables 是对要备份的数据库的所有表施加读锁

--lock-table 仅对单张表施加读锁,即使是备份整个数据库,它也是在我们备份某张表的时候才对该表施加读锁,因此适用于备份单张表

2、在服务器端书写命令,

复制代码

  1.  
    mysql> flush tables with read lock; 施加锁,表示把位于内存上的表统统都同步到磁盘上去,然后施加读锁
  2.  
    mysql> flush tables with read lock;释放读锁
  3.  
     
  4.  
    但这对于InnoDB存储引擎来讲,虽然你也能够请求道读锁,但是不代表它的所有数据都已经同步到磁盘上,
  5.  
    因此当面对InnoDB的时候,我们要使用
  6.  
    mysql> show engine innodb status;
  7.  
    看看InnoDB所有的数据都已经同步到磁盘上去了,才进行备份操作。

复制代码

2.2备份的策略:

完全备份+增量备份+二进制日志

演示备份的过程;

2.3 先给数据库做完全备份:

  1.  
    [root@www ~]# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql
  2.  
     
  3.  
    --single-transaction: 基于此选项能实现热备InnoDB表;因此,不需要同时使用--lock-all-tables;
  4.  
    --master-data=2 记录备份那一时刻的二进制日志的位置,并且注释掉,1是不注释的
  5.  
    --databases hellodb 指定备份的数据库
  6.  
    然后回到mysql服务器端,

 

 

2.4回到mysql服务器端更新数据

  1.  
    mysql> create table tb1(id int); 创建表
  2.  
    mysql> insert into tb1 values (1),(2),(3); 插入数据,这里只做演示,随便插入了几个数据

2.5先查看完全备份文件里边记录的位置:

  1.  
    [root@www backup]# cat hellodb_2013-09-08.sql | less
  2.  
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684; 记录了二进制日志的位置

2.6 在回到服务器端:

复制代码

  1.  
    mysql> show master status;
  2.  
    显示此时的二进制日志的位置,从备份文件里边记录的位置到我们此时的位置,即为增量的部分
  3.  
    +------------------+----------+--------------+------------------+
  4.  
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  5.  
    +------------------+----------+--------------+------------------+
  6.  
    | mysql-bin.000004 | 15982 | | |
  7.  
    +------------------+----------+--------------+------------------+

复制代码

2.7做增量备份

  1.  
    [root@www backup]# mysqlbinlog --start-position=15694 --stop-position=15982
  2.  
    /mydata/data/mysql-bin.000013 > /backup/hellodb_`date +$F_%H`.sql

2.8再回到服务器

  1.  
    mysql> insert into tb1 values (4),(5); 在插入一些数值
  2.  
    mysql> drop database hellodb; 删除hellodb库

2.9导出这次得二进制日志:

  1.  
    mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013
  2.  
    查看删除操作时二进制日志的位置
  3.  
    # mysqlbinlog --start-position=15982 --stop-position=16176 /mydata/data/mysql-bin.000013 > /tmp/hellodb.sql
  4.  
    //导出二进制日志

2.10先让mysql离线

  1.  
    mysql> set sql_log_bin=0; 关闭二进制日志
  2.  
    mysql> flush logs; 滚动下日志

2.11模拟数据库损坏

mysql> drop database hellodb;

2.12开始恢复数据:

  1.  
    [root@www ]# mysql < /backup/hellodb_2013-09-08.sql
  2.  
    //导入完全备份文件
  3.  
    [root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql
  4.  
    //导入增量备份文件
  5.  
    [root@www ]# mysql< hellodb.sql
  6.  
    //导入二进制文件

验证完成,显示结果为我们预想的那样

注:

1、真正在生产环境中,我们应该导出的是整个mysql服务器中的数据,而不是单个库,因此应该使用--all-databases
2、在导出二进制日志的时候,可以直接复制文件即可,但是要注意的是,备份之前滚动下日志。
3、利用lvm快照实现几乎热备的数据备份与恢复

3.1策略:

完全备份+二进制日志;

3.2准备:

注:事务日志必须跟数据文件在同一个LV上;

3.3创建lvm Lvm的创建这里就不多说了,想了解话点击http://www.jb51.net/LINUXjishu/105937.html

3.4 修改mysql主配置文件存放目录内的文件的权限与属主属组,并初始化mysql

复制代码 代码如下:

 

  1.  
    [root@www ~]# mkdir /mydata/data //创建数据目录
  2.  
    [root@www ~]# chown mysql:mysql /mydata/data //改属组属主
  3.  
    [root@www ~]# cd /usr/local/mysql/ //必须站在此目录下
  4.  
    [root@www mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data //初始化mysql

3.5修改配置文件:

  1.  
    vim /etc/my.cof
  2.  
    datadir=/mydata/data 添加数据目录
  3.  
    sync_binlog = 1 开启此功能

3.6 启动服务

  1.  
    [root@www mysql]# service mysqld start
  2.  
    mysql> set session sql_log_bin=0; 关闭二进制日志
  3.  
    mysql> source /backup/all_db_2013-09-08.sql 读取备份文件

3.7回到mysql服务器:

复制代码

  1.  
    mysql> FLUSH TABLES WITH READ LOCK;
  2.  
    请求读锁 注:不要退出,另起一个终端:
  3.  
    mysql> SHOW MASTER STATUS;
  4.  
    查看二进制文件的位置
  5.  
    +------------------+----------+--------------+------------------+
  6.  
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  7.  
    +------------------+----------+--------------+------------------+
  8.  
    | mysql-bin.000004 | 107 | | |
  9.  
    +------------------+----------+--------------+------------------+
  10.  
    1 row in set (0.00 sec)
  11.  
    mysql> FLUSH LOGS;
  12.  
    建议滚动下日志。这样备份日志的时候就会很方便了

复制代码

3.8导出二进制文件,创建个目录单独存放

  1.  
    [root@www ~]# mkdir /backup/limian
  2.  
    [root@www ~]# mysql -e 'show master status;' > /backup/limian/binlog.txt

3.9为数据所在的卷创建快照:

[root@www ~]# lvcreate -L 100M -s -p r -n mysql_snap /dev/myvg/mydata 

回到服务器端,释放读锁

  1.  
    mysql> UNLOCK TABLES;
  2.  
    [root@www ~]# mount /dev/myvg/mysql_snap /mnt/data
  3.  
    [root@www data]# cp * /backup/limian/
  4.  
    [root@www data]#lvremove /dev/myvg/mylv_snap

3.10更新数据库的数据,并删除数据目录先的数据文件,模拟数据库损坏

1

2

3

4

5

6

7

mysql>  create table limiantb (id int,name CHAR(10));

mysql> insert into limiantb values (1,'tom');

[root@www data]# mysqlbinlog --start-position=187 mysql-bin.000003 > /backup/limian/binlog.sql

[root@www backup]# cd /mydata/data/

[root@www data]#  rm -rf *

[root@www ~]# cp -a /backup/limian/* /mydata/data/

[root@www data]# chown mysql:mysql *

3.11测试

启动服务

复制代码

  1.  
    [root@www data]# service mysqld start
  2.  
    [root@www data]# mysql 登陆测试
  3.  
    mysql> SHOW DATABASES;
  4.  
    mysql> SET sql_log_bin=0
  5.  
    mysql> source/backup/limian/binlog.sql; #二进制恢复
  6.  
    mysql> SHOW TABLES; #查看恢复结果
  7.  
    mysql> SET sql_log_bin=1; #开启二进制日志
  8.  
     
  9.  
    注:此方式实现了接近于热备的方式备份数据文件,而且数据文件放在lvm中可以根据数据的大小灵活改变lvm的大小,备份的方式也很简单。

复制代码

4、基于Xtrabackup做备份恢复

官方站点:www.percona.com

优势:

1、快速可靠的进行完全备份
2、在备份的过程中不会影响到事务
3、支持数据流、网络传输、压缩,所以它可以有效的节约磁盘资源和网络带宽。
4、可以自动备份校验数据的可用性。

安装Xtrabackup

[root@www ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm 

其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得

注意:在备份数据库的时候,我们应该具有权限,但需要注意的是应该给备份数据库时的用户最小的权限,以保证安全性,

4.1前提:

应该确定采用的是单表一个表空间,否则不支持单表的备份与恢复。
在配置文件里边的mysqld段加上

 

innodb_file_per_table = 1

4.2备份策略
完全备份+增量备份+二进制日志
4.3准备个目录用于存放备份数据

[root@www ~]# mkdir /innobackup

4.4做完全备份:

[root@www ~]# innobackupex --user=root --password=mypass /innobackup/

注:

1、只要在最后一行显示 innobackupex: completed OK!,就说明你的备份是正确的。
2、另外要注意的是每次备份之后,会自动在数据目录下创建一个以当前时间点命名的目录用于存放备份的数据,那我们去看看都有什么

 

复制代码

  1.  
    [root@www 2013-09-12_11-03-04]# ls
  2.  
    backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints
  3.  
    hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile
  4.  
    [root@www 2013-09-12_11-03-04]#
  5.  
    xtrabackup_checkpoints :备份类型、备份状态和LSN(日志序列号)范围信息;
  6.  
    xtrabackup_binlog_info :mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
  7.  
    xtrabackup_logfile :非文本文件,xtrabackup自己的日志文件
  8.  
    xtrabackup_binlog_pos_innodb :二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
  9.  
    backup-my.cnf :备份时数据文件中关于mysqld的配置

复制代码

4.5回到mysql服务器端对数据进行更新操作

  1.  
    mysql> use hellodb;
  2.  
    mysql> delete from students where StuID>=24;

4.6增量备份

  1.  
    innobackupex --user=root --password=mypass --incremental /innobackup/--incremental-basedir=/innobackup/2013-09-12_11-03-04/
  2.  
    --incremental 指定备份类型
  3.  
    --incremental-basedir= 指定这次增量备份是基于哪一次备份的,这里是完全备份文件,这样可以把增量备份的数据合并到完全备份中去

4.7第二次增量

先去修改数据

  1.  
    mysql> insert into students (Name,Age,Gender,ClassID,TeacherID) values ('tom',33,'M',2,4);
  2.  
    innobackupex --user=root --password=mypass --incremental /innobackup/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
  3.  
    这里只须要把最后的目录改为第一次增量备份的数据目录即可

复制代码

  1.  
    4.8最后一次对数据更改但是没做增量备份
  2.  
    mysql> delete from coc where id=14;
  3.  
     
  4.  
    4.9把二进制日志文件备份出来,(因为最后一次修改,没做增量备份,要依赖二进制日志做时间点恢复)
  5.  
    [root@www data]# cp mysql-bin.000003 /tmp/
  6.  
     
  7.  
    4.10模拟数据库崩溃
  8.  
    [root@www data]# service mysqld stop
  9.  
    [root@www data]# rm -rf *
  10.  
     
  11.  
    恢复前准备
  12.  
     
  13.  
    4.11对完全备份做数据同步
  14.  
    [root@www ~]# innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/
  15.  
     
  16.  
    4.12对第一次增量做数据同步
  17.  
    innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
  18.  
     
  19.  
    4.13对第二次增量做数据同步
  20.  
    innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-45-53/
  21.  
    --apply-log 的意义在于把备份时没commit的事务撤销,已经commit的但还在事务日志中的应用到数据库
  22.  
     
  23.  
    注:
  24.  
     
  25.  
    对于xtrabackup来讲,它是基于事务日志和数据文件备份的,备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据库文件中的事务,还应该对其做预处理,把已提交的事务同步到数据文件,未提交的事务要回滚。因此其备份的数据库,不能立即拿来恢复。
  26.  
     
  27.  
    预处理的过程:
  28.  
     
  29.  
    首先对完全备份文件只把已提交的事务同步至数据文件,要注意的是有增量的时候,不能对事务做数据回滚,不然你的增量备份就没有效果了。
  30.  
     
  31.  
    然后把第一次的增量备份合并到完全备份文件内,
  32.  
     
  33.  
    以此类推,把后几次的增量都合并到前一次合并之后的文件中,这样的话,我们只要拿着完全备份+二进制日志,就可以做时间点恢复。

复制代码

4.14数据恢复

  1.  
    [root@www ~]# service mysqld stop
  2.  
    [root@www data]# rm -rf * 模拟数据库崩溃
  3.  
    [root@www ~]# innobackupex --copy-back /innobackup/2013-09-12_11-03-04/
  4.  
    --copy-back数据库恢复,后面跟上备份目录的位置

4.15检测:

  1.  
    [root@www ~]# cd /mydata/data/
  2.  
    [root@www data]# chown mysql:mysql *
  3.  
    [root@www data]#service mysqld start

 

检测结果数据正常。

分享到:
评论

相关推荐

    linux基于keepalived的mysql双机热备实现方案(详细过程完整版)

    因此,通过Keepalived实现的MySQL双机热备方案旨在提供无缝切换的高可用性,确保在主服务器故障时,备份服务器能立即接管服务,避免数据丢失或服务中断。 二、Keepalived简介 Keepalived是一款基于VRRP协议的高可用...

    mysql备份 热备 增备1

    在实际应用中,热备份通常是数据库管理员的基本技能之一,而Xtrabackup作为强大的备份工具,它的高效、稳定和灵活性使其成为MySQL备份的首选方案。同时,了解如何正确配置和使用备份工具,以及如何解决备份过程中...

    mysql双机热备方案

    MySQL双机热备方案是一种高可用性架构设计,旨在确保数据库服务在一台服务器故障时能够无缝切换到另一台服务器,从而避免数据丢失和业务中断。以下是对该方案的详细解释: 1. **主主复制配置** 主主复制是双机热备...

    MySQL双机互备热备自动切换.pdf

    本文档将详细介绍一种实现MySQL双机互备热备自动切换的方案,以提高数据库系统的可用性和实时性,有效缩短故障恢复时间。 ### MySQL双机互备热备自动切换方案 该方案主要基于MySQL的主从复制结构,通过配置主库和...

    mysql实现双机热备

    MySQL 双机热备是一种高可用性解决方案,确保在一个系统发生故障时,另一个系统能够立即接管服务,保证数据的连续性和一致性。该技术主要依赖于MySQL的复制功能,通过实时捕获主服务器的更新日志并应用到备用服务器...

    mysql双机热备实现方案【可测试】

    MySQL双机热备是一种高可用性(High Availability, HA)解决方案,旨在确保关键业务服务的连续性。它通过在两台服务器之间建立实时数据同步来实现,当主服务器发生故障时,备用服务器能够无缝接管服务,避免服务中断。...

    mysql数据库双机热备与主从热备

    **主从热备**(Master-Slave Replication)是一种常见的MySQL数据同步方案。在这种架构中,一个数据库服务器作为主服务器(Master),接收所有的写操作,而其他服务器作为从服务器(Slave),定期从主服务器同步数据...

    MySQL双机热备

    **MySQL双机热备**是一种通过配置使得两台MySQL服务器之间能够实时同步数据的技术方案,从而实现高可用性和故障转移的能力。该技术可以确保在一台服务器发生故障时,另一台服务器能够立即接管服务,以减少数据丢失和...

    数据库同步热备方案(武汉某医院)

    本方案针对武汉某医院的HIS系统,旨在提供一种实时或近乎实时的数据备份方法,以便在主数据库出现故障时,能迅速切换至备用数据库,减少数据丢失和业务中断的风险。 数据库同步热备的基本概念是通过特定的技术手段...

    MySQL 项目备份方案建议

    ### MySQL 项目备份方案建议 #### 一、MySQLDump备份 **Mysqldump介绍** Mysqldump作为MySQL自带的一种客户端工具,在数据库备份领域有着广泛的应用。通过使用mysqldump,用户能够创建出一个包含SQL命令的文本...

    mysql双机热备

    - 常见的双机热备方案包括基于MySQL原生的主从复制、Galera集群等。 - 根据业务需求和技术背景选择合适的方案。 2. **主从复制配置:** - 在主服务器上启用二进制日志。 - 配置从服务器连接到主服务器的信息。 ...

    MySQL数据库备份方案研究 (1).pdf

    数据备份是防止数据丢失的重要手段,分为冷备和热备。冷备通常需要关闭数据库服务,进行数据文件的复制,但这种方式在实际应用中较少。热备则可以在数据库运行时进行,分为逻辑热备份和物理热备份。逻辑热备份工具如...

    mysql双机热备.ppt

    MySQL双机热备是一种高可用性的解决方案,用于确保数据库服务在一台服务器出现故障时能无缝切换到另一台服务器,避免单点故障导致的业务中断。该方案通常结合Linux的心跳(HA)软件来监测服务器状态,并实现IP的自动...

    Legato双机热备软件培训

    双机热备是一种重要的系统冗余技术,旨在确保关键服务的持续可用性。Legato AAM( Allegro Application Manager)是一款双机热备软件,它提供了高可用性和故障切换功能,确保在主服务器出现故障时,备份服务器能够...

    MySQL从入门到实战学习教程之13数据备份.pptx

    2. 冷备、温备与热备:根据备份时数据库服务的状态,可以分为冷备(数据库完全停止服务时的备份)、温备(数据库服务降级运行时的备份)、热备(数据库服务正常运行时的备份)。热备可以进一步细分为完全备份和增量...

Global site tag (gtag.js) - Google Analytics