Mysql5.7+Orch+proxysql+keepalive_orchestrator:高可用方案proxysql

架构设计

通过gtid配置MySQL主从，通过orch实现高可用，orch通过raft实现自身的高可用，通过proxysql实现读写分离，proxysql可自身可以配置集群，通过keepalive实现虚拟IP漂移，keepalive可以自身配置集群

配置MySQL主从

1. 系统配置

mysql用户的SHELL限制设置

# vim /etc/security/limits.conf,添加如下几行
mysql soft nofile 653360
mysql hard nofile 653360
mysql soft nproc 163840
mysql hard nproc 163840
mysql soft stack unlimited
mysql hard stack unlimited

su - mysql
ulimit -a
1
2
3
4
5
6
7
8
9
10

2. 安装MySQL软件

rpm -e --nodeps mariadb-libs


cd /usr/local/
mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
ln -s mysql-5.7.17-linux-glibc2.5-x86_64 mysql

1
2
3
4
5
6
7
8

修改MySQL环境变量

vim ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

export MYSQL_HOME=/usr/local/mysql
#export PATH=$MYSQL_HOME/bin/:$PATH
export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$LD_LIBRARY_PATH
#PS1=`hostname`:'$PWD'"$ "
export MYSQL_PS1="(\u@\h [\d]> "
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin:$LD_LIBRARY_PATH

export PATH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

创建目录

mkdir -p /data/mysql/data
mkdir -p /data/mysql/log/binlog/
mkdir -p /data/mysql/log/relay/
mkdir -p /data/mysql/log/redo
mkdir -p /data/mysql/log/undo
chown -R mysql:mysql /data/mysql/
mkdir -p /usr/local/mysql/run/
mkdir -p /usr/local/mysql/log/
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
1
2
3
4
5
6
7
8
9
10

如下配置是failover必须配置，server_id、report_host按需修改
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository = ‘TABLE’
report_host = 192.168.56.55
report_port = 3306

编辑配置文件

[client]
socket = /tmp/mysql.sock
 
[mysqld]
user = mysql
port = 3306
datadir = /data/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/run/mysql.pid
default_storage_engine = InnoDB
lower_case_table_names = 1
#skip-grant-tables
########basic settings########
server-id = 17250411
autocommit = 1
character_set_server = utf8
skip_name_resolve = 1
max_connections = 2000
join_buffer_size = 1M
tmp_table_size = 256M
report_host = 192.168.56.55
report_port = 3306
max_heap_table_size=256M
tmpdir = /tmp
max_allowed_packet = 128M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
########log settings########
log-error = /usr/local/mysql/log/mysql-error.log
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/mysql-slow.log
log_slow_admin_statements = 1
long_query_time = 10
expire_logs_days = 60
########replication settings########
log_bin =  /data/mysql/log/binlog/mysql-bin
relay_log =  /data/mysql/log/relay/mysql-relay.log
relay_log_recovery = 1
binlog_format = row
log-slave-updates = ON
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository = 'TABLE'
########innodb settings########
innodb_page_size = 16K
innodb_buffer_pool_size = 16G
innodb_lock_wait_timeout = 60
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_large_prefix = 1
innodb_sort_buffer_size = 64M
innodb_page_cleaners = 4
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_lru_scan_depth = 500

innodb_log_group_home_dir =  /data/mysql/log/redo
innodb_log_files_in_group = 5
innodb_log_file_size= 800M

innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend:max:5G
innodb_flush_log_at_trx_commit = 1
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:15G

innodb_undo_directory = /data/mysql/log/undo
innodb_undo_tablespaces = 5 
innodb_undo_logs = 128
innodb_max_undo_log_size = 2G
innodb_undo_log_truncate = 1
innodb_flush_neighbors = 1
########semi sync replication settings########
plugin_dir= /usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
log-slave-updates = 1
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

3. 启动服务

初始化数据库

mysqld --initialize-insecure
1

启动数据库

mysqld_safe --user=mysql &
1

4. 主从复制

创建复制用户

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.56.%' IDENTIFIED BY 'p4ssword';
1

实验环境直接通过虚拟化克隆两个备库，修改备库server-id及uuid后，启动服务

change master to master_host='192.168.56.31',master_port=3306,master_user='repl',master_password='p4ssword',master_auto_position=1;
1

Orch配置

1. MySQL创建orch用户

用于orch监控管理MySQL数据库

CREATE USER 'orchestrator'@'192.168.56.%' IDENTIFIED BY 'orch_topology_password';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'192.168.56.%';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'192.168.56.%';
1
2
3

2. 安装Orch

mv orchestrator-3.2.6-linux-amd64.tar.gz /
tar -zxvf orchestrator-3.2.6-linux-amd64.tar.gz
1
2

3. 编制配置文件

如下为需要修改或添加的配置
不同节点修改RaftBind姐RaftAdvertise即可，修改为主机对应IP

cp /usr/local/orchestrator/orchestrator-sample-sqlite.conf.json /usr/local/orchestrator/orchestrator.conf.json
vim /usr/local/orchestrator/orchestrator.conf.json
# 要监控数据库的用户名密码
  "MySQLTopologyUser": "orchestrator",
  "MySQLTopologyPassword": "orch_topology_password",
# Failover匹配集群名
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "*"
  ],
# 配置Orch高可用
  "RaftEnabled": true,
  "RaftDataDir": "/usr/local/orchestrator",
  "RaftBind": "192.168.56.31",
  "RaftAdvertise": "192.168.56.31",
  "DefaultRaftPort": 10008,
  "RaftNodes": [
    "192.168.56.31",
    "192.168.56.32",
    "192.168.56.33"
  ]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

4. 启动Orch

nohup ./orchestrator --config=/usr/local/orchestrator/orchestrator.conf.json http &
1

5. 使用http

切换后的分布情况

ProxySQL安装

1. MySQL创建monitor用户

monitor用于ProxySQL，proxysql用于后期测试

create user 'monitor'@'%' identified by '123456';
create user 'proxysql'@'%' identified by '123456';

GRANT USAGE,process,replication slave,replication client ON *.* TO 'monitor'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO  'proxysql'@'%';
1
2
3
4
5

2. 安装依赖

yum install -y perl.x86_64
yum install -y libaio.x86_64
yum install -y net-tools.x86_64
yum install -y perl-DBD-MySQL
yum install -y gnutls
1
2
3
4
5

3. 安装ProxySQL

rpm -ivh /tmp/proxysql-2.3.2-1-centos7.x86_64.rpm
1

4. 配置

编辑配置中文件

vim /etc/proxysql.cnf
datadir="/var/lib/proxysql"

admin_variables =
{

        admin_credentials="admin:admin;cluster_demo:123456"
        mysql_ifaces="0.0.0.0:6032"
        cluster_username="cluster_demo"
        cluster_password="123456"
        cluster_check_interval_ms=200
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3

}

proxysql_servers =
(

    {
        hostname="192.168.56.31"
        port=6032
        comment="orch01"
    },

    {
        hostname="192.168.56.32"
        port=6032
        comment="orch02"
    },
	
    {
        hostname="192.168.56.33"
        port=6032
        comment="orch03"
    }
)

mysql_variables=

{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
#       interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.7.28"
        connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
        monitor_username="monitor"
        monitor_password="123456"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}

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

5. 启动服务

启动服务，配置读写分离

select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | 123456         |
| mysql-monitor_username | monitor        |
+------------------------+----------------+
2 rows in set (0.00 sec)
select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
1
2
3
4
5
6
7
8
9
10

配置组信息
根据read_only参数区分读写组

insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment) values (10,20,'read_only','test replication with read and write separation');
select * from mysql_replication_hostgroups;
+------------------+------------------+------------+-------------------------------------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment                                         |
+------------------+------------------+------------+-------------------------------------------------+
| 10               | 20               | read_only  | test replication with read and write separation |
+------------------+------------------+------------+-------------------------------------------------+
1 row in set (0.00 sec)
load mysql servers to runtime;
save mysql servers to disk;
1
2
3
4
5
6
7
8
9
10

添加服务器
max_replication_lag为10秒，如果延迟超过10秒，服务器状态会设置为SHUNNED

insert into mysql_servers(hostgroup_id,hostname,port,max_replication_lag) values(10,'192.168.56.31',3306,10),(20,'192.168.56.32',3306,10),(20,'192.168.56.33',3306,10);
mysql> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.56.31 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              |         |
| 20           | 192.168.56.32 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              |         |
| 20           | 192.168.56.33 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              |         |
| 20           | 192.168.56.33 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

load mysql servers to runtime;
save mysql servers to disk;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

配置对外访问问用户

insert into mysql_users(username,password,default_hostgroup) values('proxysql','123456',10);
select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| proxysql | 123456   | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)
load mysql users to runtime;
save mysql users to disk
1
2
3
4
5
6
7
8
9
10

配置中读写分离规则

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',10,1),(1,'^select',20,1);
select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_pattern        | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 3       | 1      | ^select.*for update$ | 10                    | 1     |
| 4       | 1      | ^select              | 20                    | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec
load mysql query rules to runtime;
save mysql query rules to disk;
1
2
3
4
5
6
7
8
9
10
11

主机恢复之前建议将主机下架

update mysql_servers set status='OFFLINE_HARD' where hostgroup_id=20 and hostname = '192.168.56.32';
确认服务器准备就绪后再恢复online状态
update mysql_servers set status='ONLINE' where hostgroup_id=20 and hostname = '192.168.56.32';
load mysql servers to runtime;
save mysql servers to disk;
1
2
3
4
5

Keepalived配置

1. 安装keepalived

yum install keepalived -y
1

2. 配置检测脚本

检测proxysql状态，proxysql终止则关闭keepalived

vim /etc/keepalived/chk_proxysql.sh
if [ `ps -C proxysql --no-header |wc -l` -eq 0 ];then
    systemctl stop keepalived
fi
1
2
3
4

3. 配置keepalived

测试过降权的切换方式，感觉较为复杂，直接采取随应用终止的方式，受不稳定因素影响较小。
priority 配置不同数值

! Configuration File for keepalived

global_defs {
    script_user root
    enable_script_security
}

vrrp_script chk_proxysql {
    script "/etc/keepalived/chk_proxysql.sh"
    interval 2 #脚本检测频率
    weight -5 #脚本执行成功与否，权重怎么计算
	fall 2 #如果连续两次检测失败，认为节点服务不可用
    rise 1 #如果连续2次检查成功则认为节点正常
}

vrrp_instance VI_1 {
    nopreempt #非抢占(preempt 意思是抢占)，权重高的节点恢复后不会重新恢复为主
    interface ens33 #网卡名称
    virtual_router_id 51 #其他节点需要与本节点一致
    priority 99 #初始权重，权重高的为主
    advert_int 1 #keepalived间心态频率时间
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.56.30/24
    }
    track_script {
        chk_proxysql
    }
}
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