Amoeba整合MMM实现高可用负载均衡,读写分离,主从复制的MySQL

时间:2022-09-20 15:04:07

主机名

物理IP

集群角色

servier_id

Monitor

192.168.1.134

MMM管理端

Master1

192.168.1.130

Master可读、可写

1

Master2

192.168.1.131

Master可读、可写

2

Slave1

192.168.1.132

Slave节点只读

3

Slave2

192.168.1.133

Slave节点只读

4

 

虚拟IP地址

IP角色

功能描述

192.168.1.140

IP

写入VIP

192.168.1.141

IP

读查询VIP可以通过LVSHAProxy等负载均衡软件对赌VIP进行负载均衡。

192.168.1.142

IP

192.168.1.143

IP

192.168.1.144

IP

Amoeba整合MMM实现高可用负载均衡,读写分离,主从复制的MySQL

1.所有DB节点主机下载mysql,mysql-server

2.编辑/etc/my.cnf文件

[mysqld]

read-only=1

server-id=1

log-bin=mysql-bin

relay-log=mysql-relay-bin

replication-wild-ignore-table=test.%

replication-wild-ignore-table=information_schema.%

其中server-id每台主机分别为1,2,3,4

3.创建复制用户并授权

       A)首先在Master1mysql库中创建复制用户,

                mysql> grant replication slave on *.* to ‘repl_user’@’192.168.1.131’ identified by ‘123456’;

                mysql> grant replication slave on *.* to ‘repl_user’@’192.168.1.132’ identified by ‘123456’;

                mysql> grant replication slave on *.* to ‘repl_user’@’192.168.1.133’ identified by ‘123456’;

                 mysql> show master status;

 

B)然后在Master2mysql库中将Master1设为自己的主服务器

       mysql> change master to master_host=’192.168.1.130’,master_user=’repl_user’,

                master_password=’123456’,master_log_file=’mysql-bin.xxxxx’,

                master_log_pos=xxx;

        其中,master_log_file,master_log_pos的值由A步骤的show master status;

        mysql> start slave;

        mysql> show slave status;

        其中Slave_IO_RunningSlave_SQL_Running,这就是在从服务器节点上运行的主从复制线程,都应该为Yes

 

C)分别在Slave1Slave2重复步骤B


D)在Master2mysql库中创建复制用户

           mysql> grant replication slave on *.* to ‘repl_user’@’192.168.1.130’ identified by ‘123456’;

           mysql> grant replication slave on *.* to ‘repl_user’@’192.168.1.132’ identified by ‘123456’;

           mysql> grant replication slave on *.* to ‘repl_user’@’192.168.1.133’ identified by ‘123456’;

           mysql> show master status;

 

E)然后在Master1mysql库中将Master2设为自己的主服务器

        mysql> change master to master_host=’192.168.1.131’,master_user=’repl_user’,

                        master_password=’123456’,master_log_file=’mysql-bin.xxxxx’,

                        master_log_pos=xxx;

                    mysql> show slave status;


4.MMM套件的安装

A)在Monitor节点:

            yum -y install mysql-mmm*   (EPEL源)

B)在每个MySQL DB节点安装mysql-mmm-agent即可。

            yum -y install mysql-mmm-agent


5.MMM集群配置

A)所有mysql节点创建monitor usermonitor agent账号

            mysql > grant replication client on *.* to 'mmm_monitor'@'192.168.1.%' identified by '123456';

            mysql > grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.%' identified by '123456';

            mysql > flush privileges;

B)配置mmm_common.conf文件(/etc/mysql-mmm/),然后分别复制到mysql节点。

active_master_role      writer

<host default>

    cluster_interface       eth0

    pid_path                /var/run/mysql-mmm/mmm_agentd.pid

    bin_path                /usr/libexec/mysql-mmm/

    replication_user        repl_user   #设置复制的用户

    replication_password    123456 #设置复制的用户

    agent_user              mmm_agent   #设置更改只读操作的用户

    agent_password          123456

</host>

<host db1>   设置db1的配置信息

    ip      192.168.1.130

    mode    master   设置db1的角色为master

    peer    db2          设置与db1对等的主机名

</host>

<host db2>

    ip      192.168.1.131

    mode    master

    peer    db1

</host>

<host db3>

    ip      192.168.1.132

    mode    slave      设置db3的角色为slave

</host>

<host db4>

    ip      192.168.1.133

    mode    slave

</host>

<role writer>    #设置可写角色模式

    hosts   db1, db2

    ips     192.168.1.140     #设置可写的虚拟IP

    mode    exclusive          db1和db2是互斥

</role>

<role reader>      #设置可读角色模式

    hosts   db1, db2,db3,db4

    ips     192.168.1.141,192.168.1.142,192.168.1.143,192.168.1.144    #设置可读的虚拟IP

    mode    balanced            负载均衡

</role>

 

C)配置mmm_agent.conf文件

        include mmm_common.conf

        this db1(mysql节点,分别换成对应的db1db2db3db4)


D)配置mmm_mon.conf文件(仅在MMM管理节点上)

include mmm_common.conf

<monitor>

    ip                  127.0.0.1

    pid_path            /var/run/mysql-mmm/mmm_mond.pid

    bin_path            /usr/libexec/mysql-mmm

    status_path         /var/lib/mysql-mmm/mmm_mond.status

    ping_ips            192.168.1.130,192.168.1.131,192.168.1.132,192.168.1.133 

测试网络可用性的IP地址列表,有一个可ping通即为网络可用,但是不能写本地IP

    flap_duration 3600    抖动的时间范围

    flap_count 3

    auto_set_online     0

 

    # The kill_host_bin does not exist by default, though the monitor will

    # throw a warning about it missing.  See the section 5.10 "Kill Host

    # Functionality" in the PDF documentation.

    #

    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host

    #

</monitor>

<host default>

    monitor_user        mmm_monitor      

    monitor_password    123456

</host>

debug 0


E)配置/etc/default/mysql-mmm-agent文件(所有mysql节点)

            ENABLED=1


6.MMM集群管理

A)在Monitor运行:

             /etc/init.d/mysql-mmm-monitor start

B)在所有mysql节点运行:

            /etc/init.d/mysql-mmm-agent start

C)分别将mysql节点设置为online状态

             mmm_control set_online db# (1,2,3,4)

D)查看集群运行状态

             mmm_control show

              mmm_control checks all


7.测试MMM实现MySQL高可用功能

          读写分离测试

           读表的时候使用mysql普通用户进行操作

           故障转移测试

                           把Master1节点的mysql服务关闭。再次查看MMM集群运行状态。

                           重启Master1节点的mysql服务,再次查看MMM集群运行状态,手动切换Mastermmm_control move_role writer db1

           测试slave节点。


8.MySQL读、写分离

实现方案:MMM整合Amoeba应用架构

Amoeba作为MySQL的分布式数据前端代理层,主要在应用层访问MySQL的时候充当SQL路由器功能,具有负载均衡,高可用性,SQL过滤,读写分离,通过Amoeba可以实现数据源的高可用、负载均衡、数据切片。

A)安装和配置JDK环境(JavaSE1.5以上的JDK版本)

          将JDK安装到/usr/local/目录下,然后设置Java环境变量。

                 export JAVA_HOME=/usr/local/jdk1.6.0_45

                 export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:

                 $JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

                 export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH

B)安装Amoeba

        mkdir /usr/local/amoeba

        tar xf amoeba-mysql-binary-2.2.0.tar.gz -C/usr/local/amoeba

        启动Amoeba

                chmod +x -R /usr/local/amoeba/bin

                /usr/local/amoeba/bin/amoeba start

                出现一个问题:

                       The stack size specified is too small, Specify at least 160k

                       Could not create the Java virtual machine.

                        把/usr/local/amoeba/bin/amoeba中的

                                   DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"

                        改为:

                                   DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

                  正常启动的时候是这样:

                             log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

                             2016-03-27 11:04:39,568 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0

                              log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf

                              2016-03-27 11:04:39,949 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.

                              2016-03-27 11:04:39,954 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:63260.

                                要时常关注这个,可以发现问题。

C)配置Amoeba

实现读写分离功能,仅需要dbServers.xmlamoeba.xml

首先配置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="manager">${defaultManager}</property>

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

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

    

<!-- mysql port -->

<property name="port">3306</property>

#下面这个配置用于设置Amoeba默认连接的数据库名,操作表必须显示指定数据库名db.table,否则会在repdb下进行操作

<!-- mysql schema -->

<property name="schema">repdb</property>

#Amoeba连接后端数据库服务器的账号和密码,因此,需要在mysql集群中创建该用户,并授权Amoeba服务器可连接。

<!-- mysql user -->

<property name="user">ixdba</property>

<!--  mysql password -->

<property name="password">123456</property>

</factoryConfig>

 

<poolConfig class="com.meidusa.amoeba.net.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,这里定义为writedb

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

<factoryConfig>

<!-- mysql ip -->

#MMM集群提供对外访问的可写VIP地址

<property name="ipAddress">192.168.1.140</property>

</factoryConfig>

</dbServer>

#设置可读dbServer

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

<factoryConfig>

<!-- mysql ip -->

#MMM集群提供对外访问的可读VIP地址

<property name="ipAddress">192.168.1.141</property>

</factoryConfig>

</dbServer>

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

<factoryConfig>

                <!-- mysql ip -->

                        <property name="ipAddress">192.168.1.142</property>

                </factoryConfig>

        </dbServer>

 

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

<factoryConfig>

                <!-- mysql ip -->

                        <property name="ipAddress">192.168.1.143</property>

                </factoryConfig>

        </dbServer>

 

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

<factoryConfig>

                <!-- mysql ip -->

                        <property name="ipAddress">192.168.1.144</property>

                </factoryConfig>

        </dbServer>

 

#dbServer组,将可读的数据库IP统一放到一组中。

<dbServer name="myslaves" virtual="true">

<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">slave1,slave2,slave3,slave4</property>

</poolConfig>

</dbServer>

</amoeba:dbServers>

 

然后配置另一个文件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.net.ServerableConnectionManager">

<!-- port -->

#Amoeba监听的端口,默认为8066

<property name="port">8066</property>

<!-- bind ipAddress -->

<!--

<property name="ipAddress">127.0.0.1</property>

 -->

<property name="manager">${clientConnectioneManager}</property>

<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="authenticator">

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

#设置客户端连接Amoeba时需要使用的账号和密码。

#实际使用的时候,是这样mysql -uroot -p123456 -h192.168.1.134(本地ip-P8066

<property name="user">root</property>

<property name="password">123456</property>

<property name="filter">

<bean class="com.meidusa.amoeba.server.IPAccessController">

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

</bean>

</property>

</bean>

</property>

</service>

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

<service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">

<!-- port -->

<!--  default value: random number

<property name="port">9066</property>

-->

<!-- bind ipAddress -->

<property name="ipAddress">127.0.0.1</property>

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

<property name="manager">${clientConnectioneManager}</property>

<property name="connectionFactory">

<bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>

</property>

</service>

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

<!-- proxy server net IO Read thread size -->

<property name="readThreadPoolSize">20</property>

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

<property name="clientSideThreadPoolSize">30</property>

<!-- mysql server data packet process thread size -->

<property name="serverSideThreadPoolSize">30</property>

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

<property name="statementCacheSize">500</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="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">

<property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>

<!--

  default value is avaliable Processors

<property name="processors">5</property>

 -->

</connectionManager>

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

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

<!--

  default value is avaliable Processors

<property name="processors">5</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>

#Amoeba默认的池。

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

#定义好的两个读、写池。

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

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

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

</queryRouter>

</amoeba:configuration>

 

D)设置Amoeba登录数据库权限

             在MMM集群的所有mysql节点上执行,为Amoeba访问MMM集群中所有mysql数据库节点授权。

                     GRANT ALL ON repdb.* TO'ixdba'@'192.168.1.134' identified by ‘123456’;

                      FLUSH PRICILEGES;


E)测试Amoeba实现读、写分离和负载均衡。

            MMM集群的所有mysql节点开启mysql的查询日志,方便检验是否成功。

            在/etc/my.cnf添加如下:

            log=/var/log/mysql_query_log(此文件自己创建,对mysql可写)

 

            在每个mysql节点的test库中创建一张表,表名为mmm_test.

                      mysql> use test;

                       mysql> create table mmm_test (id int,email varchar(60));

                       mysql> insert into mmm_test (id,email) values (100,'this is本地真实IP’);

 

在远程MySQL客户端,通过Amoeba配置文件(amoeba.xml)中指定的用户名、密码、端口号以及Amoeba服务器的IP地址连接到MySQL数据库中:

                          mysql -uroot -p123456 -h192.168.1.134 -P8066

 

                           mysql> select * from test.mmm_test;

                           +------+-----------------------+

                           | id   | email                 |

                          +------+-----------------------+

                           |  100 | this is 192.168.1.130 |

                         +------+-----------------------+

                          mysql> select * from test.mmm_test;

                         +------+-----------------------+

                          | id   | email                 |

                        +------+-----------------------+

                         |  100 | this is 192.168.1.132 |

                        +------+-----------------------+

                         由此可见负载均衡实现了。

                         如果是这样:

                           ERROR 2006 (HY000): MySQL server has gone away
                           No connection. Trying to reconnect...
                           Connection id:    11416420

 

                          就要看看你配置文件的dbServers.xml中:

                         <!-- mysql user -->
                        <property name="user">root</property>
                        <property name="password">password</property>
                        
账号密码是否配置对了。


然后测试读写分离,创建两张表mmm_test1mmm_test2

        mysql> create table mmm_test1 (id int,email varchar(60));

         mysql> create table mmm_test2 (id int,email varchar(60));

         mysql > insert into mmm_test1 (id,email) values (103,'mmm_test3@126.com');

         mysql > drop table mmm_test2;

查看mysqllog日志,由于所有节点都是只读状态,但是由于配置了,MMM,主节点有写权限。


思路是:先构建Mysql主从复制,再使用MMM实现监控和管理MySQL Master-Master的复制和服务状态,和监控Slave节点的复制以及运行状态,任意节点发生故障时是想自动切换的功能,如果任意节点故障,MMM集群就会自动屏蔽故障节点。通过Amoeba实现MySQL读写分离.


我是初学者,这是看书学习,有很多不足地方,请大家多多指教