mysql主从复制和读写分离

一个完整的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的密码不正确。