# MySQL备份与恢复

> 来源：梦飞idc云平台 | 原文：https://www.mfisp.com/9797.html | 发布：2022-09-07 | 更新：2022-09-07 | 分类：建站教程、投稿分享

### 1 备份概览

 

按备份**方法**分类：

 
 
- **Hot Backup**（热备）

 

在线备份，不影响数据库运行 
- **Cold Backup**（冷备）

 

离线备份，通过拷贝数据库物理文件 
- **Warm Backup**（温备）

 

在线进行，但需要加读锁保证数据一致性 

 

按备份**文件内容**分类：

 
 
- *逻辑备份*

 

备份后的内容是可读的，通常形式为SQL语句的文本文件，或是表内的实际数据

 

优势：呈现直观，适用于升级、迁移等工作，缺点：恢复用时较长 
- *裸文件备份*

 

拷贝数据库的物理文件，不关心数据库的运行状态，通常这类备份恢复的时间较短 

 

按备份**数据库的内容**分类：

 
 
- *完全备份*：对当前数据库的完整备份 
- *增量备份*：在完全备份的基础上，对更新的数据进行备份 
- *日志备份*：指的是对数据库二进制日志的备份 

 

### 2 冷备

 

备份内容：数据表结构定义文件(*.frm)、共享表空间文件(ibdata)、独立表空间文件(*.ibd)、重做日志文件(ib_logfile)，建议增加配置文件(my.cnf)

 

优势：

 
 
- 备份简单，仅需拷贝相关文件 
- 备份文件有利于在不同操作系统、不同MySQL版本之间恢复 
- 恢复简单，仅需把文件恢复到指定位置 
- 恢复速度快，无需执行SQL语句，无需重建索引 

 

劣势：

 
 
- 备份文件较大 
- 跨平台、操作系统、软件版本可能会出现问题 

 

### 3 逻辑备份

 

#### 3.1 mysqldump

 

mysqldump -uroot -p [arguments] > file_name  
#arguments参数  
--all-databases #备份所有数据库  
--databases dbx #备份指定数据库  
--single-transaction dbx #备份数据库的整体架构，事务控制保证备份的一致性  
--lock-tables #一般用于MyISAM，备份时仅可读  
--lock-all-tables #对所有架构所有表上锁  
--add-drop-databases #在create前先运行drop，需配合--alldatabases使用  
#通过转存的文件建立一个salve replication  
#value=1 转存文件中记录CHANGE MASTER语句  
#value=2 CHANGE MASTER语句写成注释  
--master-data[=value]   
--events #备份事件调度器  
--routines #备份存储过程和函数  
--triggers #备份触发器  
--hex-blob #将binary、varbinary、blog、bit列类型备份为16进制的格式  
--tab=path #产生tab分割的数据文件，可使用--fields-terminated-by改变分隔符  
--where #导出给定条件的数据

 

#### 3.2 SELECT … INTO OUTFILE

 

SELECT [column1],[column2] ...  
INTO  
#导出的文件名称需拥有mysql用户权限且文件不存在  
OUTFILE 'filename'  
{#fields or columns  
#列分隔符  
TERMINATED BY 'string'  
#对于字符串的包含符  
[OPTIONALLY] ENCLOSED BY 'char'  
#转译符  
ESCAPED BY 'char'  
}  
{#lines  
STARTING BY 'string'  
TERMINATED BY 'string'  
}  
FROM TABLE WHERE ...

 

#### 3.3 逻辑备份的恢复

 

mysqldump备份的文件为逻辑SQL语句，可用以下两种方法恢复

 

#在shell中执行  
mysql -uroot -p < xxx.sql  
#在MySQL交互命令行执行  
mysql> source xxx.sql

 

#### 3.4 LODA DATA INFILE

 

通过mysqldump --tab或select into outfile导出的数据需通过以下的命令导入

 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'filename'  
[REPLACE | IGNORE]  
INTO TABLE tb_name  
[CHARACTER SET charset_name]  
#[FIELDS | COLUMNS]  
TERMINATED BY 'string'  
[OPTIONALLY] ENCLOSED BY 'char'  
ESCAPED BY 'char'  
#[LINES]  
STARTING BY 'string'  
TERMINATED BY 'string'  
#[IGNORE NUMBER LINES]  
col_name or user_var  
SET col_name = expr

 

使用LOAD DATA INFILE导入需拥有FILE权限

 

mysql> load data infile 'xx.txt' into table a;

 

mysql> load data infile 'xx.txt' into table a;

 

### 4 binlog备份

 

在配置文件启动binlog日志服务

 

#查看  
mysqlbinlog mysqlbin-xxx.xxx  
#还原  
mysqlbinlog binlog.000001 | mysql -uroot -p test

 

因binlog为逻辑备份日志，可在MySQL命令行窗口使用source命令导入

 

也可设置偏移量指定二进制日志的位置，以跳过某些不想被恢复的语句

 

mysqlbinlog --start-position=10000 --stop-position=12000 | mysql -uroot =p test

 

也可使用–start-datetime和–stop-datetime

 

### 5 热备

 

#### 5.1 Ibackup

 

备份InnoDB表原理：

 
 
- 记录备份开始的重做日志文件LSN 
- 拷贝共享表空间文件和独立表空间文件 
- 拷贝后记录重做日志的检查点LSN 
- 拷贝备份期间的重做日志 

 

优点：

 
 
- 在线备份，不阻塞SQL语句 
- 备份性能好，通过复制数据库物理表空间文件和重做日志文件 
- 支持压缩备份 
- 跨平台支持 

 

恢复流程：

 
 
- 恢复表空间文件 
- 应用重做日志文件恢复InnoDB存储引擎表 

 

#### 5.2 Xtrabackup

 

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品

 

##### 5.2.1 全量备份

 
 
- 备份 
- #备份  
/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --password=pass --host=127.1 --slave-info --tmpdir=/mysql/bak/full/ /mnt/sdfs/xxxx

 
 
  - 恢复 
  - #1.停库，清空data目录  
mysqladmin -uroot -h127.1 -P3306 -p'123456' shutdown;  
rm -rf /mysql/data/*;

 

#2.恢复-应用日志  
/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --apply-log --user=root --password=pass --host=127.1 /mnt/sdfs/***

 

#3.恢复-拷贝文件  
/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --password=pass --host=127.1 --copy-back /mnt/sdfs/***

 

#4.文件修改权限  
chown -R mysql:mysql /mysql

 

#5.启动实例  
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

 

#6.[从库]从库需重新连接master  
change master to  
 master_host='192.168.100.20',  
 master_user='repl',  
 master_port=3306,  
 master_password='123456',  
 master_log_file='mysql-bin.000001',  
 master_log_pos=307;

 

#7.[从库]从库启动复制进程  
start slave; 启动连接

 

##### 5.2.2 增量备份

 

增量备份依赖于全备份，xtrabackup通过比较全备的终点与当前的LSN，生成区间内的备份文件，备份时也存在redo日志监控线程，备份过程中的增加的LSN也会写入日志中 
  - /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --user=root --host='127.0.0.1' --password='123456' --incremental /root/back --incremental-basedir=/opt/back/2022-05-13_17-46-53

 

可进行多次增备，恢复需依次准备 
  - #准备全备份日志  
/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --apply-log --redo-only /opt/back/2022-05-13_17-33-14  
#准备增备日志  
/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --apply-log --redo-only /opt/back/2022-05-13_17-33-14 --incremental-dir=/opt/back/2022-05-13_17-33-14  
#再次准备整体  
/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --apply-log /opt/back/2022-05-13_17-33-14

 

#### 5.3 快照备份

 

基于LVM对分区执行快照工作，Linux在硬盘和分区上建立一个逻辑层提高分区管理的灵活性，将离散的磁盘分区连接成一个整块的卷组，形成存储池

 

LVM使用写时复制技术创建快照，创建时仅拷贝元数据，不存在数据操作，创建过程较快。创建成功后，原始卷上有写操作时快照会跟踪原始卷的变化，将数据在改变之前拷贝到快照预留空间中，称之写时复制

 

### 6 复制

 

#### 6.1 复制原理

 

复制是MySQL提供的一种高可用、高性能的方案，用于建立大型应用，步骤分为三步：

 
 
    - 主服务器将数据更新到二进制日志中 
    - 从服务器把主服务器的而二进制日志拷贝到自己的中继日志中 
    - 从服务器重做中继日志，将更新应用到自己的数据库中 

 

同步的过程是异步的，基本上也是实时进行的，取决于主服务器的压力

 

从服务器有两个线程，

 
 
    - I/O线程：读取主服务器的二进制日志，并保存为中继日志 
    - SQL线程：复制执行中继日志 

 

#### 6.2 快照+复制的备份架构

 

复制用于备份的主要功能：

 
 
    - 数据分布 
    - 读取负载均衡 
    - 数据库备份 
    - 高可用与故障转移 

 

为解决数据误操作的问题，可以在从服务器建立分区快照，避免复制对误操作的处理能力

 

![](https://www.mfisp.com/wp-content/uploads/2022/01/13e1e49bb26b66e_1_post.png)

---
本页由 梦飞idc云平台（https://www.mfisp.com）提供。站点导航见 https://www.mfisp.com/llms.txt