MySQL数据库实现双向自动同步
|
【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命令，并且返回用户需要查询的结果。比如selectfrom就是调用SQL Interface。 ⑤ Parser：解析器，用于SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。 ⑥ Optimizer：查询优化器，用于SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。 ⑦ Cache和Buffer：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。 ⑧ Engine：存储引擎，存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。Mysql的存储引擎是插件式的。它根据MySql提供的文件访问层的一个抽象接口来定制一种文件访问机制(这种访问机制就叫存储引擎)。现在有很多种存储引擎，各个存储引擎的优势各不一样mysql数据表，最常用的MyISAM,InnoDB,BDB。 二、MySQL环境准备 为了便于看到效果，此文中我们将提前安装两个带有MySQL软件的Redhat6.5操作系统作为AB两个数据库服务器。操作系统和MySQL的安装并非此文章的介绍重点，详细安装步骤可以参照此链接()。 以下架构，简单描述了两台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的官方文档。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
