mysql主从复制和读写分离

    xiaoxiao2025-05-21  13

    一个完整的mysql读写分离环境包括以下几个部分:

    ·        应用程序client

    ·        databaseproxy

    ·        database集群

    在本次实战中,应用程序client基于c3p0连接后端的database proxy。database proxy负责管理client实际访问database的路由策略,采用开源框架amoeba。database集群采用mysql的master-slave的replication方案。整个环境的结构图如下所示:

    实战步骤与详解

    一.搭建mysql的master-slave环境

    1)分别在host1(10.20.147.110)和host2(10.20.147.111)上安装mysql(5.0.45),具体安装方法可见官方文档

    2)配置master

    首先编辑/etc/my.cnf,添加以下配置:

    log-bin=mysql-bin#slave会基于此log-bin来做replication server-id=1 #master的标示 binlog-do-db = amoeba_study #用于master-slave的具体数据库,可忽略此项

    创建数据库:

    Mysql>Create databaseamoeba_study;

    然后添加专门用于replication的用户(从库的ID):

    mysql> GRANTREPLICATION SLAVE ON *.* TO repl@192.168.56.102IDENTIFIED BY '123456';

    注:repl是用于连接的用户名,可在192.168.56.102 用 mysql –u repl –p123456 –h 192.168.56.101连接101的数据库

    Mysql>flushprivileges;

    重启mysql,使得配置生效:

    #/etc/init.d/mysqldrestart 或#service mysqld restart

    最后查看master状态:

    3)配置slave

    首先编辑/etc/my.cnf,添加以下配置:

    server-id=2 #slave的标示

    重启mysql:service mysqld restart

    配置生效后,配置与master的连接:

    mysql> CHANGEMASTER TO MASTER_HOST='192.168.56.101',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000022',MASTER_LOG_POS=1092;

    其中MASTER_HOST是master机的ip,MASTER_USER和MASTER_PASSWORD就是我们刚才在master上添加的用户,MASTER_LOG_FILE和MASTER_LOG_POS对应与master status里的信息

    最后启动slave:

    mysql> startslave;

    4)验证master-slave搭建生效

    通过show slavestatus\G查看状态,Slave_IO_Running:Yes Slave_SQL_Running: Yes则成功。

    通过查看slave机的log(/var/log/mysqld.log):

    100703 10:51:42[Note] Slave I/O thread: connected to master 'repl@10.20.147.110:3306', replication started in log 'mysql-bin.000003' at position 161261

    如看到以上信息则证明搭建成功,如果有问题也可通过此log找原因

    二.搭建database proxy (以http://www.iteye.com/topic/1113437为准)

      (Amoeba 启动方法:/root/soft/program/amoeba2.2/bin/amoebastart

      Amoeba 测试方法:关掉slave,用amoeba 账号登录mysql,插入一条数据,则主库会有该记录,从库没有该记录;查询表中的内容,读到的是从库中的数据。)

    此次实战中database proxy采用amoeba ,它的相关信息可以查阅官方文档,不在此详述

    实验环境:主机:192.168.56.101,从机:192.168.56.102,amoeba机:192.168.56.103

    1) 安装jdk,amoeba(在192.168.56.103)

    解压jdk,然后在/etc/profile 结尾添加:

    exportJAVA_HOME=/root/soft/program/jdk1.8.0_65

    exportJRE_HOME=/$JAVA_HOME/jre

    exportCLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

    exportPATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin

    使配置生效:source /etc/profile

    下载amoeba后解压到本地(/root/soft/program/amoeba2.2),即完成安装

    2)配置amoeba(注意数据库及密码)

    修改dbServer.xml:

    <!-- mysqlschema -->

    <propertyname="schema">amoeba_study</property>

     

    <!-- mysql user-->

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

     

    <!--  mysql password -->

    <propertyname="password">hch</property>

     

    <dbServername="master" parent="abstractServer">

            <factoryConfig>

                    <!-- mysql ip -->

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

            </factoryConfig>

    </dbServer>

     

    <dbServername="slave" parent="abstractServer">

            <factoryConfig>

                    <!-- mysql ip -->

                    <propertyname="ipAddress">192.168.56.102</property>

            </factoryConfig>

    </dbServer>

     

    <dbServername="slaves" virtual="true">

            <poolConfigclass="com.meidusa.amoeba.server.MultipleServerPool">

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

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

     

                    <!-- Separated bycommas,such as: server1,server2,server1 -->

                    <propertyname="poolNames">slave</property>

            </poolConfig>

    </dbServer>

     

    修改amoeba.xml:

    <propertyname="authenticator">

            <beanclass="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                    <!-- Amoeba 账号,密码 -->

                    <propertyname="user">auser</property>

     

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

     

                    <propertyname="filter">

                            <beanclass="com.meidusa.amoeba.server.IPAccessController">

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

                            </bean>

                    </property>

            </bean>

    </property>

     

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

    <propertyname="defaultPool">master</property>

     

    <!-- 写数据库 -->

    <propertyname="writePool">master</property>

    <!-- 读数据库,dbServer.xml 中配置的虚拟数据库,数据库池 -->

    <propertyname="readPool">slaves</property>

     

    修改rule.xml:

    <!DOCTYPEamoeba:rule SYSTEM "rule.dtd">

     

    <amoeba:rulexmlns:amoeba="http://amoeba.meidusa.com/">

            <tableRule name="message"schema="test" defaultPools="slaves">

     

            </tableRule>

    </amoeba:rule>

    从以上配置不然发现,写操作路由到server1(master),读操作路由到server2(slave)

    2) 数据库授权

         登录192.168.56.103 的数据库,grant allprivileges on *.* to auser@’%’identified by ‘123456’ with grant option;

    3) 启动amoeba

    [root@centos3 bin]# /root/soft/program/amoeba2.2/bin/amoeba start

    提示如下:

    log4j:WARN log4j config load completed fromfile:/root/soft/program/amoeba2.2/conf/log4j.xml

    2016-08-14 19:04:47,026 INFO context.MysqlRuntimeContext - Amoeba for Mysql currentversoin=5.1.45-mysql-amoeba-proxy-2.2.0

    log4j:WARN ip access config load completed fromfile:/root/soft/program/amoeba2.2/conf/access_list.conf

    2016-08-14 19:04:47,818 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on0.0.0.0/0.0.0.0:8066.

    2016-08-14 19:04:47,822 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on/127.0.0.1:62409.

     

    4) 测试:

    主机进入amoeba_study,建表hch(id int),进入从机关掉slave;

    主机插入数据:insert into hch values(11);

    从机插入数据:insert into hch values(22);

    新开一个窗口(任意窗口),mysql –u auser –p123456 –h 192.168.56.103 –P8066

    进入amoeba账号,use amoeba_study;

    查询hch表数据:select * from hch; ,只有22一条记录。

     

    写测试:amoeba账号 insert into hch values(33);

      主机hch表记录:11,33   从机hch表记录:22

    读测试:amoeba账号 select * from hch;

      只有22记录

    说明数据写进主机,从从机读取数据。

    进入从机,开启slave,就可实现主从复制和读写分离了。

    同时,可以通过查看master机和slave机上的日志/var/lib/mysql/mysql_log.log

    查看读写的状态和发生的位置。

    并且通过查看slave机上的日志/var/lib/mysql/mysql_log.log发现语句没在master上执行

    通过以上验证得知简单的master-slave搭建和实战得以生效

     

    Amoba配置时的一些问题:

    mysql –u auser –p123456–h 192.168.56.103 –P8066

    可以进入mysql,执行操作时提示:

    mysql> showtables;

    ERROR 2006(HY000): MySQL server has gone away

    No connection.Trying to reconnect...

    Connectionid:    26693077

    Current database:amoeba_study

    ERROR 2006(HY000): MySQL server has gone away

     

    是因为dbServer.xml中mysql的密码不正确。

     

    转载请注明原文地址: https://ju.6miu.com/read-1299118.html
    最新回复(0)