mysql mm搭建_linux上mysql MM(双主)及keepalived搭建

一、主备机IP及VIP规划： master1 10.1.1.14 VIP 10.1.1.16 master2 10.1.1.15 VIP 10.1.1.16

二、mysql MM配置 1.修改master1的my.cnf # vi /etc/my.cnf [mysqld] basedir = /usr/local/mysql/ datadir=/usr/local/mysql/data log-error=/usr/local/mysql/mysqld.log port = 3306 socket=/usr/local/mysql/mysql.sock pid-file=/usr/local/mysql/mysql.pid

expire-logs-days=10

#binlog-do-db=db1 #binlog-ignore-db=db2

server-id = 1 log-bin = binlog relay_log = relay-bin log_slave_updates =1 auto_increment_increment=2 auto_increment_offset=1

2.修改master2的my.cnf # vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql/ datadir=/usr/local/mysql/data log-error=/usr/local/mysql/mysqld.log port = 3306 socket=/usr/local/mysql/mysql.sock pid-file=/usr/local/mysql/mysql.pid

expire-logs-days=10

#binlog-do-db=db1 #binlog-ignore-db=db2

server-id=2 relay_log=relay-bin log_bin =binlog log_slave_updates =1 auto_increment_increment=2 auto_increment_offset=2

3.创建master1复制账号 grant replication slave,replication client on *.* to 'repl'@'10.1.1.15' identified by 'repl'; 4.创建master2复制账号 grant replication slave,replication client on *.* to 'repl'@'10.1.1.14' identified by 'repl'; 5.为master1配置master show master status; change master to master_host='10.1.1.15',master_user='repl',master_password='repl',master_log_file='binlog.000005',master_log_pos=154;

6.为master2配置master show master status; change master to master_host='10.1.1.14',master_user='repl',master_password='repl',master_log_file='binlog.000001',master_log_pos=154; 7.启动slave master1: start slave; master2: start slave;

三、keepalived配置 1.编辑master1的keepalived配置文件 #vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived global_defs { #配置告警通知邮箱，可以配置多个 notification_email { root@localhost } #配置邮件发送目标地址 notification_email_from mysql@xiaomi.com #配置smtp服务器地址,其必须存在 smtp_server 10.1.1.11 #配置连接smtp服务器的超时时间 smtp_connect_timeout 30 #设置运行Keepalived实例的标识，其将显示于邮件标题中 router_id mysql_ha } #监控脚本 vrrp_script chk_mysql { script "/etc/keepalived/check_mysql.sh" interval 2 weight 2 } #配置VRRP实例,实例命名任意 vrrp_instance mysql-ha { #配置Keepalived角色，MASTER为主机 BACKUP为备机，此处两个都设置为BACKUP state BACKUP #配置keepalived监测的网络接口 interface eth0 #虚拟路由标识，其为一个(1-255)的数字，一个VRRP实例中主机的该ID必须相同 virtual_router_id 66 #服务器优先级，数字越大优先级越高，一个实例中主服务器优先级要高于备服务器 priority 50 #配置主备服务器间同步检查的时间间隔(秒) advert_int 1 #配置服务器抢占模式，这里配置为非抢占模式(只需对master1配置即可) nopreempt #配置验证类型和密码 authentication { #两种验证类型{PASS|HA} auth_type PASS #指定验证密码，一个实例中的主备服务器密码要一样 auth_pass centos } track_script { #指定执行监控的服务 chk_mysql } #配置虚拟IP,可指定有多个,每个占一行 virtual_ipaddress { 10.1.1.16 } }

2.编辑master1心跳检测脚本： #vi /etc/keepalived/check_mysql.sh

#!/bin/bash #This scripts is check for Mysql Slave status counter=$(netstat -na|grep "LISTEN"|grep "3311"|wc -l) if [ "${counter}" -eq 0 ]; then service keepalived stop killall keepalived fi ping 10.1.1.14 -w1 -c1 &>/dev/null if [ $? -ne 0 ] then systemctl stop keepalived killall keepalived fi

3.编辑master2的keepalived配置文件 # vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived global_defs { #配置告警通知邮箱，可以配置多个 notification_email { root@localhost } #配置邮件发送目标地址 notification_email_from mysql@xiaomi.com #配置smtp服务器地址,其必须存在 smtp_server 10.1.1.11 #配置连接smtp服务器的超时时间 smtp_connect_timeout 30 #设置运行Keepalived实例的标识，其将显示于邮件标题中 router_id mysql_ha } # 监控监本 vrrp_script chk_mysql { script "/etc/keepalived/check_mysql.sh" interval 2 weight 2 } #配置VRRP实例,实例命名任意 vrrp_instance mysql-ha { #配置Keepalived角色，MASTER为主机 BACKUP为备机，此处两个都设置为BACKUP state BACKUP #配置keepalived监测的网络接口 interface eth0 #虚拟路由标识，其为一个(1-255)的数字，一个VRRP实例中主机的该ID必须相同 virtual_router_id 66 #服务器优先级，数字越大优先级越高，一个实例中主服务器优先级要高于备服务器 priority 49 #配置主备服务器间同步检查的时间间隔(秒) advert_int 1 #配置服务器抢占模式，这里配置为非抢占模式(只需对master1配置即可) #nopreempt #配置验证类型和密码 authentication { #两种验证类型{PASS|HA} auth_type PASS #指定验证密码，一个实例中的主备服务器密码要一样 auth_pass centos } track_script { #指定执行监控的服务 chk_mysql } #配置虚拟IP,可指定有多个,每个占一行 virtual_ipaddress { 10.1.1.16 } }

4.编辑master2检测脚本 # vim /etc/keepalived/check_mysql.sh

#!/bin/bash #This scripts is check for Mysql Slave status counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) if [ "${counter}" -eq 0 ]; then systemctl stop keepalived killall keepalived fi ping 10.1.1.15 -w1 -c1 &>/dev/null if [ $? -ne 0 ] then service keepalived stop killall keepalived fi

5.vip漂移检测 1)master1和master2上同时开启keepalived和mysql #service keepalived start #service mysqld start 2)查看master1上ip地址 ip addr 3)登录10.1.1.16上的mysql mysql -uusername -ppassword -h10.1.1.16 -P3311 4)停掉master1上的mysql服务 service mysqld stop 5)观察master1和master2上的ip地址 ip addr 6)继续在3)中的session中运行mysql命令，看看发生了什么 mysql> use information_schema;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8484829/viewspace-2674315/，如需转载，请注明出处，否则将追究法律责任。