devbox
盐析白兔
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL主从读写分离之Proxysql（openEuler版）

作者：盐析白兔 | 2024-03-13 21:03:19

赞

踩

MySQL主从读写分离之Proxysql（openEuler版）

实验目的：

基于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所在虚拟机配置：
  1. 下载并安装proxysql以及mysql客户端（mariadb）：
  2. [root@localhost ~]# yum install proxysql-2.5.5-1-centos8.x86_64.rpm
  3. [root@localhost ~]# dnf install mariadb
  4. 启动proxysql服务：
  5. [root@localhost ~]# systemctl enable --now proxysql
  6. 登录proxysql：
  7. [root@localhost ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
  8. Welcome to the MariaDB monitor.  Commands end with ; or \g.
  9. Your MySQL connection id is 1
  10. Server version: 5.5.30 (ProxySQL Admin Module)
  11.  
  12. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
  13.  
  14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15.  
  16. MySQL [(none)]> show databases;//查看数据库
  17. +-----+---------------+-------------------------------------+
  18. | seq | name          | file                                |
  19. +-----+---------------+-------------------------------------+
  20. | 0   | main          |                                     |
  21. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
  22. | 3   | stats         |                                     |
  23. | 4   | monitor       |                                     |
  24. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
  25. +-----+---------------+-------------------------------------+
  26. 5 rows in set (0.000 sec)
  27. 可见有五个库： main、disk、stats 、monitor 和 stats_history
  28. main: 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息：
  29. MySQL [(none)]> show tables from main;
  30. +----------------------------------------------------+
  31. | tables                                             |
  32. +----------------------------------------------------+
  33. | coredump_filters                                   |
  34. | global_variables                                   |
  35. | mysql_aws_aurora_hostgroups                        |
  36. | mysql_collations                                   |
  37. | mysql_firewall_whitelist_rules                     |
  38. | mysql_firewall_whitelist_sqli_fingerprints         |
  39. | mysql_firewall_whitelist_users                     |
  40. | mysql_galera_hostgroups                            |
  41. | mysql_group_replication_hostgroups                 |
  42. | mysql_hostgroup_attributes                         |
  43. | mysql_query_rules                                  |
  44. | mysql_query_rules_fast_routing                     |
  45. | mysql_replication_hostgroups                       |
  46. | mysql_servers                                      |
  47. | mysql_users                                        |
  48. | proxysql_servers                                   |
  49. | restapi_routes                                     |
  50. | runtime_checksums_values                           |
  51. | runtime_coredump_filters                           |
  52. | runtime_global_variables                           |
  53. | runtime_mysql_aws_aurora_hostgroups                |
  54. | runtime_mysql_firewall_whitelist_rules             |
  55. | runtime_mysql_firewall_whitelist_sqli_fingerprints |
  56. | runtime_mysql_firewall_whitelist_users             |
  57. | runtime_mysql_galera_hostgroups                    |
  58. | runtime_mysql_group_replication_hostgroups         |
  59. | runtime_mysql_hostgroup_attributes                 |
  60. | runtime_mysql_query_rules                          |
  61. | runtime_mysql_query_rules_fast_routing             |
  62. | runtime_mysql_replication_hostgroups               |
  63. | runtime_mysql_servers                              |
  64. | runtime_mysql_users                                |
  65. | runtime_proxysql_servers                           |
  66. | runtime_restapi_routes                             |
  67. | runtime_scheduler                                  |
  68. | scheduler                                          |
  69. +----------------------------------------------------+
  70. 36 rows in set (0.001 sec)
  71. mysql_servers: 后端可以连接 MySQL 服务器的列表
  72. mysql_users: 配置后端数据库的账号和监控的账号。
  73. mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。
  74. 注： 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容，不能通过 DML 语句修改。
  75. 只能修改对应的不以 runtime 开头的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盘以供下次重启加载。
在主库（master）中的配置：
  1. 创建proxysql的监控账户和对外访问账户：
  2. mysql> create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
  3. Query OK, 0 rows affected (0.00 sec)
  4. mysql> grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;
  5. Query OK, 0 rows affected (0.01 sec)
  6. mysql> create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by '123456';
  7. Query OK, 0 rows affected (0.00 sec)
  8. mysql> grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;
  9. Query OK, 0 rows affected (0.00 sec)
配置proxysql：
  1. 查看需要用到的表的表结构：
  2. MySQL [(none)]> show create table mysql_replication_hostgroups \G
  3. *************************** 1. row ***************************
  4.        table: mysql_replication_hostgroups
  5. Create Table: CREATE TABLE mysql_replication_hostgroups (
  6.     writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
  7.     reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
  8.     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',
  9.     comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
  10. 1 row in set (0.000 sec)
  11. 创建新的组（定义写为1，读为0）
  12. MySQL [(none)]>  insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
  13. Query OK, 1 row affected (0.000 sec)
  14. MySQL [(none)]> load mysql servers to runtime;//加载到当前生效
  15. Query OK, 0 rows affected (0.003 sec)
  16. MySQL [(none)]> save mysql servers to disk;//持久化保存
  17. Query OK, 0 rows affected (0.018 sec)
  18. ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为1的写组，read_only=1的server，slave则分到编号为0的读组
  19. MySQL [(none)]> select * from mysql_replication_hostgro
  20. +------------------+------------------+------------+---
  21. | writer_hostgroup | reader_hostgroup | check_type | co
  22. +------------------+------------------+------------+---
  23. | 1                | 0                | read_only  | pr
  24. +------------------+------------------+------------+---
  25. 1 row in set (0.000 sec)
添加主从服务器节点：
  1. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.27.137',3306);
  2. Query OK, 1 row affected (0.000 sec)
  3. MySQL [(none)]>  insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.27.139',3306);
  4. Query OK, 1 row affected (0.000 sec)
  5. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.27.140',3306);
  6. Query OK, 1 row affected (0.000 sec)
  7. MySQL [(none)]> load mysql servers to runtime;
  8. Query OK, 0 rows affected (0.002 sec)
  9. MySQL [(none)]> save mysql servers to disk;
  10. Query OK, 0 rows affected (0.015 sec)
  11. MySQL [(none)]>  select * from mysql_servers;
  12. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  13. | hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  14. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  15. | 1            | 192.168.27.137 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  16. | 0            | 192.168.27.139 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  17. | 0            | 192.168.27.140 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  18. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  19. 3 rows in set (0.000 sec)
为proxysql监控mysql后端节点：
  1. MySQL [(none)]> use monitor//使用monitor
  2. Database changed
  3. MySQL [monitor]> set mysql-monitor_username='monitor';//创建用户 
  4. Query OK, 1 row affected (0.000 sec)
  5. MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';//填写密码
  6. Query OK, 1 row affected (0.000 sec)
  7. MySQL [monitor]>  load mysql variables to runtime;//加载到当前
  8. Query OK, 0 rows affected (0.001 sec)
  9. MySQL [monitor]> save mysql variables to disk;//持久化保存
  10. Query OK, 158 rows affected (0.003 sec)
  11. MySQL [monitor]> select @@mysql-monitor_username;//查看用户名
  12. +--------------------------+
  13. | @@mysql-monitor_username |
  14. +--------------------------+
  15. | monitor                  |
  16. +--------------------------+
  17. 1 row in set (0.001 sec)
  18. MySQL [monitor]> select @@mysql-monitor_password;//查看密码
  19. +--------------------------+
  20. | @@mysql-monitor_password |
  21. +--------------------------+
  22. | Monitor@123.com          |
  23. +--------------------------+
  24. 1 row in set (0.001 sec)
  25. MySQL [monitor]> select * from monitor.mysql_server_connect_log;//查看日志信息
  26. +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
  27. | hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                           |
  28. +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
  29. | 192.168.27.140 | 3306 | 1709796180625505 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  30. | 192.168.27.137 | 3306 | 1709796181067381 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  31. | 192.168.27.139 | 3306 | 1709796181508932 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  32. | 192.168.27.140 | 3306 | 1709796240626303 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  33. | 192.168.27.137 | 3306 | 1709796241217740 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  34. | 192.168.27.139 | 3306 | 1709796241809274 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  35. | 192.168.27.139 | 3306 | 1709796300626918 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  36. | 192.168.27.137 | 3306 | 1709796301097796 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  37. | 192.168.27.140 | 3306 | 1709796301568472 | 0                       | Access denied for user 'monitor'@'192.168.27.141' (using password: YES) |
  38. | 192.168.27.139 | 3306 | 1709796306543844 | 1884                    | NULL                                                                    |
  39. | 192.168.27.140 | 3306 | 1709796307338491 | 1778                    | NULL                                                                    |
  40. | 192.168.27.137 | 3306 | 1709796308132494 | 1261                    | NULL                                                                    |
  41. | 192.168.27.137 | 3306 | 1709796366544441 | 1317                    | NULL                                                                    |
  42. | 192.168.27.139 | 3306 | 1709796367110254 | 1339                    | NULL                                                                    |
  43. | 192.168.27.140 | 3306 | 1709796367683285 | 1771                    | NULL                                                                    |
  44. +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
  45. 15 rows in set (0.000 sec)
查看心跳信息：
  1. MySQL [monitor]> select * from mysql_server_ping_log;
  2. +----------------+------+------------------+--------------------------+
  3. | hostname       | port | time_start_us    | ping_succe               |
  4. +----------------+------+------------------+--------------------------+
  5. | 192.168.27.140 | 3306 | 1709796150706279 | 0         password: YES) |
  6. | 192.168.27.139 | 3306 | 1709796150706317 | 0         password: YES) |
  7. | 192.168.27.137 | 3306 | 1709796150707742 | 0         password: YES) |
  8. | 192.168.27.139 | 3306 | 1709796160707187 | 0         password: YES) |
  9. | 192.168.27.137 | 3306 | 1709796160707414 | 0         password: YES) |
  10. | 192.168.27.140 | 3306 | 1709796160709026 | 0         password: YES) |
  11. | 192.168.27.140 | 3306 | 1709796170707830 | 0         password: YES) |
  12. | 192.168.27.137 | 3306 | 1709796170707893 | 0         password: YES) |
  13. | 192.168.27.139 | 3306 | 1709796170709330 | 0         password: YES) |
  14. | 192.168.27.140 | 3306 | 1709796180708671 | 0         password: YES) |
  15. | 192.168.27.139 | 3306 | 1709796180708437 | 0         password: YES) |
  16. | 192.168.27.137 | 3306 | 1709796180711690 | 0         password: YES) |
  17. | 192.168.27.137 | 3306 | 1709796190709041 | 0         password: YES) |
  18. | 192.168.27.140 | 3306 | 1709796190709143 | 0         password: YES) |
  19. | 192.168.27.139 | 3306 | 1709796190710542 | 0         password: YES) |
  20. | 192.168.27.137 | 3306 | 1709796200709720 | 0         password: YES) |
  21. | 192.168.27.139 | 3306 | 1709796200709721 | 0         password: YES) |
  22. | 192.168.27.140 | 3306 | 1709796200710996 | 0         password: YES) |
  23. | 192.168.27.140 | 3306 | 1709796210710277 | 0         password: YES) |
  24. | 192.168.27.137 | 3306 | 1709796210710291 | 0         password: YES) |
  25. | 192.168.27.139 | 3306 | 1709796210711528 | 0         password: YES) |
  26. | 192.168.27.137 | 3306 | 1709796220711158 | 0         password: YES) |
  27. | 192.168.27.140 | 3306 | 1709796220711010 | 0         password: YES) |
  28. | 192.168.27.139 | 3306 | 1709796220712626 | 0         password: YES) |
  29. | 192.168.27.140 | 3306 | 1709796230711681 | 0         password: YES) |
  30. | 192.168.27.137 | 3306 | 1709796230711766 | 0         password: YES) |
  31. | 192.168.27.139 | 3306 | 1709796230712791 | 0         password: YES) |
  32. | 192.168.27.139 | 3306 | 1709796240712290 | 0         password: YES) |
  33. | 192.168.27.137 | 3306 | 1709796240712403 | 0         password: YES) |
  34. | 192.168.27.140 | 3306 | 1709796240714933 | 0         password: YES) |
  35. | 192.168.27.140 | 3306 | 1709796250712823 | 0         password: YES) |
  36. | 192.168.27.139 | 3306 | 1709796250712816 | 0         password: YES) |
  37. | 192.168.27.137 | 3306 | 1709796250714279 | 0         password: YES) |
  38. | 192.168.27.139 | 3306 | 1709796260713537 | 0         password: YES) |
  39. | 192.168.27.137 | 3306 | 1709796260713528 | 0         password: YES) |
  40. | 192.168.27.140 | 3306 | 1709796260715467 | 0         password: YES) |
  41. | 192.168.27.140 | 3306 | 1709796270714115 | 0         password: YES) |
  42. | 192.168.27.139 | 3306 | 1709796270714202 | 0         password: YES) |
  43. | 192.168.27.137 | 3306 | 1709796270715440 | 0         password: YES) |
  44. | 192.168.27.140 | 3306 | 1709796280714926 | 0         password: YES) |
  45. | 192.168.27.139 | 3306 | 1709796280715011 | 0         password: YES) |
  46. | 192.168.27.137 | 3306 | 1709796280716414 | 0         password: YES) |
  47. | 192.168.27.140 | 3306 | 1709796290715577 | 0         password: YES) |
  48. | 192.168.27.137 | 3306 | 1709796290715497 | 0         password: YES) |
  49. | 192.168.27.139 | 3306 | 1709796290718626 | 0         password: YES) |
  50. | 192.168.27.140 | 3306 | 1709796300716091 | 0         password: YES) |
  51. | 192.168.27.137 | 3306 | 1709796300716161 | 0         password: YES) |
  52. | 192.168.27.139 | 3306 | 1709796300717430 | 0         password: YES) |
  53. | 192.168.27.137 | 3306 | 1709796306725083 | 234                      |
  54. | 192.168.27.140 | 3306 | 1709796306725072 | 160                      |
  55. | 192.168.27.139 | 3306 | 1709796306725193 | 132                      |
  56. | 192.168.27.137 | 3306 | 1709796316725952 | 436                      |
  57. | 192.168.27.139 | 3306 | 1709796316726154 | 259                      |
  58. | 192.168.27.140 | 3306 | 1709796316726119 | 301                      |
  59. | 192.168.27.140 | 3306 | 1709796326726613 | 501                      |
  60. | 192.168.27.139 | 3306 | 1709796326726788 | 353                      |
  61. | 192.168.27.137 | 3306 | 1709796326726752 | 396                      |
  62. | 192.168.27.137 | 3306 | 1709796336726831 | 425                      |
  63. | 192.168.27.140 | 3306 | 1709796336726960 | 319                      |
  64. | 192.168.27.139 | 3306 | 1709796336726942 | 343                      |
  65. | 192.168.27.140 | 3306 | 1709796346742368 | 1308                     |
  66. | 192.168.27.139 | 3306 | 1709796346742539 | 1165                     |
  67. | 192.168.27.137 | 3306 | 1709796346742517 | 1197                     |
  68. | 192.168.27.137 | 3306 | 1709796356743147 | 547                      |
  69. | 192.168.27.140 | 3306 | 1709796356743301 | 425                      |
  70. | 192.168.27.139 | 3306 | 1709796356743278 | 459                      |
  71. | 192.168.27.137 | 3306 | 1709796366743429 | 407                      |
  72. | 192.168.27.140 | 3306 | 1709796366743562 | 291                      |
  73. | 192.168.27.139 | 3306 | 1709796366743545 | 314                      |
  74. | 192.168.27.140 | 3306 | 1709796376745397 | 868                      |
  75. | 192.168.27.139 | 3306 | 1709796376745537 | 752                      |
  76. | 192.168.27.137 | 3306 | 1709796376745518 | 778                      |
  77. | 192.168.27.139 | 3306 | 1709796386745976 | 521                      |
  78. | 192.168.27.140 | 3306 | 1709796386746122 | 406                      |
  79. | 192.168.27.137 | 3306 | 1709796386746109 | 428                      |
  80. | 192.168.27.140 | 3306 | 1709796396746574 | 1349                     |
  81. | 192.168.27.137 | 3306 | 1709796396746710 | 1239                     |
  82. | 192.168.27.139 | 3306 | 1709796396746690 | 1266                     |
  83. | 192.168.27.137 | 3306 | 1709796406747306 | 1100                     |
  84. | 192.168.27.139 | 3306 | 1709796406747510 | 944                      |
  85. | 192.168.27.140 | 3306 | 1709796406747486 | 979                      |
  86. | 192.168.27.139 | 3306 | 1709796416748048 | 431                      |
  87. | 192.168.27.137 | 3306 | 1709796416748195 | 305                      |
  88. | 192.168.27.140 | 3306 | 1709796416748215 | 291                      |
  89. | 192.168.27.139 | 3306 | 1709796426748430 | 797                      |
  90. | 192.168.27.137 | 3306 | 1709796426748565 | 684                      |
  91. | 192.168.27.140 | 3306 | 1709796426748549 | 707                      |
  92. | 192.168.27.140 | 3306 | 1709796436748942 | 551                      |
  93. | 192.168.27.139 | 3306 | 1709796436749096 | 428                      |
  94. | 192.168.27.137 | 3306 | 1709796436749075 | 457                      |
  95. | 192.168.27.139 | 3306 | 1709796446749755 | 677                      |
  96. | 192.168.27.140 | 3306 | 1709796446749900 | 561                      |
  97. | 192.168.27.137 | 3306 | 1709796446749880 | 590                      |
  98. | 192.168.27.137 | 3306 | 1709796456753858 | 1380                     |
  99. | 192.168.27.139 | 3306 | 1709796456754148 | 1129                     |
  100. | 192.168.27.140 | 3306 | 1709796456754111 | 1176                     |
  101. | 192.168.27.140 | 3306 | 1709796466754180 | 882                      |
  102. | 192.168.27.137 | 3306 | 1709796466754360 | 741                      |
  103. | 192.168.27.139 | 3306 | 1709796466754339 | 769                      |
  104. | 192.168.27.137 | 3306 | 1709796476755056 | 389                      |
  105. | 192.168.27.139 | 3306 | 1709796476755239 | 229                      |
  106. | 192.168.27.140 | 3306 | 1709796476755187 | 294                      |
  107. | 192.168.27.139 | 3306 | 1709796486755641 | 441                      |
  108. | 192.168.27.137 | 3306 | 1709796486755703 | 397                      |
  109. | 192.168.27.140 | 3306 | 1709796486755684 | 422                      |
  110. | 192.168.27.137 | 3306 | 1709796496755840 | 479                      |
  111. | 192.168.27.140 | 3306 | 1709796496756003 | 381                      |
  112. | 192.168.27.139 | 3306 | 1709796496755990 | 401                      |
  113. | 192.168.27.139 | 3306 | 1709796506756185 | 348                      |
  114. | 192.168.27.137 | 3306 | 1709796506756298 | 260                      |
  115. | 192.168.27.140 | 3306 | 1709796506756313 | 252                      |
  116. | 192.168.27.140 | 3306 | 1709796516756932 | 517                      |
  117. | 192.168.27.139 | 3306 | 1709796516757138 | 331                      |
  118. | 192.168.27.137 | 3306 | 1709796516757160 | 367                      |
  119. | 192.168.27.140 | 3306 | 1709796526757309 | 337                      |
  120. | 192.168.27.139 | 3306 | 1709796526757433 | 236                      |
  121. | 192.168.27.137 | 3306 | 1709796526757417 | 258                      |
  122. | 192.168.27.139 | 3306 | 1709796536757782 | 458                      |
  123. | 192.168.27.137 | 3306 | 1709796536757910 | 349                      |
  124. | 192.168.27.140 | 3306 | 1709796536757894 | 372                      |
  125. | 192.168.27.140 | 3306 | 1709796546758288 | 477                      |
  126. | 192.168.27.137 | 3306 | 1709796546758460 | 327                      |
  127. | 192.168.27.139 | 3306 | 1709796546758436 | 358                      |
  128. | 192.168.27.137 | 3306 | 1709796556758969 | 473                      |
  129. | 192.168.27.139 | 3306 | 1709796556759144 | 345                      |
  130. | 192.168.27.140 | 3306 | 1709796556759119 | 377                      |
  131. | 192.168.27.140 | 3306 | 1709796566759542 | 440                      |
  132. | 192.168.27.137 | 3306 | 1709796566759671 | 330                      |
  133. | 192.168.27.139 | 3306 | 1709796566759654 | 355                      |
  134. +----------------+------+------------------+--------------------------+
  135. 129 rows in set (0.000 sec)
查看read_only监控：
  1. MySQL [monitor]> select * from mysql_server_read_only_log;//此次查询结果报错信息全为0需要进行过更改
  2. 对两个从服务器的配置文件进行更改，添加read_only=1让两从文件只课读：
  3. mysql> system vim /etc/my.cnf
  4. mysql> system systemctl restart mysql
  5. 再次查看read_only表信息：
  6.                                           |
  7. | 192.168.27.137 | 3306 | 1709796956508275 | 485             | 0         | NULL                                                                                        |
  8. | 192.168.27.139 | 3306 | 1709796956508323 | 446             | 1         | NULL                                                                                        |
  9. | 192.168.27.139 | 3306 | 1709796958008641 | 627             | 1         | NULL                                                                                        |
  10. | 192.168.27.140 | 3306 | 1709796958008789 | 516             | 1         | NULL                                                                                        |
  11. | 192.168.27.137 | 3306 | 1709796958008776 | 542             | 0         | NULL
proxysql配置对外访问账号：
  1. MySQL [monitor]> insert into 
  2. mysql_users(username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
  3. Query OK, 1 row affected (0.000 sec)
  4. MySQL [monitor]> load mysql users to runtime;
  5. Query OK, 0 rows affected (0.000 sec)
  6. MySQL [monitor]> save mysql users to disk;
  7. Query OK, 0 rows affected (0.011 sec)
  8. MySQL [monitor]> select * from mysql_users\G
  9. *************************** 1. row ***************************
  10.               username: proxysql
  11.               password: 123456
  12.                 active: 1
  13.                use_ssl: 0
  14.      default_hostgroup: 1
  15.         default_schema: NULL
  16.          schema_locked: 0
  17. transaction_persistent: 1
  18.           fast_forward: 0
  19.                backend: 1
  20.               frontend: 1
  21.        max_connections: 10000
  22.             attributes: 
  23.                comment: 
  24. 1 row in set (0.000 sec)
在从库192.168.27.139上通过对方询问账号proxysql连接，是否路由能默认到写组：
  1. [root@node1 ~]# mysql -h192.168.27.141 -uproxysql -p'123456' -P 6033
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 2
  5. Server version: 5.5.30 (ProxySQL)
  6.  
  7. Copyright (c) 2000, 2022, Oracle and/or its affiliates.
  8.  
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  12.  
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  14.  
  15. mysql> show databases;
  16. +--------------------+
  17. | Database           |
  18. +--------------------+
  19. | information_schema |
  20. | mysql              |
  21. | performance_schema |
  22. | school             |
  23. | sys                |
  24. +--------------------+
  25. 5 rows in set (0.01 sec)
  26.  
  27. mysql> select @@server_id;
  28. +-------------+
  29. | @@server_id |
  30. +-------------+
  31. |           1 |
  32. +-------------+
  33. 1 row in set (0.00 sec)
  34.  
  35. mysql> create database keme;
  36. Query OK, 1 row affected (0.01 sec)
在从库192.168.27.140上进行验证看是否能查看到keme：
  1. mysql> show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | keme               |
  7. | mysql              |
  8. | performance_schema |
  9. | school             |
  10. | sys                |
  11. +--------------------+
  12. 6 rows in set (0.00 sec)
本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号