当前位置:Gxlcms > mysql > MySQL集群架构全自动化实现切换(M-M-S-S)

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

人气教程排行