devbox
我家自动化
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

通过proxysql实现MySQL主从读写分离_proxysql reader_shunned

作者：我家自动化 | 2024-03-13 21:06:02

赞

踩

proxysql reader_shunned
原文： https://www.yuque.com/wei01/wql35u/wbgk1a

自行部署MySQL主从环境

安装proxysql

  1.  
  2. # 安装依赖包
  3. yum install gnutls perl-DBD-mysql -y
  4.  
  5. rpm -ivh /tmp/proxysql-2.3.2-1-centos7.x86_64.rpm 
  6.  
  7. # 查看相应安装文件路径，有哪些文件
  8. root@mysqlhost01:/opt/software#rpm -ql proxysql
  9. /etc/logrotate.d/proxysql
  10. /etc/proxysql.cnf
  11. /etc/systemd/system/proxysql-initial.service
  12. /etc/systemd/system/proxysql.service
  13. /usr/bin/proxysql
  14. /usr/share/proxysql/tools/proxysql_galera_checker.sh
  15. /usr/share/proxysql/tools/proxysql_galera_writer.pl
  16.  
  17. # 查看版本
  18. root@mysqlhost01:/opt/software#proxysql --version
  19. ProxySQL version 2.3.2-10-g8cd66cf, codename Truls
  20.  
  21. # 如果是单独机器，可以安装MySQL客户端
  22. yum install Percona-XtraDB-Cluster-client-80
  23.  
  24. # 查看状态
  25. root@mysqlhost01:/opt/software#systemctl start proxysql
  26. root@mysqlhost01:/opt/software#systemctl status proxysql
  27. ● proxysql.service - High Performance Advanced Proxy for MySQL
  28.    Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
  29.    Active: active (running) since Sat 2022-04-23 19:16:06 CST; 3s ago
  30.   Process: 55697 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
  31.  Main PID: 55699 (proxysql)
  32.    CGroup: /system.slice/proxysql.service
  33.            ├─55699 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
  34.            └─55700 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
  35.  
  36. Apr 23 19:16:06 mysqlhost01 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
  37. Apr 23 19:16:06 mysqlhost01 proxysql[55697]: 2022-04-23 19:16:06 [INFO] Using config file /etc/proxysql.cnf
  38. Apr 23 19:16:06 mysqlhost01 proxysql[55697]: 2022-04-23 19:16:06 [INFO] Current RLIMIT_NOFILE: 102400
  39. Apr 23 19:16:06 mysqlhost01 proxysql[55697]: 2022-04-23 19:16:06 [INFO] Using OpenSSL version: OpenSSL 1.1.1j  16 Feb 2021
  40. Apr 23 19:16:06 mysqlhost01 proxysql[55697]: 2022-04-23 19:16:06 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
  41. Apr 23 19:16:06 mysqlhost01 systemd[1]: Started High Performance Advanced Proxy for MySQL.
  42.  
  43. # 查看使用的端口
  44. root@mysqlhost01:/opt/software#netstat -anlp | grep proxysql
  45. tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      55700/proxysql      
  46. tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      55700/proxysql
  47.  
  48. 6032 是 ProxySQL 的管理端口号，6033是对外服务的端口号
  49.  
  50. ProxySQL 的用户名和密码都是默认的 admin
  51. 如果是MySQL数据库用户，则使用6033端口

proxysql 配置MySQL节点和用户

  1. # 连接proxysql
  2. root@192.168.51.21:/opt/software#mysql -uadmin -padmin -h 127.0.0.1 -P 6032
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.
  5. Your MySQL connection id is 1
  6. Server version: 5.5.30 (ProxySQL Admin Module)
  7.  
  8. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  9.  
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  13.  
  14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15.  
  16. mysql> 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.00 sec)
  27.  
  28. mysql> show tables from main;
  29. +----------------------------------------------------+
  30. | tables                                             |
  31. +----------------------------------------------------+
  32. | global_variables                                   |
  33. | mysql_aws_aurora_hostgroups                        |
  34. | mysql_collations                                   |
  35. | mysql_firewall_whitelist_rules                     |
  36. | mysql_firewall_whitelist_sqli_fingerprints         |
  37. | mysql_firewall_whitelist_users                     |
  38. | mysql_galera_hostgroups                            |
  39. | mysql_group_replication_hostgroups                 |
  40. | mysql_query_rules                                  |
  41. | mysql_query_rules_fast_routing                     |
  42. | mysql_replication_hostgroups                       |
  43. | mysql_servers                                      |
  44. | mysql_users                                        |
  45. | proxysql_servers                                   |
  46. | restapi_routes                                     |
  47. | runtime_checksums_values                           |
  48. | runtime_global_variables                           |
  49. | runtime_mysql_aws_aurora_hostgroups                |
  50. | runtime_mysql_firewall_whitelist_rules             |
  51. | runtime_mysql_firewall_whitelist_sqli_fingerprints |
  52. | runtime_mysql_firewall_whitelist_users             |
  53. | runtime_mysql_galera_hostgroups                    |
  54. | runtime_mysql_group_replication_hostgroups         |
  55. | runtime_mysql_query_rules                          |
  56. | runtime_mysql_query_rules_fast_routing             |
  57. | runtime_mysql_replication_hostgroups               |
  58. | runtime_mysql_servers                              |
  59. | runtime_mysql_users                                |
  60. | runtime_proxysql_servers                           |
  61. | runtime_restapi_routes                             |
  62. | runtime_scheduler                                  |
  63. | scheduler                                          |
  64. +----------------------------------------------------+
  65.  
  66.  
  67. mysql_servers: 后端可以连接 MySQL 服务器的列表
  68.  
  69. mysql_users: 配置后端数据库的账号和监控的账号。
  70.  
  71. mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表
  72.  
  73. runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效，执行save … to disk才能将配置持久化保存到磁盘
  74.  
  75. -- 向ProxySQL中添加MySQL节点
  76. mysql@proxysql> insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.51.21',3306);
  77. mysql@proxysql> insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.51.22',3306);
  78. -- 应用配置
  79. mysql@proxysql> load mysql servers to runtime;
  80. mysql@proxysql> save mysql servers to disk;
  81.  
  82. mysql> select * from mysql_servers\G
  83. *************************** 1. row ***************************
  84.        hostgroup_id: 10
  85.            hostname: 192.168.51.21
  86.                port: 3306
  87.           gtid_port: 0
  88.              status: ONLINE
  89.              weight: 1
  90.         compression: 0
  91.     max_connections: 1000
  92. max_replication_lag: 0
  93.             use_ssl: 0
  94.      max_latency_ms: 0
  95.             comment: 
  96. *************************** 2. row ***************************
  97.        hostgroup_id: 10
  98.            hostname: 192.168.51.22
  99.                port: 3306
  100.           gtid_port: 0
  101.              status: ONLINE
  102.              weight: 1
  103.         compression: 0
  104.     max_connections: 1000
  105. max_replication_lag: 0
  106.             use_ssl: 0
  107.      max_latency_ms: 0
  108.             comment: 
  109. 2 rows in set (0.00 sec)
  110.  
  111. -- 添加节点之后，还需要监控后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。
  112.  
  113. #mysql -uroot -pBigdata@123
  114. CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by 'ProxySQLPa55';
  115. GRANT replication client ON *.* TO 'proxysql'@'%';
  116.  
  117. -- proxysql配置
  118. mysql@proxysql> UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
  119. mysql@proxysql> UPDATE global_variables SET variable_value='ProxySQLPa55' WHERE variable_name='mysql-monitor_password';
  120.  
  121. -- 将配置写入到磁盘
  122. mysql@proxysql> LOAD MYSQL VARIABLES TO RUNTIME;
  123. mysql@proxysql> SAVE MYSQL VARIABLES TO DISK;
  124.  
  125. -- 检查监控日志,如果错误，则说明有问题（如果不指定的话  监控用户 默认是monitor用户）
  126. mysql@proxysql> SELECT hostname,port,from_unixtime(time_start_us/1000/1000),connect_success_time_us,connect_error FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;
  127. +---------------+------+----------------------------------------+-------------------------+------------------------------------------------------------------------+
  128. | hostname      | port | from_unixtime(time_start_us/1000/1000) | connect_success_time_us | connect_error                                                          |
  129. +---------------+------+----------------------------------------+-------------------------+------------------------------------------------------------------------+
  130. | 192.168.51.116| 3306 | 2022-04-25 08:43:56                    | 2161                    | NULL                                                                   |
  131. | 192.168.51.116| 3306 | 2022-04-25 08:42:56                    | 1437                    | NULL                                                                   |
  132. | 192.168.51.116| 3306 | 2022-04-25 08:41:56                    | 1237                    | NULL                                                                   |
  133. | 192.168.51.116| 3306 | 2022-04-25 08:40:56                    | 1114                    | NULL                                                                   |
  134. | 192.168.51.116| 3306 | 2022-04-25 08:40:04                    | 0                       | Access denied for user 'monitor'@'10.205.16.116' (using password: YES) |
  135. | 192.168.51.116| 3306 | 2022-04-25 08:39:04                    | 0                       | Access denied for user 'monitor'@'10.205.16.116' (using password: YES) |
  136. +---------------+------+----------------------------------------+-------------------------+------------------------------------------------------------------------+
  137.  
  138. mysql@proxysql>  SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 6;
  139. +--------------+------+------------------+----------------------+------------+
  140. | hostname     | port | time_start_us    | ping_success_time_us | ping_error |
  141. +--------------+------+------------------+----------------------+------------+
  142. | 192.168.51.21 | 3306 | 1650713803457556 | 61                   | NULL       |
  143. | 192.168.51.22 | 3306 | 1650713803298807 | 150                  | NULL       |
  144. | 192.168.51.22 | 3306 | 1650713793451431 | 217                  | NULL       |
  145. | 192.168.51.21 | 3306 | 1650713793298661 | 113                  | NULL       |
  146. | 192.168.51.22 | 3306 | 1650713783412590 | 227                  | NULL       |
  147. | 192.168.51.21 | 3306 | 1650713783298560 | 143                  | NULL       |
  148. +--------------+------+------------------+----------------------+------------+
  149.  
  150. -- 插入组类定义
  151.  
  152. mysql@proxysql> show create table mysql_replication_hostgroups \G
  153. *************************** 1. row ***************************
  154.        table: mysql_replication_hostgroups
  155. Create Table: CREATE TABLE mysql_replication_hostgroups (
  156.     writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
  157.     reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
  158.     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',
  159.     comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
  160.  
  161. -- 第一个是写组ID，读组ID，校验类型（上面几种任意一种），注释（自定义）
  162. insert into mysql_replication_hostgroups values(10,20,'read_only','mysql2122');
  163.  
  164. -- 生效配置
  165. load mysql servers to runtime;
  166. save mysql servers to disk;
  167.  
  168. -- 一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组
  169.  
  170. mysql> select * from mysql_servers\G
  171. *************************** 1. row ***************************
  172.        hostgroup_id: 10
  173.            hostname: 192.168.51.21
  174.                port: 3306
  175.           gtid_port: 0
  176.              status: ONLINE
  177.              weight: 1
  178.         compression: 0
  179.     max_connections: 1000
  180. max_replication_lag: 0
  181.             use_ssl: 0
  182.      max_latency_ms: 0
  183.             comment: 
  184. *************************** 2. row ***************************
  185.        hostgroup_id: 20  --检测到51.22节点 read_only=ON时，会变成20
  186.            hostname: 192.168.51.22
  187.                port: 3306
  188.           gtid_port: 0
  189.              status: ONLINE
  190.              weight: 1
  191.         compression: 0
  192.     max_connections: 1000
  193. max_replication_lag: 0
  194.             use_ssl: 0
  195.      max_latency_ms: 0
  196.             comment: 
  197.  
  198. -- readonly 监控
  199. mysql> select * from mysql_server_read_only_log order by time_start_us desc limit 6;
  200. +--------------+------+------------------+-----------------+-----------+-------+
  201. | hostname     | port | time_start_us    | success_time_us | read_only | error |
  202. +--------------+------+------------------+-----------------+-----------+-------+
  203. | 192.168.51.22 | 3306 | 1650766001921374 | 240             | 1         | NULL  |
  204. | 192.168.51.21 | 3306 | 1650766001903998 | 230             | 0         | NULL  |
  205. | 192.168.51.22 | 3306 | 1650766000433229 | 294             | 1         | NULL  |
  206. | 192.168.51.21 | 3306 | 1650766000403933 | 156             | 0         | NULL  |
  207. | 192.168.51.22 | 3306 | 1650765998927168 | 302             | 1         | NULL  |
  208. | 192.168.51.21 | 3306 | 1650765998903824 | 229             | 0         | NULL  |
  209. +--------------+------+------------------+-----------------+-----------+-------+
  210.  
  211. -- 设置最大lag记录（建议不设置，会影响读写路由规则）
  212. -- 如果延迟超过1秒，如果设置路由规则，则读失败，例如：
  213. -- mysql> select @@server_id;
  214. -- ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 20 after 10000ms
  215.  
  216. -- mysql> UPDATE mysql_servers SET max_replication_lag = 1 WHERE hostgroup_id=20;
  217.  
  218. -- mysql> LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
  219.  
  220. -- 只要超过lag阈值就会记录
  221. -- 也会记录到runtime_mysql_servers
  222. mysql> SELECT * FROM runtime_mysql_servers\G
  223. *************************** 1. row ***************************
  224.        hostgroup_id: 10
  225.            hostname: 192.168.51.21
  226.                port: 3306
  227.           gtid_port: 0
  228.              status: ONLINE
  229.              weight: 1
  230.         compression: 0
  231.     max_connections: 1000
  232. max_replication_lag: 0
  233.             use_ssl: 0
  234.      max_latency_ms: 0
  235.             comment: 
  236. *************************** 2. row ***************************
  237.        hostgroup_id: 20
  238.            hostname: 192.168.51.22
  239.                port: 3306
  240.           gtid_port: 0
  241.              status: SHUNNED  -- 状态异常
  242.              weight: 1
  243.         compression: 0
  244.     max_connections: 1000
  245. max_replication_lag: 1
  246.             use_ssl: 0
  247.      max_latency_ms: 0
  248.             comment: 
  249. 2 rows in set (0.00 sec)
  250.  
  251. -- 记录到mysql_server_replication_lag_log （默认采集周期 基于变量mysql-monitor_replication_lag_interval）
  252. mysql> select * from mysql_server_replication_lag_log limit 6;
  253. +--------------+------+------------------+-----------------+----------+---------------------------------------------------------------------------------------------------------+
  254. | hostname     | port | time_start_us    | success_time_us | repl_lag | error                                                                                                   |
  255. +--------------+------+------------------+-----------------+----------+---------------------------------------------------------------------------------------------------------+
  256. | 192.168.51.22 | 3306 | 1650767114031586 | 3023            | 1        | NULL                                                                                                    |
  257. | 192.168.51.22 | 3306 | 1650767124031670 | 0               | NULL     | Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation |
  258. | 192.168.51.22 | 3306 | 1650767134031768 | 2854            | 1        | NULL                                                                                                    |
  259. | 192.168.51.22 | 3306 | 1650767144031900 | 2950            | 1        | NULL                                                                                                    |
  260. | 192.168.51.22 | 3306 | 1650767154031913 | 2577            | 1        | NULL                                                                                                    |
  261. | 192.168.51.22 | 3306 | 1650767164032111 | 3236            | 0        | NULL                                                                                                    |
  262. +--------------+------+------------------+-----------------+----------+---------------------------------------------------------------------------------------------------------+
  263. 6 rows in set (0.00 sec)
  264.  
  265. -- 每次修改proxysql配置，都需要执行load 和 save操作 否则不生效

配置MySQL用户

  1. -- MySQL创建数据库，用户并授权
  2. mysql>  create database test;
  3. mysql>  CREATE USER 'test'@'%' IDENTIFIED BY 'test';
  4. mysql>  GRANT ALL ON test.* TO 'test'@'%';
  5.  
  6. -- 后端是MySQL8.0的话 需要修改环境变量
  7. -- 不然连接时，会报query_cache_size的错误
  8. update global_variables set variable_value="8.0.4 (ProxySQL)" where variable_name='mysql-server_version';
  9. load mysql variables to run;save mysql variables to disk;
  10.  
  11.  
  12. -- caching_sha2_password密码支持，否则只能用mysql_native_password
  13. update global_variables set variable_value='false' where variable_name='admin-hash_passwords';
  14.  
  15. load admin variables to runtime;
  16. save admin variables to disk;
  17.  
  18. -- 这样的话 MySQL密码在proxysql是明文存放的
  19.  
  20.  
  21. -- 插入MySQL的用户和密码default_hostgroup必须与mysql_servers的hostgroup_id一致
  22. mysql@proxysql> INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('test','test','10');
  23. Query OK, 1 row affected (0.00 sec)
  24.  
  25. -- 存储到磁盘
  26. mysql@proxysql> LOAD MYSQL USERS TO RUNTIME;
  27. mysql@proxysql> SAVE MYSQL USERS TO DISK;
  28.  
  29. mysql@proxysql> SAVE MYSQL USERS FROM RUNTIME; -- 加密密码
  30.  
  31. mysql@proxysql> select * from mysql_users\G
  32. *************************** 1. row ***************************
  33.               username: test
  34.               password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 -- 加密密码
  35.                 active: 1   -- 只有active=1的用户才是有效的用户
  36.                use_ssl: 0
  37.      default_hostgroup: 10
  38.         default_schema: NULL
  39.          schema_locked: 0
  40. transaction_persistent: 1
  41.           fast_forward: 0
  42.                backend: 1
  43.               frontend: 1
  44.        max_connections: 10000
  45.             attributes: 
  46.                comment: 
  47.  
  48. -- 测试连接
  49. root@mysqlclient:/usr/local/share#mysql -utest -ptest -h192.168.51.21 -P6033
  50. mysql: [Warning] Using a password on the command line interface can be insecure.
  51. Welcome to the MySQL monitor.  Commands end with ; or \g.
  52. Your MySQL connection id is 1025
  53. Server version: 5.5.30 (ProxySQL)
  54.  
  55. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  56.  
  57. Oracle is a registered trademark of Oracle Corporation and/or its
  58. affiliates. Other names may be trademarks of their respective
  59. owners.
  60.  
  61. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  62.  
  63. mysql> show databases;
  64. +--------------------+
  65. | Database           |
  66. +--------------------+
  67. | information_schema |
  68. | test               |
  69. +--------------------+
  70. 2 rows in set (0.01 sec)

配置读写分离

  1.  
  2. mysql@proxysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
  3. VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
  4.        (2,1,'^SELECT',20,1);
  5. mysql@proxysql> load mysql query rules to runtime;
  6. mysql@proxysql> save mysql query rules to disk;
  7.  
  8. 查看sql执行日志
  9. mysql> select * FROM stats_mysql_query_digest where digest_text like 'delete%'  order by first_seen desc limit 1\G
  10. *************************** 1. row ***************************
  11.         hostgroup: 10
  12.        schemaname: information_schema
  13.          username: test
  14.    client_address: 
  15.            digest: 0xC66101CC27808EB4
  16.       digest_text: delete from test.sbtest1 limit ?
  17.        count_star: 1
  18.        first_seen: 1650769517
  19.         last_seen: 1650769517
  20.          sum_time: 1871104
  21.          min_time: 1871104
  22.          max_time: 1871104
  23. sum_rows_affected: 200000
  24.     sum_rows_sent: 0
  25. 1 row in set (0.00 sec)
  26.  
  27. 如果从库异常，或者延迟超过设定的值，设置了读写规则就会失败，建议lag 设置为0
  28. mysql> select @@server_id;
  29. ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 20 after 10001ms
  30. mysql> begin;select @@server_id;
  31. Query OK, 0 rows affected (0.00 sec)
  32.  
  33. 但是如果读库挂了，不会自动切到写库去查询
  34.  
  35. mysql> select @@server_id;
  36. ERROR 2013 (HY000): Lost connection to MySQL server during query
  37.  
  38. +-------------+
  39. | @@server_id |
  40. +-------------+
  41. |        5121 |
  42. +-------------+
  43. 1 row in set (0.00 sec)
  44.  
  45. 查看proxysql 日志
  46. root@zp-prd-oracle-51-21:/opt/software#tail -10 /var/lib/proxysql/proxysql.log
  47. 2022-04-24 10:53:04 MySQL_Monitor.cpp:2287:monitor_replication_lag_thread(): [ERROR] Replication lag on server 192.168.51.22:3306 is NULL, using the value 60 (mysql-monitor_slave_lag_when_null)
  48. 2022-04-24 10:53:14 MySQL_Monitor.cpp:2287:monitor_replication_lag_thread(): [ERROR] Replication lag on server 192.168.51.22:3306 is NULL, using the value 60 (mysql-monitor_slave_lag_when_null)
  49. 2022-04-24 10:53:24 MySQL_Monitor.cpp:2287:monitor_replication_lag_thread(): [ERROR] Replication lag on server 192.168.51.22:3306 is NULL, using the value 60 (mysql-monitor_slave_lag_when_null)
  50. 2022-04-24 10:54:34 MySQL_HostGroups_Manager.cpp:2888:get_random_MySrvC(): [ERROR] Hostgroup 20 has no servers available! Checking servers shunned for more than 1 second
  51. 2022-04-24 10:54:36 MySQL_HostGroups_Manager.cpp:2888:get_random_MySrvC(): [ERROR] Hostgroup 20 has no servers available! Checking servers shunned for more than 1 second
  52. 2022-04-24 10:54:38 MySQL_HostGroups_Manager.cpp:2888:get_random_MySrvC(): [ERROR] Hostgroup 20 has no servers available! Checking servers shunned for more than 1 second
  53. 2022-04-24 10:54:40 MySQL_HostGroups_Manager.cpp:2888:get_random_MySrvC(): [ERROR] Hostgroup 20 has no servers available! Checking servers shunned for more than 1 second
  54. 2022-04-24 10:54:42 MySQL_HostGroups_Manager.cpp:2888:get_random_MySrvC(): [ERROR] Hostgroup 20 has no servers available! Checking servers shunned for more than 1 second
  55. 2022-04-24 10:54:44 MySQL_HostGroups_Manager.cpp:2888:get_random_MySrvC(): [ERROR] Hostgroup 20 has no servers available! Checking servers shunned for more than 1 second
  56. 2022-04-24 10:54:44 MySQL_Session.cpp:2594:handler_again___status_CONNECTING_SERVER(): [ERROR] Max connect timeout reached while reaching hostgroup 20 after 10000ms . HG status: [{"Bytes_recv":"27","Bytes_sent":"54","ConnERR":"0","ConnFree":"0","ConnOK":"1","ConnUsed":"0","Latency_us":"637","MaxConnUsed":"1","Queries":"3","Queries_GTID_sync":"0","hostgroup":"20","srv_host":"192.168.51.22","srv_port":"3306","status":"SHUNNED_REPLICATION_LAG"}]
  57.  
  58. -- 增加写库的读群组，并增加从库的权重
  59.  
  60. update mysql_servers set weight=10000000 where hostgroup_id=20 and hostname='192.168.51.22';
  61.  
  62. insert into mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.51.21',3306);
  63.  
  64. load mysql servers to runtime;
  65. save mysql servers to disk;

proxysql集群

  1. -- 查看现有节点
  2. mysql> select * from global_variables where variable_name in ('admin-admin_credentials', 'admin-cluster_password', 'mysql-monitor_password', 'admin-cluster_username', 'mysql-monitor_username');
  3. +-------------------------+----------------+
  4. | variable_name           | variable_value |
  5. +-------------------------+----------------+
  6. | admin-admin_credentials | admin:admin    |
  7. | admin-cluster_password  |                |
  8. | admin-cluster_username  |                |
  9. | mysql-monitor_password  | ProxySQLPa55   |
  10. | mysql-monitor_username  | proxysql       |
  11. +-------------------------+----------------+
  12.  
  13. -- 依次在各个proxySQL节点修改管理员密码，集群用户密码
  14. update global_variables set variable_value='admin:newadmin;cluster:cluster' where variable_name='admin-admin_credentials';
  15. update global_variables set variable_value='cluster' where variable_name='admin-cluster_username';
  16. update global_variables set variable_value='cluster' where variable_name='admin-cluster_password';
  17.  
  18. -- 下面是默认配置，不需要修改（集群同步配置）
  19. update global_variables set variable_value=1000 where variable_name='admin-cluster_check_interval_ms';
  20. update global_variables set variable_value=10 where variable_name='admin-cluster_check_status_frequency';
  21. update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_query_rules_save_to_disk';
  22. update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_servers_save_to_disk';
  23. update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_users_save_to_disk';
  24. update global_variables set variable_value='true' where variable_name='admin-cluster_proxysql_servers_save_to_disk';
  25. update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_query_rules_diffs_before_sync';
  26. update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_servers_diffs_before_sync';
  27. update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_users_diffs_before_sync';
  28. update global_variables set variable_value=3 where variable_name='admin-cluster_proxysql_servers_diffs_before_sync';
  29.  
  30. load admin variables to RUNTIME;
  31. save admin variables to disk;
  32.  
  33.  
  34. INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('192.168.51.21',6032,0,'p1');
  35. INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('192.168.51.22',6032,0,'p2');
  36.  
  37. LOAD PROXYSQL SERVERS TO RUNTIME;
  38. SAVE PROXYSQL SERVERS TO DISK;
  39.  
  40. -- 新节点验证
  41. select * from mysql_query_rules\G
  42. select * from mysql_servers;
  43. select * from mysql_users ;
  44. select * from proxysql_servers;
  45.  
  46. -- 验证集群配置是否已经同步
  47.  
  48. SELECT stats_proxysql_servers_checksums.hostname, stats_proxysql_servers_metrics.Uptime_s, stats_proxysql_servers_checksums.port, stats_proxysql_servers_checksums.name, stats_proxysql_servers_checksums.version, FROM_UNIXTIME(stats_proxysql_servers_checksums.epoch) epoch, stats_proxysql_servers_checksums.checksum, stats_proxysql_servers_checksums.diff_check FROM stats_proxysql_servers_metrics JOIN stats_proxysql_servers_checksums ON stats_proxysql_servers_checksums.hostname = stats_proxysql_servers_metrics.hostname WHERE stats_proxysql_servers_metrics.Uptime_s > 0 ORDER BY name,version desc;
  49. +--------------+----------+------+-------------------+---------+---------------------+--------------------+------------+
  50. | hostname     | Uptime_s | port | name              | version | epoch               | checksum           | diff_check |
  51. +--------------+----------+------+-------------------+---------+---------------------+--------------------+------------+
  52. | 192.168.51.22 | 1899     | 6032 | admin_variables   | 2       | 2022-04-24 09:30:27 | 0x8759E3B35A6FD155 | 0          |
  53. | 192.168.51.21 | 80602    | 6032 | admin_variables   | 2       | 2022-04-24 09:24:44 | 0x8759E3B35A6FD155 | 0          |
  54. | 192.168.51.22 | 1899     | 6032 | mysql_query_rules | 2       | 2022-04-24 09:30:35 | 0x8F7C9C6F2E6449F5 | 0          |
  55. | 192.168.51.21 | 80602    | 6032 | mysql_query_rules | 2       | 2022-04-24 02:40:54 | 0x8F7C9C6F2E6449F5 | 0          |
  56. | 192.168.51.21 | 80602    | 6032 | mysql_servers     | 16      | 2022-04-24 09:32:48 | 0x159BEACEDABA56A2 | 0          |
  57. | 192.168.51.22 | 1899     | 6032 | mysql_servers     | 3       | 2022-04-24 09:32:48 | 0x159BEACEDABA56A2 | 0          |
  58. | 192.168.51.21 | 80602    | 6032 | mysql_users       | 6       | 2022-04-24 02:23:55 | 0x57AFBBC015E19DB5 | 0          |
  59. | 192.168.51.22 | 1899     | 6032 | mysql_users       | 2       | 2022-04-24 09:30:35 | 0x57AFBBC015E19DB5 | 0          |
  60. | 192.168.51.22 | 1899     | 6032 | mysql_variables   | 2       | 2022-04-24 09:30:35 | 0x299483C6D90F1055 | 0          |
  61. | 192.168.51.21 | 80602    | 6032 | mysql_variables   | 2       | 2022-04-23 11:33:33 | 0x299483C6D90F1055 | 0          |
  62. | 192.168.51.22 | 1899     | 6032 | proxysql_servers  | 2       | 2022-04-24 09:30:32 | 0x514523051E515E87 | 0          |
  63. | 192.168.51.21 | 80602    | 6032 | proxysql_servers  | 2       | 2022-04-24 09:25:00 | 0x514523051E515E87 | 0          |
  64. +--------------+----------+------+-------------------+---------+---------------------+--------------------+------------+
  65.  
  66. 相同配置的变量 version大的变量值会把自己数据同步到version小的节点，如果version一样会复制最高的epoch值
  67.

proxysql 集群监控原理

  1. 因为集群间，所有节点都是相互监控的。所以当配置发生变动时，它们可以立即发现。当其他节点的配置发生变动时，本节点会先去检查一次它自身的配置，因为有可能remote instance 和local instance 同时发生配置变动。如果不同：
  2.  
  3. 如果它们自身的 version = 1，就去找集群内 version >1，并且 epoch 最高的节点，并立即同步。
  4. 如果version >1， 该节点开始统计和其他节点间的differ 数。
  5. 当 differ 大于 clustername_diffs_before_sync ， 并且clusternamediffs_before_sync > 0， 就去找集群内 version >1， 并且epoch 最高的节点，并立即同步。
  6. 同步过程如下：
  7.  
  8. 健康检查语句会执行一系列的SELECT 语句，例如 Select list_of_columns FROM runtime_module.
  9. SELECT hostgroup_id, hostname, port, status, weight, compression, max_connections, max_replication_lag, use_ssl, max_latency_ms, comment FROM runtime_mysql_servers;
  10. SELECT writer_hostgroup, reader_hostgroup, comment FROM runtime_mysql_replication_hostgroups;
  11. 删除本地配置。例如：
  12. DELETE FROM mysql_servers;
  13. DELETE FROM mysql_replication_hostgroups;
  14. 将新的配置insert 到本地表
  15. 内部提交LOAD module_name TO RUNTIME。 对应的version 会增加，并且生成一个新的 checksum
  16. 如果clusternamesave_to_disk =true，还会内部执行 SAVE module_name TO DISK。

proxysql配置MySQL用户

  1. CREATE USER 'test'@'%' IDENTIFIED BY 'test';
  2. GRANT ALL ON test.* TO 'test'@'%';
  3.  
  4. -- 当MySQL创建好用户，proxysql插入记录
  5. INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('test','test','10');
  6.  
  7. -- 如果是只读用户，将组设置为20
  8. INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('test','test','20');
  9.  
  10. -- proxysql生效记录
  11. LOAD MYSQL USERS TO RUNTIME;
  12. SAVE MYSQL USERS TO DISK;

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/230577
推荐阅读
相关标签

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

  

闽ICP备14008679号