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.pidexpire-logs-days=10#binlog-do-db=db1 #binlog-ignore-db=db2server-id = 1 log-bin = binlog relay_log = relay-bin log_slave_updates =1 auto_increment_increment=2 auto_increment_offset=12.修改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.pidexpire-logs-days=10#binlog-do-db=db1 #binlog-ignore-db=db2server-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 fi3.编辑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 fi5.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;