ProxySQL反向代理多台数据库

系统部署了电子大屏系统，需要从不同的业务数据库拉取数据，为了不影响线上的业务，从只读数据库进行查询，为了维护方便只暴露一个数据库访问地址，可以使用ProxySQL进行反向代理。

                                    ----->数据库192.168.0.1(db1)

客户端------->ProxySQL----->数据库192.168.0.2(db2、db3、db4、db5)

                                    ------>数据库192.168.0.3(db6)

客户端通过ProxySQL可以访问db1......db6的数据库

ProxySQL通过schemaname来识别不过的数据库路由。

#file proxysql.cfg


datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
        admin_credentials="admin:admin"
        mysql_ifaces="0.0.0.0:6032"
#       refresh_interval=2000
#       debug=true
}
mysql_variables=
{
        threads=4
        max_connections=5000
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        monitor_username="read"
        monitor_password="read.123456"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}
# defines all the MySQL servers
mysql_servers =
(       //设置服务器组
        { address="192.168.0.1" , port=3306 , hostgroup=0, weight=1, status = "ONLINE", max_connections=5000 },
        { address="192.168.0.2" , port=3306 , hostgroup=1, weight=1, status = "ONLINE", max_connections=5000 },
        { address="192.168.0.3" , port=3306 , hostgroup=2, weight=1, status = "ONLINE", max_connections=5000 },
)


# defines all the MySQL users
mysql_users:
(
        { username = "read" , password = "read.123456" , default_hostgroup = 1 , active = 1 },   //这里设置默认的组是1组
)



#defines MySQL Query Rules
mysql_query_rules:
(
        {
                rule_id=1
                active=1
                schemaname="db1"           //数据库为db1的时候 使用0组服务器
                destination_hostgroup=0
                apply=1
        },
        {
                rule_id=1
                active=1
                schemaname="db3"        //数据库为db3的时候，使用2组服务器
                destination_hostgroup=2
                apply=1
        }
)
scheduler=
(
#  {
#    id=1
#    active=0
#    interval_ms=10000
#    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
#    arg1="0"
#    arg2="0"
#    arg3="0"
#    arg4="1"
#    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
#  }
)
mysql_replication_hostgroups=
(
#        {
#                writer_hostgroup=0
#                reader_hostgroup=1
#                comment="test repl 1"
#       },
#       {
#                writer_hostgroup=50
#                reader_hostgroup=60
#                comment="test repl 2"
#        }
)
docker run -d -p 6062:6032 -p 3306:6033 -v $(pwd)/proxysql.cnf:/etc/proxysql.cnf /proxysql/proxysql

启动Docker服务

docker run -d -p 6062:6032 -p 3306:6033 -v $(pwd)/proxysql.cnf:/etc/proxysql.cnf /proxysql/proxysql