MySQL之备份和恢复（msyqldump、LVM、xtrabackup）

128次阅读

共计 9831 个字符，预计需要花费 25 分钟才能阅读完成。

备份类型：
根据备份时是服务器是否在线：
热备份（HOT）：
读写操作不受影响
温备份（WARM）：
进可以执行读操作，写操作不能执行
冷备份（COLD）：
读写操作均不能进行

根据备份时对数据文件的操作：
物理备份：
直接复制数据文件。速度快，但是跨平台移植可能受到底层文件系统格式的影响。
逻辑备份：
将数据导出至文本文件中。速度慢。但是保存文件中会丢失浮点数精度。可以使用文本处理工具进行二次处理。移植性强。备份出来的数据可能比原数据占用空间还大。

根据备份时的数据大小：
完全备份（full）：备份全部数据
增量备份（incremental）：仅备份上次完全备份或增量备份后变化的数据。
差异备份（differnetial）：仅备份上次完全备份以来变化的数据。占用空间比增量大。

增量备份和差异备份的区别：
虽然都是对变化的数据备份，但是增量在还原时要还原此前一系列的增量 + 完全备份的内容。差异备份还原时只要选择最近一个备份 + 完全备份的内容即可。

需要备份的东西：
数据、服务器配置文件、二进制日志、事务日志

二进制日志注意点：
备份时除了使用 FLUSH LOGS 以外，建议将 sync_binlog 的值设置为 1，这样可以在安全性上保证事务不会没有写入日志。

还原的时候先暂时关闭二进制日志记录功能，还原过程没必要记录二进制日志中。

备份策略：
考虑以下 2 点：
1. 数据在某个周期的变化量
2. 数据还原是所需时间

备份方案：
完全 + 增量
完全 + 差异

热备份：
MyISAM：不支持热备份，可以使用温备份
InnoDB：支持热备。可以使用 xtrabackup（物理备份）和 MySQLdump 等工具。

备份工具：
MYSQL 自带：
mysqldump 逻辑备份，对 MyISAM 可以实现温备份。InnoDB 可以实现热备。

mysqlhotcopy 物理备份，冷备份工具。基于 perl 脚本，使用 FLUSH TABLES 和 LOCK TABLES 以及 scp 完成备份。

SELECT INTO OUTFILE SELECT 语句，只能对单张表操作。

文件工具：
cp
冷备份，直接复制文件
lvm
使用逻辑卷快照功能，实现几乎热备。需要手工执行 FLUSH TABLES 和 LOCK TABLES，然后创建快照，创建完成后执行 UNLOCK TABLES，复制快照文件。
注意：MyISAM 可以直接锁表，但是对 InnoDB 而言，因为可能会涉及到事务的操作，例如从日志同步到磁盘中，或者事务未同步到磁盘中。此时如果锁表还得等待事务同步完成。

商业工具：
ibbackup
InnoDB

开源工具：
xtrabackup

#mysqldump [OPTION] DBNAME[TBNAME1,…]
默认要提前手工创建数据库，因为备份中不包含 CREATE DATABASE 命令。

[OPTION]
-u USERNAME
-p PASSWORD
-h HOST
–master-data = 0|1|2 是否记录当前的二进制文件和事件位置
0 不记录二进制日志文件及其事件位置
1 以 CHANGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器。
2 以 CHANGE MASTER TO 的方式记录位置，但是记录默认被注释。

–lock-all-tables 锁定所有表
–lock-tables
–flush-logs 执行日志滚动
–single-transaction 启动热备事务，适用于 InnoDB，借助于 MVCC 生成一个隔离级别为 REPEATABLE-READ 的事务。这样无论后台如何变化，看到的数据都不会发生变化。会自动处理表锁，不能和 –lock-all-tables 或者 –lock-tables 同时使用。

–all-databases 备份所有库，包含 CREATE DATABASE 命令，无需手动创建库
–database DBNAME,… 备份指定库，包含 CREATE DATABASE 命令，无需手动创建库

–events 备份事件调度器
–routines 备份存储过程和函数
–triggers 备份触发器

备份思路：
锁表 –> 磁盘同步数据 –> 刷新二进制日志 –> 备份二进制日志文件（最好全部保留）–> 完全备份 –> 增量备份

还原思路：
如有必要重新初始化数据库 –> 临时关闭二进制日志记录 –> 还原完全备份 –> 还原增量备份 –> 即时点还原（将编号最大的二进制日至文件通过管道送给 mysql 服务器）–> 开启二进制日志记录

备份策略：
完全 + 增量:

还原策略：
完全 + 增量 + 即时点

实例：
1. 备份 jiaowu 数据库，假设为 MyISAM 引擎，使用温备份：
mysql> LOCK TABLES;
mysql> FLUSH TABLES;
或
mysql>FLUSH TABLES WITH READ LOCK;
mysql>FULSH LOGS;
mysql>SHOW BINARY LOGS;
#mysqldump -uroot -p jiaowu> /root/backup/jiaowu.sql
mysql>UNLOCK TABLES;

或
#mysqldum -uroot -p –master-data=2 –lock-all-tables –flush-logs jiaowu > /root/backup/jiaowu.sql

2. 备份 jiaowu 数据库，假设为 InnoDB 引擎，使用热备份：
#mysqldump -uroot -p –master-data=2 –flush-logs –single-transaction jiaowu > /root/backup/jiaowu.sql

3. 完全备份：
#mysqldump -uroot -p –lock-all-tables –flush-logs –all-databases –master-data=2 > /root/backup/alldb-`date +%F-%H-%M-%S `.sql

4. 增量备份：
可以备份二进制日志或者是对某表进行备份，根据具体情况而定。
假设刷新日志后，生成最新的日志为 mysql-bin.000008。

mysql>FLUSH LOGS;
#cp mysql-bin-* /root/binlog/

#mysqlbinlog mysql-bin-000002 >/root/backup/mysql-inc/binlog-2.sql
…
#mysqlbinlog mysql-bin-000007 >/root/backup/mysql-inc/binlog-3.sql

5. 即时点备份：
#mysqlbinlog mysql-bin-0000008> /root/backup/mysql-inc/binlog-`date +%F+%H+%M+%S `.sql

6. 假设数据所在磁盘损坏，进行还原：
初始化数据库并启动服务
#cd /usr/local/mysql
#scripts/mysql_install_db –user=root –datadir=/mdata/data
#service mysqld start

导入完全备份和所有增量：
#mysql -uroot -p < /root/backup/alldb-xxxx.sql
#mysql -uroot -p < /root/backup/mysql-inc/binlog-xxxxx.sql
…
导入即时点数据：
#mysqlbinlog mysql-bin-LATEST > /root/lastbinlog.sql
#mysql -uroot -p < /root/lastbinlog.sql
或
#mysqlbinlog mysql-bin-LATEST |mysql -uroot -p

6. 使用 5 的方法虽然可以恢复，但是会被记录到二进制日志中，一般还原的操作是无需记录到二进制日志的。临时关闭当前会话的二进制日志。

mysql>SET sql_log_bin=0;
mysql>SOURCE /root/backup/alldb-xxxx.sql;
mysql>SOURCE /root/backup/mysql-inc/binlog-xxxxx.sql;
…
mysql>SOURCE /root/lastbinlog.sql;
mysql>SET sql_log_bin=1;

SELECT INTO OUTFILE：
只能对单张表进行操作。恢复速度快，一般用作单个表的备份。
存放的是纯粹的数据，各个字段和值之间用制表符（tab）分隔，无其他额外信息。

备份：
mysql>SELECT * INTO OUTFILE ‘FILE’ FROM TBNAME [WHERE …];

还原：
mysql>LOAD DATA INFILE ‘FILE’ INTO TABLE TBNAME;

注意：备份文件必须放在 mysql 用户有访问权限的目录。还原的表得提前创建表格式。

实例：
1. 备份 tutors 表：
mysql>use jiaowu;
mysql>SELECT * INTO OUTFILE‘/root/backup/selbackup/tutors.txt’FROM tutors;

2. 还原 tutors 表的内容到 tut 表中：
mysql>use jiaowu;
mysql>CREATE TABLE tut LIEK tutors;
或
mysql>CREATE TABLE tut (TID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Tname CHAR(20) NOT NULL ,Gender enum(‘F’,’M’),Age TINYINT NOT NULL);

mysql>LOAD DATA INFILE ‘/root/backup/selbackup/tutors.txt’’ INTO TABLE tut;

LVM 快照：
前提：
1. 数据要在逻辑卷上
2. 逻辑卷所在卷组必须有足够的空间使用快照卷
3. 事务日志和数据必须在同一个卷上。

备份思路：
锁表 –> 滚动日志 –> 记录二进制日志名称和位置信息 –> 创建快照 –> 释放锁 –> 挂载并备份快照卷中的数据 –> 删除快照卷 –> 备份二进制日志文件

恢复：

实例：
1. 备份 jiaowu 库：
mysql>FLUSH TABLES WITH READ LOCK;
mysql>FLUSH LOGS;

mysql>SHOW MASTER STATUS; 记录二进制日志名称和位置，为即时点还原提供依据。
或
在另一个终端上执行
#mysql -uroot -p -e `SHOW MASTER STATUS\G` > /root/backup/lvmbkup/master-`date +%F`.info

#lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata
mysql>UNLOCK TABLES;

#mont /dev/myvg/mydata-snap /mnt -o ro
#cd /mnt
#cd jiaowu
#mkdir -p /root/backup/lvmbackup/fullbackup-`data+%F`
#cp -a ./* /root/backup/lvmbackup/fullbackup-xxxxx/
#cp -a ../ibdataX /root/backup/lvmbackup/fullbackup-xxxxx/

注意：默认 InnoDB 的表空间文件为共享，复制时需要一并复制。建议将 innodb_file _per_table=1，使用单独表空间文件。这样直接复制文件夹即可。

#umount /mnt
#lvremove –force /dev/myvg/mdata-snap

#cd /root/backup/lvmbackup/fullbackup-xxxxx/
#rm -f mysql-bin.*

#cd /mydata/data
#cat /root/backup/lvmbkup/master-xxxxx.info
#mysqlbinlog –start-datetime=’2015-07-25 07:01:04′ mysql-bin.00000X mysql-bin.0000X+1 … > /root/backup/lvmbackup/incremental-`date +%F-%H-%M-%S`.sql

2. 还原：
#service mysqld stop
#cp -a /root/backup/lvmbackup/fullbackup-xxxx/* /mydata/data
#service msyqld start
#msyql -uroot -p
mysql>SET sql_log_bin=0;
mysql>SOURCE /root/backup/lvmbackup/incremental-xxxxx.sql;
mysql>SET sql_log_bin=1;

xtrabackup：
一般直接在服务器上使用此工具。

备份思路：
完全备份 –> 增量备份 –> 即时点备份

还原思路：
完全备份准备 –> 增量备份准备 –> 关闭服务器 –> 还原完全备份 –> 修改恢复数据的属主属组 –> 即时点还原 –> 重做一次完全备份（重要）

InnoDB:
full
incremental
HOT

MyISAM:
WRAM
full

XtraDB
增强功能的 InnoDB，若想使用则在编译 mysql 时替换掉 InnoDB 引擎。

安装：
#yum localinstall percona-xtrabackup-xxxx.rpm

二进制文件：
/usr/bin/innobackupex perl 脚本，调用具体的工具完成操作。

备份目录中文件：
xtrabackup_checkpoints 记录备份类型（完全 | 增量 …）、备份状态（prepared 状态等）、日志序列号（LSN）等。

LSN：InnoDB 每个储存数据的数据块都有对应的日志序列号，如果块中的数据发生了改变，则对应的 LSN 会 +1

内容如下：
backup_type=
from_lsn= 起始的 LSN 号，完全备份从 0 开始
to_lsn= 结束的 LSN 号
last_lsn= 最后一次的 LSN 号，如果是增量备份则会从此号开始。

xtrabackup_binlog_info 二进制日志文件和位置
xtrabackup_binlog_pos_innodb 二进制文件和位置以及用于 InnoDB 或 XtraDB 的二进制文件和位置
xtrabackup_binary 备份时使用的具体工具
xtrabackup_logfile
backup~my.cnf 备份命令用到的配置选项信息

#innobackupex [OPTION] DIR
[OPTION]
–user=DBUSERNAME
–password=DBUSERPASSWD
–host=DBHOST
–port=DBSERVERPORT
–no-timestamp 禁止自动生成以时间命名的文件夹
–apply-log 对备份好的数据进行准备
–use-memory NUM 指定准备时占用的内存大小，默认不指定为 100M
–copy-back 恢复完全数据
–incremental 增量备份
–incremental-basedir=FULLBACKUPDIR 指定完全备份文件路径
–incremental-dir=INCRBACKUPDIR 指定增量备份的文件路径
–redo-only 指定只进行 redo 操作。
–export 执行导出操作
–incremental-force-scan 增量备份时强制扫描全部的数据页。在 mariadb 上增量备份不指定此参数会报错。

实例：
1. 创建专门用于备份的 mysql 账号：
mysql>CREATE USER ‘bkuser’@’localhost’ IDENTIFIED BY ‘123456’
mysql>REVOKE ALL PRIVILEGES,GRANT OPTION FROM ‘bkuser’;
mysql>GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO ‘bkuser’;
mysql>FLUSH PRIVILEGES;

2. 完全备份：
#innobackupex –user=bkuser –password=123456 –host=localhost /root/backup/xtrabackup/full/
或
#innobackupex –user=root –password=123456 –host=localhost /root/backup/xtrabackup/full/

4. 第一次增量备份：
对 MyISAM 只能是完全备份。
#innobackupex –incremental /backup/xtrabackup/incr/ –incremental-basedir=/root/backup/xtrabackup/full/xxxxx/

5. 第二次增量备份：
第一次增量备份完成后，再进行增量备份的时候。–incremental-basedir 要指定增量备份的目录而不是完全备份的目录

#innobackupex –incremental /backup/xtrabackup/incr/ –incremental-basedir=/root/backup/xtrabackup/incr/xxxxx1/

以后增量备份只要指定上次增量即可

6. 准备:
备份好的数据不能直接还原，其中可能有部分事务没有提交或正在进行。已经执行的的事务同步到数据中，正在执行的事务进行回滚。

完全备份准备：
#innobackupex –apply-log –redo-only /root/backup/xtrabackup/xxxxx/

第一次增量备份准备：
#innobackupex –apply-log –redo-only /root/backup/xtrabackup/full/xxxxx/ –incremental-dir=/root/backup/xtrabackup/incr/xxxxx1/

注意：有些在完全备份中未能提交的事务在后续增量备份中已经提交过。如果不指定 –redo-only 选项则有可能这些时间会被 undo，造成恢复后无法提交。

第二次增量备份准备：
#innobackupex –apply-log –redo-only /root/backup/xtrabackup/full/xxxxx/ –incremental-dir=/root/backup/xtrabackup/incr/xxxxx2/

注意：所有备份的准备完成后后，所有的数据都将合并到完全备份中，恢复时只恢复完全备份即可。

7. 恢复：
备份二进制日志以便即时点恢复：
mysql>FLUSH LOGS
#service mysqld stop

#cd /mdata/data
#cp mysql-bin.0000* /root/backup/binlogfile