精华内容
下载资源
问答
  • 代理网 开源Web UI 当前功能包括: 干净,响应式设计 全局和每服务器选项 隐藏未使用的表(基于全局或​​每个服务器) 按任何列对内容排序(asc / desc) 在线配置编辑器 ...配置ProxySQL以进行
  • ProxySQL / Orchestrator / MySQL Docker测试 该存储库包含一个docker-compose.yml,用于启动一个小型集群,该集群由3个MySQL 5.7容器(1个主服务器和2个从属服务器),3个通过RAFT连接的Orchestrator节点以及一个...
  • k8s-proxysql群集 在Kubernetes中以集群模式运行proxysql的容器。 用法 默认情况下,该容器充当普通的proxysql实例。 要启用群集模式,需要执行以下操作。 服务 容器使用服务来发现是否已经存在主节点。 可以在以下...
  • ProxySQL离线安装包CENTOS6.5,包括所有依赖的包,
  • ProxySQL最新官方文档

    2018-11-23 21:48:07
    ProxySQL最新官方文档,带详细目录,全英文,很好用。
  • proxysql

    2019-03-14 18:32:49
    proxysqlmysql实现读写分离的方式ProxySQL简介ProxySQL安装ProxySQL的Admin管理接口admin管理接口相关的变量admin-admin_credentialsadmin-stats_credentialsadmin-mysql_ifaces多层配置系统proxysql中的库ProxySQL...

    mysql实现读写分离的方式

    mysql 实现读写分离的方式有以下几种:

    1.程序修改mysql操作,直接和数据库通信,简单快捷的读写分离和随机的方式实现的负载均衡,权限独立分配,需要开发人员协助。
    2.amoeba,直接实现读写分离和负载均衡,不用修改代码,有很灵活的数据解决方案,自己分配账户,和后端数据库权限管理独立,权限处理不够灵活。
    3.mysql-proxy,直接实现读写分离和负载均衡,不用修改代码,master和slave用一样的帐号,效率低
    4.mycat中间件
    5.proxysql中间件(推荐使用)

    ProxySQL简介

    ProxySQL 是一款可以实际用于生产环境的 MySQL 中间件,它有官方版和 percona 版两种。percona版是在官方版的基础上修改的,添加了几个比较实用的工具。生产环境建议用官方版。

    ProxySQL 是用 C++ 语言开发的,虽然也是一个轻量级产品,但性能很好(据测试,能处理千亿级的数据),功能也足够,能满足中间件所需的绝大多数功能,包括:

    1.最基本的读/写分离,且方式有多种
    2.可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说,规则很灵活。基于schema和与语句级的规则,可以实现简单的sharding(分库分表)
    3.可缓存查询结果。虽然ProxySQL的缓存策略比较简陋,但实现了基本的缓存功能,绝大多数时候也够用了。此外,作者已经打算实现更丰富的缓存策略
    4.监控后端节点。ProxySQL可以监控后端节点的多个指标,包括:ProxySQL和后端的心跳信息,后端节点的read-only/read-write,slave和master的数据同步延迟性(replication lag)

    ProxySQL安装

    配置yum源:
    [root@localhost ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
    > [proxysql_repo]
    > name= ProxySQL
    > baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
    > gpgcheck=1
    > enabled=1
    > gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    > EOF
    [proxysql_repo]
    name= ProxySQL
    baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
    gpgcheck=1
    enabled=1
    gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    [root@localhost yum.repos.d]# ls
    proxysql.repo  redhat.repo  zz.repo
    安装:
    [root@localhost ~]# yum -y install proxysql
    [root@localhost ~]# yum -y install mysql    (安装数据库)
    
    

    ProxySQL的Admin管理接口

    当 ProxySQL 启动后,将监听两个端口:

    • admin管理接口,默认端口为6032。该端口用于查看、配置ProxySQL
    • 接收SQL语句的接口,默认端口为6033,这个接口类似于MySQL的3306端口

    ProxySQL 的 admin 管理接口是一个使用 MySQL 协议的接口,所以,可以直接使用 mysql 客户端、navicat 等工具去连接这个管理接口,其默认的用户名和密码均为 admin

    例如,使用 mysql 客户端去连接 ProxySQL 的管理接口:

    开启服务:
    [root@localhost ~]# service  proxysql start
    Starting ProxySQL: 2019-03-12 09:15:29 [INFO] Using config file /etc/proxysql.cnf
    DONE!
    [root@localhost ~]# ss -antl
    State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
    LISTEN     0      128          *:6032                     *:*                  
    LISTEN     0      128          *:6033                     *:*                  
    LISTEN     0      128          *:6033                     *:*                  
    LISTEN     0      128          *:6033                     *:*                  
    LISTEN     0      128          *:6033                     *:*                  
    LISTEN     0      128          *:22                       *:*                  
    LISTEN     0      100    127.0.0.1:25                       *:*                  
    LISTEN     0      128         :::22                      :::*                  
    LISTEN     0      100        ::1:25                      :::*                  
    
    [root@localhost ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | disk          | /var/lib/proxysql/proxysql.db       |
    | 3   | stats         |                                     |
    | 4   | monitor       |                                     |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.00 sec)
    

    由于 ProxySQL 的配置全部保存在几个自带的库中,所以通过管理接口,可以非常方便地通过发送一些SQL命令去修改 ProxySQL 的配置。 ProxySQL 会解析通过该接口发送的某些对ProxySQL 有效的特定命令,并将其合理转换后发送给内嵌的 SQLite3 数据库引擎去运行

    ProxySQL 的配置几乎都是通过管理接口来操作的,通过 Admin 管理接口,可以在线修改几乎所有的配置并使其生效。只有两个变量的配置是必须重启 ProxySQL 才能生效的,它们是:
    mysql-threads 和 mysql-stacksize

    admin管理接口相关的变量

    admin-admin_credentials

    admin-admin_credentials 变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin,但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL,例如用windows上的navicat连接Linux上的ProxySQL管理接口,必须自定义一个管理员账户。

    • 添加管理员帐户
    MySQL [(none)]> select @@admin-admin_credentials;    //查看当前用户名和密码
    +---------------------------+
    | @@admin-admin_credentials |
    +---------------------------+
    | admin:admin               |
    +---------------------------+
    1 row in set (0.00 sec)
    
    //设置管理员帐号gx,密码gongxue
    MySQL [(none)]> set admin-admin_credentials='admin:admin;gx:gongxue';
    Query OK, 1 row affected (0.01 sec)
    
    MySQL [(none)]> select @@admin-admin_credentials;
    +---------------------------+
    | @@admin-admin_credentials |
    +---------------------------+
    | admin:admin;gx:gongxue    |
    +---------------------------+
    1 row in set (0.00 sec)
    
    MySQL [(none)]> load admin variables to runtime;  //使修改立即生效
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> save admin variables to disk;  //使修改永久保存到磁盘
    Query OK, 31 rows affected (0.00 sec)
    

    修改后,就可以使用该用户名和密码连接管理接口

    [root@localhost ~]# mysql -ugx -pgongxue -h127.0.0.1 -P6032
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> 
    

    所有的配置操作都是在修改main库中对应的表

    MySQL [(none)]> select * from global_variables where variable_name='admin-admin_credentials';
    +-------------------------+------------------------+
    | variable_name           | variable_value         |
    +-------------------------+------------------------+
    | admin-admin_credentials | admin:admin;gx:gongxue |
    +-------------------------+------------------------+
    1 row in set (0.00 sec)
    

    必须要区分admin管理接口的用户名和mysql_users中的用户名

    1.admin管理接口的用户是连接到管理接口(默认端口6032)上用来管理、配置ProxySQL的
    2.mysql_users表中的用户名是应用程序连接ProxySQL(默认端口6033),以及ProxySQL连接后端MySQL Servers使用的用户。它的作用是发送、路由SQL语句,类似于MySQL Server的3306端口。所以,这个表中的用户必须已经在后端MySQL Server上存在且授权了

    admin管理接口的用户必须不能存在于mysql_users中,这是出于安全的考虑,防止通过admin管理接口用户猜出mysql_users中的用户

    admin-stats_credentials

    admin-stats_credentials 变量控制admin管理接口的普通用户,这个变量中的用户没有超级管理员权限,只能查看monitor库和main库中关于统计的数据,其它库都是不可见的,且没有任何写权限

    默认的普通用户名和密码均为 stats ,与admin一样,它默认也只能用于本地登录,若想让人远程查看则要添加查看的专有用户

    查看
    MySQL [(none)]> select @@admin-stats_credentials;
    +---------------------------+
    | @@admin-stats_credentials |
    +---------------------------+
    | stats:stats               |
    +---------------------------+
    1 row in set (0.00 sec)
    
    添加专有的查看用户
    MySQL [(none)]> set admin-stats_credentials='stats:stats;gxstats:gongxue';
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [(none)]> select @@admin-stats_credentials;
    +-----------------------------+
    | @@admin-stats_credentials   |
    +-----------------------------+
    | stats:stats;gxstats:gongxue |
    +-----------------------------+
    1 row in set (0.00 sec)
    
    MySQL [(none)]> load admin variables to runtime;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> save admin variables to disk;
    Query OK, 31 rows affected (0.00 sec)
    

    这个变量中的用户必须不能存在于mysql_users表中

    使用gxstats用户远程连接查看

    [root@localhost ~]# mysql -ugxstats -pgongxue -h127.0.0.1 -P6032
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> show tables from main;
    +--------------------------------------+
    | tables                               |
    +--------------------------------------+
    | global_variables                     |
    | stats_memory_metrics                 |
    | stats_mysql_commands_counters        |
    | stats_mysql_connection_pool          |
    | stats_mysql_connection_pool_reset    |
    | stats_mysql_global                   |
    | stats_mysql_prepared_statements_info |
    | stats_mysql_processlist              |
    | stats_mysql_query_digest             |
    | stats_mysql_query_digest_reset       |
    | stats_mysql_query_rules              |
    | stats_mysql_users                    |
    | stats_proxysql_servers_checksums     |
    | stats_proxysql_servers_metrics       |
    | stats_proxysql_servers_status        |
    +--------------------------------------+
    15 rows in set (0.00 sec)
    

    admin-mysql_ifaces

    admin-mysql_ifaces 变量指定admin接口的监听地址,格式为冒号分隔的hostname:port列表。默认监听在 0.0.0.0:6032

    • 注意,允许使用UNIX的domain socket进行监听,这样本主机内的应用程序就可以直接被处理。
      例如:
    MySQL [(none)]> SET admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock';
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [(none)]> load admin variables to runtime;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> save admin variables to disk;
    Query OK, 31 rows affected (0.00 sec)
    

    多层配置系统

    proxysql中的库

    使用ProxySQL的Admin管理接口连上ProxySQL,可查看ProxySQL拥有的库

    [root@localhost ~]# mysql -ugx -pgongxue -h127.0.0.1 -P6032
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | disk          | /var/lib/proxysql/proxysql.db       |
    | 3   | stats         |                                     |
    | 4   | monitor       |                                     |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.00 sec)
    
    • 其中:

    main库是ProxySQL最主要的库,是需要修改配置时使用的库,它其实是一个内存数据库系统。所以,修改main库中的配置后,必须将其持久化到disk上才能永久保存
    disk库是磁盘数据库,该数据库结构和内存数据库完全一致。当持久化内存数据库中的配置时,其实就是写入到disk库中。磁盘数据库的默认路径为 $DATADIR/proxysql.db
    stats库是统计信息库。这个库中的数据一般是在检索其内数据时临时填充的,它保存在内存中。因为没有相关的配置项,所以无需持久化
    monitor库是监控后端MySQL节点相关的库,该库中只有几个log类的表,监控模块收集到的监控信息全都存放到对应的log表中
    stats_history库是1.4.4版新增的库,用于存放历史统计数据。默认路径为 $DATADIR/proxysql_stats.db

    ProxySQL 内部使用的是 SQLite3 数据库,无论是内存数据库还是磁盘数据库,都是通过SQLite3引 擎进行解析、操作的。它和 MySQL 的语法可能稍有不同,但ProxySQL会对不兼容的语法自动进行调整,最大程度上保证MySQL语句的有效率。
    上面描述main库的时候,只是说了内存数据库需要持久化到disk库才能永久保存配置。但实际上,修改了main库中的配置后,并不会立即生效,它还需要load到runtime的数据结构中才生效,只有在runtime数据结构中的配置才是对ProxySQL当前有效的配置

    ProxySQL多层配置系统

    ProxySQL 的配置系统非常强大,它能在线修改几乎所有配置(仅有的两个需要重启才能生效的变量为 mysql-threads 和 mysql-stacksize ),并在线生效、持久化保存。这得益于它采用的多层配置系统。
    多层配置系统结构如下:
    在这里插入图片描述
    最底层的是 disk 库和 config file 。这里需要注意,这里的 config file 就是传统的配置文件,默认为 /etc/proxysql.cnf , ProxySQL 启动时,主要是从 disk 库中读取配置加载到内存并最终加载到 runtime 生效,只有极少的几个特定配置内容是从 config file 中加载的,除非是第一次初始化 ProxySQL 运行环境(或者disk库为空)。

    中间层的是 memory ,表示的是内存数据库,其实就是 main 库。通过管理接口修改的所有配置,都保存在内存数据库(main)中。当 ProxySQL 重启或者崩溃时,这个内存数据库中的数据会丢失,所以需要 save 到 disk 库中。

    最上层的是 runtime ,它是 ProxySQL 有关线程运行时读取的数据结构。换句话说,该数据结构中的配置都是已生效的配置。所以,修改了 main 库中的配置后,必须 load 到 runtime 数据结构中才能使其生效。
    在上面的多层配置系统图中,标注了[1]、[2]、[3]、[4]、[5]的序号。每个序号都有两个操作方向from/to,其实只是所站角度不同而已。以下是各序号对应的操作:

    [1] :将内存数据库中的配置加载到RUNTIME数据结构中
    LOAD XXX FROM MEMORY
    LOAD XXX TO RUNTIME
    [2] :将RUNTIME数据结构中的配置持久化到内存数据库中
    SAVE XXX FROM RUNTIME
    SAVE XXX TO MEMORY
    [3] :将磁盘数据库中的配置加载到内存数据库中
    LOAD XXX FROM DISK
    LOAD XXX TO MEMORY
    [4] :将内存数据库中的配置持久化到磁盘数据库中
    SAVE XXX FROM MEMORY
    SAVE XXX TO DISK
    [5] :从传统配置文件中读取配置加载到内存数据库中
    LOAD XXX FROM CONFIG

    DISK/MEMORY/RUNTIME/CONFIG 可以缩写,只要能识别即可。例如MEMORY可以缩写为MEM,runtime可以缩写为run
    另外,上面的XXX是什么?这表示要加载/保存的是哪类配置。目前的ProxySQL支持以下几种:

    mysql users
    mysql servers
    mysql variables
    mysql query rules
    admin variables
    scheduler
    * proxysql_servers:目前ProxySQL集群功能还处于实验阶段,所以该类配置不应该去使用 
    

    从main库或disk库中就可以查看到

    MySQL [(none)]> show tables from disk;
    +------------------------------------+
    | tables                             |
    +------------------------------------+
    | global_variables                   |
    | mysql_collations                   |
    | mysql_group_replication_hostgroups |
    | mysql_query_rules                  |
    | mysql_query_rules_fast_routing     |
    | mysql_replication_hostgroups       |
    | mysql_servers                      |
    | mysql_users                        |
    | proxysql_servers                   |
    | scheduler                          |
    +------------------------------------+
    10 rows in set (0.01 sec)
    

    上面的结果中我给这些表都标注了一些序号,其所对应的表的内容有以下讲究:

        (1)中包含两类变量,以amdin-开头的表示admin variables,以mysql-开头的表示mysql variables。修改哪类变量,前文的XXX就代表哪类
        (2,5,6)对应的都是mysql servers
        (3,4)对应的是mysql query rules
        (7)对应的mysql users
        (9)对应的scheduler
        (N)只是一张表,保存的是ProxySQL支持的字符集和排序规则,它是不用修改的
        (8)是ProxySQL的集群配置表,该功能目前还处于实验阶段。如果想要配置该功能,则load/save proxysql_servers to/from ...
    

    启动ProxySQL时如何加载配置

    如果 ProxySQL 是刚安装的,或者磁盘数据库文件为空(甚至不存在),或者启动 ProxySQL 时使用了选项 --initial,这几种情况启动 ProxySQL 时,都会从传统配置文件 config file 中读取配置加载到内存数据库,并自动 load 到 runtime 数据结构、save到磁盘数据库,这是初始化 ProxySQL 运行环境的过程。

    如果不是第一次启动 ProxySQL ,由于已经存在磁盘数据库文件,这时 ProxySQL 会从磁盘数据库中读取几乎所有的配置(即使传统配置文件中配置了某项,也不会去解析),但有3项是必须从传统配置文件中读取,它们分别是:

    datadir:ProxySQL启动时,必须从配置文件中确定它的数据目录,因为磁盘数据库文件、日志以及其它一些文件是存放在数据目录下的。如果使用/etc/init.d/proxysql管理ProxySQL,则除了修改/etc/proxysql.cnf的datadir,还需要修改该脚本中的datadir。
    restart_on_missing_heartbeats:MySQL线程丢失多少次心跳,就会杀掉这个线程并重启它。默认值为10。
    execute_on_exit_failure:如果设置了该变量,ProxySQL父进程将在每次ProxySQL崩溃的时候执行已经定义好的脚本。建议使用它来生成一些崩溃时的警告和日志。注意,ProxySQL的重启速度可能只有几毫秒,因此很多其它的监控工具可能无法探测到ProxySQL的一次普通故障,此时可使用该变量

    不同类型的读写分离方案解析

    数据库中间件最基本的功能就是实现读写分离, ProxySQL 当然也支持。而且 ProxySQL 支持的路由规则非常灵活,不仅可以实现最简单的读写分离,还可以将读/写都分散到多个不同的组,以及实现分库 sharding (分表sharding的规则比较难写,但也能实现)。

    本文只描述通过规则制定的语句级读写分离,不讨论通过 ip/port, client, username, schemaname 实现的读写分离。

    下面描述了ProxySQL能实现的常见读写分离类型

    最简单的读写分离

    在这里插入图片描述
    这种模式的读写分离,严格区分后端的master和slave节点,且slave节点必须设置选项read_only=1

    在ProxySQL上,分两个组,一个写组HG=10,一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能,让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)

    这种模式的读写分离是最简单的,只需在mysql_users表中设置用户的默认路由组为写组HG=10,并在mysql_query_rules中加上两条简单的规则(一个select for update,一个select)即可

    这种读写分离模式,在环境较小时能满足绝大多数需求。但是需求复杂、环境较大时,这种模式就太过死板,因为一切都是monitor模块控制的

    多个读组或写组的分离模式

    前面那种读写分离模式,是通过 monitor 模块监控 read_only 来调整的,所以每一个后端集群必须只能分为一个写组,一个读组。
    但如果想要区分不同的 select ,并将不同的 select 路由到不同的节点上。例如有些查询语句的开销非常大,想让它们独占一个节点/组,其它查询共享一个节点/组,怎么实现?

    例如,下面这种模式
    在这里插入图片描述
    看上去非常简单。但是却能适应各种需求。例如,后端做了分库,对某库的查询要路由到特定的主机组

    至于各个主机组是同一个主从集群(下图左边),还是互相独立的主从集群环境(下图右边),要看具体的需求,不过这种读写分离模式都能应付
    在这里插入图片描述

    在实现这种模式时,前提是不能开启monitor模块的read_only监控功能,也不要设置mysql_replication_hostgroup 表

    例如,下面的配置实现的是上图左边的结构:写请求路由给HG=10,对test1库的select语句路由给HG=20,其它select路由给HG=30

    mysql_servers: 
    +--------------+----------+------+--------+--------+
    | hostgroup_id | hostname | port | status | weight |
    +--------------+----------+------+--------+--------+
    | 10           | host1    | 3306 | ONLINE | 1      |
    | 20           | host2    | 3306 | ONLINE | 1      |
    | 30           | host3    | 3306 | ONLINE | 1      |
    +--------------+----------+------+--------+--------+
    
    mysql_users: 
    +----------+-------------------+
    | username | default_hostgroup |
    +----------+-------------------+
    | root     | 10                |
    +----------+-------------------+
    
    mysql_query_rules: 
    +---------+-----------------------+----------------------+
    | rule_id | destination_hostgroup | match_digest         |
    +---------+-----------------------+----------------------+
    | 1       | 10                    | ^SELECT.*FOR UPDATE$ |
    | 2       | 20                    | ^SELECT.*test1\..*   |
    | 3       | 30                    | ^SELECT              |
    +---------+-----------------------+----------------------+
    
    • 查看表结构的方式:
    
    PRAGMA  table_info("表名");
    

    数据库说明:

    (admin@127.0.0.1:6032) [(none)]> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | disk          | /var/lib/proxysql/proxysql.db       |
    | 3   | stats         |                                     |
    | 4   | monitor       |                                     |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.00 sec)
    

    main 内存配置数据库,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容,不能通过dml语句修改,只能修改对应的不以 runtime 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载
    disk 是持久化到硬盘的配置,sqlite数据文件
    stats 是proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等
    monitor 库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查
    stats_history 统计信息历史库

    Proxysql管理端添加后端连接mysql主从数据库的配置

    (admin@127.0.0.1:6032) [(none)]> show tables from main;
    +--------------------------------------------+
    | tables                                     |
    +--------------------------------------------+
    | global_variables                           |  # ProxySQL的基本配置参数,类似与MySQL
    | mysql_collations                           |  # 配置对MySQL字符集的支持
    | mysql_group_replication_hostgroups         |  # MGR相关的表,用于实例的读写组自动分配
    | mysql_query_rules                          |  # 路由表
    | mysql_query_rules_fast_routing             |  # 主从复制相关的表,用于实例的读写组自动分配
    | mysql_replication_hostgroups               |  # 存储MySQL实例的信息
    | mysql_servers                              |  # 存储MySQL用户
    | mysql_users                                |  # 存储ProxySQL的信息,用于ProxySQL Cluster同步
    | proxysql_servers                           |  # 运行环境的存储校验值
    | runtime_checksums_values                   |
    | runtime_global_variables                   |
    | runtime_mysql_group_replication_hostgroups |
    | runtime_mysql_query_rules                  |
    | runtime_mysql_query_rules_fast_routing     |
    | runtime_mysql_replication_hostgroups       |  # 与上面对应,但是运行环境正在使用的配置
    | runtime_mysql_servers                      |
    | runtime_mysql_users                        |
    | runtime_proxysql_servers                   |
    | runtime_scheduler                          |
    | scheduler                                  |  # 定时任务表
    +--------------------------------------------+
    20 rows in set (0.00 sec)
    

    runtime_ 开头的是运行时的配置,这些是不能修改的。要修改 ProxySQL 的配置,需要修改了非 runtime_ 表,修改后必须执行 LOAD … TO RUNTIME 才能加载到 RUNTIME 生效,执行 save … to disk 才能将配置持久化保存到磁盘

    下面语句中没有先切换到 main 库也执行成功了,因为 ProxySQL 内部使用的 SQLite3 数据库引擎,和 MySQL 的解析方式是不一样的。即使执行了 USE main 语句也是无任何效果的,但不会报错

    在 proxysql 主机的 mysql_users 表中添加刚才在 master 上创建的账号 proxysql,proxysql 客户端需要使用这个账号来访问数据库
    default_hostgroup 默认组设置为写组,也就是1;
    当读写分离的路由规则不符合时,会访问默认组的数据库;

    (admin@127.0.0.1:6032) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','pwproxysql',1,1);
    Query OK, 1 row affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> select * from mysql_users \G
    *************************** 1. row ***************************
                  username: proxysql        # 后端mysql实例的用户名
                  password: pwproxysql      # 后端mysql实例的密码
                    active: 1               # active=1表示用户生效,0表示不生效
                   use_ssl: 0
         default_hostgroup: 1               # 用户默认登录到哪个hostgroup_id下的实例
            default_schema: NULL            # 用户默认登录后端mysql实例时连接的数据库,这个地方为NULL的话,则由全局变量mysql-default_schema决定,默认是information_schema
             schema_locked: 0
    transaction_persistent: 1       # 如果设置为1,连接上ProxySQL的会话后,如果在一个hostgroup上开启了事务,那么后续的sql都继续维持在这个hostgroup上,不论是否会匹配上其它路由规则,直到事务结束。虽然默认是0
              fast_forward: 0       # 忽略查询重写/缓存层,直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能,一般不用,路由规则 .* 就行了
                   backend: 1
                  frontend: 1
           max_connections: 10000   # 该用户允许的最大连接数
    1 row in set (0.00 sec)
    

    添加健康检测的帐号

    在mysql的 master 端添加属于proxysql的只读账号

    
    mysql> GRANT SELECT ON *.* TO 'monitor'@'172.16.12.%' IDENTIFIED BY 'monitor';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    

    在proxysql主机端修改变量设置健康检测的账号

    
    (admin@127.0.0.1:6032) [(none)]> set mysql-monitor_username='monitor';
    Query OK, 1 row affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> set mysql-monitor_password='monitor';
    Query OK, 1 row affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> load mysql variables to runtime;
    Query OK, 0 rows affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> save mysql variables to disk;
    Query OK, 97 rows affected (0.00 sec)
    
    

    添加读写分离的路由规则

    • 需求:

    将 select 查询语句全部路由至 hostgroup_id=2 的组(也就是读组)
    但是 select * from tb for update 这样的语句是会修改数据的,所以需要单独定义,将它路由至 hostgroup_id=1 的组(也就是写组)
    其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users 表中的 default_hostgroup)

    (admin@127.0.0.1:6032) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
    Query OK, 1 row affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
    Query OK, 1 row affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
    +---------+--------+----------------------+-----------------------+-------+
    | rule_id | active | match_digest         | destination_hostgroup | apply |
    +---------+--------+----------------------+-----------------------+-------+
    | 1       | 1      | ^SELECT.*FOR UPDATE$ | 1                     | 1     |
    | 2       | 1      | ^SELECT              | 2                     | 1     |
    +---------+--------+----------------------+-----------------------+-------+
    2 rows in set (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> load mysql query rules to runtime;
    Query OK, 0 rows affected (0.01 sec)
    
    (admin@127.0.0.1:6032) [(none)]> load admin variables to runtime;
    Query OK, 0 rows affected (0.00 sec)
    
    (admin@127.0.0.1:6032) [(none)]> save mysql query rules to disk;
    Query OK, 0 rows affected (0.01 sec)
    
    (admin@127.0.0.1:6032) [(none)]> save admin variables to disk;
    Query OK, 31 rows affected (0.00 sec)
    
    展开全文
  • ProxySQL中文手册.pdf

    2018-07-06 19:38:31
    ProxySQL 1.4.4官方手册的翻译。ProxySQL 1.4.4官方手册的翻译。ProxySQL 1.4.4官方手册的翻译。ProxySQL 1.4.4官方手册的翻译。
  • proxysql-2.0.5可修复'ProxySQL Error:connection is locked to hostgroup 10 but trying to reach hostgroup 20这个问题
  • ProxySQL工具 ProxySQL工具 免费软件:Apache Software License 2.0 文档: : 。 特征 AWS上的ProxySQL支持 ProxySQL和Percona XtraDB群集集成 ProxySQL调度程序 学分 该程序包是使用和项目模板创建的。
  • ProxySQL简单读写分离

    2020-12-08 19:11:44
    ProxySQL读写分离文档精选: 查询路由是proxysql的核心特性之一。 读/写分离可能是最常用的查询路由之一,而另一种最常用的查询路由是分片。 使用不同的端口进行读写分离 如果使用像HAProxy这样的代理,可以...
  • ProxySQL

    2019-10-03 08:10:13
    https://github.com/sysown/proxysql/wiki https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration关于ProxySQL的文档请见:https://github.com/sysown/ProxySQL 下载安装proxysql下载地址如下: ...
    https://github.com/sysown/proxysql/wiki
    https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
    关于ProxySQL的文档请见:https://github.com/sysown/ProxySQL

    下载安装proxysql
    下载地址如下:
    https://github.com/sysown/ProxySQL
    或者
    https://www.percona.com/downloads/proxysql/
     
    安装依赖包
    # yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
    # vim /etc/yum.repos.d/proxysql.repo
    [proxysql_repo]
    name= ProxySQL YUM repository
    baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
    gpgcheck=1
    gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    # yum install -y proxysql
     
    查看proxysql涉及到哪些文件
    # rpm -ql proxysql
    /etc/init.d/proxysql
    /etc/proxysql.cnf
    /usr/bin/proxysql
    /usr/share/proxysql/tools/proxysql_galera_checker.sh
    /usr/share/proxysql/tools/proxysql_galera_writer.pl
     
    # systemctl status proxysql.service
    ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
       Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled)
       Active: inactive (dead)
         Docs: man:systemd-sysv-generator(8)
     
    /etc/init.d/proxysql脚本涉及到如下目录、文件
    OLDDATADIR="/var/run/proxysql"
    DATADIR="/var/lib/proxysql"
    OPTS="-c /etc/proxysql.cnf -D $DATADIR"
    PIDFILE="$DATADIR/proxysql.pid"

    # more /run/systemd/generator.late/proxysql.service
    # Automatically generated by systemd-sysv-generator
    [Unit]
    Documentation=man:systemd-sysv-generator(8)
    SourcePath=/etc/rc.d/init.d/proxysql
    Description=LSB: High Performance Advanced Proxy for MySQL
    Before=runlevel2.target
    Before=runlevel3.target
    Before=runlevel4.target
    Before=runlevel5.target
    Before=shutdown.target
    After=network-online.target
    After=vmware-tools.service
    After=vmware-tools-thinprint.service
    Conflicts=shutdown.target
    [Service]
    Type=forking
    Restart=no
    TimeoutSec=5min
    IgnoreSIGPIPE=no
    KillMode=process
    GuessMainPID=no
    RemainAfterExit=yes
    ExecStart=/etc/rc.d/init.d/proxysql start
    ExecStop=/etc/rc.d/init.d/proxysql stop
    ExecReload=/etc/rc.d/init.d/proxysql reload

    检查版本
    # which proxysql
    /usr/bin/proxysql
    # proxysql --version
    ProxySQL version v1.4.10-1-g5eb0f3e, codename Truls
    # proxysql --help
    High Performance Advanced Proxy for MySQL
    USAGE: proxysql [OPTIONS]
    OPTIONS:
    -c, --config ARG             Configuraton file
    -D, --datadir ARG            Datadir
    -e, --exit-on-error          Do not restart ProxySQL if crashes
    -f, --foreground             Run in foreground
    -h, -help, --help, --usage   Display usage instructions.
    -M, --no-monitor             Do not start Monitor Module
    -n, --no-start               Starts only the admin service
    -r, --reuseport              Use SO_REUSEPORT
    -S, --admin-socket ARG       Administration Unix Socket
    -V, --version                Print version
    --idle-threads               Create auxiliary threads to handle idle connections
    --initial                    Rename/empty database file
    --reload                     Merge config file into database file
    --sqlite3-server             Enable SQLite3 Server

    ProxySQL rev. v1.4.10-1-g5eb0f3e -- Tue Aug  7 12:31:55 2018
    Copyright (C) 2013-2018 ProxySQL LLC
    This program is free and without warranty

    配置文件/etc/proxysql.cnf
    配置文件只在第一次启动的时候读取进行初始化,后面只读取db文件
    先启动,然后再修改参数

    启动proxysql
    # systemctl start proxysql
    # ps -ef | grep proxysql
    root       3157      1  0 01:21 ?        00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
    root       3158   3157  1 01:21 ?        00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
    root       3183   2821  0 01:21 pts/0    00:00:00 grep --color=auto proxysql
    # netstat -antp | grep proxysql
    tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      3158/proxysql      
    tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3158/proxysql
     
    6032是管理端口
    6033是对外服务端口
    默认管理用户及密码:admin,admin
     
    连接proxysql 6032管理端口
    # mysql -uadmin -padmin -h 127.0.0.1 -P6032
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.5.30 (ProxySQL Admin Module)
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    (admin@127.0.0.1) [(none)]> show databases;
    +-----+---------------+-------------------------------------+
    | seq   | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main             |                                                                   |
    | 2   | disk               | /var/lib/proxysql/proxysql.db              |
    | 3   | stats              |                                                                  |
    | 4   | monitor         |                                                                 |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db    |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.01 sec)

    main数据库
    内存配置数据库,即MEMORY,表里存放后端db实例、用户验证、路由规则等信息
    表名以runtime_开头的表示ProxySQL当前运行的配置内容,不能通过DML语句修改
    只能修改对应的非runtime开头的表,然后load使其生效,save使其存到硬盘以供下次重启加载
    (admin@127.0.0.1) [(none)]> use main;
    Database changed
    (admin@127.0.0.1) [main]> show tables;
    +--------------------------------------------+
    | tables                                     |
    +--------------------------------------------+
    | global_variables                            |
    | mysql_collations                            |
    | mysql_group_replication_hostgroups         |
    | mysql_query_rules                           |
    | mysql_query_rules_fast_routing              |
    | mysql_replication_hostgroups                |
    | mysql_servers                               |
    | mysql_users                                 |
    | proxysql_servers                            |
    | runtime_checksums_values                    |
    | runtime_global_variables                    |
    | runtime_mysql_group_replication_hostgroups |
    | runtime_mysql_query_rules                   |
    | runtime_mysql_query_rules_fast_routing     |
    | runtime_mysql_replication_hostgroups       |
    | runtime_mysql_servers                       |
    | runtime_mysql_users                         |
    | runtime_proxysql_servers                    |
    | runtime_scheduler                           |
    | scheduler                                    |
    +--------------------------------------------+
    20 rows in set (0.00 sec)
     
    global_variables参数设置,通过set来设置
    mysql_query_rules:
    指定Query路由到后端不同服务器的规则列表
    mysql_replication_hostgroups:
    监视指定主机组中所有服务器的read_only值,并且根据read_only的值将服务器分配给写入器或读取器主机组
    mysql_servers:
    后端可以连接MySQL服务器的列表
    mysql_users:
    配置后端数据库的账号和监控的账号
     
    disk数据库
    持久化到硬盘的配置,sqlite数据文件

    stats数据库
    proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总、执行时间等等
    (admin@127.0.0.1) [stats]> show tables from stats;
    +--------------------------------------+
    | tables                               |
    +--------------------------------------+
    | global_variables                     |
    | stats_memory_metrics                 |
    | stats_mysql_commands_counters        |
    | stats_mysql_connection_pool          |
    | stats_mysql_connection_pool_reset    |
    | stats_mysql_global                   |
    | stats_mysql_prepared_statements_info |
    | stats_mysql_processlist              |
    | stats_mysql_query_digest             |
    | stats_mysql_query_digest_reset       |
    | stats_mysql_query_rules              |
    | stats_mysql_users                    |
    | stats_proxysql_servers_checksums     |
    | stats_proxysql_servers_metrics       |
    | stats_proxysql_servers_status        |
    +--------------------------------------+
    15 rows in set (0.00 sec)
     
    monitor数据库
    存储monitor模块收集的信息,主要是对后端db的健康、延迟检查
    (admin@127.0.0.1) [main]> show tables from monitor;
    +------------------------------------+
    | tables                             |
    +------------------------------------+
    | mysql_server_connect_log           |
    | mysql_server_group_replication_log |
    | mysql_server_ping_log              |
    | mysql_server_read_only_log         |
    | mysql_server_replication_lag_log   |
    +------------------------------------+
    5 rows in set (0.00 sec)
     
    基本配置
    配置有三个层次:runtime<=>memory<=>disk/config file
    RUNTIME:代表proxysql当前生效的正在使用的配置,无法直接修改这里的配置,必须要从下一层load进来
    MEMORY:MEMORY这一层上面连接RUNTIME层,下面连接持久层。在这层可以正常操作proxysql配置,随便修改,不会影响生产环境
    修改一个配置一般都是先在MEMORY层完成,然后确认正常之后再加载到RUNTIME和持久层到磁盘上
    DISK和CONFIG FILE:持久化配置信息,重启后内存中的配置信息会丢失,所以需要将配置信息保留在磁盘中。重启时,可以从磁盘快速加载回来
     
    添加mysql服务器列表
    就是插入数据到mysql_servers
    (admin@127.0.0.1) [main]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.101',3306);
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.102',3306);
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.103',3306);
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> load mysql servers to runtime;
    Query OK, 0 rows affected (0.00 sec)
    (admin@127.0.0.1) [main]> save mysql servers to disk;
    Query OK, 0 rows affected (0.03 sec)
    (admin@127.0.0.1) [main]> select * from main.mysql_servers;
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.1.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.1.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.1.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    3 rows in set (0.00 sec)
    所有操作都要记得load to runtime和save to disk

    在mydb1库中执行
    mysql> create user 'usr_proxysql_mon'@'192.168.1.%' identified by '2wsx3edc';
    mysql> grant all privileges on *.* to 'usr_proxysql_mon'@'192.168.1.%';
    mysql> flush privileges;
     
    添加监控账号
    (admin@127.0.0.1) [main]> set mysql-monitor_username='usr_proxysql_mon';
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> set mysql-monitor_password='2wsx3edc';
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> load mysql variables to runtime;
    Query OK, 0 rows affected (0.00 sec)
    (admin@127.0.0.1) [main]> save mysql variables to disk;
    Query OK, 96 rows affected (0.30 sec)
    (admin@127.0.0.1) [main]> select * from main.global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
    +------------------------+------------------+
    | variable_name          | variable_value   |
    +------------------------+------------------+
    | mysql-monitor_password | 2wsx3edc         |
    | mysql-monitor_username | usr_proxysql_mon |
    +------------------------+------------------+
    2 rows in set (0.01 sec)
    (admin@127.0.0.1) [main]> select * from main.runtime_global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
    +------------------------+------------------+
    | variable_name          | variable_value   |
    +------------------------+------------------+
    | mysql-monitor_password | 2wsx3edc         |
    | mysql-monitor_username | usr_proxysql_mon |
    +------------------------+------------------+
    2 rows in set (0.00 sec)
    (admin@127.0.0.1) [(none)]> select * from monitor.mysql_server_connect_log;
     
    添加复制信息
    (admin@127.0.0.1) [main]> insert into mysql_replication_hostgroups values (1,2,'cluster1');
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> select * from mysql_replication_hostgroups;
    +------------------+------------------+----------+
    | writer_hostgroup | reader_hostgroup | comment  |
    +------------------+------------------+----------+
    | 1                | 2                | cluster1 |
    +------------------+------------------+----------+
    1 row in set (0.00 sec)
    (admin@127.0.0.1) [main]> select * from mysql_servers;
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.1.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.1.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.1.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    3 rows in set (0.01 sec)
    (admin@127.0.0.1) [main]> load mysql servers to runtime;
    Query OK, 0 rows affected (0.01 sec)
    (admin@127.0.0.1) [main]> save mysql servers to disk;
    Query OK, 0 rows affected (0.04 sec)
    (admin@127.0.0.1) [main]>
    (admin@127.0.0.1) [main]> select * from mysql_servers;
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.1.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.1.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.1.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    3 rows in set (0.00 sec)
     
    在mydb1库中执行
    mysql> create user 'root'@'192.168.1.%' identified by '1qaz2wsx';
    mysql> grant all privileges on *.* to 'root'@'192.168.1.%';
    mysql> create user 'msandbox'@'192.168.1.%' identified by '1qaz2wsx';
    mysql> grant all privileges on *.* to 'msandbox'@'192.168.1.%';
    mysql> flush privileges;
    配置对外访问账号
    (admin@127.0.0.1) [main]> select * from mysql_users;
    Empty set (0.00 sec)
    (admin@127.0.0.1) [main]> insert into main.mysql_users(username,password,default_hostgroup,transaction_persistent) values ('root','1qaz2wsx',1,1);
    Query OK, 1 row affected (0.01 sec)
    (admin@127.0.0.1) [main]> insert into main.mysql_users(username,password,default_hostgroup,transaction_persistent) values ('msandbox','1qaz2wsx',1,1);
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> select * from mysql_users;
    +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
    | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
    +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
    | root     | 1qaz2wsx | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
    | msandbox | 1qaz2wsx | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
    +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
    2 rows in set (0.00 sec)
    (admin@127.0.0.1) [main]> load mysql users to runtime;
    Query OK, 0 rows affected (0.00 sec)
    (admin@127.0.0.1) [main]> save mysql users to disk;
    Query OK, 0 rows affected (0.00 sec)
    # mysql -u root -p1qaz2wsx -h 127.0.0.1 -P6033
    # mysql -u msandbox -p1qaz2wsx -h 127.0.0.1 -P6033

    读写分离
    配置读写分离策略需要使用mysql_query_rules表。表中的match_pattern字段就是代表设置的规则,destination_hostgroup字段代表默认指定的分组,apply代表真正执行应用规则
    所有以select开头的语句全部分配到编号为2的读组中
    select for update会产生一个写锁,对数据查询的时效性要求高,把它分配到编号为1的写组里,其他所有操作都会默认路由到写组中
    (admin@127.0.0.1) [main]> select * from mysql_query_rules;
    Empty set (0.00 sec)
    (admin@127.0.0.1) [main]> insert into main.mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',1,1);
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> insert into main.mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',2,1);
    Query OK, 1 row affected (0.00 sec)
    (admin@127.0.0.1) [main]> load mysql query rules to runtime;
    Query OK, 0 rows affected (0.00 sec)
    (admin@127.0.0.1) [main]> save mysql query rules to disk;
    Query OK, 0 rows affected (0.05 sec)
    (admin@127.0.0.1) [(none)]> select * from stats_mysql_query_digest;
    (admin@127.0.0.1) [(none)]> select * from monitor.mysql_server_connect_log;
     
    添加一个myuser:myuser的用户密码对
    admin> select @@admin-admin_credentials;    # 当前用户名和密码
    admin> set admin-admin_credentials='admin:admin;myuser:myuser';
    admin> select @@admin-admin_credentials;
    admin> load admin variables to runtime; 
    admin> save admin variables to disk;

    ----------------------------------------------------------------------------------------------------------------------------------
    通过sqldiff和keepalive及其状态监控机制来实现ProxySQL的故障转移并且在执行vip漂移前自动判断配置文件是否一致并同步
    mydb1和mydb2连通性配置
    ssh-keygen -t rsa
    ssh-copy-id -i .ssh/id_rsa.pub root@192.168.1.101
    ssh-copy-id -i .ssh/id_rsa.pub root@192.168.1.102
     
    增加高可用能力:
    Keepalived主要功能:
    实现IP地址的漂移
    执行健康检查
    [root@mydb1 /]# wget http://www.keepalived.org/software/keepalived-1.4.5.tar.gz
    [root@mydb1 /]# tar xvfz keepalived-1.4.5.tar.gz
    [root@mydb1 /]# cd keepalived-1.4.5
    [root@mydb1 keepalived-1.4.5]# ./configure --prefix=/usr/local/keepalived
    [root@mydb1 keepalived-1.4.5]# make && make install
    复制文件到相关路径,以方便调用
    [root@mydb1 /]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
    [root@mydb1 ~]# vim /etc/init.d/keepalived
    #!/bin/sh
    #
    # Startup script for the Keepalived daemon
    #
    # processname: keepalived
    # pidfile: /var/run/keepalived.pid
    # config: /etc/keepalived/keepalived.conf
    # chkconfig: - 21 79
    # description: Start and stop Keepalived
    # Source function library
    . /etc/rc.d/init.d/functions
    # Source configuration file (we set KEEPALIVED_OPTIONS there)
    . /etc/sysconfig/keepalived
    RETVAL=0
    prog="keepalived"
    start() {
        echo -n $"Starting $prog: "
        daemon keepalived ${KEEPALIVED_OPTIONS}
        RETVAL=$?
        echo
        [ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog
    }
    stop() {
        echo -n $"Stopping $prog: "
        killproc keepalived
        RETVAL=$?
        echo
        [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog
    }
    reload() {
        echo -n $"Reloading $prog: "
        killproc keepalived -1
        RETVAL=$?
        echo
    }
    # See how we were called.
    case "$1" in
        start)
            start
            ;;
        stop)
            stop
            ;;
        reload)
            reload
            ;;
        restart)
            stop
            start
            ;;
        condrestart)
            if [ -f /var/lock/subsys/$prog ]; then
                stop
                start
            fi
            ;;
        status)
            status keepalived
            RETVAL=$?
            ;;
        *)
            echo "Usage: $0 {start|stop|reload|restart|condrestart|status}"
            RETVAL=1
    esac
    exit $RETVAL

    [root@mydb1 ~]# chmod +x /etc/init.d/keepalived
    [root@mydb1 /]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
    默认情况下Keepalived会查找/etc/keepalived/keepalived.conf文件
    [root@mydb1 /]# mkdir -p /etc/keepalived
    [root@mydb1 /]# vim /etc/keepalived/keepalived.conf
    !Configuration File for keepalived
    global_defs {
     notification_email {
      allenhu@sina.com
        }
     notification_email_from allenhu@sina.com
         smtp_server 127.0.0.1
         smtp_connect_timeout 30
         router_id ProxySQL_HA
    }
    vrrp_script check_run {
     script "/usr/local/keepalived/check_proxysql.sh"
     interval 5
    }
    vrrp_instance proxysql {
     state BACKUP
                      nopreempt
     interface ens33
     virtual_router_id 51
     priority 100
     advert_int 1
     authentication {
      auth_type PASS
      auth_pass 3306
            }
     track_script {
      check_run
     }
     virtual_ipaddress {
      192.168.1.205/24
     }
    }
    [root@mydb1 /]# vim /usr/local/keepalived/check_proxysql.sh
    #!/bin/sh
    peer_ip='192.168.1.102'  #另一台ProxySQL的ip
    peer_port=22           #ssh端口
    proxysql='proxysql-1'  #本机的ProxySQL名字(自定义)
    vip='192.168.1.205'      #keepalived的VIP
    adm_email='allenhu@sina.com'
    log=/usr/local/keepalived/proxysql.log
    alias date='date +"%y-%m-%d_%H:%M:%S"'
    #check if this keepalived is MASTER
    ip a|grep $vip
    if [ $? -ne 0 ];then  #如果当前不是主keepalived,则不需要检查
        exit 0
    fi
    #check if data port(6033) is alive
    data_port_stats=$(timeout 2  bash -c 'cat < /dev/null > /dev/tcp/0.0.0.0/6033' &> /dev/null;echo $?)
    if [ $data_port_stats -eq 0 ];then
        exit 0
    else
        #check if the other keepalived is running
        peer_keepalived=$(ssh -p$peer_port $peer_ip 'systemctl is-active keepalived.service')
        if [ "$peer_keepalived" != "active" ];then
            echo "`date`  data port of $proxysql is not available, but the BACKUP keepalived is not running, so can't do the failover" >> $log
            echo "$proxysql is not available, but the BACKUP keepalived is not running. So please check immediately!
                 "|/bin/mailx -S "from=proxysql@xxx.com <DoNotReply>" -s "proxysql error and can't failover" $adm_email
        else
            echo "`date`  data port of proxysql is not available, now SHUTDOWN keepalived." >> $log
            systemctl stop keepalived.service
        fi
    fi
    [root@mydb1 /]# vim /usr/local/keepalived/notify_master_proxysql.sh
    #!/bin/sh
    alias date='date +"%y-%m-%d_%H:%M:%S"'
    peer_ip='192.168.1.102'
    peer_port=22
    proxysql='proxysql-1'
    adm_email='allenhu@sina.com'
    log=/usr/local/keepalived/proxysql.log
    #when keepalived change to MASTER,first we should confirm the proxysql's config on this server is same with the previous proxysql
    #thus we need no to deal with the proxysql's config between this two proxysql
    rsync -a -e "ssh -p$peer_port" $peer_ip:/var/lib/proxysql/proxysql.db /tmp/proxysql.db
    db_diff=$(/usr/local/sqlite-tools/sqldiff /var/lib/proxysql/proxysql.db /tmp/proxysql.db)
    if [ $? -eq 0 ] && [ ! -z "$db_diff" ];then
        #sqldiff compare two sqlite file normally
        rsync -a /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql.db_bak
        rsync -a /tmp/proxysql.db /var/lib/proxysql/proxysql.db
        echo "`date`  local config is different from the previous MASTER proxysql, already sync" >> $log
    elif [ $? -ne 0 ];then
        #sqldiff cat't compare two sqlite file normally
        echo "`date`  can't compare config with previous MASTER proxysql, directly use local config" >> $log
    fi
    systemctl restart proxysql.service
    if [ $? -eq 0 ];then
        echo "`date`  restart proxysql successful" >> $log
        add_msg="everything is ok now"
    else
        echo "`date`  restart proxysql failed" >> $log
        add_msg="but can't restart proxysql, please check immediately!"
    fi
    echo "$proxysql's keepalived now change to MASTER, $add_msg"|/bin/mailx -S "from=proxysql@xxx.com <DoNotReply>" -s "$proxysql change to MASTER" $adm_email

    [root@mydb1 /]# chmod +x /usr/local/keepalived/check_proxysql.sh
    [root@mydb1 /]# chmod +x /usr/local/keepalived/notify_master_proxysql.sh
    [root@mydb1 /]# /etc/init.d/keepalived start
    [root@mydbl1 ~]# tail -100f /var/log/messages
    Oct 14 00:21:38 mydb1 systemd: Started LVS and VRRP High Availability Monitor.
    Oct 14 00:21:38 mydb1 Keepalived_healthcheckers[46918]: Opening file '/etc/keepalived/keepalived.conf'.
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: Registering Kernel netlink reflector
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: Registering Kernel netlink command channel
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: Registering gratuitous ARP shared channel
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: Opening file '/etc/keepalived/keepalived.conf'.
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: WARNING - default user 'keepalived_script' for script execution does not exist - please create.
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) removing protocol VIPs.
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: Using LinkWatch kernel netlink reflector...
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) Entering BACKUP STATE
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
    Oct 14 00:21:38 mydb1 Keepalived_vrrp[46919]: VRRP_Script(check_run) succeeded
    Oct 14 00:21:41 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) Transition to MASTER STATE
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) Entering MASTER STATE
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) setting protocol VIPs.
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) Sending/queueing gratuitous ARPs on ens33 for 192.168.1.205
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:42 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:42 mydb1 avahi-daemon[804]: Registering new address record for 192.168.1.205 on ens33.IPv4.
    Oct 14 00:21:42 mydb1 systemd: Stopping LSB: High Performance Advanced Proxy for MySQL...
    Oct 14 00:21:43 mydb1 proxysql: Shutting down ProxySQL: DONE!
    Oct 14 00:21:43 mydb1 systemd: Starting LSB: High Performance Advanced Proxy for MySQL...
    Oct 14 00:21:43 mydb1 proxysql: Starting ProxySQL: 2018-10-14 00:21:43 [INFO] Using config file /etc/proxysql.cnf
    Oct 14 00:21:43 mydb1 proxysql: DONE!
    Oct 14 00:21:43 mydb1 systemd: Started LSB: High Performance Advanced Proxy for MySQL.
    Oct 14 00:21:47 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:47 mydb1 Keepalived_vrrp[46919]: VRRP_Instance(proxysql) Sending/queueing gratuitous ARPs on ens33 for 192.168.1.205
    Oct 14 00:21:47 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:47 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:47 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
    Oct 14 00:21:47 mydb1 Keepalived_vrrp[46919]: Sending gratuitous ARP on ens33 for 192.168.1.205
     

    [root@mydb2 /]# wget http://www.keepalived.org/software/keepalived-1.4.5.tar.gz
    [root@mydb2 /]# tar xvfz keepalived-1.4.5.tar.gz
    [root@mydb2 /]# cd keepalived-1.4.5
    [root@mydb2 keepalived-1.4.5]# ./configure --prefix=/usr/local/keepalived
    [root@mydb2 keepalived-1.4.5]# make && make install
    复制文件到相关路径,以方便调用
    [root@mydb2 /]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
    [root@mydb2 ~]# vim /etc/init.d/keepalived
    #!/bin/sh
    #
    # Startup script for the Keepalived daemon
    #
    # processname: keepalived
    # pidfile: /var/run/keepalived.pid
    # config: /etc/keepalived/keepalived.conf
    # chkconfig: - 21 79
    # description: Start and stop Keepalived
    # Source function library
    . /etc/rc.d/init.d/functions
    # Source configuration file (we set KEEPALIVED_OPTIONS there)
    . /etc/sysconfig/keepalived
    RETVAL=0
    prog="keepalived"
    start() {
        echo -n $"Starting $prog: "
        daemon keepalived ${KEEPALIVED_OPTIONS}
        RETVAL=$?
        echo
        [ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog
    }
    stop() {
        echo -n $"Stopping $prog: "
        killproc keepalived
        RETVAL=$?
        echo
        [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog
    }
    reload() {
        echo -n $"Reloading $prog: "
        killproc keepalived -1
        RETVAL=$?
        echo
    }
    # See how we were called.
    case "$1" in
        start)
            start
            ;;
        stop)
            stop
            ;;
        reload)
            reload
            ;;
        restart)
            stop
            start
            ;;
        condrestart)
            if [ -f /var/lock/subsys/$prog ]; then
                stop
                start
            fi
            ;;
        status)
            status keepalived
            RETVAL=$?
            ;;
        *)
            echo "Usage: $0 {start|stop|reload|restart|condrestart|status}"
            RETVAL=1
    esac
    exit $RETVAL

    [root@mydb2 ~]# chmod +x /etc/init.d/keepalived
    [root@mydb2 /]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
    默认情况下Keepalived会查找/etc/keepalived/keepalived.conf文件
    [root@mydb2 /]# mkdir -p /etc/keepalived
    [root@mydb2 /]# vim /etc/keepalived/keepalived.conf
    !Configuration File for keepalived
    global_defs {
     notification_email {
      allenhu@sina.com
        }
     notification_email_from allenhu@sina.com
         smtp_server 127.0.0.1
         smtp_connect_timeout 30
         router_id ProxySQL_HA
    }
    vrrp_script check_run {
     script "/usr/local/keepalived/check_proxysql.sh"
     interval 5
    }
    vrrp_instance proxysql {
     state BACKUP
                      nopreempt
     interface ens33
     virtual_router_id 51
     priority 100
     advert_int 1
     authentication {
      auth_type PASS
      auth_pass 3306
            }
     track_script {
      check_run
     }
     virtual_ipaddress {
      192.168.1.205/24
     }
    }
    [root@mydb2 /]# vim /usr/local/keepalived/check_proxysql.sh
    #!/bin/sh
    peer_ip='192.168.1.101'  #另一台ProxySQL的ip
    peer_port=22           #ssh端口
    proxysql='proxysql-2'  #本机的ProxySQL名字(自定义)
    vip='192.168.1.205'      #keepalived的VIP
    adm_email='allenhu@sina.com'
    log=/usr/local/keepalived/proxysql.log
    alias date='date +"%y-%m-%d_%H:%M:%S"'
    #check if this keepalived is MASTER
    ip a|grep $vip
    if [ $? -ne 0 ];then  #如果当前不是主keepalived,则不需要检查
        exit 0
    fi
    #check if data port(6033) is alive
    data_port_stats=$(timeout 2  bash -c 'cat < /dev/null > /dev/tcp/0.0.0.0/6033' &> /dev/null;echo $?)
    if [ $data_port_stats -eq 0 ];then
        exit 0
    else
        #check if the other keepalived is running
        peer_keepalived=$(ssh -p$peer_port $peer_ip 'systemctl is-active keepalived.service')
        if [ "$peer_keepalived" != "active" ];then
            echo "`date`  data port of $proxysql is not available, but the BACKUP keepalived is not running, so can't do the failover" >> $log
            echo "$proxysql is not available, but the BACKUP keepalived is not running. So please check immediately!
                 "|/bin/mailx -S "from=proxysql@xxx.com <DoNotReply>" -s "proxysql error and can't failover" $adm_email
        else
            echo "`date`  data port of proxysql is not available, now SHUTDOWN keepalived." >> $log
            systemctl stop keepalived.service
        fi
    fi
    [root@mydb2 /]# vim /usr/local/keepalived/notify_master_proxysql.sh
    #!/bin/sh
    alias date='date +"%y-%m-%d_%H:%M:%S"'
    peer_ip='192.168.1.101'
    peer_port=22
    proxysql='proxysql-2'
    adm_email='allenhu@sina.com'
    log=/usr/local/keepalived/proxysql.log
    #when keepalived change to MASTER,first we should confirm the proxysql's config on this server is same with the previous proxysql
    #thus we need no to deal with the proxysql's config between this two proxysql
    rsync -a -e "ssh -p$peer_port" $peer_ip:/var/lib/proxysql/proxysql.db /tmp/proxysql.db
    db_diff=$(/usr/local/sqlite-tools/sqldiff /var/lib/proxysql/proxysql.db /tmp/proxysql.db)
    if [ $? -eq 0 ] && [ ! -z "$db_diff" ];then
        #sqldiff compare two sqlite file normally
        rsync -a /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql.db_bak
        rsync -a /tmp/proxysql.db /var/lib/proxysql/proxysql.db
        echo "`date`  local config is different from the previous MASTER proxysql, already sync" >> $log
    elif [ $? -ne 0 ];then
        #sqldiff cat't compare two sqlite file normally
        echo "`date`  can't compare config with previous MASTER proxysql, directly use local config" >> $log
    fi
    systemctl restart proxysql.service
    if [ $? -eq 0 ];then
        echo "`date`  restart proxysql successful" >> $log
        add_msg="everything is ok now"
    else
        echo "`date`  restart proxysql failed" >> $log
        add_msg="but can't restart proxysql, please check immediately!"
    fi
    echo "$proxysql's keepalived now change to MASTER, $add_msg"|/bin/mailx -S "from=proxysql@xxx.com <DoNotReply>" -s "$proxysql change to MASTER" $adm_email

    [root@mydb2 /]# chmod +x /usr/local/keepalived/check_proxysql.sh
    [root@mydb2 /]# chmod +x /usr/local/keepalived/notify_master_proxysql.sh
    [root@mydb2 /]# /etc/init.d/keepalived start
    [root@mydbl2 ~]# tail -100f /var/log/messages
    Oct 14 00:36:30 mydb2 systemd: Starting LVS and VRRP High Availability Monitor...
    Oct 14 00:36:30 mydb2 Keepalived[9349]: Starting Keepalived v1.4.5 (05/26,2018)
    Oct 14 00:36:30 mydb2 Keepalived[9349]: Running on Linux 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 (built for Linux 3.10.0)
    Oct 14 00:36:30 mydb2 Keepalived[9349]: Opening file '/etc/keepalived/keepalived.conf'.
    Oct 14 00:36:30 mydb2 systemd: PID file /var/run/keepalived.pid not readable (yet?) after start.
    Oct 14 00:36:30 mydb2 Keepalived[9350]: Starting Healthcheck child process, pid=9351
    Oct 14 00:36:30 mydb2 Keepalived[9350]: Starting VRRP child process, pid=9352
    Oct 14 00:36:30 mydb2 systemd: Started LVS and VRRP High Availability Monitor.
    Oct 14 00:36:30 mydb2 Keepalived_healthcheckers[9351]: Opening file '/etc/keepalived/keepalived.conf'.
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: Registering Kernel netlink reflector
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: Registering Kernel netlink command channel
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: Registering gratuitous ARP shared channel
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: Opening file '/etc/keepalived/keepalived.conf'.
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: WARNING - default user 'keepalived_script' for script execution does not exist - please create.
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: VRRP_Instance(proxysql) removing protocol VIPs.
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: Using LinkWatch kernel netlink reflector...
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: VRRP_Instance(proxysql) Entering BACKUP STATE
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
    Oct 14 00:36:30 mydb2 Keepalived_vrrp[9352]: VRRP_Script(check_run) succeeded
     
    ProxySQL故障转移测试:
    通过查看/var/log/messges日志,看出主备切换过程
    在192.168.1.101上关闭ProxySQL服务,看VIP是否会切换到192.168.1.102上
    开启192.168.1.101上的MySQL和keepalived,然后关闭192.168.1.102上的MySQL,看VIP是否会切换到192.168.1.101上
     

    转载于:https://www.cnblogs.com/allenhu320/p/11338860.html

    展开全文
  • proxysql-basics-master-slave:为MasterSlave拓扑设置ProxySQL的基本教程
  • proxysql集合包.zip

    2020-07-22 08:53:58
    本集合包专门用来proxysql代理mysql组复制集群实验中药用到的各种资源! 实验目的“ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“”
  • proxysql配置

    2020-12-04 14:39:17
    安装proxysql 启动proxysql服务 配置proxysql所需要的账户 在 主库(192.168.0.21) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户 监控用户 create user 'monitor'@'192.168.0.%' identified by '123456...

     初试读写分离

     

    向ProxySQL中添加MySQL节点

    查看这2个节点是否插入成功,以及它们的状态。请认真读一读每个字段的名称,混个眼熟。

    admin> select * from mysql_servers\G;
    *************************** 1. row ***************************
           hostgroup_id: 10
               hostname: 192.168.0.21
                   port: 3306
              gtid_port: 0
                 status: ONLINE
                 weight: 1
            compression: 0
        max_connections: 1000
    max_replication_lag: 0
                use_ssl: 0
         max_latency_ms: 0
                comment: 
    *************************** 2. row ***************************
           hostgroup_id: 20
               hostname: 192.168.0.22
                   port: 3306
              gtid_port: 0
                 status: ONLINE
                 weight: 1
            compression: 0
        max_connections: 1000
    max_replication_lag: 0
                use_ssl: 0
         max_latency_ms: 0
                comment: 
    2 rows in set (0.001 sec)
    
    ERROR: No query specified

     监控后端MySQL节点

    添加节点之后,还需要监控后端节点。对于后端是主从复制的环境来说,这是必须的,因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。

    首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可,因为会复制到slave上),这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(先混个眼熟:这个俗语叫做"拖后腿",术语叫做"replication lag"),则还需具备replication client权限。这里直接赋予这个权限。

    # 在master上执行:
    mysql> create user monitor@'192.168.0.%' identified by 'P@ssword1!';
    mysql> grant replication client on *.* to monitor@'192.168.100.%';

    然后回到ProxySQL上配置监控。

    set mysql-monitor_username='monitor';
    set mysql-monitor_password='P@ssword1!'; 

     以上设置实际上是在修改global_variables表,它和下面两个语句是等价的:

    PDATE global_variables SET variable_value='monitor'
     WHERE variable_name='mysql-monitor_username';
    
    UPDATE global_variables SET variable_value='P@ssword1!'
     WHERE variable_name='mysql-monitor_password';

    修改后,加载到RUNTIME,并保存到disk。

    load mysql variables to runtime;
    save mysql variables to disk;

     

    验证监控结果:ProxySQL监控模块的指标都保存在monitor库的log表中。

    以下是连接是否正常的监控(对connect指标的监控):(在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常)

    admin> select * from mysql_server_connect_log;
    +--------------+------+------------------+-------------------------+---------------+
    | hostname     | port | time_start_us    | connect_success_time_us | connect_error |
    +--------------+------+------------------+-------------------------+---------------+
    | 192.168.0.22 | 3306 | 1607137646702293 | 2245                    | NULL          |
    | 192.168.0.21 | 3306 | 1607137647853840 | 1188                    | NULL          |
    | 192.168.0.21 | 3306 | 1607137706703023 | 1178                    | NULL          |
    | 192.168.0.22 | 3306 | 1607137707450275 | 2241                    | NULL          |
    | 192.168.0.22 | 3306 | 1607137766703698 | 1497                    | NULL          |
    | 192.168.0.21 | 3306 | 1607137767862820 | 1236                    | NULL          |
    | 192.168.0.21 | 3306 | 1607137826704554 | 1458                    | NULL          |
    +--------------+------+------------------+-------------------------+---------------+
    20 rows in set (0.001 sec)

    以下是对心跳信息的监控(对ping指标的监控):

    admin> select * from mysql_server_ping_log;
    +--------------+------+------------------+----------------------+------------+
    | hostname     | port | time_start_us    | ping_success_time_us | ping_error |
    +--------------+------+------------------+----------------------+------------+
    | 192.168.0.21 | 3306 | 1607137687030736 | 406                  | NULL       |
    | 192.168.0.22 | 3306 | 1607137687164044 | 827                  | NULL       |
    | 192.168.0.21 | 3306 | 1607137697031017 | 137                  | NULL       |
    | 192.168.0.22 | 3306 | 1607137697225592 | 761                  | NULL       |
    | 192.168.0.21 | 3306 | 1607137707031923 | 414                  | NULL       |
    | 192.168.0.22 | 3306 | 1607137707146555 | 752                  | NULL       |
    | 192.168.0.22 | 3306 | 1607137717032382 | 685                  | NULL       |
    | 192.168.0.21 | 3306 | 1607137717223605 | 379                  | NULL       |
    | 192.168.0.21 | 3306 | 1607137727033259 | 439                  | NULL       |
    | 192.168.0.22 | 3306 | 1607137727207258 | 295                  | NULL       |
    +--------------+------+------------------+----------------------+------------+
    120 rows in set (0.002 sec)
    

     指定写组的id为10,读组的id为20。

    insert into mysql_replication_hostgroups values(10,20);

    一加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。 

    admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; 
    +--------------+--------------+------+--------+--------+
    | hostgroup_id | hostname     | port | status | weight |
    +--------------+--------------+------+--------+--------+
    | 10           | 192.168.0.21 | 3306 | ONLINE | 1      |
    | 20           | 192.168.0.22 | 3306 | ONLINE | 1      |
    +--------------+--------------+------+--------+--------+
    2 rows in set (0.001 sec)

    配置mysql_users 

    上面的所有配置都是关于后端MySQL节点的,现在可以配置关于SQL语句的,包括:发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。

    本小节是SQL请求所使用的用户配置,例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。

     

    ProxySQL的Admin管理接口

    • (1).admin管理接口,默认端口为6032。该端口用于查看、配置ProxySQL。
    • (2).接收SQL语句的接口,默认端口为6033,这个接口类似于MySQL的3306端口。

    例如,使用mysql客户端去连接ProxySQL的管理接口。下面使用的是管理接口的默认用户名和密码admin:admin

    [root@localhost ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt 'admin> '
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1155
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    admin> 

    由于ProxySQL的配置全部保存在几个自带的库中,所以通过管理接口,可以非常方便地通过发送一些SQL命令去修改ProxySQL的配置。ProxySQL会解析通过该接口发送的某些对ProxySQL有效的特定命令,并将其合理转换后发送给内嵌的SQLite3数据库引擎去运行。

    admin> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | disk          | /var/lib/proxysql/proxysql.db       |
    | 3   | stats         |                                     |
    | 4   | monitor       |                                     |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.001 sec)
    
    admin> show tables from disk;
    +------------------------------------+
    | tables                             |
    +------------------------------------+
    | global_variables                   |
    | mysql_aws_aurora_hostgroups        |
    | mysql_collations                   |
    | mysql_galera_hostgroups            |
    | mysql_group_replication_hostgroups |
    | mysql_query_rules                  |
    | mysql_query_rules_fast_routing     |
    | mysql_replication_hostgroups       |
    | mysql_servers                      |
    | mysql_users                        |
    | proxysql_servers                   |
    | scheduler                          |
    +------------------------------------+
    12 rows in set (0.001 sec)
    

     请注意,SQLite3和MySQL使用的SQL语法有所不同,因此能在MySQL上正常运行的命令不一定能在SQLite3上运行。例如,尽管admin管理接口接收USE命令,但不会切换默认的schema,因为SQLite3不支持USE功能。
    ProxySQL会尽量将MySQL语法自动调整为对应的SQLite3的语法,例如show databases;会转换为SQLite3上等价的语句。

    ProxySQL的配置几乎都是通过管理接口来操作的,通过Admin管理接口,可以在线修改几乎所有的配置并使其生效。只有两个变量的配置是必须重启ProxySQL才能生效的,它们是:mysql-threadsmysql-stacksize

    和admin管理接口相关的变量

    一般来说,admin接口基本不需要额外的配置,最有可能配置的是admin接口的管理员用户名、密码。

    admin-admin_credentials

    该变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin,但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL,例如用windows上的navicat连接Linux上的ProxySQL管理接口,必须自定义一个管理员账户。

    例如,添加一个myuser:myuser的用户密码对。

    admin> select @@admin-admin_credentials;    # 当前用户名和密码
    +---------------------------+
    | @@admin-admin_credentials |
    +---------------------------+
    | admin:admin               |
    +---------------------------+
    
    admin> set admin-admin_credentials='admin:admin;myuser:myuser';
    
    admin> select @@admin-admin_credentials;
    +---------------------------+
    | @@admin-admin_credentials |
    +---------------------------+
    | admin:admin;myuser:myuser |
    +---------------------------+
    
    admin> load admin variables to runtime;  # 使修改立即生效
    admin> save admin variables to disk;     # 使修改永久保存到磁盘

     

    修改后,就可以使用该用户名和密码连接管理接口。

    [root@localhost ~]# mysql -umyuser -pmyuser -h 127.0.0.1 -P 6032 --prompt 'admin> '

    所有的配置操作都是在修改main库中对应的表。

    admin> select * from global_variables
        -> where variable_name='admin-admin_credentials';
    +-------------------------+---------------------------+
    | variable_name           | variable_value            |
    +-------------------------+---------------------------+
    | admin-admin_credentials | admin:admin;myuser:myuser |
    +-------------------------+---------------------------+
    1 row in set (0.005 sec)

     所以,前面的set语句和下面的update语句是等价的:

    admin> update global_variables set variable_value=
        -> 'admin:admin;myuser:myuser' where variable_name='admin-admin_credentials';

     

    必须要区分admin管理接口的用户名和mysql_users中的用户名。

    • admin管理接口的用户是连接到管理接口(默认端口6032)上用来管理、配置ProxySQL的。
    • mysql_users表中的用户名是应用程序连接ProxySQL(默认端口6033),以及ProxySQL连接后端MySQL Servers使用的用户。它的作用是发送、路由SQL语句,类似于MySQL Server的3306端口。所以,这个表中的用户必须已经在后端MySQL Server上存在且授权了。

    目前ProxySQL的mysql_users中的用户既负责ProxySQL前端,又负责ProxySQL后端的连接,(作者说)未来版本中可能会将这两段连接的用户系统分开,让ProxySQL更加安全。

    admin管理接口的用户必须不能存在于mysql_users中,这是出于安全的考虑,防止通过admin管理接口用户猜出mysql_users中的用户。

    admin-stats_credentials

    该变量控制admin管理接口的普通用户,这个变量中的用户没有超级管理员权限,只能查看monitor库和main库中关于统计的数据,其它库都是不可见的,且没有任何写权限。

    默认的普通用户名和密码为stats:stats

    admin> select @@admin-stats_credentials;
    +---------------------------+
    | @@admin-stats_credentials |
    +---------------------------+
    | stats:stats               |
    +---------------------------+
    1 row in set (0.001 sec)
    
    admin> set admin-stats_credentials='stats:stats;mystqts:mystats';
    Query OK, 1 row affected (0.001 sec)
    
    admin> Ctrl-C -- exit!
    Aborted
    [root@localhost ~]# mysql -ustats -pstats -P6032 -h127.0.0.1
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2295
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | monitor       |                                     |
    | 3   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    3 rows in set (0.001 sec)
    
    MySQL [(none)]> show tables from main;
    +--------------------------------------+
    | tables                               |
    +--------------------------------------+
    | global_variables                     |
    | stats_memory_metrics                 |
    | stats_mysql_commands_counters        |
    | stats_mysql_connection_pool          |
    | stats_mysql_connection_pool_reset    |
    | stats_mysql_errors                   |
    | stats_mysql_errors_reset             |
    | stats_mysql_free_connections         |
    | stats_mysql_global                   |
    | stats_mysql_gtid_executed            |
    | stats_mysql_prepared_statements_info |
    | stats_mysql_processlist              |
    | stats_mysql_query_digest             |
    | stats_mysql_query_digest_reset       |
    | stats_mysql_query_rules              |
    | stats_mysql_users                    |
    | stats_proxysql_servers_checksums     |
    | stats_proxysql_servers_metrics       |
    | stats_proxysql_servers_status        |
    +--------------------------------------+
    19 rows in set (0.001 sec))

     同样,这个变量中的用户必须不能存在于mysql_users表中。

    admin-mysql_ifaces

    该变量指定admin接口的监听地址,格式为分号分隔的hostname:port列表。默认监听在0.0.0.0:6032

    注意,允许使用UNIX的domain socket进行监听,这样本主机内的应用程序就可以直接被处理。

    例如:

    SET admin-mysql_ifaces='127.0.0.1:6032;/tmp/proxysql_admin.sock'

    proxysql多层配置系统

    1.ProxySQL中的库

    使用ProxySQL的Admin管理接口连上ProxySQL,可查看ProxySQL拥有的库。

    [root@localhost ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt 'admin> '
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2465
    Server version: 5.5.30 (ProxySQL Admin Module)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    admin> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | disk          | /var/lib/proxysql/proxysql.db       |
    | 3   | stats         |                                     |
    | 4   | monitor       |                                     |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.001 sec)
    

    其中:

    • main库是ProxySQL最主要的库,是需要修改配置时使用的库,它其实是一个内存数据库系统。所以,修改main库中的配置后,必须将其持久化到disk上才能永久保存。
    • disk库是磁盘数据库,该数据库结构和内存数据库完全一致。当持久化内存数据库中的配置时,其实就是写入到disk库中。磁盘数据库的默认路径为$DATADIR/proxysql.db
    • stats库是统计信息库。这个库中的数据一般是在检索其内数据时临时填充的,它保存在内存中。因为没有相关的配置项,所以无需持久化。
    • monitor库是监控后端MySQL节点相关的库,该库中只有几个log类的表,监控模块收集到的监控信息全都存放到对应的log表中。
    • stats_history库是1.4.4版新增的库,用于存放历史统计数据。默认路径为$DATADIR/proxysql_stats.db

    ProxySQL内部使用的是SQLite3数据库,无论是内存数据库还是磁盘数据库,都是通过SQLite3引擎进行解析、操作的。它和MySQL的语法可能稍有不同,但ProxySQL会对不兼容的语法自动进行调整,最大程度上保证MySQL语句的有效率。

    上面描述main库的时候,只是说了内存数据库需要持久化到disk库才能永久保存配置。但实际上,修改了main库中的配置后,并不会立即生效,它还需要load到runtime的数据结构中才生效,只有在runtime数据结构中的配置才是对ProxySQL当前有效的配置。请看下面的"多层配置系统"。

    ProxySQL的多层配置系统

          +-------------------------+
           |         RUNTIME         |
           +-------------------------+
                  /|\          |
                   |           |
               [1] |       [2] |
                   |          \|/
           +-------------------------+
           |         MEMORY          |
           +-------------------------+ _
                  /|\          |      |\
                   |           |        \
               [3] |       [4] |         \ [5]
                   |          \|/         \
           +-------------------------+  +---------------+
           |          DISK           |  |  CONFIG FILE  |
           +-------------------------+  +---------------+

    解释下这个3层的配置系统。

    最底层的是disk库和config file。这里需要注意,这里的config file就是传统的配置文件,默认为/etc/proxysql.cnf,ProxySQL启动时,主要是从disk库中读取配置加载到内存并最终加载到runtime生效,只有极少的几个特定配置内容是从config file中加载的,除非是第一次初始化ProxySQL运行环境(或者disk库为空)。

    中间层的是memory,表示的是内存数据库,其实就是main库。通过管理接口修改的所有配置,都保存在内存数据库(main)中。当ProxySQL重启或者崩溃时,这个内存数据库中的数据会丢失,所以需要save到disk库中。

    最上层的是runtime,它是ProxySQL有关线程运行时读取的数据结构。换句话说,该数据结构中的配置都是已生效的配置。所以,修改了main库中的配置后,必须load到runtime数据结构中才能使其生效。

    admin> show tables from disk;
    +------------------------------------+
    | tables                             |
    +------------------------------------+
    | global_variables                   |
    | mysql_aws_aurora_hostgroups        |
    | mysql_collations                   |
    | mysql_galera_hostgroups            |
    | mysql_group_replication_hostgroups |
    | mysql_query_rules                  |
    | mysql_query_rules_fast_routing     |
    | mysql_replication_hostgroups       |
    | mysql_servers                      |
    | mysql_users                        |
    | proxysql_servers                   |
    | scheduler                          |
    +------------------------------------+
    12 rows in set (0.001 sec)

    以下是几个示例:注意,几乎所有配置都是在admin管理接口上修改的,这也是建议的配置方式。

    (1).向ProxySQL的mysql_servers表中添加了一个后端节点。

    load mysql servers to runtime;    # 加载到runtime使该节点的配置生效
    save mysql servers to disk;     # 将该节点的配置持久化到磁盘数据库中
    

    上面两句和下面两句是等价的,只是操作方向不同(还使用了缩写):

    load mysql servers from mem;
    save mysql servers from mem;
    

    (2).向ProxySQL的mysql_users表中添加了用于发送、处理SQL语句的用户。

    load mysql users to runtime;
    save mysql users to disk;
    

    (3).修改了以admin-开头的变量。

    load admin variables to runtime;
    save admin variables to disk;

     启动ProxySQL时如何加载配置

    如果ProxySQL是刚安装的,或者磁盘数据库文件为空(甚至不存在),或者启动ProxySQL时使用了选项--initial,这几种情况启动ProxySQL时,都会从传统配置文件config file中读取配置加载到内存数据库,并自动load到runtime数据结构、save到磁盘数据库,这是初始化ProxySQL运行环境的过程。

    如果不是第一次启动ProxySQL,由于已经存在磁盘数据库文件,这时ProxySQL会从磁盘数据库中读取几乎所有的配置(即使传统配置文件中配置了某项,也不会去解析),但有3项是必须从传统配置文件中读取的。这3项是:

    • datadir:ProxySQL启动时,必须从配置文件中确定它的数据目录,因为磁盘数据库文件、日志以及其它一些文件是存放在数据目录下的。如果使用/etc/init.d/proxysql管理ProxySQL,则除了修改/etc/proxysql.cnf的datadir,还需要修改该脚本中的datadir。
    • restart_on_missing_heartbeats:MySQL线程丢失多少次心跳,就会杀掉这个线程并重启它。默认值为10。
    • execute_on_exit_failure:如果设置了该变量,ProxySQL父进程将在每次ProxySQL崩溃的时候执行已经定义好的脚本。建议使用它来生成一些崩溃时的警告和日志。注意,ProxySQL的重启速度可能只有几毫秒,因此很多其它的监控工具可能无法探测到ProxySQL的一次普通故障,此时可使用该变量。

    关于传统配置文件

    传统配置文件默认路径为/etc/proxysql.cnf,也可以在二进制程序proxysql上使用-c--config来手动指定配置文件。

    ProxySQL的传统配置文件示例如下:浏览下即可,几乎不需要手动去配置proxysql.cnf。

     

     

     

    安装proxysql

    启动proxysql服务

     

    配置proxysql所需要的账户

    在 主库(192.168.0.21) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户

    监控用户

    create user 'monitor'@'192.168.0.%' identified by '123456';
    grant all privileges on *.* to 'monitor'@'192.168.0.%' with grant option;
    

     对外访问账户

    create user 'proxysql'@'192.168.0.%' identified by '123456';
    grant all privileges on *.* to 'proxysql'@'192.168.0.%' with grant option;

    刷新权限

    flush privileges;

    配置 ProxySQL 主从分组信息

         writer_hostgroup 和reader_hostgroup 写组和读组都要大于0且不能相同,我的环境下,写组定义与10,读组定义为20

    MySQL [disk]> show create table mysql_replication_hostgroups\G;
    *************************** 1. row ***************************
           table: mysql_replication_hostgroups
    Create Table: CREATE TABLE mysql_replication_hostgroups (
        writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
        reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
        check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
        comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
    1 row in set (0.001 sec)

    创建组

    insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');
    load mysql servers to runtime;
    save mysql servers to disk;

     查看组

    MySQL [disk]> select * from main.runtime_mysql_replication_hostgroups;
    +------------------+------------------+------------+---------+
    | writer_hostgroup | reader_hostgroup | check_type | comment |
    +------------------+------------------+------------+---------+
    | 10               | 20               | read_only  | proxy   |
    +------------------+------------------+------------+---------+
    1 row in set (0.007 sec)
    

     ProxySQL 会根据server 的read _only 的取值将服务器进行分组。 read_only=0 的server,master被分到编号为10的写组,read_only=1 的server,slave则被分到编号20的读组

    添加主从服务器节点

    用到mysql_servers 这个表

    MySQL [main]> show create table mysql_servers\G;
    *************************** 1. row ***************************
           table: mysql_servers
    Create Table: CREATE TABLE mysql_servers (
        hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
        hostname VARCHAR NOT NULL,
        port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
        gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
        status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
        weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
        compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
        max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
        max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
        use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
        max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
        comment VARCHAR NOT NULL DEFAULT '',
        PRIMARY KEY (hostgroup_id, hostname, port) )
    1 row in set (0.001 sec)
    

     添加后端服务节点

     insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.0.21',3306);
     insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.0.22',3306);
     load mysql servers to runtime;
     save mysql servers to disk;

     查询后端服务节目状态

    MySQL [main]> 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.0.21 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 20           | 192.168.0.22 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    2 rows in set (0.001 sec)

     ProxySQL监控 MySQL 后端节点

    MySQL [monitor]> set mysql-monitor_username='monitor';
    Query OK, 1 row affected (0.001 sec)
    
    MySQL [monitor]> set mysql-monitor_password='123456';
    Query OK, 1 row affected (0.000 sec)
    
    MySQL [monitor]> load mysql variables to runtime;
    
    MySQL [monitor]> save mysql variables to disk;

    验证监控信息,ProxySQL 监控模块的指标都保存在monitor库的log表中 
    以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常

    MySQL [(none)]> select * from monitor.mysql_server_connect_log;
    +--------------+------+------------------+-------------------------+---------------+
    | hostname     | port | time_start_us    | connect_success_time_us | connect_error |
    +--------------+------+------------------+-------------------------+---------------+
    | 192.168.0.21 | 3306 | 1607033681991324 | 1380                    | NULL          |
    | 192.168.0.22 | 3306 | 1607033682667369 | 2617                    | NULL          |
    | 192.168.0.22 | 3306 | 1607033741992060 | 2545                    | NULL          |
    | 192.168.0.21 | 3306 | 1607033742611537 | 1407                    | NULL          |
    | 192.168.0.21 | 3306 | 1607033801993790 | 1182                    | NULL          |
    | 192.168.0.22 | 3306 | 1607033803169030 | 2597                    | NULL          |
    | 192.168.0.22 | 3306 | 1607033861997550 | 2734                    | NULL          |
    | 192.168.0.21 | 3306 | 1607033862757922 | 1334                    | NULL          |
    | 192.168.0.22 | 3306 | 1607033921997862 | 1573                    | NULL          |
    | 192.168.0.21 | 3306 | 1607033923098728 | 1315                    | NULL          |
    | 192.168.0.22 | 3306 | 1607033981998827 | 3992                    | NULL          |
    | 192.168.0.21 | 3306 | 1607033982703823 | 1396                    | NULL          |
    | 192.168.0.22 | 3306 | 1607034041999376 | 2652                    | NULL          |
    | 192.168.0.21 | 3306 | 1607034043000317 | 1222                    | NULL          |
    | 192.168.0.21 | 3306 | 1607034102000061 | 1407                    | NULL          |
    | 192.168.0.22 | 3306 | 1607034102991168 | 2688                    | NULL          |
    | 192.168.0.22 | 3306 | 1607034107992016 | 1130                    | NULL          |
    | 192.168.0.21 | 3306 | 1607034109188560 | 1493                    | NULL          |
    | 192.168.0.22 | 3306 | 1607034130967707 | 2572                    | NULL          |
    | 192.168.0.21 | 3306 | 1607034131697356 | 1379                    | NULL          |
    | 192.168.0.22 | 3306 | 1607034169565204 | 2705                    | NULL          |
    | 192.168.0.21 | 3306 | 1607034170465170 | 1436                    | NULL          |
    | 192.168.0.22 | 3306 | 1607034229566634 | 2658                    | NULL          |
    | 192.168.0.21 | 3306 | 1607034230439729 | 1423                    | NULL          |
    +--------------+------+------------------+-------------------------+---------------+
    24 rows in set (0.001 sec)

     对心跳信息的监控(对ping 指标的监控)

    MySQL [(none)]> select * from mysql_server_ping_log limit 10;
    +--------------+------+------------------+----------------------+------------+
    | hostname     | port | time_start_us    | ping_success_time_us | ping_error |
    +--------------+------+------------------+----------------------+------------+
    | 192.168.0.21 | 3306 | 1607033728141486 | 427                  | NULL       |
    | 192.168.0.22 | 3306 | 1607033728254914 | 12588                | NULL       |
    | 192.168.0.22 | 3306 | 1607033738142454 | 881                  | NULL       |
    | 192.168.0.21 | 3306 | 1607033738298155 | 407                  | NULL       |
    | 192.168.0.21 | 3306 | 1607033748143508 | 404                  | NULL       |
    | 192.168.0.22 | 3306 | 1607033748334895 | 867                  | NULL       |
    | 192.168.0.22 | 3306 | 1607033758147693 | 962                  | NULL       |
    | 192.168.0.21 | 3306 | 1607033758335872 | 401                  | NULL       |
    | 192.168.0.22 | 3306 | 1607033768153043 | 919                  | NULL       |
    | 192.168.0.21 | 3306 | 1607033768308729 | 391                  | NULL       |
    +--------------+------+------------------+----------------------+------------+
    10 rows in set (0.001 sec)

     看看read_only的日志监控

    MySQL [(none)]> select * from mysql_server_read_only_log limit 10;
    +--------------+------+------------------+-----------------+-----------+-------+
    | hostname     | port | time_start_us    | success_time_us | read_only | error |
    +--------------+------+------------------+-----------------+-----------+-------+
    | 192.168.0.22 | 3306 | 1607033767443920 | 1069            | 1         | NULL  |
    | 192.168.0.21 | 3306 | 1607033767460705 | 583             | 0         | NULL  |
    | 192.168.0.22 | 3306 | 1607033768945522 | 1065            | 1         | NULL  |
    | 192.168.0.21 | 3306 | 1607033768964507 | 565             | 0         | NULL  |
    | 192.168.0.21 | 3306 | 1607033770445973 | 585             | 0         | NULL  |
    | 192.168.0.22 | 3306 | 1607033770471489 | 1138            | 1         | NULL  |
    | 192.168.0.22 | 3306 | 1607033771947566 | 1068            | 1         | NULL  |
    | 192.168.0.21 | 3306 | 1607033771967662 | 578             | 0         | NULL  |
    | 192.168.0.21 | 3306 | 1607033773447642 | 301             | 0         | NULL  |
    | 192.168.0.22 | 3306 | 1607033773471472 | 1382            | 1         | NULL  |
    +--------------+------+------------------+-----------------+-----------+-------+
    10 rows in set (0.001 sec)

     Monitor 模块就会开始监控后端的read_only值,当监控到read_only值,就会按照read_only的值将某些节点自动移到读写组 

     

    配置mysql_users 表,将proxysql用户添加到该表中。

    insert into mysql_users (username,password,default_hostgroup) values ('proxysql','123456',10);
    MySQL [main]> load mysql users to runtime;
    MySQL [main]> save mysql users to disk;

    default_hostgroup:该用户名默认的路由目标。例如,指定root用户的该字段值为10时,则使用 proxysql 用户发送的SQL语句默认情况下将路由到hostgroup_id=10 组中的某个节点。 

     

    在22(从库)端,用 proxysql 用户 测试是否sql路由能默认到 hostgroup_id=10,它是一个写组(读写数据)。

    [root@localhost ~]# mysql -uproxysql -p123456 -h 192.168.0.21 -P 6033
    MySQL [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | tp5                |
    +--------------------+
    7 rows in set (0.002 sec)
    
    MySQL [(none)]> select @@server_id;
    +-------------+
    | @@server_id |
    +-------------+
    |          21 |
    +-------------+
    1 row in set (0.002 sec)

    在从库上用proxysql用户创建数据库查看主库上有没有创建的数据库

    MySQL [(none)]> create database keme;
    Query OK, 1 row affected (0.002 sec)
    

     

    [root@localhost ~]# mysql -uroot -proot
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 24719
    Server version: 10.3.27-MariaDB-log MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | keme               |
    | mysql              |
    | performance_schema |
    | test               |
    | tp5                |
    +--------------------+
    8 rows in set (0.001 sec)
    
    MariaDB [(none)]> 

    如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest

    MySQL [main]> select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;

     

    展开全文
  • ProxySQL简单读写分离.zip ProxySQL简单读写分离.zip ProxySQL简单读写分离.zip ProxySQL简单读写分离.zip ProxySQL简单读写分离.zip
  • proxysql 操作clickhouse的jar
  • ProxySQL Tutorial

    2017-06-14 09:54:56
    ProxySQL Tutorial
  • ProxySQL 搭建

    2021-09-07 10:46:34
    ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL 服务),故障切换和一些 ...

    简介

    ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL 服务),故障切换和一些 SQL 的过滤功能。

    官网

    git: https://github.com/sysown/proxysql

    website: https://www.proxysql.com/

    集群

    搭建两台一模一样的,前面挂SLB分发端口

    架构

    安装依赖

    yum  install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

    部署

    # 下载
    wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm
    
    #  安装
    rpm -ivh proxysql-2.0.8-1-centos7.x86_64.rpm
    
    # 查看相应安装文件路径,有哪些文件
    [root@hostname]# rpm -ql proxysql
    /etc/logrotate.d/proxysql
    /etc/proxysql.cnf
    /etc/systemd/system/proxysql.service
    /usr/bin/proxysql
    /usr/share/proxysql/tools/proxysql_galera_checker.sh
    /usr/share/proxysql/tools/proxysql_galera_writer.pl
    
    

     /etc/proxysql.cnf 是 ProxySQL 的配置文件

    # 启动:
    systemctl start proxysql.service

    6032 是 ProxySQL 的管理端口号,6033是对外服务的端口号 ,ProxySQL 的用户名和密码都是默认的 admin

    # 登录
    mysql -uadmin -padmin -h 127.0.0.1 -P 6032

      可见有五个库: main、disk、stats 、monitor 和 stats_history 
    main: 内存配置数据库,即 MEMORY,表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息:

     库下的主要表: 

    • mysql_servers: 后端可以连接 MySQL 服务器的列表 
    • mysql_users: 配置后端数据库的账号和监控的账号。 
    • mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。

    注: 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容,不能通过 DML 语句修改。

    只能修改对应的不以 runtime 开头的表,然后 “LOAD” 使其生效,“SAVE” 使其存到硬盘以供下次重启加载。 
    disk :持久化的磁盘的配置 
    stats: 统计信息的汇总 
    monitor:一些监控的收集信息,比如数据库的健康状态等 
    stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标

    配置 ProxySQL 所需账户

    在 Master (xx) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户

    #proxysql 的监控账户
    create user 'monitor'@'10.0.0.%' identified by '123456';
    grant all privileges on *.* to 'monitor'@'10.0.0.%' with grant option;
     
    #proxysql 的对外访问账户
    create user 'proxysql'@'10.0.0.%' identified by '123456';
    grant all privileges on *.* to 'proxysql'@'10.0.0.%' with grant option;
     
    flush privileges;

    展开全文
  • ProxySQL备份策略

    2020-03-02 19:41:10
    ProxySQL Series : ProxySQL Backup Startegies 介绍 我们深知ProxySQL是MySQL的领先SQL感知代理之一。 在本博客中,我将解释ProxySQL的备份和还原策略。 我认为,关于该主题的博客仍然结构不完善。 如果您正在寻找...
  • 文档中写了详细的proxysql搭建过程,绕过很多坑点。希望各位老司机指正。
  • proxysql读写分离

    2020-08-14 21:57:04
    数据库中间件最基本的功能就是实现读写分离, ProxySQL 当然也支持。而且 ProxySQL 支持的路由规则非常灵活,不仅可以实现最简单的读写分离,还可以将读/写都分散到多个不同的组,以及实现分库 sharding (分表...
  • ProxySQL 源码探析

    2020-11-15 17:29:02
    ProxySQL 源码探析ProxySQL 架构介绍多级配置系统介绍ProxySQL 实例生命周期启动初始化启动方式重载启动在 RUNTIME 修改配置修改配置后传到其他配置层ProxySQL 配置配置 Backend Service配置 ProxySQLProxySQL 读写...
  • 文章目录1. mysql的主从复制高可用性解决方案MHA1.1 MHA配置1.2 mysql master 的配置1.3 mysql slave的配置2. client 的配置3. route 的配置4. 主DNS 的配置5. firewall 的配置6.... 测试后端的网站能否访问8....
  • MySQL中间件proxysql

    2020-08-14 22:33:11
    文章目录1....ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,495
精华内容 998
关键字:

ProxySQL