MySQL读写分离ProxySQL安装部署

ゝ一纸荒年。 2024-03-23 10:08 30阅读 0赞

## ProxySQL简介 ##

ProxySQL为MySQL的中间件，其有两个版本官方版和percona版，percona版是基于官方版基础上修改而来。ProxySQL是由C++语言开发，轻量级但性能优异（支持处理千亿级数据），其具有中间件所需要的绝大多数功能，如：

*  多种方式的读写分离
 *  定制基于用户、基于schema、基于语言的规则对SQL语句进行路由
 *  缓存查询结果
 *  后端节点的控制

## 安装 ##

### 下载安装 ###

# 获取centos版本信息下载对应的
    cat /etc/centos-release
    
    curl -O  https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/7/proxysql-2.5.0-1-centos7.x86_64.rpm
    
    
    yum install proxysql-2.5.0-1-centos7.x86_64.rpm

### docker 安装(推荐) ###

vim proxysql.cnf

datadir="/var/lib/proxysql"
    
    admin_variables=
    {
        admin_credentials="admin:admin;radmin:radmin"
        mysql_ifaces="0.0.0.0:6032"
    }
    
    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"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        monitor_username="monitor"
        monitor_password="monitor"
        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
    }

启动

docker run --privileged=true -p 16032:6032 -p 16033:6033 -p 16070:6070 -d -v /home/lys/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql

推荐使用这个

docker run --privileged=true network=host  -d -v /home/lys/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql

## 配置 ##

#### 登录 ####

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

#### 插入主备节点 ####

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.6.8.174',3306);
    INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'10.6.8.147',3306);

#### 设置用户 ####

UPDATE global_variables SET variable_value='monitor12345678' WHERE variable_name='mysql-monitor_username';
     UPDATE global_variables SET variable_value='monitor12345678' WHERE variable_name='mysql-monitor_password';

Admin>SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
    +----------------------------------------------------------------------+-----------------+
    | variable_name                                                        | variable_value  |
    +----------------------------------------------------------------------+-----------------+
    | mysql-monitor_enabled                                                | true            |
    | mysql-monitor_connect_timeout                                        | 600             |
    | mysql-monitor_ping_max_failures                                      | 3               |
    | mysql-monitor_ping_timeout                                           | 1000            |
    | mysql-monitor_read_only_max_timeout_count                            | 3               |
    | mysql-monitor_replication_lag_interval                               | 10000           |
    | mysql-monitor_replication_lag_timeout                                | 1000            |
    | mysql-monitor_replication_lag_count                                  | 1               |
    | mysql-monitor_groupreplication_healthcheck_interval                  | 5000            |
    | mysql-monitor_groupreplication_healthcheck_timeout                   | 800             |
    | mysql-monitor_groupreplication_healthcheck_max_timeout_count         | 3               |
    | mysql-monitor_groupreplication_max_transactions_behind_count         | 3               |
    | mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1               |
    | mysql-monitor_galera_healthcheck_interval                            | 5000            |
    | mysql-monitor_galera_healthcheck_timeout                             | 800             |
    | mysql-monitor_galera_healthcheck_max_timeout_count                   | 3               |
    | mysql-monitor_replication_lag_use_percona_heartbeat                  |                 |
    | mysql-monitor_query_interval                                         | 60000           |
    | mysql-monitor_query_timeout                                          | 100             |
    | mysql-monitor_slave_lag_when_null                                    | 60              |
    | mysql-monitor_threads_min                                            | 8               |
    | mysql-monitor_threads_max                                            | 128             |
    | mysql-monitor_threads_queue_maxsize                                  | 128             |
    | mysql-monitor_wait_timeout                                           | true            |
    | mysql-monitor_writer_is_also_reader                                  | true            |
    | mysql-monitor_username                                               | monitor12345678 |
    | mysql-monitor_password                                               | monitor12345678 |
    | mysql-monitor_history                                                | 600000          |
    | mysql-monitor_connect_interval                                       | 60000           |
    | mysql-monitor_ping_interval                                          | 10000           |
    | mysql-monitor_read_only_interval                                     | 1500            |
    | mysql-monitor_read_only_timeout                                      | 500             |
    +----------------------------------------------------------------------+-----------------+

#### 检查登录情况 ####

Admin>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
    +------------+------+------------------+-------------------------+-----------------------------------------------------------------------------+
    | hostname   | port | time_start_us    | connect_success_time_us | connect_error                                                               |
    +------------+------+------------------+-------------------------+-----------------------------------------------------------------------------+
    | 10.6.8.147 | 3306 | 1681299772585477 | 0                       | Access denied for user 'monitor12345678'@'10.6.8.174' (using password: YES) |
    | 10.6.8.174 | 3306 | 1681299771791832 | 0                       | Access denied for user 'monitor12345678'@'10.6.8.174' (using password: YES) |
    | 10.6.8.174 | 3306 | 1681299767550776 | 0                       | Access denied for user 'monitor'@'10.6.8.174' (using password: YES)         |
    +------------+------+------------------+-------------------------+-----------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

放开白名单即可

然后健康检查结果

Admin>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
    +------------+------+------------------+-------------------------+---------------+
    | hostname   | port | time_start_us    | connect_success_time_us | connect_error |
    +------------+------+------------------+-------------------------+---------------+
    | 10.6.8.174 | 3306 | 1681300972640975 | 604                     | NULL          |
    | 10.6.8.147 | 3306 | 1681300971793675 | 1768                    | NULL          |
    | 10.6.8.147 | 3306 | 1681300912640330 | 1582                    | NULL          |
    +------------+------+------------------+-------------------------+---------------+
    3 rows in set (0.00 sec)
    
    Admin>SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
    +------------+------+------------------+----------------------+------------+
    | hostname   | port | time_start_us    | ping_success_time_us | ping_error |
    +------------+------+------------------+----------------------+------------+
    | 10.6.8.174 | 3306 | 1681301032123280 | 178                  | NULL       |
    | 10.6.8.147 | 3306 | 1681301031931784 | 458                  | NULL       |
    | 10.6.8.174 | 3306 | 1681301022061284 | 225                  | NULL       |
    +------------+------+------------------+----------------------+------------+
    3 rows in set (0.00 sec)

#### 配置主备切换的自动检测 ####

all the MySQL backend servers that are either configured in hostgroup 1 or 2 will be placed into their respective hostgroup based on their read\_only value:

If they have , they will be moved to hostgroup 1read\_only=0  
If they have , they will be moved to hostgroup 2read\_only=1

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
    
    # 生效
     LOAD MYSQL SERVERS TO RUNTIME;

检查生效

Admin>SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
    +------------+------+------------------+-----------------+-----------+-------+
    | hostname   | port | time_start_us    | success_time_us | read_only | error |
    +------------+------+------------------+-----------------+-----------+-------+
    | 10.6.8.174 | 3306 | 1681809841749869 | 278             | 1         | NULL  |
    | 10.6.8.147 | 3306 | 1681809841734498 | 650             | 0         | NULL  |
    | 10.6.8.174 | 3306 | 1681809840250699 | 258             | 1         | NULL  |
    +------------+------+------------------+-----------------+-----------+-------+
    3 rows in set (0.00 sec)

#### 配置读写分离规则 ####

Key points about these query rules (and query rules in general):

*  Query rules are processed as ordered by rule\_id
 *  Only rules that have active=1 are processed
 *  The first rule example uses caret (^) and dollar ($) : these are special regex characters that mark the beginning and the end of a pattern i.e. in this case match\_digestormatch\_pattern should completely match the query
 *  The second rule in the example doesn’t use caret or dollar : the match could be anywhere in the query
 *  The question mark is escaped as it has a special meaning in regex  
    apply=1 means that no further rules should be evaluated if the current rule was matched

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'monitor12345678','^SELECT',2,1);
    
    
    # 生效
    LOAD MYSQL QUERY RULES TO RUNTIME;

#### 验证读写分离 ####

Admin>SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest where hg=2 ORDER BY sum_time asc;
    +----+----------+------------+---------------------------------------------------+
    | hg | sum_time | count_star | digest_text                                       |
    +----+----------+------------+---------------------------------------------------+
    | 2  | 515      | 1          | SELECT * FROM `test01`.`aa` WHERE `a` = ? LIMIT ? |
    | 2  | 9565     | 13         | SELECT * FROM `test01`.`aa` LIMIT ?,?             |
    +----+----------+------------+---------------------------------------------------+
    2 rows in set (0.01 sec)
    
    Admin>SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
    +----+---------------+-----------------+
    | hg | SUM(sum_time) | SUM(count_star) |
    +----+---------------+-----------------+
    | 1  | 505998        | 300             |
    | 2  | 10080         | 14              |
    +----+---------------+-----------------+
    2 rows in set (0.00 sec)

## 参考文献 ##

https://proxysql.com/documentation/installing-proxysql/

https://proxysql.com/documentation/ProxySQL-Configuration/

https://www.cnblogs.com/keme/p/12290977.html\#4%E9%85%8D%E7%BD%AE-proxysql-%E6%89%80%E9%9C%80%E8%B4%A6%E6%88%B7  
https://proxysql.com/documentation/getting-started/