qldump 备份所有表_mysql进阶篇（三种备份方法总结：lvm, mysqldump, xtrabackup）

最新推荐文章于 2023-02-25 22:04:13 发布

林罗

最新推荐文章于 2023-02-25 22:04:13 发布

阅读量211

点赞数

文章标签： qldump 备份所有表

本文链接：https://blog.csdn.net/weixin_28717611/article/details/112881109

版权

二进制日志详解：

二进制日志通常作为备份的重要资源，所以再说备份之前先总结一下二进制日志的相关内容

1. 二进制日志的内容

引起mysql服务器改变的任何操作。

复制功能依赖于此日志。

从服务器通过复制主服务器的二进制日志完成主从复制，在执行之前保存于中继日志中。

从服务器通常可以关闭二进制日志以提升性能。

2. 二进制文件的文件表现形式：

默认在安装目录下，存在mysql-bin.00001, mysql-bin.00002的二进制文件

另外还有mysql-bin.index用来记录被mysql管理的二进制文件列表

如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使得mysql管理混乱

3. 二进制文件查看相关mysql命令。

SHOW MASTER STATUS ; 查看正在使用的二进制文件

MariaDB [(none)]> SHOW MASTER STATUS ;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 245 | | |

+------------------+----------+--------------+------------------+

FLUSH LOGS; 手动滚动二进制日志

MariaDB [(none)]> FLUSH LOGS;

MariaDB [(none)]> SHOW MASTER STATUS ;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000004 | 245 | | |

+------------------+----------+--------------+------------------+

滚动以后，mysql重新创建一个新的日志mysql-bin.000004

SHOW BINARY LOGS 显示使用过的二进制日志文件

MariaDB [(none)]> SHOW BINARY LOGS ;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 30373 |

| mysql-bin.000002 | 1038814 |

| mysql-bin.000003 | 288 |

| mysql-bin.000004 | 245 |

SHOW BINLOG EVENTS 以表的形式查看二进制文件

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G;

*************************** 99. row ***************************

Log_name: mysql-bin.000001

Pos: 30225

Event_type: Query

Server_id: 1

End_log_pos: 30354

Info: use `mysql`; DROP TEMPORARY TABLE `tmp_proxies_priv` /* generated by server */

4. MySQL二进制文件读取工具mysqlbinlog

Usage: mysqlbinlog [options] log-files

--start-datetime

--stop-datetime

--start-position

--stop-position

# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001

截取一下结果：

# at 30225

#151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0

use `mysql`/*!*/;

SET TIMESTAMP=1448858615/*!*/;

SET @@session.pseudo_thread_id=1/*!*/

根据以上截取结果第二行，进行解释二进制日志内容

1) 时间点： 151130 12:43:35

2) 服务器ID： server id 1

服务器ID主要用于标记日志产生的服务器，主要用于双主模型中，互为主从，确保二进制文件不会被相互循环复制

3) 记录类型： Query

4) 线程号： thread_id = 1

5) 语句的时间戳和写入二进制日志文件的时间差； exec_time=0

6) 事件内容

7) 事件位置 #at 30225

8) 错误代码 error_code=0

9) 事件结束位置 end_log_pos也就是下一事件开始的位置

5. 二进制日志格式

由bin_log_format={statement|row|mixed}定义

1)statement:基于语句，记录生成数据的语句

缺点在于如果当时插入信息为函数生成，有可能不同时间点执行结果不一样，

例如： INSERT INTO t1 VALUE (CURRENT_DATE());

2)row:基于行数据

缺点在于，有时候数据量会过大

3) mixed：混合模式，又mysql自行决定何时使用statement, 何时使用row 模式

6. 二进制相关参数总结

1)log_bin = {ON|OFF}

还可以是个文件路径，主要用于控制全局binlog的存放位置和是否开启

2) log_bin_trust_function_creators

是否记录在

3) sql_log_bin = {ON|OFF}

会话级别是否关闭binlog，如果关闭当前会话内的操作将不会记录

4) sync_binlog是否马上同步事务类操作到二进制日志中

5) binlog_format = {statement|row|mixed} 二进制日志的格式，上面单独提到了

6) max_binlog_cache_size =

二进制日志缓冲空间大小，仅用于缓冲事务类的语句；

7) max_binlog_stmt_cache_size =

语句缓冲，非事务类和事务类共用的空间大小

8) max_binlog_size =

二进制日志文件上限，超过上限后则滚动

9) 删除二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

MariaDB> PURGE BINARY LOGS TO 'mysql-bin.010';

MariaDB> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

建议：切勿将二进制日志与数据文件放在一同设备；

二进制日志备份和恢复：

为什么做备份：

1. 灾难恢复

2. 审计，数据库在过去某一个时间点是什么样的

3. 测试

备份的目的：

1. 用于恢复

2. 备份结束后，需要周期性的做恢复测试

备份类型：

1. 根据备份时，服务器是否在线

1) 冷备(cold backup)：服务器离线，读写操作都不能进行

2) 温备份：全局施加共享锁，只能读不能写

3) 热备(hot backup)：数据库在线，读写照样进行

2. 根据备份时的数据集分类

1) 完全备份(full backup)

2) 部分备份(partial backup)

3. 根据备份时的接口

1) 物理备份(physical backup)：直接复制数据文件，打包归档

特点：

不需要额外工具，直接归档命令即可，但是跨平台能力比较差

如果数据量超过几十个G，则适用于物理备份

2) 逻辑备份(logical backup)：把数据抽取出来保存在sql脚本中

特点：

可以使用文本编辑器编辑

导入方便，直接读取sql语句即可

逻辑备份恢复时间慢，占据空间大

无法保证浮点数的精度

恢复完数据库后需要重建索引

4. 根据备份整个数据还是变化数据

1) 完全备份 full backup

2) 增量备份 incremental backup

在不同时间点起始备份一段数据

比较节约空间

3) 差异备份 differential backup

备份从每个时间点到上一次全部备份之间的数据，随着时间增多二增多

比较容易恢复

对于很大的数据库，可以考虑主从模型，备份从服务器的内容。

5. 备份策略，需要考虑因素如下

备份方式

备份实践

备份成本

锁时间

时长

性能开销

恢复成本

恢复时长

所能够容忍丢失的数据量

6. 备份内容

1) 数据库中的数据

2) 配置文件

3) mysql中的代码：存储过程，存储函数，触发器

4) OS 相关的配置文件，chrontab 中的备份策略脚本

5) 如果是主从复制的场景中：跟复制相关的信息

6) 二进制日志文件需要定期备份，一旦发现二进制文件出现问题，需马上对数据进行完全备份

7. 常用备份工具

1)mysqldump:逻辑备份工具

innodb：热备，温备

MyISAM, Aria: 温备

单线程备份恢复比较慢

2)mysqldumper: 多线程的mysqldump

3)vm-snapshot:

接近于热备的工具：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；

使用cp、tar等工具进行物理备份；

备份和恢复速度较快；

很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此；

4)SELECT clause INTO OUTFILE '/path/to/somefile'

LOAD DATA INFILE '/path/from/somefile'

部分备份工具，不会备份关系定义，仅备份表中的数据；

逻辑备份工具，快于mysqldump，因为不备份表格式信息

5)Innobase: 商业备份工具, innobackup

InnoDB热备，增量备份；

MyISAM温备，不支持增量，只有完全备份

属于物理备份，速度快；

6)Xtrabackup: 由Percona提供的开源备份工具

InnoDB热备，增量备份；

MyISAM温备，不支持增量；

7)mysqlhotcopy: 接近冷备，基本没用

mysqldump工具基本使用

1. mysqldump [OPTIONS] database [tables…]

还原时库必须存在，不存在需要手动创建

--all-databases:　备份所有库

--databases db1 db2 ...: 备份指定的多个库，如果使用此命令，恢复时将不用手动创建库

--lock-all-tables：请求锁定所有表之后再备份，对MyISAM、InnoDB、Aria做温备

--lock-table: 对正在备份的表加锁，但是不建议使用，如果其它表被修改，则备份后表与表之间将不同步

--single-transaction: 能够对InnoDB存储引擎实现热备；

启动一个很大的大事物，基于MOCC可以保证在事物内的表版本一致

自动加锁不需要，再加--lock-table, 可以实现热备

备份代码：

--events: 备份事件调度器代码

--routines: 备份存储过程和存储函数

--triggers：备份触发器

备份时滚动日志：

--flush-logs: 备份前、请求到锁之后滚动日志；

方恢复备份时间点以后的内容

复制时的同步位置标记：主从架构中的，主服务器数据。效果相当于标记一个时间点。

--master-data=[0|1|2]

0: 不记录

1：记录为CHANGE MASTER语句

2：记录为注释的CHANGE MASTER语句

2. 使用mysqldump备份大体过程：

1) 请求锁：–lock-all-tables或使用–singe-transaction进行innodb热备；

2) 滚动日志：–flush-logs

3) 选定要备份的库：–databases

4) 记录二进制日志文件及位置：–master-data=

FLUSH TABLES5 WITH READ LOCK;

3. 恢复：

恢复过程无需写到二进制日志中

建议：关闭二进制日志，关闭其它用户连接；

4. 备份策略：基于mysqldump

备份：mysqldump+二进制日志文件；

周日做一次完全备份：备份的同时滚动日志

周一至周六：备份二进制日志；

恢复：

完全备份+各二进制日志文件中至此刻的事件

5. 实例：

1) 完全备份mysql数据库，并实现还原

备份之前的数据库

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mysql |

| students |

| test |

| testdb |

+--------------------+

此时要确保二进制日志开启，我实验的时候发现不知道为什么没开，于是在配置文件中server段加一行

# vim /etc/my.cnf 中添加log_bin=/data/mysqldata/mysql_bin 然后重启服务

# service mysqld restart

# ls /data/mysqldata/mysql_bin.* 可以看到一下三个文件，证明二进制已然开启

mysql_bin.000001 mysql_bin.000002 mysql_bin.index

考虑到远程连接，为安全考虑，最好不用管理员账号备份，所以需要创建一个用户专门用作mysqldump备份

mysql> GRANT SELECT,SHOW DATABASES,LOCK TABLES,RELOAD,EVENT ON *.* TO 'dumpper'@'192.168.37.%' IDENTIFIED BY 'dumpper';

Query OK, 0 rows affected (0.00 sec)

然后使用mysqldump进行备份

# mysqldump --events --master-data=2 --all-databases --lock-all-tables --flush-logs -udumpper -h192.168.37.129 -pdumpper > /tmp/dump_bak.sql

这里的选项对应以上给dumpper用户赋予的权限

SHOW DATABASES ---> --all-databases

LOCK TABLES ---> --lock-all-tables

RELOAD ---> --flush-logs

EVENT ---> --events

SUPPER ---> --master-data 主要授予SHOW MASTER STATUS权限

在hellodb.students表中插入一行，再删除一个hellodb 然后尝试恢复

mysql> INSERT hellodb.students (Name,Age,Gender) VALUE ('Linghu Chong',28,'M');

mysql> DROP DATABASE hellodb;

此时需要关闭session级别的二进制日志使得，恢复内容不记录日志

mysql> SET SESSION sql_log_bin='OFF';

mysql> SOURCE /tmp/dump_bak.sql;

此时数据库恢复到，插入Linghu Chong 这一行之前，然后通过二进制日志恢复直到数据库被删除之前的内容

由于设置了--master-data选项，所以在备份文件中可以找到如下一行

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000005', MASTER_LOG_POS=106;

这一行标记了新的二进制文件从那个点起始，通过查看二进制日志mysql_bin.000005可以得知，还需要恢复106到134也就是hellodb被删除之前的数据

# mysqlbinlog --start-position 106 --stop-position 271 /data/mysqldata/mysql_bin.000005 > binlog106_271.sql

mysql>SOURCE /tmp/binlog106_271.sql

mysql> SELECT * FROM hellodb.mysql 可以看到最后一行，以前插入的数据重新恢复

| 26 | Linghu Chong | 28 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

2) 编写脚本，并加入crontab, 为了看效果比较快，这里设定每小时执行一次

脚本如下：

#!/bin/bash

## Using mysqldump to backup the all databases

function backup {

prefix=$1

outputdir=$2

[ -d $outputdir ] ||(echo 'No output dir, creating one!' && mkdir -p $outputdir)

now=`/bin/date +'%Y_%b_%d_%k%M'`

/usr/local/mysql/bin/mysqldump --events --master-data=2 --all-databases --single-transaction --flush-logs -udumpper -h192.168.98.129 -pdumpper > $outputdir/${prefix}_${now}.sql

}

function main {

case $# in

backup $1 $2

;;

echo 'Usage: ./mysqldump_wrapper.sh prefix outputdir'

;;

esac

}

main $*

周期任务计划表

# crontab -e 添加如下内容

* * * * * /usr/local/mysql/mysqldump_wrapper.sh back /tmp/mysqlback

## 如果正常的话，在备份目录中，每分钟将备份一次

-rw-r--r-- 1 root root 521824 Dec 1 11:42 back_2015_Dec_01_1142.sql

-rw-r--r-- 1 root root 521824 Dec 1 11:43 back_2015_Dec_01_1143.sql

rw-r--r-- 1 root root 521824 Dec 1 11:44 back_2015_Dec_01_1144.sql

lvm-snapshot：基于LVM快照的备份

关于快照：

1. 事务日志跟数据文件必须在同一个卷上；

2. 刚刚创立的快照卷，里面没有任何数据，所有数据均来源于原卷

3. 一旦原卷数据发生修改，修改的数据将复制到快照卷中，此时访问数据一部分来自于快照卷，一部分来自于原卷

4. 当快照使用过程中，如果修改的数据量大于快照卷容量，则会导致快照卷崩溃。

5. 快照卷本身不是备份，只是提供一个时间一致性的访问目录。

基于快照备份几乎为热备：

1. 创建快照卷之前，要请求MySQL的全局锁；在快照创建完成之后释放锁；

2. 如果是Inoodb引擎，当flush tables 后会有一部分保存在事务日志中，却不在文件中。因此恢复时候，需要事务日志和数据文件

但释放锁以后，事务日志的内容会同步数据文件中，因此备份内容并不绝对是锁释放时刻的内容，由于有些为完成的事务已经完成，但在备份数据中因为没完成而回滚。因此需要借助二进制日志往后走一段

基于快照备份注意事项：

1. 事务日志跟数据文件必须在同一个卷上；

2. 创建快照卷之前，要请求MySQL的全局锁；在快照创建完成之后释放锁；

3. 请求全局锁完成之后，做一次日志滚动；做二进制日志文件及位置标记(手动进行)；

备份与恢复的大体步骤

备份

1. 请求全局锁，并滚动日志

mysql> FLUSH TABLES WITH READ LOCK;

mysql> FLUSH LOGS;

2. 做二进制日志文件及位置标记(手动进行)；

# mysql -e 'show master status' > /path/to/orignal_volume

3. 创建快照卷

# lvcreate -L -s -n -p r /path/to/some_lv

4.释放全局锁

5. 挂载快照卷并备份

6.备份完成之后，删除快照卷

恢复：

1. 二进制日志保存好；

提取备份之后的所有事件至某sql脚本中；

2. 还原数据，修改权限及属主属组等，并启动mysql

3. 做即时点还原

4. 生产环境下，一次大型恢复后，需要马上进行一次完全备份。

备份与恢复事例：

环境，实现创建了一个test_vg卷组，里面有个mylv1用来装mysql数据，挂载到/data/mysqldata

备份：

1. 创建备份专用的用户，授予权限FLUSH LOGS 和 LOCK TABLES

MariaDB > GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'192.168.98.%' IDENTIFIED BY 'lvm';

MariaDB > FLUSH PRIVILEGES;

2. 记录备份点

# mysql -ulvm -h192.168.98.129 -plvm -e 'SHOW MASTER STATUS' > /tmp/backup_point.txt

3. 创建快照卷并挂载快照卷

# lvcreate -L 1G -s -n lvmbackup -p r /dev/test_vg/mylv1

# mount -t ext4 /dev/test_vg/lvmbackup /mnt/

4. 释放锁

# mysql -ulvm -h192.168.98.129 -plvm -e 'UNLOCK TABLES'

## 做一些模拟写入工作

MariaDB [test]> create database testdb2

5. 复制文件

# cp /data/mysqldata /tmp/backup_mysqldata -r

6. 备份完成卸载，删除快照卷

# umount /mnt

# lvmremove /dev/test_vg/lvmbackup

还原：假如整个mysql服务器崩溃，并且目录全部被删除

1. 数据文件复制回源目录

# cp -r /tmp/backup_mysqldata/* /data/mysqldata/

MariaDB [test]> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mysql |

| mysqldata |

| openstack |

| performance_schema |

| test |

+--------------------+

此时还没有testdb2，因为这个是备份之后创建的，因此需要通过之前记录的二进制日志位置向后还原

2. 查看之前记录的记录点。向后还原

# cat /tmp/backup_point.txt

FilePositionBinlog_Do_DBBinlog_Ignore_DB

mysql-bin.000001245

# mysqlbinlog /data/binlog/mysql-bin.000001 --start-position 245 > tmp.sql

MariaDB [test]> source /data/mysqldata/tmp.sql

MariaDB [test]> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mysql |

| mysqldata |

| openstack |

| performance_schema |

| test |

| testdb2 |

+--------------------+

8 rows in set (0.00 sec)

testdb2 已经被还原回来。

使用Xtrabackup进行MySQL备份：

安装

1. 简介

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

1) 备份过程快速、可靠；

2) 备份过程不会打断正在执行的事务；

3) 能够基于压缩等功能节约磁盘空间和流量；

4) 自动实现备份检验；

5) 还原速度快；

2. 安装

其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得。本机使用2.2.12版本

# yum install percona-toolkit-2.2.16-1.noarch.rpm

# yum install percona-xtrabackup-2.3.2-1.el6.x86_64.rpm

完全备份

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

Usage: innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

--user: 需要创建一个拥有最小权限的用户

MariaDB [(none)]> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost' IDENTIFIED BY 'xtrauser' ;

MariaDB [(none)]> FLUSH PRIVILEGES ;

/path/to/BACKUP_DIR

备份出来的数据存放目录，外加包含一些xtrabackup的元数据

使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。

事例：

# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup/

...

151202 14:52:01 Executing UNLOCK TABLES

151202 14:52:01 All tables unlocked

151202 14:52:01 Backup created in directory '/tmp/xtrabackup//2015-12-02_14-51-56'

MySQL binlog position: filename 'mysql-bin.000001', position '952'

151202 14:52:01 [00] Writing backup-my.cnf

151202 14:52:01 [00] ...done

151202 14:52:01 [00] Writing xtrabackup_info

151202 14:52:01 [00] ...done

xtrabackup: Transaction log of lsn (1752057) to (1752057) was copied.

151202 14:52:01 completed OK!

当看到最后这一行时候，说明备份已经完成

在备份的同时，innobackupex还会在备份目录中创建如下文件：

1. xtrabackup_checkpoints: 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(Log Serial Number日志序列号)范围信息；

# cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1752057

last_lsn = 1752057

compact = 0

recover_binlog_info = 0

在mysql中，存储数据的数据块会有按照顺序的ID，如果某一块数据被修改，将会赋予新的ID。根据这些ID，可以标记数据的新老成都。xtrabackup也就是使用这些ID来进行备份，和增量备份

每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

2. xtrabackup_binlog_info: mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

# cat xtrabackup_binlog_info

mysql-bin.000001952

3. xtrabackup_info: 包含很多xtrabackup工具信息以及所备份的数据库信息

# cat xtrabackup_info

uuid = 3073ff65-98c1-11e5-9af1-000c29622425

name =

tool_name = innobackupex

tool_command = --user=xtrauser --password=... /tmp/xtrabackup/

tool_version = 2.3.2

ibbackup_version = 2.3.2

server_version = 5.5.46-MariaDB-log

start_time = 2015-12-02 14:51:59

end_time = 2015-12-02 14:52:01

lock_time = 0

binlog_pos = filename 'mysql-bin.000001', position '952'

innodb_from_lsn = 0

innodb_to_lsn = 1752057

partial = N

incremental = N

format = file

compact = N

compressed = N

encrypted = N

4. backup-my.cnf —— 备份命令用到的配置选项信息；

# cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

innodb_checksum_algorithm=innodb

innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:10M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=5242880

innodb_fast_checksum=false

innodb_page_size=16384

innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

准备(prepare)一个完全备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。

因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：实际上就是把未完成的事务提交，准备工作需要在还原之前才执行，在这之前都能执行准备工作。

Usage: innobackupex --apply-log /path/to/BACKUP-DIR

事例：

# innobackupex --apply-log /tmp/xtrabackup/2015-12-02_15-10-53/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1752598

151202 15:19:05 completed OK!

出现这几行，说明准备完成

在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

从一个完全备份中恢复数据

注意：恢复不用启动MySQL

innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取 DATADIR目录的相关信息。

usage: innobackupex --copy-back /path/to/BACKUP-DIR

# innobackupex --copy-back /tmp/xtrabackup/2015-12-02_15-10-53/

如果执行正确，其输出信息的最后几行通常如下：

151202 15:23:32 [01] Copying ./mysql/time_zone_transition.MYI to /data/mysqldata/mysql/time_zone_transition.MYI

151202 15:23:32 [01] ...done

151202 15:23:32 completed OK!

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：

# chown -R mysql:mysql /data/mysqldata

我发现的坑：

这里我发现一个坑，如果备份时，在配置文件中没有明确定义innodb_log_size大小，这里可能无法启动服务。

原因是，xtrabackup的默认innodb_log_size可能与mysql不一致。这里使用的xtrabackup是比较新的版本2.3.2

默认生成的日志大小为50331648而不是5242880，所以会出现问题。解决方法是手工设定一下日志大小，写在配置文件中。

使用innobackupex进行增量备份

每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

1. 备份过程：

要实现第一次增量备份，可以使用下面的命令进行：

usage: innobackupex –incremental /backup –incremental-basedir=BASEDIR

BASEDIR：

指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

举例:

先做一次完全备份，在之前完全备份的基础上做两次增量，之间创建两个数据库

全备份：

# innobackupex --user=xtrauser --password=xtrauser --no-timestamp /tmp/xtrabackup/full_backup

MariaDB [(none)]> CREATE DATABASE testdb1；

第一次增量：

# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/full_backup

MariaDB [(none)]> CREATE DATABASE testdb2；

第二次增量：

# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/2015-12-02_17-15-35

2. 准备过程

“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：

1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。

2)基于所有的备份将未提交的事务进行“回滚”。于是，操作就变成了：不能回滚，因为有可能第一次备份时候没提交，在增量中已经成功提交

使用方法如下：

# innobackupex --apply-log --redo-only BASE-DIR

接着执行：

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二个增量：

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

这样一个一个准备完成后，所有增量将合并至全备份中。

其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；

事例：

# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup

# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2015-12-02_17-15-35

# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2015-12-02_17-17-14

3. 恢复过程：与完全备份类似，直接copy-back完全备份的那个目录。此时所有的增量已经正好到完全备份的目录中

# rm -rf /data/mysqldata/*

# innobackupex --copy-back /tmp/xtrabackup/full_backup/

# chown -R mysql:mysql /data/mysqldata

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| test |

| test1 |

| testdb1 |

| testdb2 |

+--------------------+

8 rows in set (0.04 sec)

可以看到后面创建的两个testdb1, testdb2 全部恢复回来

Xtrabackup的“流”及“备份压缩”功能

Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用–stream选项即可。如：

usage: innobackupex –stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

事例：

# innobackupex --user=xtrauser --password=xtrauser --stream=tar /tmp/xtrabackup/ | gzip > /tmp/xtrabackup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用类似如下命令将数据备份至其它服务器：

usage: innobackupex –stream=tar /backup | ssh user@hostname "cat – > /backups/`date +%F_%H-%M-%S`.tar"

# innobackupex --user=xtrauser --password=xtrauser --stream=tar /tmp/xtrabackup | ssh root@192.168.98.129 "cat - > /tmp/`date +%F_%H-%M-%S`.tar"

这里有个bug，会进入无限log记录，好像是新版本的问题，目前还不知道怎么解决。

此外，在执行本地备份时，还可以使用–parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：

usage： innobackupex –parallel /path/to/backup

同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用–remote-host选项来实现：貌似此功能在2.1以后就被取消了

usage: innobackupex –remote-host=root@hostname /path/IN/REMOTE/HOST/to/backup

导入或导出单张表

默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项(严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项)，并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。

在创建数据库之前，在配置文件中server段下面写入innodb_file_per_table=1, 记得是1不是on也不是yes,这个坑出现在5.1版本中，我看了后面的mariadb没有这个问题

1) “导出”表

导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过–export选项将某表导出了：

usage: innobackupex –apply-log –export /path/to/backup

事例：

# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup

# innobackupex --apply-log --export /tmp/2015-12-03_12-37-35/

此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。

# ls *.exp

classes.exp coc.exp courses.exp scores.exp students.exp teachers.exp toc.exp

2 )“导入”表

使用show CREATE TABLE mytable; 来查看原始表创建命令

要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：

事例：这里以students 表为例

mysql> SHOW CREATE TABLE hellodb.students \G;

*************************** 1. row ***************************

Table: students

Create Table: CREATE TABLE `students` (

`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID` int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

在testdb库中创建这个表

mysql> CREATE TABLE `students` (

`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID` int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

然后将此表的表空间删除：

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

接下来，将来自于“导出”表的服务器的students表的students.ibd和students.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：

注意权限。。。。

# cp /tmp/2015-12-03_12-37-35/hellodb/students{.ibd,.exp} /data/mysqldata/testdb/

# chown mysql.mysql /data/mysqldata/testdb/students.*

mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

好像这里也有bug 明天试一下

尝试结果如下：

1) 在mysql 5.1 版本中无法实现

2) mariadb 5.5 也不行

3) 只有在mysql 5.6中可以成功实现

最后这里是马哥对于备份和恢复的几点经验之谈，请允许我无耻的总结在这里

备份注意：

1. 将数据和备份放在不同的磁盘设备上；异机或异地备份存储较为理想；

2. 备份的数据应该周期性地进行还原测试；

3. 每次灾难恢复后都应该立即做一次完全备份；

4. 针对不同规模或级别的数据量，要定制好备份策略；

5. 二进制日志应该跟数据文件在不同磁盘上，并周期性地备份好二进制日志文件；

从备份中恢复应该遵循步骤：

1. 停止MySQL服务器；

2. 记录服务器的配置和文件权限；

3. 将数据从备份移到MySQL数据目录；其执行方式依赖于工具；

4. 改变配置和文件权限；

5. 以限制访问模式重启服务器；mysqld的–skip-networking选项可跳过网络功能；

方法：编辑my.cnf配置文件，添加如下项：

skip-networking

socket=/tmp/mysql-recovery.sock

6. 载入逻辑备份(如果有)；而后检查和重放二进制日志；

7. 检查已经还原的数据；

8. 重新以完全访问模式重启服务器；

注释前面在my.cnf中添加的选项，并重启；

原创文章，作者：以马内利，如若转载，请注明出处：http://www.178linux.com/9781