MySQL集群架构全自动化实现切换（M-M-S-S)

实现无人工干预下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