【IT168 技术】本文将探讨如何通过MySQL数据库的高级特性，实现数据库的双向自动同步，确保数据的冗余与完整性。通过以往真实的项目实战与经验，把操作实施过程全部记录下来，主要有以下几个主要内容。
1、简介
2、MySQL的环境准备
3、MySQL的配置
4、MySQL的测试
一、简介
1、背景介绍
最近接到一个项目，由于项目本身所用软件均为开源，所以在数据库选择上也采用了业界通用的开源数据库软件MySQL作为其后台数据存储仓库。为了确保数据的安全性和及时性，我们需要配置如何实现两台MySQL数据库内容的双向自动同步与监控，来保障数据库的数据冗余和数据安全。
2、MySQL介绍
MySQL数据库是业界著名的开源关系型数据库之一，也是一种关联数据库管理系统。关联数据库将数据保存在不同的表中，而不是将所有的数据放在一个大仓库中，这样就增加了速度提高了灵活性，搭配PHP和Apache可以组成良好的开发环境。
3、基本概念
为了更好的理解和配置MySQL，需要提前了解以下概念，由于我们在项目中使用的功能有限，所以仅介绍一些基本概念。
3.1 视图
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询(即：包含一 个SQL查询)，仅仅是用来查看存储在别处的数据的一种设施。视图可以嵌套，但不能索引，也不能有关 联的触发器或默认值。并非所有视图都是可更新的，如果MySQL不能正确确定被更新的基数据，则不允 许更新(包括插入和删除)。视图不能更新的情况：(1)分组，使用GROUP BY和HAVING;(2)联接; (3)子查询;(4)并;(5)聚集函数，Min/Count/Sum等;(6)DISTINCT;(7)导出列。
3.2 存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合(实际上是一种函数)，可将其视为批处理文件，虽然它们的作用不仅限于批处理。使用存储过程有3个主要的好处：简单、安全、高性能。比如启动的服务，相关的设定等。
3.3 触发器
触发器是MySQL响应DELETE/INSERT/UPDATE语句而自动执行的一条MySQL语句(或位于BEGIN/END间的一组语句)。只有表才支持触发器，视图不支持，临时表也不支持。触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，所以每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE的之前和之后)。单一触发器不能与多个事件或多个表关联。
4、MySQL的结构图
为了更好的了解和配置MySQL，就必须先了解一下MySQL的体系结构。如下图所示：
▲MySQL体系架构图
理解MySQL的体系架构对于成功的配置和调试至关重要。以下将对架构图进行简要的说明：
① Connectors指的是不同语言中与SQL的交互接口，例如适用于Java的JDBC，.Netframework的ODBC。
② Management Serveices & Utilities：系统管理和控制工具集合，例如备份还原，安全复制等功能。
③ Connection Pool：连接池，用于管理缓冲用户连接，线程处理等需要缓存的需求。
④ SQL Interface：SQL接口，用于接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。
⑤ Parser：解析器，用于SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
⑥ Optimizer：查询优化器，用于SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。
⑦ Cache和Buffer：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
⑧ Engine：存储引擎，存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。Mysql的存储引擎是插件式的。它根据MySql提供的文件访问层的一个抽象接口来定制一种文件访问机制(这种访问机制就叫存储引擎)。现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB。
二、MySQL环境准备
为了便于看到效果，此文中我们将提前安装两个带有MySQL软件的Redhat6.5操作系统作为AB两个数据库服务器。操作系统和MySQL的安装并非此文章的介绍重点，详细安装步骤可以参照此链接(http://www.mysql.com/downloads/)。
以下架构，简单描述了两台MySQL服务器自动同步数据的过程。
1、硬件列表
▲Master数据库服务器
▲ Slave数据库服务器
2、逻辑部署架构图
▲ MySQL同步逻辑架构图
三、MySQL 的数据库同步配置
1、配置Develop数据库同步
配置的前提是，我们已经顺利的将MySQL组件安装到了两个不同的系统中，并分别创建了developDB和testingDB同时建议将selinux功能关闭，开放所有的防火墙。通过ssh指令，可以远程登录到安装好的Linux服务器，可以通过如下指令，检查MySQL是否安装及其版本。
▲软件及版本检查
1、首先开始对开发数据库设置开发数据库同步,通过Linux自带的VI工具打开MySQL的配置文件。
my.cnf，路径为/etc/my.cnf。添加以下内容：
server-id=1 (mysql标示)
log-bin=mysql-bin(开启log-bin二进制日志文件，默认存在/var/lib/mysql下日志文 件以mysql-bin为开头)
binlog-do-db =developDB(developDB为需要同步的数据库名，如需同步多个数据库，可以另起行如binlog-do-db =testing)
# binlog-ignore-db=db_name (不进行同步日志的数据库，不需要的话注释掉)
max-binlog-size=104857600(日志的大小，超出会自动生成一个新的)
master-host=192.168.2.4(同步主机)
master-user=develop(同步用户)
master-password=*****(同步用户密码)
master-port=3306
replicate-do-db=developDB(同步数据库)
修改完后保存退出，并重启mysql 如：service mysql restart (重启正常，表示配置没错误)
2、在作为开发数据库的mysql上建立一个账户专门用于测试数据库来进行数据同步。
▲授权访问
在测试数据库上测试账户develop是否可以访问开发数据库上的mysql。
mysql -u develop -p -h 192.168.2.4(输入密码***，可以访问说明设置正确)
2、配置Testing数据库同步
通过ssh指令，可以远程登录到安装好的另一台Linux服务器，可以通过如下指令，检查MySQL是否安装及其版本。
▲软件及版本检查
1、首先开始对测试数据库设置数据库同步,通过Linux自带的VI工具打开MySQL的配置文件
my.cnf，路径为/etc/my.cnf。添加如下内容：
server-id= 2 (mysql标示，不能出现重复)
log-bin=mysql-bin(开启log-bin二进制日志文件，默认存在/var/lib/mysql下日志文件以mysql-bin为开头)
binlog-do-db = developDB(developDB为需要同步的数据库名，如需同步多个数据库，可以另起行如binlog-do-db=testing)
max-binlog-size=104857600(日志的大小，超出会自动生成一个新的
master-host=192.168.2.3 (同步Master的ip地址)
master-user=develop(同步所需的账号)
master-password=***** (同步账号的密码)
master-port=3306 (mstart 中mysql的访问端口)
replicate-do-db=developDB (所需同步的数据库名)
master-connect-retry=60 (主服务器宕机或连接丢失的情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数。
log-bin=mysql-bin (开启log-bin二进制日志文件)
修改完后保存退出，并重启mysql，service mysql restart (重启正常，表示配置没错误)
2、在作为测试数据库的mysql上建立一个账户专门用于开发数据库来进行数据同步。
▲授权访问
在开发数据库上测试账户develop是否可以访问测试数据库上的mysql。
mysql -u develop -p -h 192.168.2.3(输入密码***，可以访问说明设置正确)
3、验证数据库同步
完成双方数据库同步的配置后，我们需要在不同的主机进行配置验证。首先通过ssh登录开发数据库mysql> 输入show master status;，显示如下信息：
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 189 | developDB | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>输入 show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.3
Master_User: develop
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 207
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 344
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: developDB
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 207
Relay_Log_Space: 344
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
通过sssh 登录测试数据库输入以下命令：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 207 | developDB | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.4
Master_User: develop
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 189
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: developDB
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 189
Relay_Log_Space: 326
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
如果两次的命令输入均显示如下内容，就证明我们的配置已经成功并生效。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
四、测试
通过前面的配置和验证，两台MySQL服务器已经建立了同步机制并可以正常运行了。下面将通过在双方数据库上添加新的表，来测试另外的服务器上是否可以及时同步过去。
1、首先登录开发数据库，在数据库developDB中建立一张新表，名字为test。
mysql>use developDB;
mysql>create table test(id int);
完成后，转到测试数据库服务器，通过如下命令查询，是否已经同步。
mysql>use developDB;
mysql>show tables;
+------------------+
| Tables_in_developDB |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)
由此可以知道，新建表格test已经被同步到测试数据库中。
2、登录测试数据库服务器，在数据库developDB中建立一张新表，名字为test2。
mysql>use developDB;mysql>create table test2(id int);
完成后，转到开发数据库服务器，通过如下命令查询，是否已经同步。
mysql>use developDB;
mysql>show tables;
+------------------+
| Tables_in_developDB |
+------------------+
| test2 |
+------------------+
1 row in set (0.00 sec)
通过测试可以确认，数据库可以进行双向的自动同步，确保数据的冗余和高可用。
五、总结
本文主要介绍了MySQL同步的配置及验证，通过配置使其应用到实际的生产环境中。除此之外，也可以利用my.cnf配置文件，实现单向及多主的同步，甚至有选择性的同步。本文中暂时不讨论其他的同步方式，建议参考MySQL的官方文档。
