MySQL中间件ProxySQL

原创

于 2023-09-12 18:29:02 发布 · 613 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

在MySQL中，常用的数据库中间件有两种模式，代理模式和客户端模式。

代理模式的产品有：MySQL Router、ProxySQL、MyCAT等。

代理模式的优缺点。

优点：1、对开发语言没有限制，适用于任何兼容MySQL协议的客户端。2、对应于程序完全透明，应于程序可以直接将代理节点作为MySQL使用。

缺点：1、实现复制。2、代理节点存在单点风险，需要考虑代理节点的高可用。

安装ProxySQL

1、下载ProxySQL安装包
[root@node04 ~]# wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql-2.5.5-1-centos7.x86_64.rpm

2、安装ProxySQL
[root@node04 ~]# yum install proxysql-2.5.5-1-centos7.x86_64.rpm -y
[root@node04 ~]# proxysql --version
ProxySQL version 2.5.5-10-g195bd70, codename Truls

3、启动ProxySQL
[root@node04 ~]# systemctl start proxysql

服务启动后，默认会开启两个端口：6032和6033。6032是管理端口，默认管理用户名和密码都是admin。admin只允许在本地登录。6033是对外服务端口。
[root@node04 ~]# netstat -anp |grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      36162/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      36162/proxysql

4、登录ProxySQL
[root@node04 ~]# mysql -h127.0.0.1 -uadmin -padmin -P6032 --prompt 'ProxySQL> '
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, 2023, Oracle and/or its affiliates.

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.

ProxySQL> \s
--------------
mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          1
Current database:       admin
Current user:           admin
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.30 (ProxySQL Admin Module)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               6032
Binary data as:         Hexadecimal
Uptime:                 16 min 43 sec

Threads: 0  Questions: 0  Slow queries: 0
--------------
ProxySQL> 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> show tables from disk;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_settings                            |
| global_variables                           |
| mysql_aws_aurora_hostgroups                |
| mysql_collations                           |
| mysql_firewall_whitelist_rules             |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users             |
| mysql_galera_hostgroups                    |
| mysql_gro