mysql双主加keepalive_Linux两节点+keepalive搭建MySQL双主集群

----Linux 6配置方法

环境描述：

OS：RHEL6.6_X64

MySQL-VIP：192.168.142.150

MySQL-master1：192.168.142.141

MySQL-master2：192.168.142.142

1、配置两台Mysql主主同步

[root@m1 ~]# yum install mysql-server mysql -y

[root@m1 ~]# service mysqld start

[root@m1 ~]# mysqladmin -u root Ins_9988

[root@m1 ~]# vi /etc/my.cnf #开启二进制日志，设置id

[mysqld]

server-id = 141 #backup这台设置142

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库

auto-increment-increment = 2 #字段变化增量值

auto-increment-offset = 1 #初始字段ID为1

slave-skip-errors = all #忽略所有复制产生的错误

[root@master ~]# service mysqld restart

[root@m1 mysql]# mysql -uroot -pIns_9988

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.30-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2020, 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>

mysql> show master status;

+------------------+----------+--------------+--------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+--------------------------+-------------------+

| mysql-bin.000001 | 154 | | mysql,information_schema | |

+------------------+----------+--------------+--------------------------+-------------------+

1 row in set (0.00 sec)

mysql>

MySQL主从配置如下：

[root@ m1 ~]# mysql -u root -pIns_9988

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.142.142' IDENTIFIED BY 'replication';

mysql> flush privileges;

mysql> change master to

master_host='192.168.142.142',

master_user='replication',

master_password='replication',

master_log_file='mysql-bin.000004',

master_log_pos=154; #对端状态显示的值

mysql> start slave; #启动同步

[root@m2 ~]# mysql -u root -pIns_9988

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.142.141' IDENTIFIED BY 'replication';

mysql> flush privileges;

mysql> change master to

master_host='192.168.142.141',

master_user='replication',

master_password='replication',

master_log_file='mysql-bin.000004',

master_log_pos=154;

mysql> start slave;

----keepalived安装

configure: error:

!!! OpenSSL is not properly installed on your system. !!!

!!! Can not include OpenSSL headers files. !!!

yum -y install openssl-devel

tar -xzvf keepalived-2.1.3.tar.gz

cd keepalived-2.1.3

./configure --prefix=/usr/local/keepalived

make

make install

---将keepalived配置成系统服务

cp /root/keepalived-2.1.3/keepalived/etc/init.d/keepalived /etc/init.d/

chmod +x /etc/init.d/keepalived

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

chkconfig --add keepalived

chkconfig keepalived on

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

---配置keepalived

192.168.142.141上面：

vi /etc/keepalived/keepalived.conf

global_defs {

router_id MYSQL_HA #标识，双主相同

}

vrrp_instance VI_1 {

state BACKUP #两台配置此处均是BACKUP

interface eth0

virtual_router_id 51 #主备相同

priority 100 #优先级，另一台backup改为90

advert_int 1

nopreempt #不抢占，只在优先级高master的机器上设置即可，优先级低backup的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.142.150

}

}

virtual_server 192.168.142.150 3306 {

delay_loop 2 #每个2秒检查一次real_server状态

#lb_algo wrr #LVS算法，用不到，我们就关闭了

#lb_kind DR #LVS模式,如果不关闭，备用服务器不能通过VIP连接主MySQL

persistence_timeout 60 #会话保持时间，同一IP的连接60秒内被分配到同一台真实服务器

protocol TCP

real_server 192.168.142.141 3306 { #检测本地mysql，backup也要写检测本地mysql

weight 3

notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换

TCP_CHECK {

connect_timeout 10 #连接超时时间

nb_get_retry 3 #重连次数

delay_before_retry 3 #重连间隔时间

connect_port 3306 #健康检查端口

}

}

}

192.168.142.142上面：

vi /etc/keepalived/keepalived.conf

global_defs {

router_id MYSQL_HA #标识，双主相同

}

vrrp_instance VI_1 {

state BACKUP #两台配置此处均是BACKUP

interface eth0

virtual_router_id 51 #主备相同

priority 90 #优先级，另一台backup改为90

advert_int 1

nopreempt #不抢占，只在优先级高master的机器上设置即可，优先级低backup的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.142.150

}

}

virtual_server 192.168.142.150 3306 {

delay_loop 2 #每个2秒检查一次real_server状态

#lb_algo wrr #LVS算法，用不到，我们就关闭了

#lb_kind DR #LVS模式,如果不关闭，备用服务器不能通过VIP连接主MySQL

persistence_timeout 60 #会话保持时间，同一IP的连接60秒内被分配到同一台真实服务器

protocol TCP

real_server 192.168.142.142 3306 { #检测本地mysql，backup也要写检测本地mysql

weight 3

notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换

TCP_CHECK {

connect_timeout 10 #连接超时时间

nb_get_retry 3 #重连次数

delay_before_retry 3 #重连间隔时间

connect_port 3306 #健康检查端口

}

}

}

vi /usr/local/keepalived/mysql.sh

#!/bin/bash

pkill keepalived

-----查看server_id uuid等参数

SHOW VARIABLES LIKE '%server_%';

----Linux 7版本配置方法

MySQL配置文件需添加如下配置：

vi /etc/my.cnf

###################Add for Master-Slave MySQL######################

[mysqld]

server-id = 1 #backup这台设置2

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema,sys #忽略写入binlog日志的库

auto-increment-increment = 2 #字段变化增量值

auto-increment-offset = 1 #初始字段ID为1

slave-skip-errors = all #忽略所有复制产生的错误

###################Add for Master-Slave MySQL######################

service mysqld restart

MySQL主从配置：

[root@ hostname1~]# mysql -u root -pIns_9988

mysql> CREATE USER 'replication'@'192.168.100.129' IDENTIFIED WITH mysql_native_password BY 'Rep_9988';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.100.129';

mysql> flush privileges;

mysql> change master to

master_host='192.168.100.129',

master_user='replication',

master_password='Rep_9988',

master_log_file='mysql-bin.000001',

master_log_pos=155; #对端状态显示的值

mysql> start slave; #启动同步

[root@hostname2 ~]# mysql -u root -pIns_9988

mysql> CREATE USER 'replication'@'192.168.100.128' IDENTIFIED WITH mysql_native_password BY 'Rep_9988';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.100.128';

mysql> flush privileges;

mysql> change master to

master_host='192.168.100.128',

master_user='replication',

master_password='Rep_9988',

master_log_file='mysql-bin.000001',

master_log_pos=155;

mysql> start slave;