基于amoeba实现MySQL读写分离

╰半夏微凉° 2022-07-19 00:07 195阅读 0赞

说明：本配置基于centos6.4\_x86，两台mysql服务器均为源码编译（5.6.24版本），amoeba代理为2.2.0版本

<table> 
 <thead> 
  <tr> 
   <th>server</th> 
   <th align="center">use</th> 
   <th align="right">ip</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master</td> 
   <td align="center">mysql主</td> 
   <td align="right">192.168.0.172</td> 
  </tr> 
  <tr> 
   <td>slave</td> 
   <td align="center">mysql从</td> 
   <td align="right">192.168.0.173</td> 
  </tr> 
  <tr> 
   <td>amoeba</td> 
   <td align="center">将用户请求代理至mysqlserver</td> 
   <td align="right">192.168.0.176</td> 
  </tr> 
 </tbody> 
</table>

**一、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/][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">
    
     <property name="loadbalance">1</property>
      
      <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  
![这里写图片描述][20160806170422584]  
执行一些读写操作，在两台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上实现写操作  
![这里写图片描述][20160806181030736]  
slave上实现读操作  
![这里写图片描述][20160806181106222]

[https_sourceforge.net_projects_amoeba_files_Amoeba_20for_20mysql]: https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/
[20160806170422584]: /images/20220718/2a06c0bca98844c7b902bf42fa8de23d.png
[20160806181030736]: /images/20220718/bd9249b4ce5349daa556f854719a7fb3.png
[20160806181106222]: /images/20220718/050a4e47acb94562bc5c68f39970a14e.png