前段时间使用MySQL作为数据存储做了一个小项目。项目上线运行了几十天之后，数据已经越来越多，达到了100多M。用mysqldump每天备 份全量数据然后传输到另外一台机器上这种方式进行数据备份，久而久之越来越慢。于是开始研究如何利用mysql的主从同步功能实现自动备份。如果实现自动 备份，主从服务器之间只需要在有数据更新时同步一点增量数据，不会在备份时占用大量的CPU和内网的网络带宽资源了。介绍主从同步之前，还是先从基础的 mysqldump备份开始讲起。

mysqldump

mysqldump是mysql数据库提供的一个数据备份工具。顾名思义，mysqldump可以把mysql数据库导出成sql语句文件，并保存到磁盘上。mysqldump命令产生的.sql文件包含一系列SQL INSERT语句，可以用来进行数据恢复。

假定我们在星期日下午1点进行了备份，此时负荷较低。下面的命令可以完全备份所有数据库中的所有表：

使用mysqldump进行数据备份，至少有两个问题：

1 mysqldump运行时，需要消耗一定的计算资源。而且数据库越大，消耗的计算资源也就越多，因此可能会造成系统在备份时运行效率低，容易造成用户卡死。

2 对mysqldump备份的数据进行恢复，会丢掉从备份点开始的更新数据。

为了解决第2点的问题， mysql文档中给出了一个解决办法。那就是利用mysqlbinlog二进制文件保存增量的数据。采用全量mysqldump+增量mysqlbinlog的方式进行数据恢复。

下面介绍mysqlbinlog

mysqlbinlog

mysqlbinlog就是mysql的二进制数据文件。在对mysql进行一些配置之后，mysql会把数据库的更新操作都记录在一个文件中。mysqlbinlog可以在mysqld的--bin-log选项或者在配置文件(my.cnf或者my.ini)中打开。

[mysqld]

log-bin=mysql-bin   //[必须]启用二进制日志

在启用了二进制日志以后，在mysql的数据目录下，会出现一些以数字为结尾的文件，例如：

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 mysql-bin.000001

-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 mysql-bin.000002

这些文件就是二进制的日志文件。每次mysql启动都会增加一个文件。

下面回到上节提出的问题，如何采用全量mysqldump+增量mysqlbinlog的方式进行数据恢复？

方法其实很简单，在每次使用mysqldump进行全量数据备份时，用--flush-logs选项：

 mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

在使用这样的语句进行备份之后，mysql就会关闭原来的二进制日志文件，开启一个新的二进制日志文件。比如，新开启的二进制日志文件为 mysql-bin.000003。 那么在进行数据恢复的时候，你可以利用backup.sql进行全量恢复+ mysql-bin.000003进行增量同步。

数据恢复的方法也很简单。

mysqlbinlog是一个读取 mysql二进制日志输出sql语句的命令行工具。使用方法可以从 http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/client-side-scripts.html#mysqlbinlog 查到。

还记得上文提出的mysqldump备份的两个问题吗，现在第二个问题解决了，第一个问题还没有解决

“ mysqldump运行时，需要消耗一定的计算资源。而且数据库越大，消耗的计算资源也就越多，因此可能会造成系统在备份时运行效率低，容易造成用户卡死。”

下文中我们利用mysql主从同步来解决这个问题。

主从同步

主从同步的含义非常简单。通过一定的设置，让两台或者多台mysql服务器的数据保持一致。设置的方法网上已经有很多方法了，推荐这篇帖子http://369369.blog.51cto.com/319630/790921

设置成主从同步之后，基本上就免去了每天全量备份之苦。而且一但主数据库出问题，可以马上切换到从数据库进行服务，大大减少了故障恢复的时间。

我讲一讲我在配置中遇到的2个问题：

1 在从服务器上 show slave status时，显示 Slave_SQL_Running: No. 错误的原因是 mysql 数据库的db表已经存在，不能再建立。

错误的原因是这样的， 我在每台数据上都运行了 mysql_install_db这个命令安装了 mysql test info_schema这3个数据库。当我主从同步开始时，主数据库要向从数据库同步建立mysql数据库的操作。而从数据库已经建立了mysql数据 库。

我解决的方法是在配置文件里指明写二进制文件的数据库名称。只有真正需要同步的业务数据库才写二进制文件。

主数据库：

[mysqld]

binlog-do-db=exampledb

从数据库：

[mysqld]

replicate-do-db=exampledb

2 我的主数据库已经运行有一段时间了。在从服务器设置master_log_pos的时候设置成主服务器的当前日志位置。结果同步时也出现了Slave_SQL_Running: No. 错误的原因是： 执行Insert语句时数据表没有建立。

错误的原因也很简单，我在从数据库里面还没有建立对应的数据库，而同步的操作为插入数据。

解决的方法是通过mysqldump对主数据库进行一次全量数据备份，并且在从数据库中恢复这个备份之后才开始进行主从同步。