MySQL数据备份与同步

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

mysqldump

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

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

使用mysqldump进行数据备份&＃xff0c;至少有两个问题&＃xff1a;

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

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

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

下面介绍mysqlbinlog

mysqlbinlog

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

[mysqld]

log-bin&＃61;mysql-bin   //[必须]启用二进制日志

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

-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启动都会增加一个文件。

下面回到上节提出的问题&＃xff0c;如何采用全量mysqldump&＃43;增量mysqlbinlog的方式进行数据恢复&＃xff1f;

方法其实很简单&＃xff0c;在每次使用mysqldump进行全量数据备份时&＃xff0c;用--flush-logs选项&＃xff1a;

mysqldump --single-transaction --flush-logs --master-data&＃61;2 > backup.sql

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

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

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

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

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

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

主从同步

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

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

我讲一讲我在配置中遇到的2个问题&＃xff1a;

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

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

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

主数据库&＃xff1a;

[mysqld]

binlog-do-db&＃61;exampledb

从数据库&＃xff1a;

[mysqld]

replicate-do-db&＃61;exampledb

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

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

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