MySQL read/write Splitting Based on Amoeba in CentOS

MySQL read/write Splitting Based on Amoeba in CentOS

Note: This configuration is based on CentOS 6.4_x86. Both MySQL servers are source code compiled (version 5.6.24), And amoeba proxy is version 2.2.0.

Server	Use	Ip
Master	Mysql master	192.168.0.172
Slave	Mysql slave	192.168.0.173
Amoeba	Proxy user requests to mysqlserver	192.168.0.176

Master-Slave MySQL and read/write splitting (Amoeba) in Linux

Use Amoeba for MySQL database read/write splitting

Use Amoeba to implement MySQL read/write splitting

CentOS system Amoeba + MySL Master/Slave read/write splitting configuration tutorial

I. Implementation of mysql Server Based on GTID master-slave Replication
1. Configure the service configuration file for the master and slave nodes
Master node:

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

Slave node:

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

2. Create and copy users on the master node.

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-000c2919c9d0mysql> 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 starts the replication thread

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-000c2919c9d0mysql> 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: 0Master_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. Create a Test Library to view the MySQLmaster process. The binary log has been sent to salve.

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

Ii. installation and configuration of amoeba nodes
1. Configure the java environment

# 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. Install 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. Modify the ameoba configuration file
Amoeba frontend access configuration

# Vim/usr/local/amoeba-2.2.0/conf/amoeba. xml <property name = "port"> 3306 </property> # change the default port 8066 to 3306, this makes it easy for front-end programs to connect to the database transparently <property name = "ipAddress"> 0.0.0.0 </property> # This setting can be used when multiple NICs exist, indicating binding any address, that is, the IP address used by amoeba for external access <property name = "user"> root </property> # The user <property name = "password"> mypass </property> # password used by the agent to connect the client to the amoeba <property name = "defaultPool"> master </property> # default access node <property name = "writePool"> master </property> <property name = "readPool"> slave </property> # read/write splitting configuration, read and Write pools and dbServer. nodes configured in xml

4. amoeba backend proxy configuration

# Vim/usr/local/amoeba-2.2.0/conf/dbServers. xml <property name = "user"> root </property> # default connection to mysql server user <property name = "password"> pass </property> # default connection to mysql server password, if the preceding two items are not separately defined in dbserver, the <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"> # Service Group, 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. Environment Variable Configuration

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

6. Start amoeba

# Amoeba startThe stack size specified is too small, Specify at least 160 kcocould not create the Java virtual machine. if the preceding error is reported, edit the binary script # vim/usr/local/amoeba-2.2.0/bin/amoebaDEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss128k" to ult_opts = "-server-Xms256m- xmx256m-Xss256k"

7. Connect to mysql through amoeba

Execute some read/write operations and use tcpdump to capture packets on two mysql servers

[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

Write operations on the master

Read operations on slave

This article permanently updates the link address: