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;