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

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

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

Linux下MySQL主从复制（Master-Slave）与读写分离（Amoeba）实践

使用Amoeba 实现MySQL DB 读写分离 

用Amoeba实现MySQL的读写分离 

CentOS系统 Amoeba+MySL主从读写分离配置教程 

一、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