Mysql双主配置要点

书史足自悦，安用勤与劬。这篇文章主要讲述Mysql双主配置要点相关的知识，希望能为你提供帮助。
主要配置

server-id = 2
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
#binlog-ignore=mysql
#binlog-ignore=infomation_schema
replicate-do-db=test

数据库复制用户

create user repl@192.168.137.202 identified with mysql_native_password by replP@ssw0rd;
grant replication slave on *.* to repl@192.168.137.202;
flush privileges;

Node01关键配置
##/etc/my.cnf.d/mysql-server.cnf

[mysqld]

server-id=1
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=1
relay-log=/var/lib/mysql/node1-relay-bin
relay-log-index=/var/lib/mysql/node1-relay-bin.index
log-error=/var/log/mysql/mysql.error
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/node1-relay-log.info
log-bin=/var/lib/mysql/node1-bin

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

Node02关键配置
##/etc/my.cnf.d/mysql-server.cnf

[mysqld]

server-id=2
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=2
relay-log=/var/lib/mysql/node2-relay-bin
relay-log-index=/var/lib/mysql/node2-relay-bin.index
log-error=/var/log/mysql/mysql.error
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/node2-relay-log.info
log-bin=/var/lib/mysql/node2-bin

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

Node01 keepalived配置
##/etc/keepalived.conf

! Configuration File for keepalived

vrrp_instance VI_1
nopreempt
state BACKUP
interface ens33
virtual_router_id 51
priority 101
advert_int 1
authentication
auth_type PASS
auth_pass 1111

virtual_ipaddress
192.168.137.205/24 dev ens33 label ens33:1

#track_script
#chk_haproxy
#
track_interface
ens33 weight 5

Node02 Keepalived配置
##/etc/keepalived.conf

! Configuration File for keepalived

vrrp_instance VI_1
nopreempt
state BACKUP
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication
auth_type PASS
auth_pass 1111

virtual_ipaddress
192.168.137.205/24 dev ens33 label ens33:1

#track_script
#chk_haproxy
#
track_interface
ens33 weight 5

Haproxy mysql check user
?

?mysql>
 create user hacheck@%;
?

?

Node01 Haproxy配置
###/etc/haproxy.conf

global
user haproxy
group haproxy
defaults
mode http
log global
retries 2
timeout connect 3000ms
timeout server 10m
timeout client 10m

listen STATS
bind 192.168.137.205:80
stats enable
stats hide-version
stats uri /stats
stats refresh 10s
stats auth admin:password

listen DATABASE-NODES 192.168.137.205:2727
bind 192.168.137.205:2727
mode tcp
option mysql-check user hacheck

server node1 192.168.137.201:3306 check
server node2 192.168.137.202:3306 check

Node01 Keepalived配置更新
【Mysql双主配置要点】##/etc/keepalived.conf

! Configuration File for keepalived

vrrp_script chk_haproxy
script "killall -0 haproxy"# check the haproxy process
interval 2 # every 2 seconds
weight 2 # add 2 points if OK

vrrp_instance VI_1
nopreempt
state BACKUP
interface ens33
virtual_router_id 51
priority 101
advert_int 1
authentication
auth_type PASS
auth_pass 1111

virtual_ipaddress
192.168.137.205/24 dev ens33 label ens33:1

track_script
chk_haproxy

track_interface
ens33 weight 5