当前位置： sql数据库同步方式-sql数据库表同步
【IT168技术】本文将讨论如何利用MySQL数据库的高级特性，实现数据库的双向自动同步，保证数据冗余和完整性。 通过以往真实项目的实战经验，记录整个操作实施过程，主要包括以下主要内容。
1 简介
2. MySQL环境准备
3.MySQL配置
4.MySQL测试
一、简介
一、背景介绍
最近接到一个项目，因为项目中使用的软件本身是开源的，所以在数据库选型上也采用了业界常见的开源数据库软件MySQL作为其后台数据存储仓库。 为了保证数据的安全和及时性，我们需要配置如何实现两个MySQL数据库内容的双向自动同步和监控，以保证数据库的数据冗余和数据安全。
2. MySQL简介
MySQL数据库是业界知名的开源关系型数据库之一，也是一种关系型数据库管理系统。 关系数据库将数据存储在不同的表中，而不是将所有数据放在一个大仓库中，这样可以提高速度并提高灵活性。 可与PHP、Apache形成良好的开发环境。
3. 基本概念
为了更好的理解和配置MySQL，需要提前了解以下概念。 由于我们在项目中使用的功能有限，这里只介绍一些基本概念。
3.1 视图
视图是虚拟表。 与包含数据的表不同，视图只包含在使用时动态检索数据的查询（即：包含 SQL 查询），并且仅仅是查看存储在别处的数据的工具。 视图可以嵌套，但不能被索引，也不能有关联的触发器或默认值。 并不是所有的视图都是可更新的，如果 MySQL 不能正确判断正在更新的基础数据，则不允许更新（包括插入和删除）。 无法更新视图的情况：（1）分组，使用GROUP BY和HAVING； (2) 加入； (3) 子查询； （四）工会； (5)聚合函数、Min/Count/Sum等； (6) DISTINCT ;(7) 导出列。
3.2 存储过程
简单来说，存储过程就是为以后使用而保存的一条或多条MySQL语句（实际上是一个函数）的集合，可以看成是一个批处理文件，尽管它们的作用不限于批处理。 使用存储过程有三个主要好处：简单性、安全性和高性能。 比如启动的服务，相关的设置等等。
3.3 触发器
触发器是 MySQL 响应 DELETE/INSERT/UPDATE 语句而自动执行的 MySQL 语句（或 BEGIN/END 之间的一组语句）。 触发器只支持表，不支持视图，也不支持临时表。 每个表的每个事件都定义了触发器，每个表的每个事件只允许一个触发器，因此每个表最多支持 6 个触发器（在每个 INSERT、UPDATE 和 DELETE 之前和之后）。 单个触发器不能与多个事件或多个表相关联。
4. MySQL的结构图
为了更好的理解和配置MySQL，首先要了解MySQL的架构。 如下所示：
▲ MySQL架构图
了解 MySQL 的体系结构对于成功配置和调试至关重要。 下面将对架构图进行简要说明：
① 连接器指的是与不同语言的SQL交互的接口，比如JDBC for Java，ODBC for .Netframework。
② Management Services & Utilities：系统管理和控制工具的集合，如备份和恢复、安全复制等功能。
③连接池：连接池用于管理缓存的用户连接，线程处理，以及其他需要缓存的需求。
④ SQL Interface：SQL接口，用于接受用户的SQL命令，返回用户需要查询的结果。 比如select from就是调用SQL Interface。
⑤ Parser：解析器，用于传递给解析器的SQL命令，由解析器进行校验和解析。 解析器由 Lex 和 YACC 实现，是一个很长的脚本。
⑥ Optimizer：查询优化器，用于SQL语句在查询前使用查询优化器对查询进行优化。 他正在使用“select-projection-join”策略进行查询。
⑦ Cache和Buffer：查询缓存。 如果查询缓存中有命中的查询结果，查询语句可以直接从查询缓存中取数据。 这种缓存机制是由一系列的小缓存组成的。 如表缓存、记录缓存、键缓存、权限缓存等。
⑧ Engine：存储引擎，存储引擎是MySql中处理文件的具体子系统。 也是Mysql最有特色的地方。 Mysql的存储引擎是插件式的。 它基于MySql提供的文件访问层抽象接口，定制了一种文件访问机制（这种访问机制称为存储引擎）。 现在有很多种存储引擎，每一种存储引擎的优点都不一样。 最常用的是 MyISAM、InnoDB 和 BDB。
二、MySQL环境准备
为了方便看到效果，本文我们先安装两台Redhat6.5操作系统和MySQL软件作为AB的两台数据库服务器。 操作系统和MySQL的安装不是本文的重点。 详细安装步骤请参考此链接（）。
下面的架构简单描述了两台MySQL服务器之间自动同步数据的过程。
1.硬件清单
▲主数据库服务器
▲ 从数据库服务器
2.逻辑部署架构图
▲MySQL同步逻辑架构图
3.MySQL数据库同步配置
1.配置Development数据库同步
配置的前提是我们已经成功将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=*****（同步用户密码）
主端口=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 > input show master status;，显示如下信息：
+----------------+----------+------------+---- --------------+
| 文件 | 当前位置 | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+------------+---- --------------+
| mysql-bin.000005 | 189 | 开发数据库 | |
+----------------+----------+------------+---- --------------+
一组中的 1 行（0.00 秒）
mysql> 输入显示从站状态\G
*************************** 1. 行 ******************** * *****
Slave_IO_State：等待master发送事件
Master_Host：192.168.2.3
Master_User：开发
主端口：3306
连接重试：60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos：207
中继日志文件：mysqld-relay-bin.000002
Relay_Log_Pos：344
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running：是
Slave_SQL_Running：是
Replicate_Do_DB：开发数据库
复制_忽略_DB：
复制_做_表：
复制_忽略_表：
Replicate_Wild_Do_Table：
Replicate_Wild_Ignore_Table：
Last_Errno：0
最后一个错误：
跳过计数器：0
Exec_Master_Log_Pos：207
中继日志空间：344
Until_Condition：无
直到_Log_File：
直到_Log_Pos：0
Master_SSL_Allowed：否
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher：
Master_SSL_Key：
Seconds_Behind_Master：0
一组中的 1 行（0.00 秒）
通过sssh登录test数据库，输入以下命令：
mysql> 显示主状态；
+----------------+----------+------------+---- --------------+
| 文件 | 当前位置 | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+------------+---- --------------+
| mysql-bin.000005 | 207 | 开发数据库 | |
+----------------+----------+------------+---- --------------+
一组中的 1 行（0.00 秒）
mysql> 显示从站状态\G
*************************** 1. 行 ******************** * *****
Slave_IO_State：等待master发送事件
Master_Host：192.168.2.4
Master_User：开发
主端口：3306
连接重试：60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos：189
中继日志文件：mysqld-relay-bin.000002
Relay_Log_Pos：326
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running：是
Slave_SQL_Running：是
Replicate_Do_DB：开发数据库
复制_忽略_DB：
复制_做_表：
复制_忽略_表：
Replicate_Wild_Do_Table：
Replicate_Wild_Ignore_Table：
Last_Errno：0
最后一个错误：
跳过计数器：0
Exec_Master_Log_Pos：189
中继日志空间：326
Until_Condition：无
直到_Log_File：
直到_Log_Pos：0
Master_SSL_Allowed：否
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher：
Master_SSL_Key：
Seconds_Behind_Master：0
一组中的 1 行（0.00 秒）
如果在两个命令输入中显示如下内容，则证明我们的配置已经成功且有效。
Slave_IO_Running：是
Slave_SQL_Running：是
4.测试
通过前面的配置和验证，两台MySQL服务器已经建立了同步机制，可以正常运行。 接下来，我们将测试是否可以通过在两个数据库上添加新表来及时同步其他服务器。
1、首先登录开发数据库，在数据库developDB中新建一张表，命名为test。
mysql>使用developDB；
mysql>创建表测试（id int）；
完成后，进入测试数据库服务器，通过以下命令查询是否已经同步。
mysql>使用developDB；
mysql>显示表；
+------------------+
| 表_in_developDB |
+------------------+
| 测试 |
+------------------+
一组中的 1 行（0.00 秒）
由此我们可以知道，新表test已经同步到test数据库中了。
2. 登录test数据库服务器，在数据库developDB中新建表test2。
mysql>使用 developDB;mysql>创建表 test2(id int);
完成后，到开发数据库服务器，通过以下命令查询是否已经同步。
mysql>使用developDB；
mysql>显示表；
+------------------+
| 表_in_developDB |
+------------------+
|测试2|
+------------------+
一组中的 1 行（0.00 秒）
通过测试可以确认，数据库可以进行双向自动同步，保证数据冗余和高可用。
五、总结
本文主要介绍MySQL同步的配置和验证sql数据库同步方式，并通过配置应用到实际生产环境中。 另外，还可以利用my.cnf配置文件实现单向多主同步sql数据库同步方式，甚至选择性同步。 本文暂不讨论其他同步方式。 推荐参考MySQL官方文档。返回搜狐查看更多
上一篇 
