MySQL备份与复制

浏览数：23 / 时间：2015年06月12日

1、二进制日志

二进制日志记录了数据库的所有改变，使得任何slave都可以执行相同的更新。一般来说，开启二进制日志大概会有1%的性能损耗，它有两个重要的使用场景：

（1）备份：在某个时间点t作了一次备份，然后利用binary log记录从这个时间点t后的所有对数据库的改动,然后下一次还原的时候，利用时间点t的备份文件和这个binary log文件，就可以将数据还原至最新时点。

（2）复制：在master端开启binary log后，binary log记录所有数据库的改动，然后slave端获得这个binary log文件内容，就可以在slave端进行同样的操作，使master和slave保持一致。

Tips:

（1）二进制日志按master上的提交顺序记录事务；

（2） select语句一般不会被记录，因为它对数据库不产生变动；

（3）那些尚没有但是可能改变数据库的语句也会记录下来，如drop table if exists 或create table if not exists，以及那些不匹配任何行的语句，如带有where条件的delete和update语句。

2、MySQL 备份

2.1 备份类型

（1）按备份操作方式：

备份方式

优点　　　　　　　　　　

缺点

逻辑备份

1. 逻辑备份是可以用编译器或像grep和sed之类的命令查看和操作的普通文件；

2. 恢复简单，非常灵活；

3. 与存储引擎无关。

1. 还原时需要mysql加载和解释语句，转化为存储格式，并重建索引，所以会比较慢；

2. 无法保证导出后再还原出来的一定是同样的数据。浮点数、软件BUG等都会导致问题；

3. 必须由数据库服务器完成生成逻辑备份的工作，因此要使用更多的CPU周期。

物理备份

1. 基于文件的物理备份，只需要将需要的文件复制到其他地方即可完成备份；

2. 恢复更简单；

3. 恢复快，因为MySQL服务器不需要执行任何SQL或构建索引。

1. InnoDB的原始文件通常比相应的逻辑备份要大得多；

2. 物理备份不总是可以跨平台、操作系统及MySQL版本。文件名大小写敏感和浮点格式可能会遇到麻烦。

（2）按是否备份全部数据：

完全备份

增量备份

差异备份

　　一般情况下，根据备份策略组合使用：完全+增量；完全+差异

2.2 常用备份工具

2.2.1 mysqldump

mysqldump作为重要的MySQL备份工具，功能相当强大。备份参数、恢复策略，需要仔细研究。

（1）基本语法

备份单个数据库或单个数据库中的指定表：

mysqldump [OPTIONS] database [tb1] [tb2]…

备份多个数据库：

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

备份所有数据库：

mysqldump [OPTIONS] --all-databases [OPTIONS]

（2）选项[OPTIONS]说明

--default-character-set=charset

指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

--lock-all-tables，-x

在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。

--lock-tables

和 --lock-all-tables 类似，不过是锁定当前导出的数据表，而不是锁定全部库下的表。本选项只适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 选项。

--no-create-info，-t

只导出数据，而不添加 CREATE TABLE 语句。

--no-data, -d

只导出数据库表结构，不导出任何数据。

--opt

这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 mysqldump 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

--quick，-q

该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

--routines，-R

导出存储过程以及自定义函数。

--single-transaction

该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。

本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。
要想导出大表的话，应结合使用--quick 选项。

--triggers

同时导出触发器。该选项默认启用，用 --skip-triggers 禁用它。

（3）注意事项：

以下是使用mysqldump进行数据迁移时，需要注意的一些事项：

a. 表结构备份和数据备份最好分开来做

b. 在进行表结构备份的时候，记得加上--add-drop-database=false和--add-drop-table=false，以免在进行增量恢复时，覆盖已经存在的数据；如若需要全量恢复，可以先手工drop database或者drop table，再使用表结构备份脚本来恢复表结构

c. innodb要想维持数据一致性，加上--single-transaction，这个参数不会阻塞读写

d. 备份blob数据，需要加上--hex-blob，否则恢复的时候可能会报错

e. 如果想要进行增量数据恢复，那么备份的时候最好加上--add-locks。否则在恢复过程中，表将无法进行读写。

f. 备份完成之后，tail -n 1 backup_file。成功的备份会显示如下信息“-- Dump completed on 2014-04-06 10:40:32”

2.2.2 mysqlbinlog

（1）基本语法　

mysqlbinlog [options] log-files

（2）常用[OPTIONS]说明

--start-position：开始位置

--stop-position：结束位置

--start-date ‘yyyy-mm-dd hh:mm:ss‘：开始时间

--stop-date ‘yyyy-mm-dd hh:mm:ss‘ ：结束时间

在时间点17:17:24，删除了数据库中的几条记录，可以通过mysqlbinlog进行恢复。

　　基于时间点的恢复：

mysqlbinlog --stop-date="2014-04-07 17:17:24" mysql-bin.000001|mysql -uroot -h127.0.0.1

基于位置的恢复：　

mysqlbinlog --stop-position="793" mysql-bin.000001|mysql -uroot -h127.0.0.1

2.2.3 Xtrabackup

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

（1）备份过程快速、可靠；

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

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

（4）自动实现备份检验；

（5）还原速度快；

3、MySQL复制

3.1 复制过程

总的来说，复制有三个步骤：

（1）在主库上把数据更改记录到二进制日志中；

（2）备库将主库的二进制日志复制到其本地的中继日志中；

首先，备库会启动一个工作线程，称为I/O线程，I/O线程会建立一个到主库的TCP/IP连接；然后在主库上启动一个特殊的二进制转储线程（binlog dump，该线程没有对应的SQL语句），这个二进制转储线程会读取主库上二进制日志中的事件。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知有新的事件产生时才会被唤醒，备库I/O线程会将接收到的事件记录到中继日志中。

（3）备库读取中继日志中的事件，将其重放到备库数据之上。

当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中。

架构的优点：实现了获取事件和重发事件的解耦，允许这两个过程异步进行。I/O线程能够独立于SQL线程之外工作。

缺点：主库上并发运行的查询在备库上只能串行化执行，因为只有一个SQL线程来重放中继日志的事件。这是很多工作负载的瓶颈所在。

3.2 配置复制

创建复制账号

GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘10.250.7.50‘ IDENTIFIED BY‘rep123‘;

配置主库

对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值：

重启master，运行SHOW MASTER STATUS

配置备库

备库在my.cnf中增加类似的配置，如下：

server_id是必须且唯一的。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log；

relay_log指定中继日志的位置和命名；

log_slave_updates表示允许备库将其重放的事件也记录到自身的二进制日志中。

启动复制

告诉备库如何连接到主库并重放其二进制日志。

mysql> change master to master_host=‘10.250.7.60‘,master_user=‘rep‘,master_password=‘rep123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=107;

mysql> start salve；

运行SHOW SLAVE STATUS查看输出结果：

3.3 复制模式

复制模式	优点	缺点
Statement	1. 语句的方式执行复制过程基本就是执行SQL语句，这意味着所有在服务器上发生的变更都以一种容易理解的方式运行，出问题时可以很好的定位； 2. 不需要记录每一行数据的变化，减少了 binlog 日志量，节省 I/O 以及存储资源，提高性能。	1. 对于触发器或者存储过程，存在大量bug； 2. 很多情况下无法正确复制
Row	1. binlog会非常清楚的记录下每一行数据修改的细节，非常容易理解； 2. 几乎没有基于行的复制模式无法处理的场景，对于所有的SQL构造、触发器、存储过程都能正确执行。	1. 会产生大量的日志内容 2. 难以定位问题 3. 难以进行时间点恢复
Mixed	默认情况下使用基于语句的复制方式，如果发现语句无法被正确的复制，就切换成基于行的复制模式。