一、MySQL 5.6介绍
MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现，且更加健壮。
复制功能的改进：
1、支持多线程复制，指的是针对每个database开启相应的独立线程。
2、支持启用GTID，就是每个mysql服务器都一个全局的事务ID，GTID，这个是由每个服务器的UUID（唯一的）和server_id组成的，mysql会保证事务与GTID之间1:1的映射，保证了GTID的全局唯一性。
二、Amoeba介绍
Amoeba是一个SQL请求的路由器，可以为mysql提供负载均衡、读写分离、高可用性提供机制，用户需要结合使用MySQL的Replication等机制来实现副本同步等功能，它主要在应用层访问MySQL的时候充当query 路由功能，专注分布式数据库 proxy 开发，座落与Client、DB Server(s)之间，对客户端透明，它是一个虚拟的mysql，对外提供mysql协议，客户端连接amoeba就象连接mysql一样。
Amoeba主要优势：
1、数据切分后复杂数据源整合；
2、提供数据切分规则并降低数据切分规则给数据库带来的影响；
3、降低数据库与客户端连接；
4、读写分离路由。
Amoeba不足：
1、不支持事务；
2、不支持存储过程；
3、不适合从amoeba导数据的场景或者对大数据量查询的query并不合适；
4、不支持分库分表，amoeba只支持分数据库实例，每个被切分的节点需要保持库表结构一致。
三、实验环境
192.168.30.115 OS：CentOS 6.4 x86_64 amoeba.luojianlong.com
192.168.30.116 OS：CentOS 6.4 x86_64 master.luojianlong.com
192.168.30.117 OS：CentOS 6.4 x86_64 slave.luojianlong.com
MySQL version：mysql-5.6.13-linux-glibc2.5-x86_64
Amoeba version：amoeba-mysql-binary-2.1.0-RC5
拓扑图如下：
首先，分别在master.luojianlong.com和slave.luojianlong.com上安装mysql 5.6.13，这里使用通用的二进制包来安装
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
[root@master ~]
# useradd -r -u 27 mysql
[root@master ~]
# tar zxvf mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@master ~]
# cd /usr/local/
[root@master
local
]
# ln -s mysql-5.6.13-linux-glibc2.5-x86_64 mysql
[root@master
local
]
# ln -s mysql-5.6.13-linux-glibc2.5-x86_64 mysql
[root@master
local
]
# cd mysql
[root@master mysql]
# mkdir /mydata/data -p
[root@master mysql]
# chown -R root.mysql ./*
[root@master mysql]
# chown -R mysql.mysql /mydata/data/
[root@master mysql]
# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data
[root@master mysql]
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@master mysql]
# chkconfig --add mysqld
[root@master mysql]
# chkconfig mysqld on
[root@master mysql]
# chmod +x /etc/rc.d/init.d/mysqld
[root@master ~]
# vi /etc/profile.d/mysql.sh
export
PATH=
/usr/local/mysql/bin
:$PATH
[root@master ~]
# . /etc/profile.d/mysql.sh
# 由于mysql 5.6版本默认的配置文件配置参数很少，需要自己定制，为了方便，这里使用mysql 5.5的配置文件
[root@master mysql]
# cp my-large.cnf /etc/my.cnf
[root@master mysql]
# vi /etc/my.cnf
# 在[mysqld]中添加如下
datadir =
/mydata/data
innodb_file_per_table = 1
binlog-
format
=ROW
log-bin=master-bin
log-slave-updates=
true
gtid-mode=on
enforce-gtid-consistency=
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
report-port=3306
port=3306
report-host=192.168.30.116
|
在slave.luojianlong.com中也使用相同的方式安装mysql 5.6
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
# 安装完成后，修改配置文件
[root@slave ~]
# vi /etc/my.cnf
# 添加如下
datadir =
/mydata/data
innodb_file_per_table = 1
binlog-
format
=ROW
log-slave-updates=
true
gtid-mode=on
enforce-gtid-consistency=
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
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=
/mydata/data
report-host=192.168.30.117
|
binlog-format：二进制日志的格式，有row、statement和mixed几种类型；
需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；
master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
sync-master-info：启用之可确保无信息丢失；
slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；
log-bin：启用二进制日志，这是保证复制功能的基本前提；
server-id：同一个复制拓扑中的所有服务器的id号必须惟一；
分别启动俩台服务器的mysql
|
1
2
3
4
|
[root@master ~]
# service mysqld start
Starting MySQL............ SUCCESS!
[root@slave ~]
# service mysqld start
Starting MySQL... SUCCESS!
|
在master服务器上创建复制用户
|
1
2
3
4
|
mysql> grant replication slave on *.* to
'repluser'
@
'192.168.30.%'
identified by
'123456'
;
Query OK, 0 rows affected (0.06 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
|
启动从节点的复制线程
|
1
2
3
4
|
mysql> change master to master_host=
'192.168.30.116'
, master_user=
'repluser'
, master_password=
'123456'
, master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.45 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.12 sec)
|
在slave上查看线程启动情况
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.30.116
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 539
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 751
Relay_Master_Log_File: master-bin.000002
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: 539
Relay_Log_Space: 955
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: b05c9b5c-bfac-11e3-a4bc-000c29f3fcba
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: b05c9b5c-bfac-11e3-a4bc-000c29f3fcba:1-2
Executed_Gtid_Set: b05c9b5c-bfac-11e3-a4bc-000c29f3fcba:1-2
Auto_Position: 1
1 row
in
set
(0.00 sec)
|
Connect_Retry：slave服务器连接master服务器的重试次数；
Exec_Master_Log_Pos：当前slave服务器最后一次所执行二进制日志的位置；
Relay_Log_Space：relaylog的空间；
Seconds_Behind_Master：slave滞后master的时长（通过当前服务器的时间戳与二进制日志中的时间戳对比得到的）；
Slave_IO_Running：slave服务器I/O线程的运行情况；
Slave_SQL_Running：slave服务器SQL线程的运行情况；
发现gtid复制已经启动成功，并可以查看主从服务器的uuid（对于每一个mysql服务器都是唯一的）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show global variables like
'%uuid'
;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | b05c9b5c-bfac-11e3-a4bc-000c29f3fcba |
+---------------+--------------------------------------+
1 row
in
set
(0.00 sec)
mysql> show global variables like
'%uuid'
;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | ba83181a-bfac-11e3-a4bc-000c295b50f9 |
+---------------+--------------------------------------+
1 row
in
set
(0.00 sec)
|
在slave 可以看到启动的复制线程数
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show processlist;
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 3 | system user | | NULL | Connect | 67092 | Waiting
for
master to send event | NULL |
| 4 | system user | | NULL | Connect | 50955 | Slave has
read
all relay log; waiting
for
the slave I
/O
thread to update it | NULL |
| 5 | system user | | NULL | Connect | 65267 | Waiting
for
an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 50961 | Waiting
for
an event from Coordinator | NULL |
| 151 | root | localhost | NULL | Query | 0 | init | show processlist |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
5 rows
in
set
(0.00 sec)
|
在master创建数据库，看是否同步到slave
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> create database student;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| student |
|
test
|
| testdb |
+--------------------+
7 rows
in
set
(0.00 sec)
|
发现已经同步到slave
主从服务器数据不一致的常见原因：
1、在slave服务器上直接执行写操作；
2、基于SQL语句的复制，建议使用基于数据本身的复制；
3、relaylog日志出现故障；
4、数据库架构复制有问题；
5、在一个数据库里面混合使用myisam和innodb引擎；
6、mysql的主从版本不一致（从服务器的版本必须高于主服务器版本）；
7、使用myisam存储引擎时，执行的语句被意外终止；
修复主从服务器数据不一致的方案：
1、使用pt-table-checksum工具，能够监控主从服务器数据表中的数据是否一致；
2、使用pt-table-sync工具，能够实现同步主从服务器上表的数据；
防止主从复制崩溃后带来的损失：
1、设置master服务器的sync_binlog=1，意思是只要有数据更新就立即同步到磁盘上面，同时要求磁盘本身不具有缓存能力；
2、使用innodb存储引擎时，当slave服务器意外关闭后，重新启动会重新读取master_info文件，这个时候master_info数据没刷新到磁盘上面，这样会导致数据复制出现错误，这个时候可以在slave上启用innodb_overwrite_relay_log_info；可以覆盖relay_log_info里面的数据，避免造成数据丢失；
3、或者也可以使用工具pt-slave-restart，从节点意外关闭时，用于安全启动复制进程；
4、当master服务器二进制日志出现错误时，可以在slave服务器上执行set global sql_slave_skip_counter=1;start slave; 跳过错误，或者删除二进制日志重新生成，然后重新建立复制；
下面开始配置amoeba，首先安装jdk环境
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@amoeba ~]
# tar zxvf jdk-7u25-linux-x64.gz -C /usr/local/
[root@amoeba ~]
# cd /usr/local/
[root@amoeba
local
]
# ln -s jdk1.7.0_25 jdk
[root@amoeba
local
]
# vi /etc/profile.d/jdk.sh
export
JAVA_HOME=
/usr/local/jdk
export
CLASSPATH=$CLASSPATH:$JAVA_HOME
/lib
:$JAVA_HOME
/jre/lib
export
PATH=
/usr/local/jdk/bin
:$PATH
[root@amoeba
local
]
# . /etc/profile.d/jdk.sh
[root@amoeba
local
]
# java -version
java version
"1.7.0_25"
Java(TM) SE Runtime Environment (build 1.7.0_25-b15)
Java HotSpot(TM) 64-Bit Server VM (build 23.25-b01, mixed mode)
|
下载安装amoeba
|
1
2
3
4
5
6
7
|
[root@amoeba ~]
# mkdir /usr/local/amoeba
[root@amoeba ~]
# tar zxvf amoeba-mysql-binary-2.1.0-RC5.tar.gz -C /usr/local/amoeba/
[root@amoeba ~]
# vi /etc/profile.d/amoeba.sh
export
PATH=
/usr/local/amoeba/bin
:$PATH
[root@amoeba ~]
# . /etc/profile.d/amoeba.sh
[root@amoeba ~]
# cp /usr/local/amoeba/conf/amoeba.xml /usr/local/amoeba/conf/amoeba.xml.bak
[root@amoeba ~]
# cp /usr/local/amoeba/conf/dbServers.xml /usr/local/amoeba/conf/dbServers.xml.bak
|
调整amoeba配置文件，设置读写分离
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[root@amoeba ~]
# vi /usr/local/amoeba/conf/amoeba.xml
# 在<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">子标签中修改如下几项
<property name=
"port"
>3306<
/property
>
<property name=
"ipAddress"
>0.0.0.0<
/property
>
<property name=
"password"
>mypass<
/property
>
# 在<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">子标签中启用修改下面几项
<property name=
"writePool"
>server1<
/property
>
<property name=
"readPool"
>server2<
/property
>
[root@amoeba ~]
# vi /usr/local/amoeba/conf/dbServers.xml
# 启动并修改 <dbServer name="abstractServer" abstractive="true">子标签中的
<property name=
"password"
>mypass<
/property
>
# 修改 <dbServer name="server1" parent="abstractServer">子标签中的
<property name=
"ipAddress"
>192.168.30.116<
/property
>
# 新添加一个dbServer如下
<dbServer name=
"server2"
parent=
"abstractServer"
>
<factoryConfig>
<!-- mysql ip -->
<property name=
"ipAddress"
>192.168.30.117<
/property
>
<
/factoryConfig
>
<
/dbServer
>
|
在master和slave中分别创建用于amoeba连接俩台mysql的用户
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
# 在master上面创建
mysql> grant all on *.* to
'root'
@
'192.168.30.%'
identified by
'mypass'
;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#发现已经同步到slave
mysql> show grants
for
'root'
@
'192.168.30.%'
;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants
for
root@192.168.30.% |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO
'root'
@
'192.168.30.%'
IDENTIFIED BY PASSWORD
'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'
|
+-------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
启动amoeba
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
[root@amoeba ~]
# vi /usr/local/amoeba/bin/amoeba
DEFAULT_OPTS=
"-server -Xms256m -Xmx256m -Xss128k"
# 修改为
DEFAULT_OPTS=
"-server -Xms256m -Xmx256m -Xss256k"
[root@amoeba ~]
# amoeba start
log4j:WARN log4j config load completed from
file
:
/usr/local/amoeba/conf/log4j
.xml
2014-04-09 15:09:15,121 INFO context.MysqlRuntimeContext - Amoeba
for
Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.1.0-RC5
log4j:WARN ip access config load completed from
file
:
/usr/local/amoeba/conf/access_list
.conf
2014-04-09 15:09:15,482 INFO net.ServerableConnectionManager - Amoeba
for
Mysql listening on
/0
.0.0.0:3306.
2014-04-09 15:09:15,489 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on
/127
.0.0.1:53201.
[root@amoeba ~]
# netstat -anptl | grep :3306
tcp 0 0 :::3306 :::* LISTEN 19208
/java
tcp 0 0 ::ffff:192.168.30.115:33408 ::ffff:192.168.30.116:3306 ESTABLISHED 19208
/java
tcp 0 0 ::ffff:192.168.30.115:59880 ::ffff:192.168.30.117:3306 ESTABLISHED 19208
/java
[root@amoeba ~]
# mysql -u root -pmypass -h 192.168.30.115 -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1783342624
Server version: 5.1.45-mysql-amoeba-proxy-2.1.0-RC5 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
test
|
+--------------------+
4 rows
in
set
(0.03 sec)
|
启动成功，并访问正常
下面使用tcpdump命令抓包测试读写分离效果
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
[root@master ~]
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116
[root@slave ~]
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117
# amoeba服务器上登录mysql,并创建数据库
mysql> create database abc;
Query OK, 1 row affected (0.03 sec)
[root@master ~]
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116
tcpdump: verbose output suppressed, use -
v
or -vv
for
full protocol decode
listening on eth0, link-
type
EN10MB (Ethernet), capture size 65535 bytes
15:16:42.492828 IP 192.168.30.115.33408 > 192.168.30.116.3306: Flags [P.],
seq
1149949367:1149949391, ack 113323290, win 123, options [nop,nop,TS val 8373727 ecr 10837334], length 24
E..L&"@.@.VR...s...t....D.....-....{4].....
......]V.....create database abc
15:16:42.501837 IP 192.168.30.115.33408 > 192.168.30.116.3306: Flags [.], ack 12, win 123, options [nop,nop,TS val 8373736 ecr 11221763], length 0
E..4&
#@.@.Vi...s...t....D.....-%...{.I.....
......;.
15:16:42.502356 IP 192.168.30.117.38626 > 192.168.30.116.3306: Flags [.], ack 3194819787, win 170, options [nop,nop,TS val 9637577 ecr 11221763], length 0
E..4.0@.@..Q...u...t.........m.............
......;.
# 发现在master上面抓到了创建数据库的包
[root@slave ~]
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117
tcpdump: verbose output suppressed, use -
v
or -vv
for
full protocol decode
listening on eth0, link-
type
EN10MB (Ethernet), capture size 65535 bytes
# slave上面没有抓到任何包
# 下面在amoeba上面执行创建表，插入，查询语句
mysql> use abc
Database changed
mysql> create table
test
(
id
int);
Query OK, 0 rows affected (0.41 sec)
mysql> insert into
test
values(1);
Query OK, 1 row affected (0.04 sec)
mysql>
select
* from
test
;
+------+
|
id
|
+------+
| 1 |
+------+
1 row
in
set
(0.01 sec)
[root@master ~]
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116
tcpdump: verbose output suppressed, use -
v
or -vv
for
full protocol decode
listening on eth0, link-
type
EN10MB (Ethernet), capture size 65535 bytes
15:20:42.054548 IP 192.168.30.115.33419 > 192.168.30.116.3306: Flags [P.],
seq
976065559:976065590, ack 2591052457, win 123, options [nop,nop,TS val 8613289 ecr 11418139], length 31
E..S."@.@..J...s...t....:-...pR....{.H.....
..m...:......insert into
test
values(1)
15:20:42.085305 IP 192.168.30.117.38626 > 192.168.30.116.3306: Flags [.], ack 3194820258, win 189, options [nop,nop,TS val 9877161 ecr 11461346], length 0
E..4.2@.@..O...u...t.........m.......*.....
........
15:20:42.086686 IP 192.168.30.115.33419 > 192.168.30.116.3306: Flags [.], ack 12, win 123, options [nop,nop,TS val 8613320 ecr 11461347], length 0
E..4.
#@.@..h...s...t....:-.6.pR....{./.....
..m.....
[root@slave ~]
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117
tcpdump: verbose output suppressed, use -
v
or -vv
for
full protocol decode
listening on eth0, link-
type
EN10MB (Ethernet), capture size 65535 bytes
15:20:52.076327 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [P.],
seq
201259223:201259232, ack 2661648116, win 115, options [nop,nop,TS val 8623261 ecr 9819401], length 9
E..=..@.@......s...u...............s.&.....
....... .....abc.
15:20:52.076821 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [.], ack 12, win 115, options [nop,nop,TS val 8623261 ecr 9887103], length 0
E..4..@.@......s...u...............s
#q.....
........
15:20:52.077609 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [P.],
seq
9:32, ack 12, win 115, options [nop,nop,TS val 8623262 ecr 9887103], length 23
E..K..@.@......s...u...............s.......
.............
select
* from
test
15:20:52.118046 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [.], ack 82, win 115, options [nop,nop,TS val 8623303 ecr 9887105], length 0
E..4..@.@......s...u...........E...s"......
观察抓包情况，发现所有的更新操作都在master，查询操作都在slave 上面
|
登录主从数据库查看数据是否一致
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
# master
mysql> use abc
Database changed
mysql>
select
* from
test
;
+------+
|
id
|
+------+
| 1 |
+------+
1 row
in
set
(0.00 sec)
# slave
mysql> use abc
Database changed
mysql>
select
* from
test
;
+------+
|
id
|
+------+
| 1 |
+------+
1 row
in
set
(0.00 sec)
# 发现数据一致
|
四、Amoeba简单的优化配置：
1、 修改amoeba启动参数，增加JAVA虚拟机的内存，修改amoeba执行程序，查找DEFAULT_OPTS，修改为以下内容：
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k"
2、 amoeba.xml中关于多线程和网络的优化
|
1
2
3
4
5
6
7
|
[root@amoeba ~]
# vim /usr/local/amoeba/conf/amoeba.xml
# 修改如下几项
<property name=
"readThreadPoolSize"
>500<
/property
>
<property name=
"clientSideThreadPoolSize"
>500<
/property
>
<property name=
"serverSideThreadPoolSize"
>500<
/property
>
<property name=
"sendBufferSize"
>256<
/property
>
<property name=
"receiveBufferSize"
>128<
/property
>
|
3、 amoeba.xml中还有一个处理客户端连接和网络信息使用CPU核数的配置，不过默认配置是主机拥有的CPU核的数量，一般不用修改，如果你的amoeba负载较高，并且机器上还有其他服务，你就可以降低这个配置，避免系统或者其他服务没有资源，或者出现资源争抢，从而导致整理的处理能力下降。
查找amoeba.xml文件中connectionManagerList关键字，修改
|
1
2
3
4
5
6
|
[root@amoeba ~]
# vi /usr/local/amoeba/conf/amoeba.xml
<!--
default value is avaliable Processors
<property name=
"processors"
>5<
/property
>
-->
# 取消注释，并设置processors数量。
|
到此，MySQL 5.6基于GTID的复制，以及Amoeba读写分离的实现配置完成。
本文转自ljl_19880709 51CTO博客，原文链接：http://blog.51cto.com/luojianlong/1392844，如需转载请自行联系原作者
