mysql 如何存储二进制数据 mysql保存二进制数据

转载

夜行者3号 2023-10-28 19:08:28

文章标签 mysql 如何存储二进制数据 mysql提取5号的数据库 mysql 二进制日志 sql 文章分类 MySQL 数据库

二进制日志简要:

二进制日志通常作为备份的重要资源，所以再说备份之前我们来回顾下前面专题讲过的二进制日志内容。

1.二进制日志内容

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

复制功能依赖于此日志。

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

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

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

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

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

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

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

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

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |    22094 |              |                  |
+------------------+----------+--------------+------------------+
#> FLUSH LOGS;手动滚动二进制日志mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      107 |              |                  |
+------------------+----------+--------------+------------------+
#滚动以后，mysql重新创建一个新的日志mysql-bin.000008
#> SHOW BINARY LOGS 显示使用过的二进制日志文件。mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |      2576 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       126 |
| mysql-bin.000005 |       126 |
| mysql-bin.000006 |       126 |
| mysql-bin.000007 |     22137 |
| mysql-bin.000008 |       107 |
+------------------+-----------+
#> SHOW BINLOG EVENTS;以表的形式查看二进制文件SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.33-log, Binlog ver: 4

4.MySQL二进制日志文件读取工具mysqlbinlogUsage: mysqlbinlog [options] log-files

--start-datetime
--stop-datetime
--start-position
--stop-position

截取指定位置的日志记录# mysqlbinlog --start-position 15642 --stop-position 15643 /mydata/data/mysql-bin.000001

--截取结果如下:

# at 15642

#160612 16:56:52 server id 1 end_log_pos 15760 Query thread_id=6 exec_time=0 error_code=0

use `hellodb`/*!*/;根据以上截取结果第二行，进行解释二进制日志内容

1)时间点： 160612 16:56:52

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

3)记录类型： Query

4)线程号： thread_id = 6

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

6)事件内容

7)事件位置 #at 15642

8)错误代码 error_code=0

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

5.二进制日志格式

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

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

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

例如:mysql > INSERT INTO tb1 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 }

删除二进制日志例:mysql> PURGE BINARY LOGS TO 'mysql-bin.000001';

mysql> PURGE BINARY LOGS BEFORE '2016-06-12 00:00:00';

Tips:切勿将二进制日志与数据文件放在同一设备及同一目录下。

二进制日志备份与恢复:

为什么要做备份:

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相关的配置文件，crontab中的备份策略脚本

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

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

7.常用的备份工具

1)mysqldump:逻辑备份工具

innodb：热备，温备

MyISAM,Aria：温备

单线程备份恢复比较慢

2)mysqldumper：多线程的mysqldump

3)vm-snapshot：

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

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

备份与恢复速度较快

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

4)通过查询语句备份数据:mysql > SELECT * FROM tb1 INTO OUTFILE '/path/to/somefile'; --导出查询数据为文本文件

mysql > 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=mysql > FLUSH TABLES WITH READ LOCK;

3.恢复:

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

Tips:关闭二进制日志，关闭其他用户连接；

4.备份策略:基于mysqldump

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

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

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

恢复:

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

5.二进制备份恢复使用案例

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

备份之前的数据库及数据情况mysql> SHOW DATABASES; --查询当前数据库服务器下所有的数据库

+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

--开启mysql二进制日志功能

# vim /etc/my.cnf
[server]
log-bin=/mydata/data/mysql-bin

--修改配置文件后重启mysql服务

# service mysqld restart

--经安全考虑在备份的时候最好不要使用管理员账号操作，所以需要创建一个专门的用户用来做mysqldump备份

mysql> GRANT SELECT,SHOW DATABASES,LOCK TABLES,RELOAD,EVENT,SUPER ON *.* TO 'sqldump'@'172.16.100.%' IDENTIFIED BY 'sqldum';

然后使用mysqldump进行备份# mysqldump --events --master-data=2 --all-databases --lock-all-tables --flush-logs -usqldump -h172.16.100.7 -psqldump > /tmp/all_dump_bak.sql

以上赋予sqldump用户权限对应分析:

SHOW DATABASES --> --all-databases
LOCK TABLES --> --lock-all-tables
RELOAD ---> --flush-logs
EVENT ---> --events

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

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

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

mysql> DROP DATABASE hellodb;

恢复:

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

mysql> SET SESSION sql_log_bin='OFF';

mysql> SOURCE /tmp/all_dump_bak.sql;

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

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

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=107;

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

# mysqlbinlog --start-position 107 --stop-position 340 /mydata/data/mysql-bin.000010 > /tmp/binlog107_340.sql

mysql> SOURCE /tmp/binlog107_340.sql;

mysql> SELECT * FROM hellodb.students；--查询我们可以看到最后一行是自己插入的数据

| 26 | Samlee GZ | 28 | M | NULL | NULL |

2)使用shell脚本自动定时备份--mysqldump方式:

脚本内容如下:

#!/bin/bash
#
## Using mysqldump to backup the all databases
function backup {
prefix=$1
outputdir=$2
[ -d $outputdir ] || (echo "No output dir,create one!" && mkdir -p $outputdir)
now=`/bin/date +'%Y_%b_%d_%k%M'`
mysqldump='/usr/local/mysql/bin/mysqldump'
mysqldump --events -master-data=2 --all-databases --single-transaction --flush-logs -usqldump -h172.16.100.7 -psqldump > $outputdir/${prefix}_${now}.sql
}
function main {
case $# in
2)
backup $1 $2
;;
*)
echo 'Usage: ./mysqldump_backup.sh prefix outputdir'
;;
esac
}
main $*
You have new mail in /var/spool/mail/root

设置周期任务计划表# crontab -e

* * * * * /usr/local/mysql/bin/mysqldump_backup.sh back /tmp/mysqlback
# ll /tmp/mysqlback/    --实现每分钟备份一次
-rw-r--r-- 1 root root 0 Jun 14 11:13 back_2016_Jun_14_1113.sql
-rw-r--r-- 1 root root 0 Jun 14 11:14 back_2016_Jun_14_1114.sql
-rw-r--r-- 1 root root 0 Jun 14 11:15 back_2016_Jun_14_1115.sql
-rw-r--r-- 1 root root 0 Jun 14 11:16 back_2016_Jun_14_1116.sql
-rw-r--r-- 1 root root 0 Jun 14 11:17 back_2016_Jun_14_1117.sql
-rw-r--r-- 1 root root 0 Jun 14 11:18 back_2016_Jun_14_1118.sql
-rw-r--r-- 1 root root 0 Jun 14 11:19 back_2016_Jun_14_1119.sql
-rw-r--r-- 1 root root 0 Jun 14 11:20 back_2016_Jun_14_1120.sql

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

关于快照:

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

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

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

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

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

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

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

2.如果是Innodb引擎，当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.生产环境下，一次大型恢复后，需要马上进行一次完全备份。

使用快照卷备份恢复mysql实例:

环境，实现创建一个myvg的卷组，mydata逻辑卷用来存储mysql数据，挂载至/mydata/data

备份:

1.创建备份专用的账号，授予权限FLUSH LOGS和LOCK TABLESmysql> GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'172.16.100.%' IDENTIFIED BY 'lvm';

mysql> FLUSH PRIVILEGES;

2.请求全局锁，锁定数据库并且滚动日志mysql > FLUSH TABLES WITH READ LOCK;

mysql > FLUSH LOGS;

3.记录备份点# mysql -ulvm -h172.16.100.7 -plvm -e 'SHOW MASTER STATUS' > /tmp/backp_point.txt

# cat /tmp/backp_point.txt

File Position Binlog_Do_DB Binlog_Ignore_DB

mysql-bin.000010 499

4.创建快照卷并挂载快照卷及创建备份文件提取点# lvcreate -L 100M -s -n mydata-snap -p r /dev/myvg/mydata

# mount -t ext4 -o ro /dev/myvg/mydata-snap /mnt/

# mkdir /backups

5.释放锁# mysql -ulvm -h172.16.100.7 -plvm -e 'UNLOCK TABLES';

## 模拟写入操作

mysql> UNLOCK TABLES;

mysql> CREATE DATABASE samleedb;

6.复制快照里面文件至备份文件存储目录# cp -ar /mnt/data/ /backups/data-2016-06-14

7.备份完成，删除快照卷，减少磁盘I/O# umount /mnt/

# lvremove /dev/myvg/mydata-snap

故障模拟恢复: 数据库存储目录损坏或整个服务器崩溃，并且数据存储目录全部被删除

1.模拟数据损坏故障:[root@mysql ~]# service mysqld stop

Shutting down MySQL.                                       [  OK  ]
[root@mysql ~]# rm -rf /mydata/data/*
[root@mysql ~]# service mysqld start
Starting MySQL....The server quit without updating PID file[FAILED]a/data/mysql.samlee.com.pid).

--此时发现mysql无法启动了

2.将备份的数据文件复制回源目录再启动mysql# cp -arp /backups/data-2016-06-14/* /mydata/data/

# service mysql start
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| samlee             |
| test               |
+--------------------+

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

3.查看之前记录的记录点。向后还原# cat /tmp/backp_point.txt

File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000010    499
# mysqlbinlog /mydata/data/mysql-bin.000010 --start-position 499 > /tmp/2016_06_14.sql
mysql> source /tmp/2016_06_14.sql;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| samlee             |
| test               |
| samleedb             |
+--------------------+

使用Xtrabackup进行MySQL备份

安装:

1.简介

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

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

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

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

4)自动实现备份校验；

5)还原速度快；

2.安装及安装源获取

其最新版本可以从以下URL获得:https://www.percona.com/downloads/XtraBackup/

安装# yum -y install percona-toolkit-2.2.16-1.noarch.rpm

# yum -y install libev-4.15-1.el6.rf.x86_64.rpm

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

备份实现:

1.完全备份:

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户:Usage: innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

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

mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost' IDENTIFIED BY 'xtrauser';
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'xtrauser';
mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost';
mysql> FLUSH PRIVILEGES;

/path/to/BACKUP_DIR：备份出来的数据存储目录，外加包含一些xtrabackup的元数据

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

示例:备份# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup/

160615 09:47:34 Executing UNLOCK TABLES
160615 09:47:34 All tables unlocked
160615 09:47:34 Backup created in directory '/tmp/xtrabackup//2016-06-15_09-47-27'
MySQL binlog position: filename 'mysql-bin.000011', position '655'
160615 09:47:34 [00] Writing backup-my.cnf
160615 09:47:34 [00]        ...done
160615 09:47:34 [00] Writing xtrabackup_info
160615 09:47:34 [00]        ...done
xtrabackup: Transaction log of lsn (1604789) to (1604789) was copied.
160615 09:47:35 completed OK!

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

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

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

backup_type = full-backuped
from_lsn = 0
to_lsn = 1604789
last_lsn = 1604789
compact = 0
recover_binlog_info = 0

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

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

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

mysql-bin.000011 655

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

uuid = 21c7cde7-329b-11e6-b888-000c2923351b
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.33-log
start_time = 2016-06-15 09:47:31
end_time = 2016-06-15 09:47:34
lock_time = 0
binlog_pos = filename 'mysql-bin.000011', position '655'
innodb_from_lsn = 0
innodb_to_lsn = 1604789
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

5.xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

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

准备(prepare)一个完全备份

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

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

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

示例:

# innobackupex --apply-log /tmp/xtrabackup/2016-06-15_09-47-27/
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1605142
160615 10:26:54 completed OK!

看到这几行说明已经完成"准备"

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

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

注意：恢复不用启动MySQLinnobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。usage:innobackupex --copy-back /path/to/BACKUP-DIR

# innobackupex --copy-back /tmp/xtrabackup/2016-06-15_09-47-27/

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

160615 10:46:34 [01] ...done

160615 10:46:34 completed OK!

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

# service mysqld start

使用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

--创建测试数据库

mysql> CREATE DATABASE samlee1;

第一次增量备份:# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/full_backup/

mysql> CREATE DATABASE samlee2;

第二次增量备份:# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/2016-06-15_13-26-08/

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/2016-06-15_13-26-08

# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2016-06-15_13-28-42/

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

# rm -rf /mydata/data/*
# innobackupex --copy-back /tmp/xtrabackup/full_backup/
# chown -R mysql:mysql /mydata/data
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| samlee             |
| samlee1            |
| samlee2            |
| test               |
+--------------------+

可以看到我们前面创建的两个数据库都已经恢复成功了。

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

甚至也可以使用类似如下命令将数据备份至其它服务器：# innobackupex --stream=tar /backup | ssh user@www.samlee.com "cat - > /backups/`date +%F_%H-%M-%S`.tar"

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

同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：# innobackupex --remote-host=root@www.samlee.com /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) “导出”表

导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过–export选项将某表导出了：Usage: innobackupex –apply-log –export /path/to/backup

示例：# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup/

# innobackupex --apply-log --export /tmp/xtrabackup/2016-06-15_14-45-06/

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

# ls /tmp/xtrabackup/2016-06-15_14-45-06/hellodb/*.exp

classes.exp students.exp scores.exp toc.exp

coc.exp teachers.exp courses.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
1 row in set (0.02 sec)在samlee库中创建这个表:
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 samlee.students DISCARD TABLESPACE;

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

##注意权限

# cp /tmp/xtrabackup/2016-06-15_14-45-06/hellodb/students{.ibd,.exp} /mydata/data/samlee/

# chown mysql.mysql /mydata/data/samlee/students.*

mysql> ALTER TABLE samlee.students IMPORT TABLESPACE;

备份注意：

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

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

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

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