MySQL主从读写分离之Proxysql（openEuler版超详解析）

已于 2024-03-17 15:43:03 修改
阅读量558 收藏 8
点赞数 10
文章标签： mysql 数据库 linux openEuler 华为
于 2024-03-07 15:52:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_69037277/article/details/136533483
版权

实验目的：

基于proxysql实现MySQL的主从读写分离。

实验过程：

前期准备：

一共有四台虚拟机，其中三台为配置好的一主两从虚拟机，还有一台干净的虚拟机用来配置proxysql。

主机名地址
master192.168.27.137
node1192.168.27.139
node2192.168.27.140
proxysql192.168.27.141

proxysql下载：（此处链接为官方，也可进入percona或者github官网进行下载适合的版本）ProxySQL - A High Performance Open Source MySQL Proxyicon-default.png?t=N7T8https://www.proxysql.com/

实验过程：

在proxysql所在虚拟机配置：
下载并安装proxysql以及mysql客户端（mariadb）：
[root@localhost ~]# yum install proxysql-2.5.5-1-centos8.x86_64.rpm
[root@localhost ~]# dnf install mariadb
启动proxysql服务：
[root@localhost ~]# systemctl enable --now proxysql
登录proxysql：
[root@localhost ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
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)
可见有五个库： main、disk、stats 、monitor 和 stats_history
main: 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息：
MySQL [(none)]> show tables from main;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| coredump_filters                                   |
| 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                         |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_coredump_filters                           |
| 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_hostgroup_attributes                 |
| 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                                          |
+----------------------------------------------------+
36 rows in set (0.001 sec)
mysql_servers: 后端可以连接 MySQL 服务器的列表
mysql_users: 配置后端数据库的账号和监控的账号。
mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。
注： 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容，不能通过 DML 语句修改。
只能修改对应的不以 runtime 开头的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盘以供下次重启加载。
在主库（master）中的配置：
创建proxysql的监控账户和对外访问账户：
mysql> create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;
Query OK, 0 rows affected (0.00 sec)
配置proxysql：
查看需要用到的表的表结构：
MySQL [(none)]> 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.000 sec)
创建新的组（定义写为1，读为0）
MySQL [(none)]>  insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> load mysql servers to runtime;//加载到当前生效
Query OK, 0 rows affected (0.003 sec)
MySQL [(none)]> save mysql servers to disk;//持久化保存
Query OK, 0 rows affected (0.018 sec)
ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为1的写组，read_only=1的server，slave则分到编号为0的读组
MySQL [(none)]> select * from mysql_replication_hostgro
+------------------+------------------+------------+---
| writer_hostgroup | reader_hostgroup | check_type | co
+------------------+------------------+------------+---
| 1                | 0                | read_only  | pr
+------------------+------------------+------------+---
1 row in set (0.000 sec)
添加主从服务器节点：
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.27.137',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]>  insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.27.139',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.27.140',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.002 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.015 sec)
MySQL [(none)]>  select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.27.137 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 192.168.27.139 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 192.168.27.140 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 sec)
为proxysql监控mysql后端节点：
MySQL [(none)]> use monitor//使用monitor
Database changed
MySQL [monitor]> set mysql-monitor_username='monitor';//创建用户 
Query OK, 1 row affected (0.000 sec)
MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';//填写密码
Query OK, 1 row affected (0.000 sec)
MySQL [monitor]>  load mysql variables to runtime;//加载到当前
Query OK, 0 rows affected (0.001 sec)
MySQL [monitor]> save mysql variables to disk;//持久化保存
Query OK, 158 rows affected (0.003 sec)
MySQL [monitor]> select @@mysql-monitor_username;//查看用户名
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.001 sec)
MySQL [monitor]> select @@mysql-monitor_password;//查看密码
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| Monitor@123.com          |
+--------------------------+
1 row in set (0.001 sec)
MySQL [monitor]> select * from monitor.mysql_server_connect_log;//查看日志信息
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                           |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| 192.168.27.140 | 3306 | 1709796180625505 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.137 | 3306 | 1709796181067381 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.139 | 3306 | 1709796181508932 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.140 | 3306 | 1709796240626303 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.137 | 3306 | 1709796241217740 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.139 | 3306 | 1709796241809274 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.139 | 3306 | 1709796300626918 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.137 | 3306 | 1709796301097796 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.140 | 3306 | 1709796301568472 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
| 192.168.27.139 | 3306 | 1709796306543844 | 1884                    | NULL                                                                    |
| 192.168.27.140 | 3306 | 1709796307338491 | 1778                    | NULL                                                                    |
| 192.168.27.137 | 3306 | 1709796308132494 | 1261                    | NULL                                                                    |
| 192.168.27.137 | 3306 | 1709796366544441 | 1317                    | NULL                                                                    |
| 192.168.27.139 | 3306 | 1709796367110254 | 1339                    | NULL                                                                    |
| 192.168.27.140 | 3306 | 1709796367683285 | 1771                    | NULL                                                                    |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
15 rows in set (0.000 sec)
查看心跳信息：
MySQL [monitor]> select * from mysql_server_ping_log;
+----------------+------+------------------+--------------------------+
| hostname       | port | time_start_us    | ping_succe               |
+----------------+------+------------------+--------------------------+
| 192.168.27.140 | 3306 | 1709796150706279 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796150706317 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796150707742 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796160707187 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796160707414 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796160709026 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796170707830 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796170707893 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796170709330 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796180708671 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796180708437 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796180711690 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796190709041 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796190709143 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796190710542 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796200709720 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796200709721 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796200710996 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796210710277 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796210710291 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796210711528 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796220711158 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796220711010 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796220712626 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796230711681 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796230711766 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796230712791 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796240712290 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796240712403 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796240714933 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796250712823 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796250712816 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796250714279 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796260713537 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796260713528 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796260715467 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796270714115 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796270714202 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796270715440 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796280714926 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796280715011 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796280716414 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796290715577 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796290715497 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796290718626 | 0         password: YES) |
| 192.168.27.140 | 3306 | 1709796300716091 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796300716161 | 0         password: YES) |
| 192.168.27.139 | 3306 | 1709796300717430 | 0         password: YES) |
| 192.168.27.137 | 3306 | 1709796306725083 | 234                      |
| 192.168.27.140 | 3306 | 1709796306725072 | 160                      |
| 192.168.27.139 | 3306 | 1709796306725193 | 132                      |
| 192.168.27.137 | 3306 | 1709796316725952 | 436                      |
| 192.168.27.139 | 3306 | 1709796316726154 | 259                      |
| 192.168.27.140 | 3306 | 1709796316726119 | 301                      |
| 192.168.27.140 | 3306 | 1709796326726613 | 501                      |
| 192.168.27.139 | 3306 | 1709796326726788 | 353                      |
| 192.168.27.137 | 3306 | 1709796326726752 | 396                      |
| 192.168.27.137 | 3306 | 1709796336726831 | 425                      |
| 192.168.27.140 | 3306 | 1709796336726960 | 319                      |
| 192.168.27.139 | 3306 | 1709796336726942 | 343                      |
| 192.168.27.140 | 3306 | 1709796346742368 | 1308                     |
| 192.168.27.139 | 3306 | 1709796346742539 | 1165                     |
| 192.168.27.137 | 3306 | 1709796346742517 | 1197                     |
| 192.168.27.137 | 3306 | 1709796356743147 | 547                      |
| 192.168.27.140 | 3306 | 1709796356743301 | 425                      |
| 192.168.27.139 | 3306 | 1709796356743278 | 459                      |
| 192.168.27.137 | 3306 | 1709796366743429 | 407                      |
| 192.168.27.140 | 3306 | 1709796366743562 | 291                      |
| 192.168.27.139 | 3306 | 1709796366743545 | 314                      |
| 192.168.27.140 | 3306 | 1709796376745397 | 868                      |
| 192.168.27.139 | 3306 | 1709796376745537 | 752                      |
| 192.168.27.137 | 3306 | 1709796376745518 | 778                      |
| 192.168.27.139 | 3306 | 1709796386745976 | 521                      |
| 192.168.27.140 | 3306 | 1709796386746122 | 406                      |
| 192.168.27.137 | 3306 | 1709796386746109 | 428                      |
| 192.168.27.140 | 3306 | 1709796396746574 | 1349                     |
| 192.168.27.137 | 3306 | 1709796396746710 | 1239                     |
| 192.168.27.139 | 3306 | 1709796396746690 | 1266                     |
| 192.168.27.137 | 3306 | 1709796406747306 | 1100                     |
| 192.168.27.139 | 3306 | 1709796406747510 | 944                      |
| 192.168.27.140 | 3306 | 1709796406747486 | 979                      |
| 192.168.27.139 | 3306 | 1709796416748048 | 431                      |
| 192.168.27.137 | 3306 | 1709796416748195 | 305                      |
| 192.168.27.140 | 3306 | 1709796416748215 | 291                      |
| 192.168.27.139 | 3306 | 1709796426748430 | 797                      |
| 192.168.27.137 | 3306 | 1709796426748565 | 684                      |
| 192.168.27.140 | 3306 | 1709796426748549 | 707                      |
| 192.168.27.140 | 3306 | 1709796436748942 | 551                      |
| 192.168.27.139 | 3306 | 1709796436749096 | 428                      |
| 192.168.27.137 | 3306 | 1709796436749075 | 457                      |
| 192.168.27.139 | 3306 | 1709796446749755 | 677                      |
| 192.168.27.140 | 3306 | 1709796446749900 | 561                      |
| 192.168.27.137 | 3306 | 1709796446749880 | 590                      |
| 192.168.27.137 | 3306 | 1709796456753858 | 1380                     |
| 192.168.27.139 | 3306 | 1709796456754148 | 1129                     |
| 192.168.27.140 | 3306 | 1709796456754111 | 1176                     |
| 192.168.27.140 | 3306 | 1709796466754180 | 882                      |
| 192.168.27.137 | 3306 | 1709796466754360 | 741                      |
| 192.168.27.139 | 3306 | 1709796466754339 | 769                      |
| 192.168.27.137 | 3306 | 1709796476755056 | 389                      |
| 192.168.27.139 | 3306 | 1709796476755239 | 229                      |
| 192.168.27.140 | 3306 | 1709796476755187 | 294                      |
| 192.168.27.139 | 3306 | 1709796486755641 | 441                      |
| 192.168.27.137 | 3306 | 1709796486755703 | 397                      |
| 192.168.27.140 | 3306 | 1709796486755684 | 422                      |
| 192.168.27.137 | 3306 | 1709796496755840 | 479                      |
| 192.168.27.140 | 3306 | 1709796496756003 | 381                      |
| 192.168.27.139 | 3306 | 1709796496755990 | 401                      |
| 192.168.27.139 | 3306 | 1709796506756185 | 348                      |
| 192.168.27.137 | 3306 | 1709796506756298 | 260                      |
| 192.168.27.140 | 3306 | 1709796506756313 | 252                      |
| 192.168.27.140 | 3306 | 1709796516756932 | 517                      |
| 192.168.27.139 | 3306 | 1709796516757138 | 331                      |
| 192.168.27.137 | 3306 | 1709796516757160 | 367                      |
| 192.168.27.140 | 3306 | 1709796526757309 | 337                      |
| 192.168.27.139 | 3306 | 1709796526757433 | 236                      |
| 192.168.27.137 | 3306 | 1709796526757417 | 258                      |
| 192.168.27.139 | 3306 | 1709796536757782 | 458                      |
| 192.168.27.137 | 3306 | 1709796536757910 | 349                      |
| 192.168.27.140 | 3306 | 1709796536757894 | 372                      |
| 192.168.27.140 | 3306 | 1709796546758288 | 477                      |
| 192.168.27.137 | 3306 | 1709796546758460 | 327                      |
| 192.168.27.139 | 3306 | 1709796546758436 | 358                      |
| 192.168.27.137 | 3306 | 1709796556758969 | 473                      |
| 192.168.27.139 | 3306 | 1709796556759144 | 345                      |
| 192.168.27.140 | 3306 | 1709796556759119 | 377                      |
| 192.168.27.140 | 3306 | 1709796566759542 | 440                      |
| 192.168.27.137 | 3306 | 1709796566759671 | 330                      |
| 192.168.27.139 | 3306 | 1709796566759654 | 355                      |
+----------------+------+------------------+--------------------------+
129 rows in set (0.000 sec)
查看read_only监控：
MySQL [monitor]> select * from mysql_server_read_only_log;//此次查询结果报错信息全为0需要进行过更改
对两个从服务器的配置文件进行更改，添加read_only=1让两从文件只课读：
mysql> system vim /etc/my.cnf
mysql> system systemctl restart mysql
再次查看read_only表信息：
                                          |
| 192.168.27.137 | 3306 | 1709796956508275 | 485             | 0         | NULL                                                                                        |
| 192.168.27.139 | 3306 | 1709796956508323 | 446             | 1         | NULL                                                                                        |
| 192.168.27.139 | 3306 | 1709796958008641 | 627             | 1         | NULL                                                                                        |
| 192.168.27.140 | 3306 | 1709796958008789 | 516             | 1         | NULL                                                                                        |
| 192.168.27.137 | 3306 | 1709796958008776 | 542             | 0         | NULL
proxysql配置对外访问账号：
MySQL [monitor]> insert into 
mysql_users(username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
Query OK, 1 row affected (0.000 sec)
MySQL [monitor]> load mysql users to runtime;
Query OK, 0 rows affected (0.000 sec)
MySQL [monitor]> save mysql users to disk;
Query OK, 0 rows affected (0.011 sec)
MySQL [monitor]> select * from mysql_users\G
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1
               use_ssl: 0
     default_hostgroup: 1
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
            attributes: 
               comment: 
1 row in set (0.000 sec)
在从库192.168.27.139上通过对方询问账号proxysql连接，是否路由能默认到写组：
[root@node1 ~]# mysql -h192.168.27.141 -uproxysql -p'123456' -P 6033
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 2
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2022, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> create database keme;
Query OK, 1 row affected (0.01 sec)
在从库192.168.27.140上进行验证看是否能查看到keme：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| keme               |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
真的jemas
关注
  • 10
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ProxySQL--灵活强大的MySQL代理层
奋进的K
09-25 3万+
本文是我在学习和验证ProxySQL的过程中，从初识（对其机制猜想或者仅凭几次命令的结果臆断其原理），到逐渐深入（模拟各种场景测试、抓包分析、与作者交流）过程中的思路和方法和结论的记录。ProxySQL项目网址亮点： 几乎所有的配置均可在线更改（其配置数据基于SQLite存储），无需重启proxysql 基于正则和client_addr的可完全自主控制的路由规则 详细的状态统计，统计结果和pt-qu
Mycat实现mysql主从读写分离的配置文件
01-18
这是Mycat实现mysql主从读写分离时用到的的配置文件。 内容包括：schema.xml和server.xml。 请根据实际情况替换里面的数据库地址、用户、密码。 相关操作教程：...
MySQL高可用实现：主从结构下ProxySQL中的读写分离
Enmotech的博客
06-12 501
墨墨导读：ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。ProxySQL提供强大的路由规则。当应用程序自身不支持读写分离时，DBA可以通过配置路由规...
ProxySQL的基本配置
最新发布
xiaozuo2011925的博客
06-11 354
本文概括介绍了ProxySQL的基本配置，例如添加后端MySQL数据库实例，监控后端的MySQL、通过MySQL的read_only自动识别主备库、添加连接用户、sysbench压力测试、查询规则的路由、ProxySQL是否开启查询缓存等
ProxySQL实现mysql8主从同步读写分离
weixin_68421640的博客
04-07 1058
1.1 前言 ProxySQL是 MySQL 的高性能、高可用性、协议感知代理。以下为结合主从复制对ProxySQL读写分离、黑白名单、路由规则等做些基本测试。1.2 基本介绍 先简单介绍下ProxySQL及其功能和配置，主要包括：最基本的读/写分离，且方式有多种；可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由，规则很灵活；动态加载配置，即绝大部分的配置可以在线修改，但有少部分参数还是需要重启来生效；可缓存查询结果。虽然缓存策略比较简陋，但实现了基本的缓存功能；
ProxySQL
jeffery_love的博客
06-17 151
官方站点：https://proxysql.com/ 官方手册：https://github.com/sysown/proxysql/wiki ProxySQL安装 基于YUM仓库安装 cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/
MySQL主从复制读写分离.zip
03-14
MySQL主从复制与读写分离是数据库架构中的重要策略，用于提高系统性能和可用性。在高并发的互联网应用中，这种设计模式尤为常见。本文将深入探讨这两个概念及其实施方法。 **一、MySQL主从复制** MySQL主从复制是...
MySQL主从复制与读写分离原理及用法详解
09-08
MySQL主从复制与读写分离是数据库高可用性和负载均衡的重要技术。主从复制允许数据从一个服务器（主服务器）自动同步到其他服务器（从服务器），而读写分离则是将读操作分散到从服务器，以减轻主服务器的压力，提高...
详解MySQL主从复制读写分离搭建
12-16
MySQL主从复制，读写分离的设置非常简单： 修改配置my.cnf文件 master 和 slave设置的差不多： [mysqld] log-bin=mysql-bin server-id=222 log-bin=mysql-bin的意思是：启用二进制日志。 server-id=222的意思是...
基于Mycat的Mysql主从复制读写分离配置详解与示例
12-25
本文将围绕如何基于Mycat实现Mysql主从复制以及读写分离进行详细介绍。Mycat作为一个中间件，支持SQL92标准，能够跨语言、平台和数据库工作，尤其适合于大数据量的数据库架构。 首先，我们来了解读写分离的好处。...
ProxySQL Tutorial
06-14
ProxySQL Tutorial
ProxySQL:一个高性能的 MySQL 代理-开源
06-15
ProxySQL 是 MySQL 的开源、高性能、高可用性、数据库协议感知代理。 它是由于缺乏高性能开源代理而创建的，并且能够在数十万个后端服务器上扩展数百万个连接，同时获得开源的自由。 ProxySQL 提供端到端的 MySQL 连接处理、实时统计和数据库流量检查。 它具有故障转移检测、丰富的查询规则定义和数据库防火墙，可保护您的数据库免受恶意活动或有问题的应用程序部署的影响。 ProxySQL 受到世界各地众多组织的信赖，可以满足他们的 MySQL 可扩展性和高可用性目标。
ProxySQL中文手册.pdf
07-06
ProxySQL 1.4.4官方手册的翻译。ProxySQL 1.4.4官方手册的翻译。ProxySQL 1.4.4官方手册的翻译。ProxySQL 1.4.4官方手册的翻译。
【ProxySQL 介绍】
06-03 375
ProxySQL  ProxySQL 是一个高性能，高可用性，的数据中间层项目。   高性能 ProxySQL 具有先进的多核架构。 它从根本上构建，支持数十万个并发连接，复用到可能数百个后端服务器。 最大的 ProxySQL 部署跨越了几百个代理。     高效的工作负载管理 ProxySQL 可帮助您从 MySQL 集群中挤出最后一滴性能，而无需控制生成查询的应用程序。  ...
ProxySQL--基础--2.4--部署--管理MSYQL节点
zhou920786312的博客
12-12 185
添加MySQL节点监控后端MySQL节点添加 普通用户这3个步骤虽然需要操作的过程不多，但是涉及的内容还是比较多的。强烈建议将这3个表的所有字段都了解一遍。不仅如此，要熟练使用ProxySQL，还应该对main库中的表的各个字段都比较熟悉，至少要知道它们什么意思。# 连接到ProxySQL的管理接口# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。# 查看下各节点是否都是 ONLINE。
ProxySQL快速入门
weixin_41866717的博客
12-26 575
ProxySQL
ProxySQL 入门教程
easylife206的专栏
07-03 2865
ProxySQL 作为一款强大的中间件为 MySQL 的架构提供了有力的支持。目前可以很好的支持 Master Slave\ MGR \ PXC等，并提供连接池、读写分离...
ProxySQL重要知识
青阳的博客
12-11 123
参考帖子：https://www.cnblogs.com/you-men/p/13057844.html
MySQL主从复制与读写分离实战配置
MySQL主从复制与读写分离是一种常见的数据库集群架构，用于提高数据库性能和可用性。在本文中，作者分享了在Linux环境下实现这一功能的具体步骤。首先，环境准备阶段非常重要，包括设置MySQL主服务器（IP: 192.168....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值