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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Linux两节点+keepalive搭建MySQL双主集群，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7866字，纯文字阅读大概需要12分钟。

内容图文

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

 

内容总结

以上是互联网集市为您收集整理的Linux两节点+keepalive搭建MySQL双主集群全部内容，希望文章能够帮你解决Linux两节点+keepalive搭建MySQL双主集群所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---