参照51cto blog搭建了mysql读写分离和高可用,测试了下MMM架构及amoeba,在这里记录下:

1、首先介绍一下mysql-mmm这个工具是干嘛使的?

    众所周知,mysql自身提供了AB复制。我们也可以很轻松的实现master-master双向复制,同时再为其中的一个master节点搭建一个slave库。这样就实现了master1与master2之间的双向复制,同时master1与slave1之间主从复制这样的架构。这样整个体系中就存在两个master,正常情况下只有一个master对外提供服务。如果对外提供服务的master意外宕机了,这时mysql本身并不具备failover切换的能力,这样尽管系统中还有一个正常的master节点,但应用仍不可用,这个正常的master尽管存在,但无疑是个摆设。mysql-mmm就是在这样的条件下诞生的。

    Mysql-MMM是Master-Master Replication Manager for MySQL(mysql主主复制管理器)的简称,该项目来自于Google,旨在用来监控mysql主主复制和做失败转移。其原理是将真实数据库节点的IP映射为虚拟IP集,在这个虚拟的IP集中,有一个用于write的IP,多个用于read的IP,这个用于write的虚拟IP映射着数据库集群中的两台master的真实IP,以此来实现failover的切换,如果觉得不是很明白,没有关系,后边具体配置部分还会再做说明。

    Mysql-MMM是一个开源的项目,官网:http://mysql-mmm.org

2、接着来说amoeba是个什么物件?

    可能您听说过mysql-proxy,这个mysql官方维护的一个实现mysql读写分离的工具,曾经测试使用过,但没有在生产中使用。网上大家讨论比较多的是mysql-proxy的配置比较麻烦,其实不是的,单说mysql-proxy的配置的话是比较简单的,不比amoeba麻烦多少,主要是mysql-proxy自身不带有启动脚本,如果你想实现像mysql服务那样的启动方式就需要自己来编写服务脚本。这里实现mysql读写分离,使用淘宝开源出来的amoeba,amoeba是用java开发出来的一款软件,其配置文件为xml格式。选择amoeba是因为amoeba是淘宝在生产环境中使用过的,经过实践测试的,相比mysql-proxy来说,风险性要小一些。

3、最后来说keepalived

    keeplived是用来实现服务的高可用的一款优秀的工具,需要说明的是keepalived会为代理的服务虚拟一个IP,用于外部访问,正常情况下,这个虚拟IP是绑定在master上的。master通过脚本来周期性判断服务是否正常运行,如果发现服务异常,就会停掉keepalived服务,这时原本绑定在master上的虚拟IP就会浮动到backup上,由于这个虚拟IP仍然存在,所以外部仍旧可以访问这个服务。

上面是别人总结的,基本可以了解MMM、amoeba、keepalive是做什么的,具体的可以去Google。

实验环境:

mysql0.example.com     192.168.8.228
mysql1.example.com     192.168.8.226
mysql2.example.com     192.168.8.222
mysql3.example.com     192.168.8.223
mysql4.example.com     192.168.8.224
mysql5.example.com     192.168.8.225

mysql版本:mysql  Ver 14.14 Distrib 5.6.16, for Linux (x86_64) using  EditLine wrapper

所有节点系统为centos 6.3 64bit,使用epel源安装相应的软件包mysql-mmm和keepalived。

架构图:

QQ图片20141208174223

关于mysql-mmm的部署规划:

mysql-mmm分为monitor端和agent端,实验中在所有的mysql节点(192.168.8.222-192.168.8.225)上安装agent端,在192.168.8.226上安装monitor端。

好了,下面我们将一步一步来实现

1、搭建mysql集群,基本的mysql安装这里不再介绍(这里主主复制、主从复制的搭建是在全新安装的数据库的基础上,所以在设置同步参数时,binlog为mysql-bin.000001)

a、mysql 主主复制

    首先停掉mysql2、mysql3上的mysql服务,修改配置文件,mysql2配置如下:

[root@mysql2 ~]# cat /etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql-bin.log
sync_binlog=1
log-slave-updates
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1
character-set-server=utf8

binlog_format=mixed

relay-log = relay-log
relay-log-index = relay-log.index

server-id = 1

[mysql]
no-auto-rehash
default-character-set=utf8

mysql2配置文件,注意server-id不能重复

[root@mysql3 ~]# cat /etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql-bin.log
sync_binlog=1
log-slave-updates
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1
character-set-server=utf8

binlog_format=mixed

relay-log = relay-log
relay-log-index = relay-log.index

server-id = 11

[mysql]
no-auto-rehash
default-character-set=utf8

重启mysql2、mysql3上的mysql服务

mysql2、mysql3上都执行添加同步用户的操作:

mysql> grant replication slave on *.* to ‘rep’@’192.168.8.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.08 sec)

mysql> select user,host,password from mysql.user;
+———-+————-+——————————————-+
| user | host | password |
+———-+————-+——————————————-+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| repluser | 192.168.8.% | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |

mysql3上面设置同步参数:

mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.8.222′,
-> MASTER_PORT=3306,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’123456′,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=783;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

注意上面的MASTER_LOG_POS=783中的783是通过下面命令在其主库中查询的,mysql3的主库为mysql2

mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 783 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.02 sec)

设置好mysql3的主库同步参数后,启动其slave的角色

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.222
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 783
Relay_Log_File: relay-log.000006
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果看到上面的最后两项都为Yes,代表同步成功。

mysql2上设置同步参数:

mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.8.223′,
-> MASTER_PORT=3306,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’123456′,
-> MASTER_LOG_FILE=’mysql-bin.000003′,
-> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.19 sec)

同样需要到mysql2的主库mysql3上去查看二进制日志的位置来确定MASTER_LOG_POS的值

mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000003 | 120 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

设置好mysql2的主库同步参数后,启动其slave的角色

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.223
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果看到上面的最后两项都为Yes,代表同步成功。至此,主主复制完成。

b、为mysql2搭建主从复制,salve节点为mysql4、mysql5

配置mysql4的配置文件

[root@mysql4 ~]# cat /etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

server-id=22
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1
character-set-server=utf8

relay-log = relay-log
relay-log-index = relay-log.index

[mysql]
no-auto-rehash
default-character-set=utf8

mysql4上设置同步参数:

mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.8.222′,
-> MASTER_PORT=3306,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’123456′,
-> MASTER_LOG_FILE=’mysql-bin.000002′,
-> MASTER_LOG_POS=120;

查询主库的MASTER_LOG_POS

mysql> show mas
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000002 | 120 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

设置好mysql4的主库同步参数后,启动slave:

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.222
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql5与mysql4的配置过程一样,需要注意的是mysql5配置文件中的server-id不能跟其他节点重复。

2、搭建mysql-mmm

 首先在mysql中添加mysql-mmm访问mysql的用户,由于已经配置了主主、主从复制,所以只需要在mysql2上进行授权即可

mysql> GRANT REPLICATION CLIENT ON *.* TO ‘mmm_monitor’@’192.168.8.%’ IDENTIFIED BY ‘123456’;
Query OK, 0 rows affected (0.16 sec)

mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO ‘mmm_agent’@’192.168.8.%’ IDENTIFIED BY ‘123456’;
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

在mysql1-mysql5这5个节点上安装mysql-mmm

[root@mysql1 mysql]# yum install mysql-mmm*

在mysql1上配置mmm_common.conf,然后将该文件拷贝到其他节点的相同位置,覆盖默认的mmm-common.conf文件

[root@mysql1 ~]# cat /etc/mysql-mmm/mmm_common.conf
active_master_role writer

<host default>
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user rep                                ## mysql中复制用的同步用户
replication_password 123456              ## mysql中复制用的同步密码
agent_user mmm_agent                        ## mysql中mysql-mmm的agent用户
agent_password 123456                        ## mysql中mysql-mmm的agent密码
</host>

<host db1>
ip 192.168.8.222
mode master
peer db2
</host>

<host db2>
ip 192.168.8.223
mode master
peer db1
</host>

<host db3>
ip 192.168.8.224
mode slave
</host>

<host db4>
ip 192.168.8.225
mode slave
</host>

## 定义虚拟IP192.168.8.247为wirte的IP,可以看到映射的是两个master
<role writer>
hosts db1, db2
ips 192.168.8.247
mode exclusive
</role>

## 定义虚拟IP192.168.8.248-251为read的IP,同时使用负载均衡模式

<role reader>
hosts db1, db2, db3, db4
ips 192.168.8.248, 192.168.8.249, 192.168.8.250, 192.168.8.251
mode balanced
</role>

将mysql1上修改好的mmm_common.conf拷贝到其他个节点上的相同位置覆盖默认的mmm_common.conf

Mysql1上修改monitor的配置文件mmm_mon.conf

[root@mysql1 ~]# cat /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf

<monitor>
ip 127.0.0.1
pid_path /var/run/mysql-mmm/mmm_mond.pid
bin_path /usr/libexec/mysql-mmm
status_path /var/lib/mysql-mmm/mmm_mond.status

 ## 这里的ip填写mysql集群各节点的真实IP
ping_ips 192.168.8.222,192.168.8.223,192.168.8.224,192.168.8.225

auto_set_online 60

# The kill_host_bin does not exist by default, though the monitor will
# throw a warning about it missing. See the section 5.10 “Kill Host
# Functionality” in the PDF documentation.
#
# kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host
#
</monitor>

<host default>
monitor_user mmm_monitor                    ##mysql中设置的mmm_monitor用户
monitor_password 123456                         ##mysql中设置的mmm_monitor密码
</host>

debug 0                                  ##如果设为1,会在启动时打印DEBUG信息,用于排障

monitor的配置到此结束,紧接着来配置mysql2-mysql5上的agent,以mysql2为例:

[root@mysql2 ~]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf

# The ‘this’ variable refers to this server. Proper operation requires
# that ‘this’ server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1

非常的简单,只要修改最后一行,跟mmm_common.conf中的配置要一致。mysql3中改为db2,mysql4中改为db3,mysql5中改为db4。

mysql1上启动monitor

/etc/init.d/mysql-mmm-monitor start

mysql2、mysql3、mysql4、mysql5中启动agent

/etc/init.d/mysql-mmm-agent start

mysql1上查看状态:

[root@mysql1 ~]# mmm_control show  
  db1(192.168.8.222) master/AWAITING_RECOVERY. Roles:
  db2(192.168.8.223) master/AWAITING_RECOVERY. Roles:
  db3(192.168.8.224) slave/AWAITING_RECOVERY. Roles:
  db4(192.168.8.225) slave/AWAITING_RECOVERY. Roles:
 第一次使用,发现是状态是等待恢复,使用命令改变状态
[root@mysql1 ~]# mmm_control set_online db1
再次查看状态

[root@localhost ~]# mmm_control show
db1(192.168.8.222) master/ONLINE. Roles: reader(192.168.8.249)
db2(192.168.8.223) master/ONLINE. Roles: reader(192.168.8.251), writer(192.168.8.247)
db3(192.168.8.224) slave/ONLINE. Roles: reader(192.168.8.248)
db4(192.168.8.225) slave/ONLINE. Roles: reader(192.168.8.250)

 现在停掉mysql3上的mysql,查看虚拟IP的浮动

[root@mysql3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL………. SUCCESS!

mysql1上查看状态,发现write绑定到了mysql2上面了:

[root@localhost ~]# mmm_control show
db1(192.168.8.222) master/ONLINE. Roles: reader(192.168.8.249), writer(192.168.8.247)
db2(192.168.8.223) master/HARD_OFFLINE. Roles:
db3(192.168.8.224) slave/ONLINE. Roles: reader(192.168.8.248), reader(192.168.8.251)
db4(192.168.8.225) slave/ONLINE. Roles: reader(192.168.8.250)

查看mysl4中原本与mysql3做同步的slave现在的master是那个?

[root@localhost ~]# mysql -uroot -p -e “show slave status\G”
Enter password:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.222
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

发现原本与mysql3同步的mysql4和mysql5已经自动与mysql2同步,是不是觉得很神奇,这就是mysqk-MMM的功能,但是mysql-mmm也有缺陷,后面在说!!!

现在我们重启mysql3的mysql,查看mysql1上的状态是否会改变:

[root@mysql3 ~]# /etc/init.d/mysqld start
Starting MySQL… SUCCESS!

mysql1上查看状态,mysql3变为待恢复状态

[root@localhost ~]# mmm_control show
db1(192.168.8.222) master/ONLINE. Roles: reader(192.168.8.249), writer(192.168.8.247)
db2(192.168.8.223) master/AWAITING_RECOVERY. Roles:
db3(192.168.8.224) slave/ONLINE. Roles: reader(192.168.8.248), reader(192.168.8.251)
db4(192.168.8.225) slave/ONLINE. Roles: reader(192.168.8.250)

  隔一小段时间,再次查看,发现其中的一个read 虚拟IP已经浮动到mysql3上,此时writer仍位于mysql2上,当然slave此时会从mysql2来同步数据。直到mysql2上的mysql服务不可用之后,才会重新切换回与mysql3同步。

[root@localhost ~]# mmm_control show
db1(192.168.8.222) master/ONLINE. Roles: reader(192.168.8.249), writer(192.168.8.247)
db2(192.168.8.223) master/ONLINE. Roles: reader(192.168.8.251)
db3(192.168.8.224) slave/ONLINE. Roles: reader(192.168.8.248)
db4(192.168.8.225) slave/ONLINE. Roles: reader(192.168.8.250)

MMM-mysql集群搭建完毕,下面介绍用amoeba实现负载均衡和读写分离

3、amoeba读写分离及负载均衡

在mysql0、mysql1上安装配置amoeba

在mysql0、mysql1上安装配置jdk和mysql客户端,实验中使用jdk1.7

## 安装mysql客户端
yum install mysql -y
## 安装jdk
 [root@mysql1 ~]# rpm -ivh jdk-7u60-linux-x64.rpm
## 验证jdk环境是否安装成功
[root@mysql1 ~]# java -version
java version “1.7.0_60”
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) 64-Bit Server VM (build 24.60-b09, mixed mode)
## 配置环境变量
[root@localhost ~]# cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/usr/local/mysql/bin/:/opt/amoeba-mysql/bin

export JAVA_HOME=/usr/java/jdk1.7.0_60
export JRE_HOME=/usr/java/jdk1.7.0_60/jre
export CLASSPATH=./:/usr/java/jdk1.7.0_60/lib:/usr/java/jdk1.7.0_60/jre/lib
export PATH

## 使环境变量生效
[root@mysql1 ~]# . .bash_profile 
 
mysql1上安装amoeba
[root@mysql1 ~]# tar xf amoeba-mysql-binary-2.2.0.tar.gz  -C /opt/
[root@mysql1 ~]# cd  /opt/amoeba-mysql/

将amoeba的bin命令添加到环境变量

PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/usr/local/mysql/bin/:/opt/amoeba-mysql/bin

[root@mysql1 ~]# . .bash_profile 

尝试启动amoeba,验证是否安装成功,若出现如下信息,则表明安装成功

[root@mysql1 ~]# amoeba start
log4j:WARN log4j config load completed from file:/opt/amoeba-mysql/conf/log4j.xml
2014-12-03 16:40:14,589 INFO context.MysqlRuntimeContext – Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/opt/amoeba-mysql/conf/access_list.conf
2014-12-03 16:40:15,192 INFO net.ServerableConnectionManager – Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2014-12-03 16:40:15,198 INFO net.ServerableConnectionManager – Amoeba Monitor Server listening on /127.0.0.1:5019.
^C2014-12-03 16:40:38,499 WARN net.ServerableConnectionManager – Amoeba for Mysql shutdown completed!
2014-12-03 16:40:38,499 WARN net.ServerableConnectionManager – Amoeba Monitor Server shutdown completed!

 如出现下面的错误:

[root@mysql1 ~]# amoeba start
The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
按照下面的方法解决
root@mysql1 ~]# cd /opt/amoeba-mysql/bin/
[root@mysql1 bin]# vim amoeba
## 将DEFAULT_OPTS=”-server -Xms256m -Xmx256m -Xss256k”这行中的数值调大一些,我这里全部设置成256
接着开始配置amoeba
[root@mysql1 ~]# cd /opt/amoeba-mysql/
[root@mysql1 amoeba-mysql]# vim conf/dbServers.xml 

<?xml version=”1.0″ encoding=”gbk”?>

<!DOCTYPE amoeba:dbServers SYSTEM “dbserver.dtd”>
<amoeba:dbServers xmlns:amoeba=”http://amoeba.meidusa.com/”>

<!–
Each dbServer needs to be configured into a Pool,
such as ‘multiPool’ dbServer
–>

<dbServer name=”abstractServer” abstractive=”true”>
<factoryConfig class=”com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory”>
<property name=”manager”>${defaultManager}</property>
<property name=”sendBufferSize”>64</property>
<property name=”receiveBufferSize”>128</property>

<!– mysql port –>
<property name=”port”>3306</property>

<!– mysql schema –>

<!– 实验中使用的是javashop库 –>
<property name=”schema”>test</property>

<!– mysql user –>

 <!– kora用户名是用来远程连接数据库javashop用的,需要提前在数据库中对该用户授权,在hadoop2上授权就可以了,其他节点就会同步授权。–>

<property name=”user”>kora</property>

<!– mysql password –>

<!– 特别要注意这个地方,默认情况下这行是被注释掉的,会导致连接失败 –>

<property name=”password”>123456</property>

</factoryConfig>

<poolConfig class=”com.meidusa.amoeba.net.poolable.PoolableObjectPool”>
<property name=”maxActive”>500</property>
<property name=”maxIdle”>500</property>
<property name=”minIdle”>10</property>
<property name=”minEvictableIdleTimeMillis”>600000</property>
<property name=”timeBetweenEvictionRunsMillis”>600000</property>
<property name=”testOnBorrow”>true</property>
<property name=”testOnReturn”>true</property>
<property name=”testWhileIdle”>true</property>
</poolConfig>
</dbServer>

<!– 这里的master会对外承担写入的功能 –>

<dbServer name=”master” parent=”abstractServer”>
<factoryConfig>
<!– mysql ip –>
<!– 这里的IP对应mysql-mmm设置的writer的虚拟ip –>

<property name=”ipAddress”>192.168.8.247</property>
</factoryConfig>
</dbServer>

<dbServer name=”slave1″ parent=”abstractServer”>
<factoryConfig>
<!– mysql ip –>
<!– 这里的IP对应mysql-mmm设置的reader的虚拟ip中的其中一个 –>
<property name=”ipAddress”>192.168.8.248</property>
</factoryConfig>
</dbServer>

<dbServer name=”slave2″ parent=”abstractServer”>
<factoryConfig>
<!– mysql ip –>
<!– 这里的IP对应mysql-mmm设置的reader的虚拟ip中的其中一个 –>
<property name=”ipAddress”>192.168.8.249</property>
</factoryConfig>
</dbServer>

<dbServer name=”slave3″ parent=”abstractServer”>
<factoryConfig>
<!– mysql ip –>
<!– 这里的IP对应mysql-mmm设置的reader的虚拟ip中的其中一个 –>
<property name=”ipAddress”>192.168.8.250</property>
</factoryConfig>
</dbServer>

<dbServer name=”slave4″ parent=”abstractServer”>
<factoryConfig>
<!– mysql ip –>
<!– 这里的IP对应mysql-mmm设置的reader的虚拟ip中的其中一个 –>
<property name=”ipAddress”>192.168.8.251</property>
</factoryConfig>
</dbServer>

<!– 这里将上面设置的slave添加到一个虚拟的组virtualslave里面,对外提供读的功能,同时实现负载均衡 –>
<dbServer name=”virtualslave” 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”>slave1,slave2,slave3,slave4</property>
</poolConfig>
</dbServer>

</amoeba:dbServers>

[root@mysql1 amoeba-mysql]# vim conf/amoeba.xml

<?xml version=”1.0″ encoding=”gbk”?>

<!DOCTYPE amoeba:configuration SYSTEM “amoeba.dtd”>
<amoeba:configuration xmlns:amoeba=”http://amoeba.meidusa.com/”>

<proxy>

<!– service class must implements com.meidusa.amoeba.service.Service –>
<service name=”Amoeba for Mysql” class=”com.meidusa.amoeba.net.ServerableConnectionManager”>
<!– port –>
<!– 为了方便应用程序访问,将端口改为3306 –>
<property name=”port”>3306</property>

<!– bind ipAddress –>
<!– 这里需要监听在0.0.0.0 –>
<property name=”ipAddress”>0.0.0.0</property>

<property name=”manager”>${clientConnectioneManager}</property>

<property name=”connectionFactory”>
<bean class=”com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory”>
<property name=”sendBufferSize”>128</property>
<property name=”receiveBufferSize”>64</property>
</bean>
</property>

<property name=”authenticator”>
<bean class=”com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator”>

 <!-- 这里的kora用户跟数据库的用户无关,是应用程序使用amoeba做代理连接数据库时的用户,是amoeba的用户 –>

<property name=”user”>kora</property>

<property name=”password”>123456</property>

<property name=”filter”>
<bean class=”com.meidusa.amoeba.server.IPAccessController”>
<property name=”ipFile”>${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>

</service>

<!– server class must implements com.meidusa.amoeba.service.Service –>
<service name=”Amoeba Monitor Server” class=”com.meidusa.amoeba.monitor.MonitorServer”>
<!– port –>
<!– default value: random number –>
<property name=”port”>9066</property>

<!– bind ipAddress –>
<!– 这里也将监听在0.0.0.0 –>
<property name=”ipAddress”>0.0.0.0</property>
<property name=”daemon”>true</property>
<property name=”manager”>${clientConnectioneManager}</property>
<property name=”connectionFactory”>
<bean class=”com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory”></bean>
</property>

</service>

<runtime class=”com.meidusa.amoeba.mysql.context.MysqlRuntimeContext”>
<!– proxy server net IO Read thread size –>
<property name=”readThreadPoolSize”>20</property>

<!– proxy server client process thread size –>
<property name=”clientSideThreadPoolSize”>30</property>

<!– mysql server data packet process thread size –>
<property name=”serverSideThreadPoolSize”>30</property>

<!– per connection cache prepared statement size –>
<property name=”statementCacheSize”>500</property>

<!– query timeout( default: 60 second , TimeUnit:second) –>
<property name=”queryTimeout”>60</property>
</runtime>

</proxy>

<!–
Each ConnectionManager will start as thread
manager responsible for the Connection IO read , Death Detection
–>
<connectionManagerList>
<connectionManager name=”clientConnectioneManager” class=”com.meidusa.amoeba.net.MultiConnectionManagerWrapper”>
<property name=”subManagerClassName”>com.meidusa.amoeba.net.ConnectionManager</property>
<!–
default value is avaliable Processors
<property name=”processors”>5</property>
–>
</connectionManager>
<connectionManager name=”defaultManager” class=”com.meidusa.amoeba.net.MultiConnectionManagerWrapper”>
<property name=”subManagerClassName”>com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!–
default value is avaliable Processors
<property name=”processors”>5</property>
–>
</connectionManager>
</connectionManagerList>

<!– default using file loader –>
<dbServerLoader class=”com.meidusa.amoeba.context.DBServerConfigFileLoader”>
<property name=”configFile”>${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>

<queryRouter class=”com.meidusa.amoeba.mysql.parser.MysqlQueryRouter”>
<property name=”ruleLoader”>
<bean class=”com.meidusa.amoeba.route.TableRuleFileLoader”>
<property name=”ruleFile”>${amoeba.home}/conf/rule.xml</property>
<property name=”functionFile”>${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name=”sqlFunctionFile”>${amoeba.home}/conf/functionMap.xml</property>
<property name=”LRUMapSize”>1500</property>
<!– 这里设置默认的连接,一般设置为writer对应的dbserver –>
<property name=”defaultPool”>master</property>

<!– 这里设置谁作为writer,对应dbServer.xml中的master –>
<property name=”writePool”>master</property>
<!– 这里设置谁作为reader,对应dbServer.xml中的virtualslave组 –> 
<property name=”readPool”>virtualslave</property>

<property name=”needParse”>true</property>
</queryRouter>
</amoeba:configuration>

将mysql1上配置好的amoeba的安装目录scp到mysql0上,然后配置环境变量就OK了

[root@mysql1 ~]# scp  -r /opt/amoeba-mysql/ mysql0:/opt/
root@mysql0’s password:

 配置环境变量

[root@mysql0 ~]# cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

#stty erase ^H
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/usr/local/mysql/bin/:/opt/amoeba-mysql/bin

export JAVA_HOME=/usr/java/jdk1.7.0_60
export JRE_HOME=/usr/java/jdk1.7.0_60/jre
export CLASSPATH=./:/usr/java/jdk1.7.0_60/lib:/usr/java/jdk1.7.0_60/jre/lib
export PATH

在mysql0、mysql1上启动amoeba

[root@mysql1 amoeba-mysql]# amoeba start &
[1] 5088
[root@localhost amoeba-mysql]# log4j:WARN log4j config load completed from file:/opt/amoeba-
mysql/conf/log4j.xml
2014-12-03 17:45:30,278 INFO context.MysqlRuntimeContext – Amoeba for Mysql current versoin=5.1.45-
mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/opt/amoeba-mysql/conf/access_list.conf
2014-12-03 17:45:30,897 INFO net.ServerableConnectionManager – Amoeba for Mysql listening on
0.0.0.0/0.0.0.0:3306.
2014-12-03 17:45:30,909 INFO net.ServerableConnectionManager – Amoeba Monitor Server listening on
/0.0.0.0:9066.

使用amoeba连接后端的mysql

[root@mysql1 amoeba-mysql]# mysql -ukora -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1543364161
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2014, 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 |
| magedudb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.03 sec)

    可见,amoeba工作正常,需要注意的是这里的kora用户是amoeba的用户。-h参数后面的IP为127.0.0.1,说明连接的是本机的mysql数据库,但是本机我们是没有安装mysql数据库的,只是安装了mysql的客户端,即mysql命令,但是仍然连接到了后端的mysql中去,是不是感觉很不可思议!哈哈,过程是这样的,首先用户使用mysql -u kora -p -h 127.0.0.1命令请求连接时(没有指定端口默认为3306),由于amoeba监听在0.0.0.0:3306,当然会接受这条命令的请求,于是开始和amoeba.xml文件中的登录口令做校验,发现用户名和密码与amoeba的用户名密码完全吻合,好,执行代理,代理中设置了writer的master以及reader的virtualslave,通过负载均衡,可能这时连接请求被负载到slave1上去了,slave1在dbServer.xml中设置的虚拟IP为192.168.8.247,slave1继承了abstractServer中的端口3306,用户名kora,schema javashop,密码123456,mysql客户端发出的连接请求经过amoeba代理后变成了mysql -u kora -p123456 -h 192.168.8.247 -D javashop -P 3306。由于虚拟IP192.168.8.247是在mysql-mmm中映射出来的,很自然的这条命令进入mysql-mmm中,根据配置文件的映射关系,最终转化为了mysql -u kora -p123456 -h 192.168.8.223 -D javashop -P 3306,最终成功连接上后端的数据库。

    再来捋一下,mysql客户端的命令首先经过amoeba代理,然后再经过mysql-mmm代理,最终到达后端的mysql集群,这个过程其实就是真实IP->虚拟IP->真实IP的转换,只不过中间还有负载均衡。

    验证amoeba实现了读写分离功能的测试办法,这里只说思路,亲测可用,这个不必担心,首先在mysql2上创建一张表,然后插入一条数据,这时4个节点中都会存在一条记录。这时停掉mysql4、mysql5与mysql2的同步,在mysql4、mysql5中各插入一条不一样的记录。然后打开mysql4、mysql5与mysql2的同步,最终的结果是mysql2、mysql3中有一条一样的数据,mysql4、mysql5中除了含有一条与mysql2、mysql3一样的数据外,还有一条各自的数据,这时用amoeba连入,不停的执行select * from tab;就能看到数据的条数在不断的变化,这就说明负载均衡和读写分离已经实现。

4、keepalived实现amoeba的HA

mysql0、mysql1上安装keepalived

yum install keepalived -y

mysql1作为keepalived的master,配置如下:

[root@mysql1 ~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}

#vrrp_script check_services {
# script “/opt/check_services.sh”
# interval 2
#}

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.8.252
}

# track_script {
# check_services
# }
}

check_services.sh脚本,当amoeba服务挂掉后,stop keepalived,让虚拟IP浮动到backup上,写入crontab,如下:

[root@mysql1 ~]# crontab -l
*/2 * * * * /bin/sh /opt/check_services.sh >/dev/null 2>&1

#!/bin/bash
#author: kora
#date: 2014-10-21 
#description: check service such as amoeba
#
 
MYSQL_CLIENT=/usr/bin/mysql
MYSQL_HOST=127.0.0.1
MYSQL_USER=kora
MYSQL_PORT=3306
MYSQL_PASSWD=upbjsxt
CHECK_TIME=3
MYSQL_OK=1
NOW=`date`
STATUS=’OK!’
 
AMOEBA_STATUS=`ps -ef |grep -v “grep”|grep amoeba|wc -l`
pidfile=/var/lock/subsys/`basename $0`.pid
 
if [ -f “$pidfile” ] && [ -e /proc/`cat $pidfile` ]
then 
    exit 1
fi
 
trap `rm -rf $pidfile; exit 0` 1 2 3 15
echo $$ > “$pidfile”
 
if [ “$AMOEBA_STATUS” -eq 0 ] 
then
    amoeba start > /dev/null &
    sleep 5
fi
 
while [ “$CHECK_TIME” -ne 0 ]
do
    let “CHECK_TIME-=1”
    “$MYSQL_CLIENT” -h “$MYSQL_HOST” -u “$MYSQL_USER” -p”$MYSQL_PASSWD” -P “$MYSQL_PORT” -e “show databases;” > /dev/null 2>&1
    if [ “$?” -eq 0 ]
    then
        MYSQL_OK=1
        CHECK_TIME=0
    else
        MYSQL_OK=0
    fi
done
 
if [ “$MYSQL_OK” -eq 0 ]
then 
    status=’fail!’
    /etc/init.d/keepalived stop
fi
 
echo “$NOW:$STATUS” >> ~/keepalived.log
exit 0

mysql0作为keepalived的backup,配置如下:

[root@mysql0 ~]# vim /etc/keepalived/keepalived.conf 

! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.8.252

}
}

由master和backup的配置文件看出,keepalived为amoeba配置的虚拟IP为192.168.8.252,在mysql0、mysql1上启动keepalived

/etc/init.d/keepalived  start

在mysql1上查看虚拟IP是否绑定在了网卡上

[root@mysql1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:2b:4b:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.8.226/24 brd 192.168.8.255 scope global eth0
inet 192.168.8.252/32 scope global eth0
inet6 fe80::5054:ff:fe2b:4be2/64 scope link
valid_lft forever preferred_lft forever

192.168.8.252已经绑定上来了,使用mysql客户端连接这个虚拟IP

[root@mysql1 ~]# mysql -u kora -p -h 192.168.8.252
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 75177700
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2014, 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 |
| magedudb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)

说明keepalived已经成功代理了amoeba,验证当mysql1上的amoeba服务挂掉后,虚拟IP是否会自动转移,amoeba是否仍能对外服务

mysql1中的amoeba服务停掉后,虚拟ip不见了

[root@mysql1 ~]# amoeba stop
amoeba server shutting down with port=9066

[root@mysql1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:2b:4b:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.8.226/24 brd 192.168.8.255 scope global eth0
inet6 fe80::5054:ff:fe2b:4be2/64 scope link
valid_lft forever preferred_lft forever

mysql0上出现了虚拟IP 192.168.8.252

[root@mysql0 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:cf:7a:c8 brd ff:ff:ff:ff:ff:ff
inet 192.168.8.228/24 brd 192.168.8.255 scope global eth0
inet 192.168.8.252/32 scope global eth0
inet6 fe80::5054:ff:fecf:7ac8/64 scope link
valid_lft forever preferred_lft forever

 mysql1上验证amoeba是否仍能对外服务

[root@mysql1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:2b:4b:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.8.226/24 brd 192.168.8.255 scope global eth0
inet6 fe80::5054:ff:fe2b:4be2/64 scope link
valid_lft forever preferred_lft forever

[root@mysql1 ~]# mysql -u kora -p -h 192.168.8.252
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 585475652
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2014, 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 |
| magedudb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)

ok,没有问题,在mysql1上的amoeba服务停掉之后,应用程序不需要做任何改动,仍可以访问后台数据,至此keepalived已经实现了amoeba的HA。

补充:
整个过程其实就是:虚拟ip–>真实IP->负载均衡+虚拟IP->真实IP的转换

mysql-MMM存在缺陷:
首先就是如果mysql-mmm-monitor挂了 无法实现failover了,其中一台master mysql挂了,不会自动切换到另一台master mysql,整个mysql集群就failed。
其次切换后slave同步会自动切换到新的主master上面,包括bin-log日志都会去匹配新master mysql的二进制日志,如果双主mysql中同步没有完全,一台主mysql在写binlog的时候挂了,日志还没发给另一台主mysql的话,这些数据就会丢失了,slave马上切换ip会去同步新的主mysql日志,而这台新主mysql并没有完全同步原主mysql日志数据,最终导致切换时这部分数据丢失。

MySQL索引原理及慢查询优化

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更 好的使用...

阅读全文

Mysql添加删除索引

使用索引,能够提高查询的速度 1、创建索引 1)ALTER TABLE 创建普通索引,UNIQUE索引或者PRIMARY KEY索引 ALTER TABLE table_name ADD INDEX index_name(colu...

阅读全文

nginx反向代理问题汇总及监控脚本

今天配置nginx的反向代理,配置文件如下: worker_processes 1; events { worker_connections 1024;} http { include mime.types; default_type application/...

阅读全文

  1. Pingback: 玻镁板

  2. Pingback: fastest wow gold

  3. Pingback: 西门塔尔牛