读写分离+HA（mysql proxy+keepalived+mysql mmm）续
Mysql-mmm套件安装：在所有的数据库服务器，包括监控机上安装mysql-mmm套件
#解压mysql-mmm套件
# tar –xzvfmysql-mmm-2.2.1.tar.gz
#进入mysql-mmm路径
# cd mysql-mmm-2.2.1
在所有的数据库服务器上都运行(master1,master2,slave1,slave2)
# /usr/local/mysql/bin/mysql -uroot –p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.87-terry-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#创建用户权限
mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.21.%' IDENTIFIED BY '123456';
#创建用户权限
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.21.%' IDENTIFIED BY '123456';
#刷新权限
mysql> flush privileges;
配置所有数据库服务器包括监控机上的文件
# vi /etc/mysql-mmm/mmm_common.conf
active_master_role writer
cluster_interface eth0
#pid文件存储每次进程启动的pid
pid_path /var/run/mmm_agentd.pid
#路径位置
bin_path /usr/libexec/mysql-mmm/
#复制用户
replication_user replication
#复制用户的明码
replication_password 123456
#Agent用户
agent_user mmm_agent
#agent用户的密码
agent_password 123456
#服务器数据库1
#数据库服务器真实IP地址
ip 192.168.21.11
#数据库服务器模式
mode master
peer db2
#数据库服务器真实IP地址
Ip 192.168.21.12
#数据库服务器模式
mode master
peer db1
#数据库服务器真实IP地址
ip 192.168.21.13
#数据库服务器模式
mode slave
#数据库服务器真实IP地址
ip 192.168.21.14
#数据库服务器模式
mode slave
#写入角色
#主机编号
hosts db1, db2
#虚拟IP
ips 192.168.21.101
#模式-独占
mode exclusive
#只读角色
#主机编号
hosts db1, db2, db3,db4
#虚拟IP
ips 192.168.21.111,192.168.21.112,192.168.21.114,192.168.21.115
#模式 负载均衡模式
mode balanced
修改监控机的mysql_mmm的配置
# vi /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
#监控机的IP
ip 127.0.0.1
#pid文件路径
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
#状态文件路径
status_path /var/lib/misc/mmm_mond.status
#可以ping的真实agent的IP
ping_ips 192.168.21.11,192.168.21.12,192.168.21.13,192.168.21.14
#发现节点丢失则过6秒进行切换
auto_set_online 6
monitor_user mmm_monitor
monitor_password 123456
debug 0
#启动数据库服务器上的agent
# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon:
# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon:
# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon:
# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon:
#启动监控机上的monitor
# /etc/init.d/mysql-mmm-monitor start
Starting MMM Monitor Daemon:
#在监控机上的查看各个agent的信息
# mmm_control show
db1(192.168.21.11) master/ONLINE. Roles: reader(192.168.21.112), writer(192.168.21.101)
db2(192.168.21.12) master/ONLINE. Roles: reader(192.168.21.111)
db3(192.168.21.13) slave/ONLINE. Roles: reader(192.168.21.115)
db4(192.168.21.14) slave/ONLINE. Roles: reader(192.168.21.114)
数据库的HA完毕
实现读写分离机制
设置代理
在192.168.21.6上安装mysql-proxy
#解压lua，并且安装
tar zxf lua-5.1.2.tar.gz && cd lua-5.1.2 && make linux install
修改rw-splitting.lua这个脚本是用来控制读写分离的
#mysql-proxy中参数的含义
--proxy-read-only-backend-addresses为只读的机器
--proxy-backend-addresses为写入机器
--keepalive 支持keealived检测心跳
--proxy-lua-script=rw-splitting.lua运用rw-splitting.lua脚本进行读写分离
./mysql-proxy
--proxy-read-only-backend-addresses=192.168.21.102:3306
--proxy-read-only-backend-addresses=192.168.21.103:3306
--proxy-backend-addresses=192.168.21.101:3306
--keepalive
--proxy-lua-script=rw-splitting.lua
测试
mysql –uproxytest –pproxytest –P4040 h192.168.21.200
用keepalived实现mysql-proxy高可用
在proxy1和proxy2上安装
#下载keepalived
wgethttp://www.keepalived.org/software/keepalived-1.1.19.tar.gz
#解压keepalived
tar zxvf keepalived-1.1.19.tar.gz
cd keepalived-1.1.19
#配置初始化参数，安装在/usr/local/keepalived目录下
./configure --prefix=/usr/local/keepalived
#编译
make
#编译安装
make install
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cd /etc/keepalived/
配置keepalived，
# vi /etc/keepalived/keepalived.conf
bal_defs {
#配置邮件通知
notification_email {
xulean@gmail.com
}
notification_email_from xulean@gmail.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id test1
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
smtp_alert
authentication {
auth_type PASS
auth_pass 123
}
#配置虚拟IP
virtual_ipaddress {
192.168.21.200/32 scope global eth0
}
}
#启动keepalived
# /etc/rc.d/init.d/keepalived start
Starting keepalived:
# /etc/rc.d/init.d/keepalived start
Starting keepalived:
#在两台mysql-proxy上看是否启动了虚拟IP
# ip add|grep 192.168.21.200
inet 192.168.21.200/32 scope global eth0
虚拟IP绑定成功
对数据库进行调整（引擎）
为使数据库能支持事务，数据库主库采用innoDB引擎，slave库采用默认innodb引擎，在主库级别设置autocommit为0，备库autocommit为1，说明主库不能进行自动提交，而备库可以实现自动提交的功能，因为我们针对的是主的操作。
页:
[1]