MySQL集群架构全自动化实现切换(M-M-S-S)
时间:2021-07-01 10:21:17
帮助过:33人阅读
实现无人工干预下mysqlz中MM自动切换,S自动寻找存活的M同步数据,图示如下: 1.正常情况下,写只有一个服务器在提供服务,另外
实现无人工干预下mysqlz中MM自动切换,S自动寻找存活的M同步数据,,
图示如下:
1.正常情况下,写只有一个服务器在提供服务,另外一个备份,中间通过keepalived实现
2.只读服务器,可以多台服务器同时提供服务,也可以只有一台提供服务,keepalived都可以实现,我这边目前写的主要是一台提供服务。
环境搭建步骤:
1.给四台服务器安装mysql,keepalived。(版本一致)
2.master1中mysql,keepalived配置:
cnf
port
=3306
socket
sock
port
=3306
socket
sock
datadir
skip_name_resolve
skiplocking
key_buffer_size =384M
max_allowed_packet =1M
table_open_cache =512
sort_buffer_size =2M
read_buffer_size =2M
read_rnd_buffer_size =8M
myisam_sort_buffer_size =64M
thread_cache_size =8
query_cache_size =32M
thread_concurrency =8
max_connections =1000
log_bin_trust_function_creators=1
transaction_isolationcommitted
slaveall
replimydb
replicatemysql
replicatems_state
logbin
server
binlog_format=row
innodb_buffer_pool_size
quick
max_allowed_packet rehash
key_buffer_size =256M
sort_buffer_size =256M
read_buffer =2M
write_buffer
interactive-timeout
conf
keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from loc
smtp_server XXXX
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script check_run {
script "/opt/keepalived_check_mysql.sh"
interval 5}
vrrp_sync_group VG1
VI_1
}}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111}
track_script {
check_run
}
virtual_ipaddress
sh
#!/bin/bash
MYSQLmysql
MYSQL_HOST
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME
MYSQL_OK
$MYSQL
MYSQL_OK=1
echo "MYSQL is good"else
MYSQL_OK=0
echo $MYSQL_OK
check_mysql_helth
CHECK_TIMEkeepalived stop
sleep 1done
3.上面三个配置文件my.cnf文件每个机器都要server-id = 11此参数修改不一样即可
keepalived.conf 其中Slave1服务器跟Master1一样,其他两台去掉script "/opt/keepalived_check_mysql.sh"
vip也需要修改
4.mysql建立同步账号:
在Master1服务器上:
GRANT ALL PRIVILEGES ON
GRANT ALL PRIVILEGES ON
flush privileges;
嫌麻烦就直接给整个网段授权:
在Master2上同样运行
5.Slave1,Slave2上建立同步
stop slave;
change master to master_host
start slave;
注意: master_log_file,master_log_pos参数是在master1上show master status;查到的。
6.Slave1,Slave2上检测脚本
checkmysql.sh :M1,M2状态检测
ChangeNode.sh:Slave对应远程Master服务器修改以及数据同步。
checkstatus.sh :修改Master后,停止再次修改,避免数据丢失。
sh
#!/bin/bash
CDR=/opt/shell
cd $CDR
echo
echo log
fireturn $Node01_MYSQL_OK
echo
echo log
fireturn $Node02_MYSQL_OK
}
CHECK_TIME
echo log
check_Node01_mysql_helth
CHECK_TIME
echo log
sh $CDRsh $NodeIP02
#exit 1fi
sleep 1done
CHECK_TIME
check_Node02_mysql_helth
CHECK_TIME
echo log
sh $CDRsh $NodeIP01
sleep 1done
修改Master脚本
sh
#!/bin/bash#Change another master mysql
CDR=/opt/shell
cd $CDR