CentOS下基于Amoeba实现MySQL读写分离

176次阅读

共计 6313 个字符，预计需要花费 16 分钟才能阅读完成。

说明：本配置基于 CentOS 6.4_x86，两台 MySQL 服务器均为源码编译（5.6.24 版本），amoeba 代理为 2.2.0 版本

server	use	ip
master	mysql 主	192.168.0.172
slave	mysql 从	192.168.0.173
amoeba	将用户请求代理至 mysqlserver	192.168.0.176

Linux 下 MySQL 主从复制（Master-Slave）与读写分离（Amoeba）实践 http://www.linuxidc.com/Linux/2016-05/130905.htm

使用 Amoeba 实现 MySQL DB 读写分离 http://www.linuxidc.com/Linux/2015-02/113542.htm

用 Amoeba 实现 MySQL 的读写分离 http://www.linuxidc.com/Linux/2013-12/94235.htm

CentOS 系统 Amoeba+MySL 主从读写分离配置教程 http://www.linuxidc.com/Linux/2015-10/124115.htm

一、mysql 服务器基于 GTID 主从复制的实现
1、配置主从节点的服务配置文件
master 节点：

[root@master ~]# cat /etc/my.cnf |grep "^\s*[^#\t]*s"
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
basedir = /usr/local/mysql
log-bin=master-bin
log-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
socket=/tmp/mysql.sock

slave 节点：

[root@slave data]# cat /etc/my.cnf |grep "^\s*[^#\t]*s"
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
basedir = /usr/local/mysql
log-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
log-bin=mysql-bin.log
socket=/tmp/mysql.sock

2、master 创建复制用户

mysql> grant replication slave on *.* to dbsync@192.168.0.173 identified by 'syncpass';
mysql> show global variables like '%uuid%'\G
*************************** 1. row ***************************
Variable_name: server_uuid
        Value: 9652c294-25d4-11e6-898b-000c2919c9d0
mysql> show master status\G
*************************** 1. row ***************************
             File: master-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

3、slave 启动复制线程

mysql>  change master to master_host='192.168.0.172',master_user='dbsync',master_password='syncpass',master_auto_position=1;
mysql> show global variables like '%uuid%'\G
*************************** 1. row ***************************
Variable_name: server_uuid
        Value: 997046fa-5b8e-11e6-a7e2-000c2919c9d0
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.172
                  Master_User: dbsync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 151
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 363
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 151
              Relay_Log_Space: 567
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 9652c294-25d4-11e6-898b-000c2919c9d0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1

3、创建一个测试书库库查看 MySQLmaster 进程，显示已经发送二进制日志给 salve

mysql> create database reliacatedb;
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 2. row ***************************
     Id: 3
   User: dbsync
   Host: slave:33608
     db: NULL
Command: Binlog Dump GTID
   Time: 259
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

二、amoeba 节点安装配置
1、配置 Java 环境

# yum install -y java-1.6.0-openjdk
# vim /etc/profile.d/jdk.sh 
export JAVA_HOME=/usr/
export PATH=$PATH:$JAVA_HOME/bin
# source /etc/profile.d/jdk.sh

2、安装 ameoba
https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/

# wget https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/download
# mkdir !$
mkdir /usr/local/amoeba-2.2.0
# tar xvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba-2.2.0

3、修改 ameoba 配置文件
amoeba 前端访问配置

# vim /usr/local/amoeba-2.2.0/conf/amoeba.xml
 <property name="port">3306</property> # 将默认端口 8066 改为 3306，便于实现前端程序连接数据库的透明性 
 <property name="ipAddress">0.0.0.0</property> # 有多块网卡时可按此设置，表示绑定任意地址，即 amoeba 对外访问的 IP 地址 
 <property name="user">root</property> # 客户端连接 amoeba 的代理使用的用户 
 <property name="password">mypass</property> # 客户端连接 amoeba 的代理使用的密码 
 <property name="defaultPool">master</property> # 默认访问节点 
 <property name="writePool">master</property>
 <property name="readPool">slave</property>
 # 读写分离配置，读池和写池和 dbServer.xml 中配置的节点相关

4、amoeba 后端代理配置

# vim /usr/local/amoeba-2.2.0/conf/dbServers.xml
<property name="user">root</property> # 默认连接 mysql server 的用户
<property name="password">pass</property> # 默认连接 mysql server 的密码，以上两项如不在下文中的 dbserver 中单独定义，则直接继承此处定义
  <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
  <property name="ipAddress">192.168.0.172</property>
                </factoryConfig>
  </dbServer>

  <dbServer name="slave"  parent="abstractServer">
                <factoryConfig>
  <property name="ipAddress">192.168.0.173</property>
                </factoryConfig>
  </dbServer>

  <dbServer name="multiPool" 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">master,slave,slave,slave</property>
                </poolConfig>
        </dbServer>

5、环境变量配置

# vim /etc/profile.d/amoeba.sh 
export AMOEBA_HOME=/usr/local/amoeba-2.2.0/
export PATH=$AMOEBA_HOME/bin/:$PATH
source /etc/profile.d/amoeba.sh

6、启动 amoeba

# amoeba start
The stack size specified is too small, Specify at least 160k
Could not create the Java virtual machine.
如果报以上错误，需编辑二进制脚本
# vim /usr/local/amoeba-2.2.0/bin/amoeba
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"
修改为如下
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

7、通过 amoeba 连接 mysql
CentOS 下基于 Amoeba 实现 MySQL 读写分离
执行一些读写操作，在两台 mysql 服务器上使用 tcpdump 抓包

[root@master ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.0.172
[root@slave ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.0.173

master 上实现写操作
CentOS 下基于 Amoeba 实现 MySQL 读写分离
slave 上实现读操作