mysql在处理数据量庞大的时候单主机负荷过大,引入mysql的读写分离。
实现读写分离的方式有:
ProxySQL 是一款可以实际用于生产环境的 MySQL 中间件,它有官方版和 percona 版两种。percona版是在官方版的基础上修改的,添加了几个比较实用的工具。生产环境建议用官方版。
ProxySQL 是用 C++ 语言开发的,虽然也是一个轻量级产品,但性能很好(据测试,能处理千亿级的数据),功能也足够,能满足中间件所需的绝大多数功能,包括:
分库分表
传统单机数据储存会随着数据量的增加,导致操作性能下降。
解决方法:
更换更好的硬件。成本高,且瓶颈在数据库本身的话以后数据还会增加,治标不治本。
将一个大库里的数据分散到不同数据库中,大表也拆分为若干小表。这样就不会集中访问同一个数据库造成访问瓶颈。
下载网址:
https://www.proxysql.com/documentation/installing-proxysql/
[root@node4 ~]# systemctl stop firewalld.service [root@node4 ~]# setenforce 0 //配置proxy源 [root@node4 ~]# cat /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.1.x/centos/8/ gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key //清除缓存,安装proxysql和mysql客户端 [root@node4 ~]# yum clean all [root@node4 ~]# yum makecache [root@node4 ~]# yum -y install proxysql mariadb
原理图:
6033端口监听开发以root身份发送的sql语句,,并根据需求将sql语句分发到各mysql服务器上(如读请求给读服务器,写给写服务器)。admin账户通过6032端口管理proxysql。
proxysql启动后监听2个端口:
[root@node4 ~]# systemctl enable --now proxysql-initial.service [root@node4 ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:6032 0.0.0.0:* LISTEN 0 128 0.0.0.0:6033 0.0.0.0:* LISTEN 0 128 0.0.0.0:6033 0.0.0.0:* LISTEN 0 128 0.0.0.0:6033 0.0.0.0:* LISTEN 0 128 0.0.0.0:6033 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 128 [::]:22 [::]:* //通过mysql客户端连接管理接口(本地连接) [root@node4 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 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, 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 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.000 sec)
该变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin,但是这个默认的用户只能在本地使用。
//node4机器上远程登录访问报错 [root@node3 ~]# mysql -uadmin -padmin -P6032 -h192.168.94.130 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1040 (42000): User 'admin' can only connect locally
自定义新管理员账户
//proxysql端添加新账户 [root@node4 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ... MySQL [(none)]> select @@admin-admin_credentials; #查看现有账户 +---------------------------+ | @@admin-admin_credentials | +---------------------------+ | admin:admin | +---------------------------+ 1 row in set (0.001 sec) MySQL [(none)]> set admin-admin_credentials='admin:admin;fxx:fxx123'; #添加新账户 Query OK, 1 row affected (0.001 sec) MySQL [(none)]> select @@admin-admin_credentials; #再次查看 +---------------------------+ | @@admin-admin_credentials | +---------------------------+ | admin:admin;fxx:fxx123 | +---------------------------+ 1 row in set (0.001 sec) MySQL [(none)]> load admin variables to runtime; #生效 Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> save admin variables to disk; #将修改保存到磁盘 Query OK, 43 rows affected (0.001 sec) //远程以新建账户fxx登录 [root@node3 ~]# mysql -ufxx -pfxx123 -P6032 -h192.168.94.130 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 5 ... mysql>
所有的配置操作就是在更改main库对应的表
//比如这个用户就是更改了main库中global_variables表的admin-admin_credentials MySQL [(none)]> select * from global_variables where variable_name='admin-admin_credentials' -> ; +-------------------------+------------------------+ | variable_name | variable_value | +-------------------------+------------------------+ | admin-admin_credentials | admin:admin;fxx:fxx123 | +-------------------------+------------------------+ 1 row in set (0.002 sec)
注意区分admin管理接口的用户名和mysql_users的用户名
该变量是控制的是admin管理接口的普通用户,普通用户没有管理员权限,只能查看main库和monitor库关于统计的数据,且没有写权限。
这个变量中的用户必须不能存在于mysql_users表中
默认用户密码都为stats,和admin用户一样,默认用户仅作本地登录,远程需要添加指定用户。
//添加用户 MySQL [(none)]> select @@admin-stats_credentials; +---------------------------+ | @@admin-stats_credentials | +---------------------------+ | stats:stats | +---------------------------+ 1 row in set (0.001 sec) MySQL [(none)]> set admin-stats_credentials='stats:stats;fxxstat:fxx123'; Query OK, 1 row affected (0.001 sec) MySQL [(none)]> load admin variables to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> save admin variables to disk; Query OK, 43 rows affected (0.001 sec) MySQL [(none)]> select @@admin-stats_credentials; +----------------------------+ | @@admin-stats_credentials | +----------------------------+ | stats:stats;fxxstat:fxx123 | +----------------------------+ 1 row in set (0.001 sec) //远程登录查看 [root@node3 ~]# mysql -ufxxstat -pfxx123 -P6032 -h192.168.94.130 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql>
admin-mysql_ifaces 变量指定admin接口的监听地址,格式为冒号分隔的hostname:port列表。默认监听在 0.0.0.0:6032
//修改默认端口 MySQL [main]> set admin-mysql_ifaces='0.0.0.0:8080'; Query OK, 1 row affected (0.000 sec) MySQL [main]> select @@admin-mysql_ifaces; +----------------------+ | @@admin-mysql_ifaces | +----------------------+ | 0.0.0.0:8080 | +----------------------+ 1 row in set (0.001 sec) MySQL [(none)]> load admin variables to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> save admin variables to disk; Query OK, 43 rows affected (0.001 sec) //退出重写登录发现无法登录 [root@node4 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115) [root@node4 ~]# ss -antl|grep 8080 LISTEN 0 128 0.0.0.0:8080 0.0.0.0:* //更改新端口登录 [root@node4 ~]# mysql -uadmin -padmin -P8080 -h127.0.0.1 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, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
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.000 sec)
proxysql内部使用的sqlite3数据库。它和mysql语句有些许不同,proxysql会对不兼容的部分调整,最大程度保证mysql语句的有效率。
#上面描述main库的时候,只是说了内存数据库需要持久化到disk库才能永久保存配置。但实际上,修改了main库中的配置后,并不会立即生效,它还需要load到runtime的数据结构中才生效,只有在runtime数据结构中的配置才是对ProxySQL当前有效的配置 MySQL [(none)]> show tables; +----------------------------------------------------+ | tables | +----------------------------------------------------+ | 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_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | restapi_routes | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_aws_aurora_hostgroups | | runtime_mysql_firewall_whitelist_rules | | runtime_mysql_firewall_whitelist_sqli_fingerprints | | runtime_mysql_firewall_whitelist_users | | runtime_mysql_galera_hostgroups | | 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_restapi_routes | | runtime_scheduler | | scheduler | +----------------------------------------------------+ 32 rows in set (0.001 sec)