Carry の Blog
Carry の Blog
2022-03-10
目录

MySQL配合Consul读写分离

# 实现原理

  1. consul服务带有服务发现功能，实现的原理是，通过脚本判断myql主从状态，并返回在线或者离线状态。

  2. consul服务带有动态dns功能，配置好读r_core.server.prod和写w_core.server.prod两种域名，若脚本判断MySQL工作正常，则给consul服务返回该IP处于在线状态，域名能正常解析IP，若若脚本判断MySQL工作异常，则给consul服务返回改IP处于离线状态，域名无法解析IP。

3.脚本判断为数据库是否异常的条件有

- 是否read_only

- 数据库宕机是否宕机

- 主从延迟过高，延迟高于15秒，，于是读域名`r_core.server.prod`将不解析该IP

- 是否read_only，若只读则`w_core.server.prod`不会解析该ip

# 从库检测脚本实现

#!/bin/bash
host=$1
port=$2
user="dba_consul"
password="xxxxxxxxxxxxxxxx"
repl_check_user="dba_consul"
repl_check_pwd="xxxxxxxxxxxxxxxx"

master_comm="/usr/local/mysql/bin/mysql -h $host -u$user  -P $port -p$password"
slave_comm="/usr/local/mysql/bin/mysql -h $host -u$repl_check_user  -P $port -p$repl_check_pwd"

# 判断mysql是否存活
value=$($master_comm -Nse "select 1")
if [ -z $value ]; then
  echo "MySQL Server is Down....."
  exit 2
fi

#get_slave_count=0
#is_slave_role=0
#slave_mode_repl_delay=0
#master_mode_repl_delay=0
#slave_mode_repl_status=0
max_delay=10

get_slave_hosts=$($master_comm -Nse "select substring_index(HOST,':',1) from information_schema.PROCESSLIST where user='dba_repl' and COMMAND = 'Binlog Dump GTID';")
get_slave_count=$($master_comm -Nse "select count(1) from information_schema.PROCESSLIST where user='dba_repl' and COMMAND = 'Binlog Dump GTID';")
is_slave_role=$($master_comm -e "show slave status\G" | grep -Ewc "Slave_SQL_Running|Slave_IO_Running")

## 单点模式(如果 get_slave_count=0 and is_slave_role=0 and get_slave_hosts=0 )
function single_mode() {
  if [ $get_slave_count -eq 0 ] && [ $is_slave_role -eq 0 ]; then
    echo "MySQL $port  Instance is Single Master........"
    exit 0
  else
    exit 2
  fi
}

### 从节点模式(如果 get_slave_count=0 and is_slave_role=2 )
function slave_mode() {
  #如果是从节点,必须满足不延迟,
  if [ $is_slave_role -ge 2 ]; then
    echo "MySQL $port  Instance is Slave........"
    $master_comm -e "show slave status\G" | egrep -w "Master_Host|Master_User|Master_Port|Master_Log_File|Read_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos|Relay_Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Exec_Master_Log_Pos|Relay_Log_Space|Seconds_Behind_Master"
    slave_mode_repl_delay=$($master_comm -e "show slave status\G" | grep -w "Seconds_Behind_Master" | awk '{print $NF}')
    slave_mode_repl_status=$($master_comm -e "show slave status\G" | grep -Ec "Slave_IO_Running: Yes|Slave_SQL_Running: Yes")
    if [ X"$slave_mode_repl_delay" == X"NULL" ]; then
      slave_mode_repl_delay=99999
      exit 2
    fi
    if [ $slave_mode_repl_delay != "NULL" -a $slave_mode_repl_delay -gt $max_delay -a $slave_mode_repl_status -ge2 ]; then
      exit 2
    fi
  fi
}

function master_mode() {
  ###如果是主节点，必须满足从节点为延迟或复制错误。才可读
  if [ $get_slave_count -gt 0 -a $is_slave_role -eq 0 ]; then
    echo "MySQL   Instance is Master........"
    for my_slave in $get_slave_hosts; do
      master_mode_repl_delay=$($slave_comm -h $my_slave -e "show slave status\G" | grep -w "Seconds_Behind_Master" | awk '{print $NF}')
      master_mode_repl_thread=$($slave_comm -h $my_slave -e "show slave status\G" | grep -Ec "Slave_IO_Running: Yes|Slave_SQL_Running: Yes")
      if [ X"$master_mode_repl_delay" == X"NULL" ]; then
        master_mode_repl_delay=99999
        exit 0
      fi

      if [ $master_mode_repl_delay -lt $max_delay -a $master_mode_repl_thread -ge 2 ]; then
        exit 2
      fi

      # master_mode_repl_delay > max_delay and master_mode_repl_thread >=2
      if [ $master_mode_repl_delay -gt $max_delay -a $master_mode_repl_thread -ge 2 ]; then
        exit 0
      fi
    done
  fi
}

#single_mode
slave_mode
master_mode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

# 主库检测脚本实现

#!/bin/bash
host=$1
port=$2
user="dba_consul"
password="xxxxxxxxxxxxxx"

comm="/usr/local/mysql/bin/mysql -h $host -u$user -P $port -p$password"
value=$($comm -Nse "select 1")
# 判断mysql是否存活
if [ -z $value ]; then
  exit 2
fi

echo "MySQL $port  Instance is Master........"

slave_info=$($comm -e "show slave status" | wc -l)
# 判断是不是从库
if [ $slave_info -ne 0 ]; then
  echo "MySQL $port  Instance is Slave........"
  $comm -e "show slave status\G" | egrep -w "Master_Host|Master_User|Master_Port|Master_Log_File|Read_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos|Relay_Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Exec_Master_Log_Pos|Relay_Log_Space|Seconds_Behind_Master"
  exit 2
fi

# 判断是否super_read_only,
super_read_only_info=$($comm -e "show variables like '%super_read_only%';" | egrep super_read_only | awk -F ' ' '{print$2}')
if [ X"$super_read_only_info" == X"ON" ]; then
  echo "MySQL $port  Instance is reboot recently........"
  exit 2
fi

# 判断是否read_only
super_read_only_info=$($comm -e "show variables like '%read_only%';" | egrep super_read_only | awk -F ' ' '{print $2}')
if [ X"$read_only_info" == X"ON" ]; then
  echo "MySQL $port  Instance is reboot recently........"
  exit 2
fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# consul 配置文件

1. r_mysql.json

{
  "services": [
    {
      "ID": "r_core01",
      "Name": "r_procore",
      "Tags": [
        "coredb_read_3366"
      ],
      "Address": "10.10.10.1",
      "Port": 3366,
      "check": {
        "args": [
          "/data/consul_data/script/check_mysql_slave.sh",
          "10.10.10.1",
          "3366"
        ],
        "interval": "2s",
        "timeout": "2s"
      }
    },
    {
      "ID": "r_core02",
      "Name": "r_procore",
      "Tags": [
        "coredb_read_3366"
      ],
      "Address": "10.10.10.2",
      "Port": 3366,
      "check": {
        "args": [
          "/data/consul_data/script/check_mysql_slave.sh",
          "10.10.10.2",
          "3366"
        ],
        "interval": "2s",
        "timeout": "2s"
      }
    },
    {
      "ID": "r_core03",
      "Name": "r_procore",
      "Tags": [
        "coredb_read_3366"
      ],
      "Address": "10.10.10.3",
      "Port": 3366,
      "check": {
        "args": [
          "/data/consul_data/script/check_mysql_slave.sh",
          "10.10.10.3",
          "3366"
        ],
        "interval": "2s",
        "timeout": "2s"
      }
    }
  ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

2. w_mysql.json

{
  "services": [
    {
      "ID": "w_core01",
      "Name": "w_procore",
      "Tags": [
        "coredb_write_3366"
      ],
      "Address": "10.10.10.1",
      "Port": 3366,
      "check": {
        "args": [
          "/data/consul_data/script/check_mysql_master.sh",
          "10.10.10.1",
          "3366"
        ],
        "interval": "2s",
        "timeout": "2s"
      }
    },
    {
      "ID": "w_core02",
      "Name": "w_procore",
      "Tags": [
        "coredb_write_3366"
      ],
      "Address": "10.10.10.2",
      "Port": 3366,
      "check": {
        "args": [
          "/data/consul_data/script/check_mysql_master.sh",
          "10.10.10.2",
          "3366"
        ],
        "interval": "2s",
        "timeout": "2s"
      }
    },
    {
      "ID": "w_core03",
      "Name": "w_procore",
      "Tags": [
        "coredb_write_3366"
      ],
      "Address": "10.10.10.3",
      "Port": 3366,
      "check": {
        "args": [
          "/data/consul_data/script/check_mysql_master.sh",
          "10.10.10.3",
          "3366"
        ],
        "interval": "2s",
        "timeout": "2s"
      }
    }
  ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

3. consul_server.json

{
  "server": true,
  "datacenter": "ss",
  "client_addr": "0.0.0.0",
  "advertise_addr":"10.10.10.1",
  "node_name": "mysqlcoredb01",
  "enable_syslog": false,
  "bootstrap_expect": 2,
  "domain": "ss",
  "log_file": "/data/consul_data/consul_server.log",
  "enable_local_script_checks": true,
  "data_dir": "/data/consul_data",
  "recursors" : ["8.8.8.8"],
  "ports" : {
    "dns" : 53,
    "http": 8500,
    "server": 8300
  },
  "retry_join": [ "10.10.10.1","10.10.10.2","10.10.10.3" ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# consul system service 配置

[Unit]
Description=consul
Documentation=https://www.consul.io/docs/index.html
Wants=network-online.target
After=network-online.target

[Service]
PrivateTmp=true
WorkingDirectory=/usr/bin/

User=consul
Group=consul

ExecStart=/usr/bin/consul agent -config-dir=/etc/consul.d
ExecStop=/usr/bin/consul  leave
ExecReload=/usr/bin/consul reload
StandardOutput=journal
StandardError=inherit

[Install]
WantedBy=multi-user.target
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
上次更新: 4/24/2025

← Gh-ost重建表，清除表碎片率→

最近更新
01
tidb fast ddl
04-04
02
TiDB配置文件调优 原创
04-03
03
如何移除TiDB中的表分区 原创
04-03
更多文章>
Theme by Vdoing