gtid实现MySQL高可用_consul1.6实现Mysql-Gtid主从读写分离和高可用-03

规划

Mysql Gtid主从配置

https://blog.51cto.com/9025736/2500516

consul集群搭建

https://blog.51cto.com/9025736/2500976

创建mysql用户并授权(mysql服务器需要授权)

MySQL [(none)]> grant all on *.* to 'han'@'%' identified by 'hanye131';

MySQL [(none)]> flush privileges;

配置consul (consul client端 server3 和server4)

[root@server4 consul.d]# cd /etc/consul.d

[root@server4 consul.d]# cat mysql-write.json

{

"services": [

{

"name": "mysql-master",

"tags": [

"mysql-179"

],

"address": "192.168.31.179",

"port": 3306,

"checks": [

{

"args": ["/usr/bin/bash","/data/mysql_master.sh"],

"interval": "10s",

"timeout": "1s"

}

]

}

]

}

[root@server4 consul.d]# cat mysql-readonly.json

{

"services": [

{

"name": "mysql-slave",

"tags": [

"mysql-180"

],

"address": "192.168.31.180",

"port": 3306,

"checks": [

{

"args": ["/usr/bin/bash","/data/mysql_slave.sh"],

"interval": "10s",

"timeout": "1s"

}

]

}

]

}

[root@server4 consul.d]# cat /data/mysql_master.sh

#!/bin/bash

mysql -uhan -phanye131 -h 192.168.31.179 -e "select 1;"

if [ $? -eq 0 ]; then

exit 0

else

exit 2

fi

[root@server4 consul.d]# cat /data/mysql_slave.sh

#!/bin/bash

mysql -uhan -phanye131 -h 192.168.31.180 -e "select 1;"

if [ $? -eq 0 ]; then

exit 0

else

exit 2

fi

[root@server4 consul.d]# scp /data/mysql_*.sh server3:/data/

[root@server4 consul.d]# scp /etc/consul.d/mysql-* server3:/etc/consul.d/

重载consul(server3和server4)

consul reload

测试检测

停止server3 consul

[root@server2 consul.d]# dig mysql-master.service.consul

[root@server2 consul.d]# dig mysql-slave.service.consul

正常登录访问

高可用配置

配置consul (consul client端 server3 和server4)

[root@server3 consul.d]# cat mysql-write.json

{

"services": [

{

"id": "mysql1",

"name": "mysql",

"tags": [

"master"

],

"address": "192.168.31.178",

"port": 3306,

"checks": [

{

"args": ["/usr/bin/bash","/data/mysql_master.sh"],

"interval": "5s"

}

]

},

{

"id": "mysql2",

"name": "mysql",

"tags": [

"master"

],

"address": "192.168.31.179",

"port": 3306,

"checks": [

{

"args": ["/usr/bin/bash","/data/mysql_master2.sh"],

"interval": "5s"

}

]

}

]

}

脚本

[root@server3 consul.d]# cat /data/mysql_master.sh /data/mysql_master2.sh

#!/bin/bash

mysql -uhan -phanye131 -h 192.168.31.179 -e "select 1;"

if [ $? -eq 0 ]; then

exit 0

else

exit 2

fi

#!/bin/bash

mysql -uhan -phanye131 -h 192.168.31.178 -e "select 1;"

if [ $? -eq 0 ]; then

exit 0

else

exit 2

fi

我这里脚本仅测试Mysql服务是否在线 MHA或者GTID实现mysql集群 需要自己配合脚本处理