Linux两节点+keepalive搭建MySQL双主集群

2022-10-25 17:02:21

----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)

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主从配置如下：

[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;

码农公寓

相关文章