主要配置

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配置更新

##/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
}
}

Enable GTID

gtid-mode=ON
enforce-gtid-consistency=1
log-slave-updates=1

Enable SSL

ssl=on
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem