MySQL 备份的三种方式

落日映苍穹つ 2022-06-14 02:45 298阅读 0赞

# 一. 备份的目的 #

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

# 二、备份需要考虑的问题 #

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

# 三、备份的类型 #

## 1.根据是否需要数据库离线 ##

*  **冷备（cold backup）**：需要关mysql服务，读写请求均不允许状态下进行；
 *  **温备（warm backup）**： 服务在线，但仅支持读请求，不允许写请求；
 *  **热备（hot backup）**：备份的同时，业务不受影响。

注：

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

## 2、根据要备份的数据集合的范围 ##

完全备份：full backup，备份全部字符集。

增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。

差异备份：differential backup 上次完全备份以来改变了的数据。

建议的恢复策略：

完全+增量+二进制日志
    完全+差异+二进制日志

## 3、根据备份数据或文件 ##

1.物理备份：直接备份数据文件

优点：
    备份和恢复操作都比较简单，能够跨mysql的版本，
    恢复速度快，属于文件系统级别的
    
    建议：
    不要假设备份一定可用，要测试
    mysql>check tables；检测表是否可用

2.逻辑备份: 备份表中的数据和代码

恢复简单、
    备份的结果为ASCII文件，可以编辑
    与存储引擎无关
    可以通过网络备份和恢复
    缺点：
    备份或恢复都需要mysql服务器进程参与
    备份结果占据更多的空间，
    浮点数可能会丢失精度
    还原之后，缩影需要重建

# 四：备份的对象 #

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

# 五、备份和恢复的实现 #

## 1、利用select into outfile实现数据的备份与还原 ##

mysql> use hqq;//打开数据库
    mysql> select * from tb1 into outfile f:/DB/Backup/tb1.bak;//备份数据
    mysql> delete from tb1;//删除表中所有数据
    mysql> load data local infile 'f:/DB/Backup/tb1.bak' into table tb1;//导入备份文件

注意  
如果出现中文乱码 请加上character set utf8

load data local infile 'f:/DB/Backup/tb1.bak' into table tb1 character set utf8;

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

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

### 2.1 施加读锁的方式 ###

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

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

2.在服务器端书写命令

*  mysql> flush tables with read lock;  
    
    
     *  施加锁，表示把位于内存上的表统统都同步到磁盘上去，然后施加读锁
 *  mysql> flush tables with read lock;  
    
    
     *  释放读锁

但这对于InnoDB存储引擎来讲，虽然你也能够请求道读锁，但是不代表它的所有数据都已经同步到磁盘上，因此当面对InnoDB的时候，我们要使用mysql> show engine innodb status; 看看InnoDB所有的数据都已经同步到磁盘上去了，才进行备份操作。

### 2.2备份的策略 ###

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

1.先给数据库做完全备份

C:\Users\heqianqian>mysqldump -uroot --single-transaction --master-data=2 --databases hqq > f:/DB/Backup/hqq.sql

–single-transaction: 基于此选项能实现热备InnoDB表；因此，不需要同时使用–lock-all-tables；

–master-data=2 记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的

–databases hellodb 指定备份的数据库

2.在数据库更新数据

mysql> create table user(id int,name varchar(100));
    Query OK, 0 rows affected (0.46 sec)
    
    mysql> insert into user(id,name) values(1,'hqq');
    Query OK, 1 row affected (0.08 sec)
    
    mysql> insert into user(id,name) values(2,'mary');
    Query OK, 1 row affected (0.16 sec)
    
    mysql> insert into user(id,name) values(3,'jack');
    Query OK, 1 row affected (0.07 sec)
    
    mysql> insert into user(id,name) values(4,'lucky');
    Query OK, 1 row affected (0.06 sec)

3.进行增量备份

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

打开刚才生成的备份文件 找到下面这一行

-- -- Position to start replication or point-in-time recovery from --
    
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=120;

可以知道日志文件是mysql-bin.000007 备份文件里记录的位置是120

4.查看当前记录的位置

可以看到当前位置是1123

由此可以知道刚才进行更新操作的记录位置是120-1123

5.完成增量备份

mysqlbinlog --no-defaults --start-position=120 --stop-position=1123 /data/mysql-bin.000013 > f:/DB/Backup/hqq_add.sql

6.再对数据库进行更新操作

mysql> drop table t1;
    Query OK, 0 rows affected (0.22 sec)

7.进行二进制文件备份

mysqlbinlog --no-defaults --start-position=1123 --stop-position=1236 /data/mysql-bin.000013 > f:/DB/Backup/hqq_bin.sql

8.关闭日志文件 并且回滚日志

mysql> set sql_log_bin=0;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> flush logs;
    Query OK, 0 rows affected (0.19 sec)