Mysql主从同步 读写分离

一.概述
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

初始配置:关闭防火墙或将3306与8066添加例外,关闭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 表; //数据库查询

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

(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

修改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

修改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

注:MySQL主与MySQL从服务器server-id 不能相同,MySQL主的ID为1,其他从服务器的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;

其次查看slave1和slave2的状态。分别如下:
mysql> show slave status \G
Slave1状态如下:

Slave2状态如下:

(3). 主从同步测试
在amoeba-mysql-master服务器的MySQL新建数据库文件master_test数据口中新建表tongbu进行测试。

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

六.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,

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

  1. 安装Java
    因为amoeba为java语言开发,所以需要安装jdk运行环境。我们使用yum安装jdk1.6
    yum list available java* (查看java安装包)
    yum install java-1.6.0-openjdk(这里安装java1.6)

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

  1. 修改amoeba的配置文件amoeba.xml和dbServers.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的服务管理方式截图如下:

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

(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的主从服务器密码最好设置相同。

定义server1为master,server2和server3为slave

定义write为server1,read为server2、server3所在pool的数据库轮训规则,支持轮训、权重、HA,所在pool的数据库服务器,多台配置已”, ”隔开。server2,server3为轮训方式工作,默认从server1到server3,也可以重复添加比如server1,server2,server3,server3,这样server3在查询中被链接2次。写的服务器也可以不用添加到读的pool,这样就实现server1写,server2,3读。
地址池中的write和read要与dbServer.xml中设置的池名称相同

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

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

在从服务器上连接amoeba测试:
命令如下mysql -u root -p -h 172.16.200.81 -P 8066 (登录输入的密码为amoeba的密码)

七.读写分离测试。

  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配置文件中设置的密码

(2). 停止MySQL的主从同步(该操作需要在从服务器上操作即可)。相关操作命令如下:
STOP SLAVE IO_THREAD; #停止IO进程
STOP SLAVE SQL_THREAD; #停止SQL进程
STOP SLAVE; #停止IO和SQL进程

(2). 停止MySQL的主从同步(该操作需要在从服务器上操作即可)。相关操作命令如下:
STOP SLAVE IO_THREAD; #停止IO进程
STOP SLAVE SQL_THREAD; #停止SQL进程
STOP SLAVE; #停止IO和SQL进程


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

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

  1. 读测试
    (1). 分别登录到两台从服务器
    使用master_test库中创建 write_test表文件,并分别插入write_wangzx相关数据数据,(1,’wangzx’,’slave1’);和(1,’wangzx’,’slave2’); 。
    mysql> insert into write_test values(1,’wangzx’,’slave1’);

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

(2).测试数据的读取以及amoeba的轮询。
通过远程连接到amoeba查看读取的数据表信息,通过表信息查看轮询主机。
输入mysql> select * from write_test;查看相关表信息,将命令连续操作两次,查看到表信息不同,并且未读取到主服务器表信息,说明读取数据时只在从服务器上进行读取,并且能实现从服务器轮询读取数据,实现负载功能。

八、错误解决方法

Amoeba启动错误:

amoeba start
The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
解决方法:
打开bin目录下 的amoeba启动文件
vim amoeba
修改58行的Xss参数:
DEFAULT_OPTS=”-server -Xms1024m -Xmx1024m -Xss128k”
修改为:
DEFAULT_OPTS=”-server -Xms1024m -Xmx1024m -Xss256k”

配置错误:元素类型为 “poolConfig” 的内容必须匹配 “(property)*”。

网上拷贝的代码,格式化之后长这样,肉眼能看到的空白其实不是空格或tab,因为这两种会被格式化掉

解决方法:格式化一下xml文件,是不是有特殊字符或者空白行,删除无效代码即可

文档更新时间: 2019-12-20 17:59   作者:老王