在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_group_replication_hostgroups |
| mysql_hostgroup_attributes