mysql ---- innodb-4-备份和恢复

8 备份和恢复

8.1 概述

分类：（根据备份方法、备份后的文件、备份数据库的内容分类）

备份的方法：

1. Hot Backup：热备（online backup 在线备份）
2. Cold Backup：冷备（offline backup 离线备份）
3. Warm Backup：温备（同样是在线备份，只不过是通过加全局锁来保证数据的一致性）

备份后的文件分类：

1. 逻辑备份

   指备份后的文件内容是可读的，通常是文本文件，内容一般是 sql 语句，或者是表内实际的数据

   如：mysqldump 和SELECT * INTO OUTFILE的方法

2. 裸文件备份

   指拷贝数据库的物理文件（数据库可以在运行状态（需要工具），也可以停止）

备份数据库的内容分类：

1. 完全备份
2. 增量备份
3. 日志备份

8.2 冷备

冷备非常简单，只需要备份mysql数据库的 frm 文件、共享表空间文件、独立表空间文件（*.ibd）、重做日志文件（也可以加上 my.cnf 文件）

优点：

1. 备份简单，只需要拷贝文件
2. 备份文件易于在不同的操作系统、不同 mysql 版本上进行恢复
3. 恢复简单，只需要拷贝到指定的位置
4. 速度快，不需要执行任何 sql 和重建索引

缺点：

1. 冷备文件比逻辑备份的文件大很多
2. 不总是轻易的跨平台。（mysql 版本、文件大小写敏感、浮点数格式等问题）

8.3 逻辑备份

8.3.1 mysqldump

语法：

mysqldump [arguments] > file_name;

# 备份所有数据库
mysqldump --all-databases > dump.sql;

# 备份指定数据库
mysqldump --databases db1 db2 > dump.sql;

# 对 test 这个架构备份 (--single-transaction 用来保证数据的一致性，用于引擎为 Innodb 的)
mysqldump --single-transaction test > test_backup.sql;

一些重要的参数：

1. --single-transaction： 在备份开始前，先执行START TRANSACTION命令，以此来保证数据一致性，该参数只对 InnoDB 引擎有效。
2. --lock-tables(-l)：依次锁住每个架构下的所有表，一般用于 MyISAM 引擎，备份时只能进行读取操作。
3. --add-drop-databases：在 CREATE DATABASES 前先执行 DROP DATABASES（该参数一般和--all-databases或者--databases一起使用）

8.3.2 SELECT ... INTO OUTFILE

该语句也是逻辑备份的方法，将一张表中的数据导出。

SELECT [column 1],[column 2] ...
INTO 
OUTFILE 'file_name'
[
  {fields|columns}											--- 列
  [TERMINATED BY 'string']              ---每个列的分隔符
  [[OPTIONALLY] ENCLOSED BY 'char']     ---对于字符串的包含符
  [ESCAPED BY 'char']										--- 转义符
]
[
  LINES																	--- 行
  [STARTING BY 'string']								--- 每行开始符号
  [TERMINATED BY 'string']							--- 每行的结束符号
]
FROM TABLE WEHRE ....;

-- 如果上述参数没有指定，则默认以下参数：
-- FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'
-- LINES TERMINATED BY '
' STARTING BY ''


-- 

select * into outfile '/root/a.txt' from a;
-- 默认分隔符

8.3.3 逻辑备份的恢复

mysqldump 恢复操作比较简单，因为都是 sql 语句，因此直接执行这个文件就可以了

# 
mysql -uroot -p < test_backup.sql

或者

-- 通过 source 命令恢复
> mysql source /home/test_backup.sql

mysqldump 可以恢复数据库，可以导出存储过程、触发器、时间、数据，但是不能导出视图，因此对于存在视图的，需要单独处理（1.导出视图定义；2。保存视图定义的 frm 文件）

8.3.4 LOAD DATA INFILE

若是通过 mysqldump 或者 SELECT ... INTO OUTFILE 导出的数据需要恢复时，可以通过 LOAD DATA INFILE命令进行导入

load data infile '/home/a.sql' into table a;
--通常为了加快导入，忽略外键的检查

set @@foreign_key_checks=0;
load data infile '/home/a.sql' into table a;
set @@foreign_key_checks=1;

8.3.5 mysqlimport

mysqlimport 是 MysqL 数据库提供的一个命令程序，本质上还是 LOAD DATA INFILE命令。

-- 使用两个线程，导入两个文件
mysqlimport --use-threads=2 test /home/t.txt /home/a.txt

8.4 二进制日志备份和恢复

二进制日志非常关键，可以用它来完成 point-in-time（时间点）恢复工作。mysql 数据库的复制也需要二进制日志。默认情况下不会开启二进制日志，通常设置如下：

[mysqld]
log-bin
sync-binlog=1
innodb_support_xa=1

备份二进制日志文件前，可以通过 FLUSH LOGS 命令来生成一个新的二进制日志文件，然后备份之前的二进制日志文件

使用方法：

mysqlbinlog [options] log_file ...
# 还原
mysqlbinlog binlog.000001 | mysql -uroot -p test
# 恢复多个二进制日志文件
mysqlbinlog binglog.[0-10]* | mysql -uroot -p test

# 也可以先通过 mysqlbinlog 导出一个文件，然后在通过 source 导入（好处：能够对文件进行修改）
mysqlbinlog binlog.00001 > /tmp/statements.sql
mysqlbinlog binlog.00002 >> /tmp/statements.sql
mysql -uroot -p -e "source /tmp/statements.sql" 
# -e  execute 执行 sql

8.5 热备

8.5.1 ibbackup

收费

8.5.2 XtraBackup

# 完备
./xtrabackup --backup

8.5.3 XtraBackup增量备份

原理：

1. 先完成一个完备，并记录下此时的检查点的 LSN
2. 再进行增量备份时，比较表空间中每个页的 LSN 是否大于上次备份时的 LSN，如果是，则备份该页，同时记录此时的检查点的 LSN

# 完备
./xtrabackup --backup --target-dir=/backup/base
# 增量
./xtrabackup --backup --target-dir=/backup/delta --incremental-basedir=/backup/base
# prepare
./xtrabackup --prepare --target-dir=/backup/base
# apply incremental backup
./xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/delta

8.7 复制

8.7.1 原理

复制步骤：

1. 主服务器把数据更新记录到二进制日志中
2. 从服务器把主服务器的二进制日志拷贝到自己的中继日志（RelayLog）中
3. 从服务器重做中继日志中的时间，把更新应用到自己的数据库中

从服务器有两个线程：一个是 IO 线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是 sql 线程，复制执行中继日志。

查看主从服务器的状态：SHOW SLAVE STATUS 、SHOW MASTER STATUS。