Mysql主从同步 读写分离

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:



 Mysql主从同步 读写分离

 

目录

一.概述4

实验目的4

三.试验环境4

方案说明4

五.Mysql主从同步配置5

1. 安装与初始化数据库5

2. 主从服务器均授予MySQL远程登录权限。5

3. 主从同步配置5

六.Amoeba读写分离配置13

1. Amoeba下载安装13

2. 安装Java13

3. 修改amoeba的配置文件amoeba.xmldbServers.xml的配置。14

4. 启动amoeba进行测试。20

七.读写分离测试。21

1. 写测试21

2. 读测试24

 一.概述

Amoeba是一个以MySQL为底层数据存储,并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求,依据用户事先设置的规则,将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比,作者强调的是amoeba配置的方便(基于XML的配置文件,用SQLJEP语法书写规则,比基于lua脚本的MySQL Proxy简单)。

实验目的

实际的生产环境中,由单台Mysql作为独立的数据库是完全不能满足实际需求的,无论是在安全性,高可用性以及高并发等各个方面。通过主从同步(Master-Slave)的方式来同步数据,再通过读写分离(amobe)来提升数据库的并发负载能力。

部署MySQL主从同步与读写分离,同时可以使我们真正的了解其内部工作原理,更好的认识安畅云数据库,快速定位故障并及时解决,为客户提供更专业的IT服务。

三.试验环境

设备名称

管理IP

操作系统

用途

Slave1

172.16.200.82

Centos 6.5 64bit

Mysql5.1.73

Slave2

172.16.200.80

Centos 6.5 64bit

Mysql5.1.73

amoeba-mysql-master

172.16.200.81

Centos 6.5 64bit

Amobe 3.0.5 Mysql5.1.73

 

初始配置:关闭防火墙或将33068066添加例外,关闭Selinux

方案说明

本方案为利用amobe实现mysql数据库读写分离,提升数据库并发负载能力,同时配置mysql主从同步,增加数据库安全性和高可用性能。

 

五.Mysql主从同步配置

1. 安装与初始化数据库

主从服务器均安装MySQL,并设置开机自启动。

Yum install mysql mysql-devel mysql-server

/etc/init.d/mysqld start

Chkcofig mysqld on

分别设置MySQL数据库密码为123.com

mysqladmin -u root password "123.com"

注:

所有机器的MySQL数据库密码要相同,amoeba配置要求。

2. 主从服务器均授予MySQL远程登录权限。

mysql> grant all privileges on *.* to  root@"172.16.200.%"  identified by "123.com";

mysql> flush privileges;

注:

我这里为配置简单,将root用户开启远程登录,在生产环境中建议新建MySQL用户并设置相关的登录权限。例如:限制源IP,允许访问的库文件等。

3. 主从同步配置

(1). 在amoeba-mysql-master上创建数据库文件

相关操作命令如下:

create database 数据库名称;               //创建数据库

Use 数据库;                             //改变所使用的数据库

create table 表名称(字段名称 数据类型);//创建表

description 表名称;                       //查看表结构

select 字段 from 表;                    //数据库查询

wKioL1nkcESA7orbAALWuJzRekw879.png-wh_50 

查看amoeba-mysql-master目前数据库列表文件是否创建成功

 wKioL1nkcF2A46h3AAAMqheR9RQ445.png-wh_50

(2). 分别修改主从服务器的MySQL配置文件对新建数据库master_test文件进行同步。

修改amoeba-mysql-master服务器的/etc/my.cnf文件配置如下:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

log-bin=mysql-bin   #打开mysql二进制日志

server-id=1     #设置mysql_id,主从不能相同

binlog-do-db=master_test   #设置二进制日志记录的库

binlog-ignore-db=mysql   ##设置二进制日志不记录的库

sync_binlog=1

symbolic-links=0

# Disabling symbolic-links is recommended to prevent assorted security risks

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 wKiom1nkcy7S0bE6AAAxTxm3xf4450.png-wh_50

修改slave1服务器/etc/my.cnf配置如下:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

log-bin=mysql-bin

server-id=2

master-user=root

master-host=172.16.200.81

master-password=123.com

master-port=3306

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 wKiom1nkc0OjVYVQAAAmkJSXw5A454.png-wh_50

修改slave1服务器/etc/my.cnf配置如下:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

log-bin=mysql-bin

server-id=3

master-user=root

master-host=172.16.200.81

master-password=123.com

master-port=3306

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 wKioL1nkcKPRAkVZAAAoT43Nokc275.png-wh_50

注:MySQL主与MySQL从服务器server-id 不能相同,MySQL主的ID1,其他从服务器的ID均未1以下,保证不相同即可。

(2). 进行数据同步

amoeba-mysql-master服务器MySQL数据master_test数据库打包分别copy到从服务器MySQL数据目录/var/lib/mysql/下,并进行解压。拷贝打包好的数据可以使用scp命令。

scp master_test.tar.gz root@172.16.200.82:/var/lib/mysql/

scp master_test.tar.gz root@172.16.200.83:/var/lib/mysql/

数据解压完成分别登录主从服务器查看数据库主从同步状态

首先查看master服务器状态

mysql> show master status;

wKioL1nkcLmgCztWAAARrI-gBqQ220.png-wh_50 

其次查看slave1和slave2的状态。分别如下:

mysql> show slave status \G

Slave1状态如下:

 wKiom1nkc4WC5nn6AABFzWxeat8848.png-wh_50

Slave2状态如下:

 wKiom1nkc6jx-Aq0AABFrDqBA5I071.png-wh_50

(3). 主从同步测试

    amoeba-mysql-master服务器的MySQL新建数据库文件master_test数据口中新建表tongbu进行测试。

 

wKiom1nkc8aizOBRAAAuSrlULZk494.png-wh_50

wKioL1nkcRGRK6qbAAAu6iBD2ws573.png-wh_50


测试数据库同步配置已完成。

 

六.Amoeba读写分离配置

1. Amoeba下载安装

    登陆amoeba官网下载相应的版本https://sourceforge.net/projects/amoeba/files/,我这里下载的是amoeba-mysql-3.0.5-RC-distribution.zip。

将下载好的amoeba解压至相应的目录并修改相应的名称,我这里讲amoeba解压到了当前用户目录下,并更名为amoeba-mysql-3.0.5-RC,

wKiom1nkc-yggjYgAAAT7ei3azI219.png-wh_50 

至此amoeba安装完成,后面根据需要进行配置文件修改即可。

2. 安装Java

因为amoeba为java语言开发,所以需要安装jdk运行环境。我们使用yum安装jdk1.6

yum list available java* (查看java安装包)

wKioL1nkcUmx0EZvAACoOoFa9gs919.png-wh_50

yum install java-1.6.0-openjdk(这里安装java1.6)

配置环境变量:

java路径为/usr/bin/java, 编辑amoeba  bin/下的启动程序launcher(3.0以上版本)或者amoeba(3.0以下版本)添加如下变量JAVA_HOME=/usr。

3. 修改amoeba的配置文件amoeba.xmldbServers.xml的配置。

(1). 修改后的amoeba.xml的配置文件如下:

<?xml version="1.0" encoding="gbk"?>

 

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

 

<proxy>


<!-- service class must implements com.meidusa.amoeba.service.Service -->

<service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">

<!-- port -->

<property name="port">8066</property>  #服务端口


<!-- bind ipAddress -->

<property name="ipAddress">172.16.200.81</property> #主机地址(amoeba)


<property name="connectionFactory">

<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">

<property name="sendBufferSize">128</property>

<property name="receiveBufferSize">64</property>

</bean>

</property>


<property name="authenticateProvider">

<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">


<property name="user">root</property> #(amoeba的用户名)


<property name="password">root</property> #(amoeba的密码)


<property name="filter">

<bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">

<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>

</bean>

</property>

</bean>

</property>


</service>


<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">


<!-- proxy server client process thread size -->

<property name="executeThreadSize">128</property>


<!-- per connection cache prepared statement size  -->

<property name="statementCacheSize">500</property>


<!-- default charset -->

<property name="serverCharset">utf8</property>


<!-- query timeout( default: 60 second , TimeUnit:second) -->

<property name="queryTimeout">60</property>

</runtime>


</proxy>


<!--

Each ConnectionManager will start as thread

manager responsible for the Connection IO read , Death Detection

-->

<connectionManagerList>

<connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">

<property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>

</connectionManager>

</connectionManagerList>


<!-- default using file loader -->

<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">

<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>

</dbServerLoader>


<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">

<property name="ruleLoader">

<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">

<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>

<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>

</bean>

</property>

<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>

<property name="LRUMapSize">1500</property>

<property name="defaultPool">write</property>


<property name="writePool">write</property>

<property name="readPool">read</property>

<property name="needParse">true</property>

</queryRouter>

</amoeba:configuration>

注:对于amoeba.xml配置需要注意修改的地方,主要是管理IP地址和amoeba的服务管理方式截图如下:

 wKiom1nkdB3Bx_L-AACIjlwFzN4381.png-wh_50

确认池名称要与dbServer.xml中的名称相同

wKioL1nkcXuSy6KSAAAiXmoriSw376.png-wh_50

(2). 修改后的dbServers.xml的配置文件如下:

<?xml version="1.0" encoding="gbk"?>

 

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">

<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

 

<!--

Each dbServer needs to be configured into a Pool,

If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:

 add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig

 such as 'multiPool' dbServer   

-->


<dbServer name="abstractServer" abstractive="true">

<factoryConfig

class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">

<property name="connectionManager">${defaultManager}</property>

<property name="sendBufferSize">64</property>

<property name="receiveBufferSize">128</property>


<!-- mysql port -->

<property name="port">3306</property> #mysql服务端口


<!-- mysql schema -->

<property name="schema">master_test</property> #需要做读写分离的库文件


<!-- mysql user -->

<property name="user">root</property> #MySQL用户名


<property name="password">123.com</property> #MySQL密码

</factoryConfig>

 

<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">

<property name="maxActive">500</property>

<property name="maxIdle">500</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testOnReturn">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>

 

<dbServer name="server1"  parent="abstractServer">  #命令dbServer

<factoryConfig>

<!-- mysql ip -->

<property name="ipAddress">172.16.200.81</property> #dbServer 管理IP()

 

</factoryConfig>

</dbServer>


<dbServer name="server2"  parent="abstractServer">

<factoryConfig>

<!-- mysql ip -->

<property name="ipAddress">172.16.200.82</property> #dbServer 管理IP()

 

</factoryConfig>

</dbServer>


        <dbServer name="server3"  parent="abstractServer">

           <factoryConfig>

             <!-- mysql ip -->

             <property name="ipAddress">172.16.200.80</property> #dbServer 管理IP()

 

                </factoryConfig>

        </dbServer>

 

<dbServer name="write" virtual="true">  #write是一个虚拟的数据库的写节点

<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

<property name="loadbalance">1</property>


<!-- Separated by commas,such as: server1,server2,server1 -->

<property name="poolNames">server1</property>

</poolConfig>

</dbServer>

 

     <dbServer name="read" virtual="true"> #read是一个虚拟的数据库的读节点

         <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

              <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

              <property name="loadbalance">1</property>

 

              <!-- Separated by commas,such as: server1,server2,server1 -->

              <property name="poolNames">server3,server2</property> #负载轮询设置

          </poolConfig>

     </dbServer>

 


</amoeba:dbServers>

 

注:dbServers.xml需要的配置包括数据库的端口、用户名、密码、进行读写分离的库文件和读写地址池,已经轮询设置。

MySQL的主从服务器密码最好设置相同。

wKioL1nkcZPDeIhxAAA_BHX9aso767.png-wh_50 

定义server1masterserver2server3slave

 wKioL1nkcabBASucAABHdXQJPVo776.png-wh_50

定义writeserver1readserver2server3所在pool的数据库轮训规则,支持轮训、权重、HA所在pool的数据库服务器,多台配置已””隔开。server2server3为轮训方式工作,默认从server1server3,也可以重复添加比如server1,server2,server3server3,这样server3在查询中被链接2次。写的服务器也可以不用添加到读的pool,这样就实现server1写,server2,3读。

地址池中的writeread要与dbServer.xml中设置的池名称相同

 wKiom1nkdH6zDvVoAABWEuzfx10603.png-wh_50

至此amoeba全部配置完成。启动测试。

4. 启动amoeba进行测试。

   进入amoeba的文件路径下的bin/目录,执行./launcher(3.0版本)即可。

wKioL1nkcdyApPWTAACBtO2gVX0917.png-wh_50 

在从服务器上连接amoeba测试:

命令如下mysql -u root -p -h 172.16.200.81 -P 8066  (登录输入的密码为amoeba的密码)

wKiom1nkdKLzyW1TAAAu25rByVA698.png-wh_50 

七.读写分离测试。

1. 写测试

使用另外一台安装了MySQL客户端服务器的主机远程连接amoeba服务并在master_test库中创建 write_test表文件,并插入write_wangzx数据测试数据的的写入库。(此操作前需要将主从同步停止,更能直观反映文件写入是访问的是mysql-master服务器)

(1). 通过远程amoeba服务创建write_test表文件。

[root@i-d5g65b9d ~]# mysql -u root -p -h 172.16.200.81 -P 8066

密码为amoeba.xml配置文件中设置的密码

wKioL1nkcf-C7LtHAAAwHoW477o367.png-wh_50 

 

(2). 停止MySQL的主从同步(该操作需要在从服务器上操作即可)。相关操作命令如下:

STOP SLAVE IO_THREAD;      #停止IO进程

STOP SLAVE SQL_THREAD;     #停止SQL进程

STOP SLAVE;                             #停止IO和SQL进程

 wKiom1nkdMegsNweAAAV06b06l8022.png-wh_50

启动主从同步命令如下:(补充说明)

START SLAVE IO_THREAD;    #启动IO进程

START SLAVE SQL_THREAD;  #启动SQL进程

START SLAVE;                             #启动IO和SQL进程

 

(3). 在远程MySQL的服务器上使用master_test库中创建 write_test表文件,并插入write_wangzx相关数据数据,(1,'wangzx','master');。

mysql>  insert into write_test values(1,'wangzx','master');

wKioL1nkciaTz57pAAAXFGOeSEw881.png-wh_50 

分别登录主从服务器的MySQL查看上图新建数据是否存在。

主服务器查看的数据信息如下图:

wKioL1nkcjzTKFwpAAAL387t4_c497.png-wh_50 

两台从服务器查看到的信息如下:

Slave1信息如下;

wKiom1nkdQWDjcQPAAAUJrLLneI634.png-wh_50 

Slave2信息如下;

wKioL1nkcmGhuv5hAAAHwCK12ao076.png-wh_50

从以上信息判断,数据写入的库文件写入到了MySQL主服务器。

2. 读测试

(1). 分别登录到两台从服务器

使用master_test库中创建 write_test表文件,并分别插入write_wangzx相关数据数据,(1,'wangzx','slave1');和(1,'wangzx','slave2'); 。

mysql> insert into write_test values(1,'wangzx','slave1');

wKioL1nkcnHTv-dFAAAVdE1HdVA565.png-wh_50 

mysql> insert into write_test values(1,'wangzx','slave2');

wKiom1nkdTeiKlDoAAAVeP71xaQ895.png-wh_50 

(2).测试数据的读取以及amoeba的轮询。

通过远程连接到amoeba查看读取的数据表信息,通过表信息查看轮询主机。

输入mysql> select * from write_test;查看相关表信息,将命令连续操作两次,查看到表信息不同,并且未读取到主服务器表信息,说明读取数据时只在从服务器上进行读取,并且能实现从服务器轮询读取数据,实现负载功能。

wKioL1nkcpDjtaYVAAAWzvMO30g005.png-wh_50 

 

 

 

 

 本文转自 Bill_Xing 51CTO博客,原文链接:http://blog.51cto.com/zhanx/1972904



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
209 0
|
2月前
|
负载均衡 监控 关系型数据库
MySQL 官宣:支持读写分离了!!
【10月更文挑战第8天】MySQL的读写分离功能显著提升了数据库性能、可用性和可靠性。通过将读写操作分配至不同服务器,有效减轻单个服务器负载,提高响应速度与吞吐量,并增强系统稳定性。此外,它还支持便捷的扩展方式,可通过增加只读服务器提升读操作性能。实现读写分离的方法包括软件层面(如使用数据库中间件)和硬件层面(使用独立服务器)。使用时需注意数据一致性、负载均衡及监控管理等问题。
133 0
|
2月前
|
SQL 存储 关系型数据库
Mysql主从同步 清理二进制日志的技巧
Mysql主从同步 清理二进制日志的技巧
31 1
|
3月前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
361 11
|
3月前
|
关系型数据库 MySQL Java
MySQL主从复制实现读写分离
MySQL主从复制(二进制日志)、 Sharding-JDBC实现读写分离
MySQL主从复制实现读写分离
|
4月前
|
关系型数据库 MySQL Linux
mysql 主从同步 实现增量备份
【8月更文挑战第28天】mysql 主从同步 实现增量备份
52 3
|
4月前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版产品使用问题之使用CTAS同步MySQL到Hologres时出现的时区差异,该如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4月前
|
SQL 存储 关系型数据库
实时计算 Flink版产品使用问题之同步MySQL多张表的过程中,内存释放依赖于什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4月前
|
SQL 存储 关系型数据库
MySQL主从同步延迟原因与解决方法
MySQL主从同步延迟原因与解决方法
648 0
|
4月前
|
SQL 关系型数据库 MySQL
mysql读写分离,主从同步
本文介绍了如何在Laravel项目中配置数据库读写分离,并实现MySQL主从同步。主要步骤包括:在`config/database.php`中设置读写分离配置;为主机授予从机访问权限;配置各MySQL服务器的`/etc/my.cnf`文件以确保唯一的`server-id`;以及通过SQL命令设置主从关系并启动从服务。文章还针对一些常见错误提供了排查方法。最后通过验证确认主从同步是否成功。[原文链接](https://juejin.cn/post/6901581801458958344)。版权所有者为作者佤邦帮主,转载请遵循相关规定。