Mariadb之主从复制的读写分离

前端之家收集整理的这篇文章主要介绍了Mariadb之主从复制的读写分离前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

  首先我们来回顾下代理的概念,所谓代理就是指的是一端面向客户端,另外一端面向服务端,代理客户端访问服务端,我们把这种代理叫正向代理;代理服务端响应客户端我们叫做反向代理,这个我们在之前Nginx系列博客中阐述过这样的概念;不管是正向代理还是反向代理他们都是代理,他们都有一个共同点就是代表一端(客户端/服务端)访问或响应另一端;简单讲代理就是即充当服务端角色又充当客户端角色;在mariadb的主从复制集群中,读的能力被扩展了,而写的能力始终没有被扩展;这样一来对于主服务器就存在单点的问题,通常除了做双主可解决主节点单点的问题,我们还可以给主节点做高可用;而对于mariadb的主从复制集群来讲,虽然读的能力提升了,但通常情况后端数据库服务器是直接面向程序,这意味着程序要知道读请求和写请求该发往不同的数据库服务器上;在用户发来读请求,这个程序它会分析用户的请求,然后把用户的请求代理到后端server上;也就是说我们需要一个程序能够解析用户的读写操作,把对应的操作代理到后端不同的节点上;这样一来用户的读操作始终均衡的被调度到从节点,写操作调度到主节点;proxysql这款软件就有我们上面说的功能,它能够将用户发来的读写操作,通过proxysql的语句路由,把对应请求分别发送到不同节点执行;如下图所示:

  从上面的图片可以看到,proxysql就是一代理,面向程序它就是一数据库服务器,程序把读操作和写操作都发送给它,然后proxysql通过我们定义的路由规则,把对应语句再代理到不同的后端主从架构节点上执行;接下来我们来看看proxysql的配置和使用吧;以下实验室基于mariadb的主从复制集群上做的,有关主从复制的配置请参考https://www.cnblogs.com/qiuhom-1874/tag/mariadb%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/

  1、安装proxysql(添加yum源,直接用yum安装)

  2、查看proxysql的简介

  1. [root@lxc ~]# yum info proxysql
  2. Loaded plugins: fastestmirror
  3. Loading mirror speeds from cached hostfile
  4. * base: mirrors.aliyun.com
  5. * extras: mirrors.aliyun.com
  6. * updates: mirrors.aliyun.com
  7. Available Packages
  8. Name : proxysql
  9. Arch : x86_64
  10. Version : 2.0.12
  11. Release : 1
  12. Size : 9.8 M
  13. Repo : proxysql_repo/7
  14. Summary : A high-performance MysqL proxy
  15. URL : https://proxysql.com/
  16. License : GPL+
  17. Description : A high-performance MysqL proxy
  18.  
  19. [root@lxc ~]#

  提示:如果添加了yum直接yum info proxysql能够看到以上信息,表示我们添加到yum源已经生效;

  3、安装proxysql

  1. [root@lxc ~]# yum install proxysql
  2. Loaded plugins: fastestmirror
  3. proxysql_repo | 2.9 kB 00:00:00
  4. Loading mirror speeds from cached hostfile
  5. * base: mirrors.aliyun.com
  6. * extras: mirrors.aliyun.com
  7. * updates: mirrors.aliyun.com
  8. Resolving Dependencies
  9. --> Running transaction check
  10. ---> Package proxysql.x86_64 0:2.0.12-1 will be installed
  11. --> Processing Dependency: libgnutls.so.28(GNUTLS_3_1_0)(64bit) for package: proxysql-2.0.12-1.x86_64
  12. --> Processing Dependency: libgnutls.so.28(GNUTLS_3_0_0)(64bit) for package: proxysql-2.0.12-1.x86_64
  13. --> Processing Dependency: gnutls for package: proxysql-2.0.12-1.x86_64
  14. --> Processing Dependency: libgnutls.so.28(GNUTLS_1_4)(64bit) for package: proxysql-2.0.12-1.x86_64
  15. --> Processing Dependency: libgnutls.so.28()(64bit) for package: proxysql-2.0.12-1.x86_64
  16. --> Running transaction check
  17. ---> Package gnutls.x86_64 0:3.3.29-9.el7_6 will be installed
  18. --> Processing Dependency: trousers >= 0.3.11.2 for package: gnutls-3.3.29-9.el7_6.x86_64
  19. --> Processing Dependency: libnettle.so.4()(64bit) for package: gnutls-3.3.29-9.el7_6.x86_64
  20. --> Processing Dependency: libhogweed.so.2()(64bit) for package: gnutls-3.3.29-9.el7_6.x86_64
  21. --> Running transaction check
  22. ---> Package nettle.x86_64 0:2.7.1-8.el7 will be installed
  23. ---> Package trousers.x86_64 0:0.3.14-2.el7 will be installed
  24. --> Finished Dependency Resolution
  25.  
  26. Dependencies Resolved
  27.  
  28. =====================================================================================================================================================================
  29. Package Arch Version Repository Size
  30. =====================================================================================================================================================================
  31. Installing:
  32. proxysql x86_64 2.0.12-1 proxysql_repo 9.8 M
  33. Installing for dependencies:
  34. gnutls x86_64 3.3.29-9.el7_6 base 680 k
  35. nettle x86_64 2.7.1-8.el7 base 327 k
  36. trousers x86_64 0.3.14-2.el7 base 289 k
  37.  
  38. Transaction Summary
  39. =====================================================================================================================================================================
  40. Install 1 Package (+3 Dependent packages)
  41.  
  42. Total download size: 11 M
  43. Installed size: 41 M
  44. Is this ok [y/d/N]: y
  45. Downloading packages:
  46. (1/4): gnutls-3.3.29-9.el7_6.x86_64.rpm | 680 kB 00:00:00
  47. (2/4): nettle-2.7.1-8.el7.x86_64.rpm | 327 kB 00:00:00
  48. (3/4): trousers-0.3.14-2.el7.x86_64.rpm | 289 kB 00:00:00
  49. warning: /var/cache/yum/x86_64/7/proxysql_repo/packages/proxysql-2.0.12-1-centos7.x86_64.rpm: Header V4 RSA/SHA256 Signature,key ID 79953b49: NOKEYMB 00:00:04 ETA
  50. Public key for proxysql-2.0.12-1-centos7.x86_64.rpm is not installed
  51. (4/4): proxysql-2.0.12-1-centos7.x86_64.rpm | 9.8 MB 00:28:05
  52. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
  53. Total 6.7 kB/s | 11 MB 00:28:05
  54. Retrieving key from https://repo.proxysql.com/Proxysql/repo_pub_key
  55. Importing GPG key 0x79953B49:
  56. Userid : "rene cannnao (Proxysql Repository) <rene.cannao@gmail.com>"
  57. Fingerprint: 1448 bf69 3ca6 00c7 99eb 9358 04a5 62fb 7995 3b49
  58. From : https://repo.proxysql.com/Proxysql/repo_pub_key
  59. Is this ok [y/N]: y
  60. Running transaction check
  61. Running transaction test
  62. Transaction test succeeded
  63. Running transaction
  64. Installing : trousers-0.3.14-2.el7.x86_64 1/4
  65. Installing : nettle-2.7.1-8.el7.x86_64 2/4
  66. Installing : gnutls-3.3.29-9.el7_6.x86_64 3/4
  67. Installing : proxysql-2.0.12-1.x86_64 4/4
  68. warning: group proxysql does not exist - using root
  69. warning: group proxysql does not exist - using root
  70. Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
  71. Verifying : gnutls-3.3.29-9.el7_6.x86_64 1/4
  72. Verifying : proxysql-2.0.12-1.x86_64 2/4
  73. Verifying : nettle-2.7.1-8.el7.x86_64 3/4
  74. Verifying : trousers-0.3.14-2.el7.x86_64 4/4
  75.  
  76. Installed:
  77. proxysql.x86_64 0:2.0.12-1
  78.  
  79. Dependency Installed:
  80. gnutls.x86_64 0:3.3.29-9.el7_6 nettle.x86_64 0:2.7.1-8.el7 trousers.x86_64 0:0.3.14-2.el7
  81.  
  82. Complete!
  83. [root@lxc ~]#

  提示:除此以上方式安装proxysql,当然也可以直接使用wget 去github上的项目地址下载

  1. [root@lxc ~]# wget https://github.com/sysown/proxysql/releases/download/v2.0.12/proxysql-2.0.12-1-centos7.x86_64.rpm

  提示:下载好proxysql 包后,然后通过yum直接安装即可;推荐使用yum来安装下载好的包,它可以解决依赖关系,不推荐使用rpm 安装;

  4、查看proxysql包安装的文件列表

  1. [root@lxc ~]# rpm -ql proxysql
  2. /etc/logrotate.d/proxysql
  3. /etc/proxysql.cnf
  4. /etc/systemd/system/proxysql-initial.service
  5. /etc/systemd/system/proxysql.service
  6. /usr/bin/proxysql
  7. /usr/share/proxysql/tools/proxysql_galera_checker.sh
  8. /usr/share/proxysql/tools/proxysql_galera_writer.pl
  9. [root@lxc ~]#

  提示:从上面的信息可以看到proxysql配置文件是/etc/proxysql.cnf,二进制文件是/usr/bin/proxysql,unit file是/etc/systemd/system/proxysql.service

  5、配置proxysql

  1. [root@lxc ~]# cat /etc/proxysql.cnf
  2. ########################################################################################
  3.  
  4. datadir="/var/lib/proxysql"
  5. errorlog="/var/lib/proxysql/proxysql.log"
  6.  
  7. admin_variables=
  8. {
  9. admin_credentials="admin:admin"
  10. # MysqL_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
  11. MysqL_ifaces="0.0.0.0:6033"
  12. # refresh_interval=2000
  13. # debug=true
  14. }
  15.  
  16. MysqL_variables=
  17. {
  18. threads=4
  19. max_connections=2048
  20. default_query_delay=0
  21. default_query_timeout=36000000
  22. have_compress=true
  23. poll_timeout=2000
  24. # interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
  25. interfaces="0.0.0.0:3306"
  26. default_schema="information_schema"
  27. stacksize=1048576
  28. server_version="5.5.30"
  29. connect_timeout_server=3000
  30. # make sure to configure monitor username and password
  31. # https://github.com/sysown/proxysql/wiki/Global-variables#MysqL-monitor_username-MysqL-monitor_password
  32. monitor_username="monitor"
  33. monitor_password="monitor"
  34. monitor_history=600000
  35. monitor_connect_interval=60000
  36. monitor_ping_interval=10000
  37. monitor_read_only_interval=1500
  38. monitor_read_only_timeout=500
  39. ping_interval_server_msec=120000
  40. ping_timeout_server=500
  41. commands_stats=true
  42. sessions_sort=true
  43. connect_retries_on_failure=10
  44. }
  45.  
  46.  
  47. # defines all the MysqL servers
  48. MysqL_servers =
  49. (
  50. {
  51. address = "192.168.0.22" # no default,required . If port is 0,address is interpred as a Unix Socket Domain
  52. port = 3306 # no default,address is interpred as a Unix Socket Domain
  53. hostgroup = 3 # no default,required
  54. status = "ONLINE" # default: ONLINE
  55. weight = 1 # default: 1
  56. compression = 0 # default: 0
  57. max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold,the server is shunned
  58. },{
  59. address = "192.168.0.23" # no default,address is interpred as a Unix Socket Domain
  60. hostgroup = 4 # no default,the server is shunned
  61. }
  62. )
  63.  
  64.  
  65. # defines all the MysqL users
  66. MysqL_users:
  67. (
  68. {
  69. username = "root" # no default,required
  70. password = "admin123.com" # default: ''
  71. default_hostgroup = 3 # default: 0
  72. active = 1 # default: 1
  73. }
  74. )
  75.  
  76.  
  77.  
  78. #defines MysqL Query Rules
  79. MysqL_query_rules:
  80. (
  81. {
  82. rule_id=1
  83. active=1
  84. match_pattern="^SELECT .* FOR UPDATE$"
  85. destination_hostgroup=3
  86. apply=1
  87. },{
  88. rule_id=2
  89. active=1
  90. match_pattern="^SELECT"
  91. destination_hostgroup=4
  92. apply=1
  93. }
  94. )
  95.  
  96.  
  97.  
  98. MysqL_replication_hostgroups=
  99. (
  100. {
  101. writer_hostgroup=3
  102. reader_hostgroup=4
  103. comment="test repl 1"
  104. }
  105. )
  106.  
  107. [root@lxc ~]#

  提示:proxysql配置文件分几个大段,admin_variables这个段里主要配置管理相关变量,比如管理用户名和密码,监听的地址和端口等等;MysqL_variables配置proxysql连接后端MysqL相关配置,里面有连接后端的server监听的端口,线程数,最大连接数,以及监控相关参数;MysqL_servers配置后端MysqL/mariadb服务器的地址端口,以及权重,所属组等;MysqL_users配置连接后端MysqL/mariadb所需的账号和密码以及默认连接到的组;MysqL_query_rules配置查询规则,那些语句是写操作,那些语句是读操作;MysqL_replication_hostgroups配置写操作对应的组号和读操作对应组号;这里需要注意一点,如果一个配置段中有多个大括号,相互之间用逗号隔离,如果是最后一个大括号需要把后面的逗号去掉;

  6、在主从复制节点创建上面配置的账号信息

  创建root账号

  1. [root@docker_node01 ~]# MysqL
  2. Welcome to the MariaDB monitor. Commands end with ; or \g.
  3. Your MariaDB connection id is 9
  4. Server version: 5.5.65-MariaDB MariaDB Server
  5.  
  6. Copyright (c) 2000,2018,Oracle,MariaDB Corporation Ab and others.
  7.  
  8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  9.  
  10. MariaDB [(none)]> select user,host,password from MysqL.user;
  11. +---------+-------------------+-------------------------------------------+
  12. | user | host | password |
  13. +---------+-------------------+-------------------------------------------+
  14. | root | localhost | |
  15. | root | docker\_node01.io | |
  16. | root | 127.0.0.1 | |
  17. | root | ::1 | |
  18. | rpluser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
  19. +---------+-------------------+-------------------------------------------+
  20. 5 rows in set (0.00 sec)
  21.  
  22. MariaDB [(none)]> grant all on *.* to 'root'@'192.168.0.%' identified by 'admin123.com' with grant option;
  23. Query OK,0 rows affected (0.01 sec)
  24.  
  25. MariaDB [(none)]> select user,password from MysqL.user;
  26. +---------+-------------------+-------------------------------------------+
  27. | user | host | password |
  28. +---------+-------------------+-------------------------------------------+
  29. | root | localhost | |
  30. | root | docker\_node01.io | |
  31. | root | 127.0.0.1 | |
  32. | root | ::1 | |
  33. | rpluser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
  34. | root | 192.168.0.% | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
  35. +---------+-------------------+-------------------------------------------+
  36. 6 rows in set (0.00 sec)
  37.  
  38. MariaDB [(none)]>

  提示:在主节点创建账号会自动同步到从节点,所以从节点可以不用再创建;

  创建monitor账号

  1. MariaDB [(none)]> grant all on *.* to 'monitor'@'192.168.0.%' identified by 'monitor' with grant option;
  2. Query OK,password from MysqL.user;
  3. +---------+-------------------+-------------------------------------------+
  4. | user | host | password |
  5. +---------+-------------------+-------------------------------------------+
  6. | root | localhost | |
  7. | root | docker\_node01.io | |
  8. | root | 127.0.0.1 | |
  9. | root | ::1 | |
  10. | rpluser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
  11. | root | 192.168.0.% | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
  12. | monitor | 192.168.0.% | *1975D095AC033CAF4E1BF94F7202A9BBFEEB66F1 |
  13. +---------+-------------------+-------------------------------------------+
  14. 7 rows in set (0.00 sec)
  15.  
  16. MariaDB [(none)]>

  7、启动proxysql

  提示:可以看到我们配置的proxysql管理端监听在6033,启动4个线程来连接后端mariadb服务器,对应的端口都已经处于监听状态了;

  8、连接管理端口

  1. [root@lxc ~]# MysqL -uadmin -padmin -h192.168.0.21 -P6033
  2. ERROR 1040 (42000): User 'admin' can only connect locally
  3. [root@lxc ~]# MysqL -uadmin -padmin -h127.0.0.1 -P6033
  4. Welcome to the MariaDB monitor. Commands end with ; or \g.
  5. Your MysqL connection id is 7
  6. Server version: 5.5.30 (Proxysql Admin Module)
  7.  
  8. Copyright (c) 2000,MariaDB Corporation Ab and others.
  9.  
  10. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  11.  
  12. MysqL [(none)]> show databases;
  13. +-----+---------------+-------------------------------------+
  14. | seq | name | file |
  15. +-----+---------------+-------------------------------------+
  16. | 0 | main | |
  17. | 2 | disk | /var/lib/proxysql/proxysql.db |
  18. | 3 | stats | |
  19. | 4 | monitor | |
  20. | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
  21. +-----+---------------+-------------------------------------+
  22. 5 rows in set (0.001 sec)
  23.  
  24. MysqL [(none)]>

  提示:管理接口默认只能本地连接,但是我们配置管理接口监听在本机所有地址上,所以连接时指定主机地址要使用127.0.0.1才可以,如果使用localhost,它默认会通过sock文件去连接;从上面的结果可以看到,在proxysql上有5个库,其中main库主要存放我们刚才配置相关的表在里面;如下

  1. MysqL [(none)]> use main
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  4.  
  5. Database changed
  6. MysqL [main]> show tables;
  7. +----------------------------------------------------+
  8. | tables |
  9. +----------------------------------------------------+
  10. | global_variables |
  11. | MysqL_aws_aurora_hostgroups |
  12. | MysqL_collations |
  13. | MysqL_firewall_whitelist_rules |
  14. | MysqL_firewall_whitelist_sqli_fingerprints |
  15. | MysqL_firewall_whitelist_users |
  16. | MysqL_galera_hostgroups |
  17. | MysqL_group_replication_hostgroups |
  18. | MysqL_query_rules |
  19. | MysqL_query_rules_fast_routing |
  20. | MysqL_replication_hostgroups |
  21. | MysqL_servers |
  22. | MysqL_users |
  23. | proxysql_servers |
  24. | restapi_routes |
  25. | runtime_checksums_values |
  26. | runtime_global_variables |
  27. | runtime_MysqL_aws_aurora_hostgroups |
  28. | runtime_MysqL_firewall_whitelist_rules |
  29. | runtime_MysqL_firewall_whitelist_sqli_fingerprints |
  30. | runtime_MysqL_firewall_whitelist_users |
  31. | runtime_MysqL_galera_hostgroups |
  32. | runtime_MysqL_group_replication_hostgroups |
  33. | runtime_MysqL_query_rules |
  34. | runtime_MysqL_query_rules_fast_routing |
  35. | runtime_MysqL_replication_hostgroups |
  36. | runtime_MysqL_servers |
  37. | runtime_MysqL_users |
  38. | runtime_proxysql_servers |
  39. | runtime_restapi_routes |
  40. | runtime_scheduler |
  41. | scheduler |
  42. +----------------------------------------------------+
  43. 32 rows in set (0.001 sec)
  44.  
  45. MysqL [main]> select * from MysqL_servers;
  46. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  47. | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  48. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  49. | 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  50. | 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  51. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  52. 2 rows in set (0.001 sec)
  53.  
  54. MysqL [main]>

  提示:main库中的表以runtime开头的表示当前生效的配置,如果我们要运行时修改配置,我们需要先修改不是runtime开头的表,然后通过load 加载到runtime开头的表中,然后在save存储到文件

  proxysql的多层配置系统

  1. +-------------------------+
  2. | RUNTIME |
  3. +-------------------------+
  4. /|\ |
  5. | |
  6. [1] | [2] |
  7. | \|/
  8. +-------------------------+
  9. | MEMORY |
  10. +-------------------------+ _
  11. /|\ | |\
  12. | | \
  13. [3] | [4] | \ [5]
  14. | \|/ \
  15. +-------------------------+ +-------------------------+
  16. | DISK | | CONFIG FILE |
  17. +-------------------------+ +-------------------------+

  提示:从上面的图可以看到proxysql的配置系统分三层,最上面一层是runtime层,该层主要是说通过读取第二层的memory层,而memory主要通过disk或configfile层来获取配置;第一次启动proxysql时,它会从config file层读取配置文件内容到memory层,然后runtime层读取memory层,随后我们就可以通过修改memory层,然后通过load加载到runtime层,或者save存储到disk层;

  在各层间移动配置

  1. MysqL user相关操作:
  2. LOAD MysqL USERS FROM MEMORY / LOAD MysqL USERS TO RUNTIME
  3. MysqL user从内存数据库加载到运行时数据结构,反之亦然
  4. SAVE MysqL USERS TO MEMORY / SAVE MysqL USERS FROM RUNTIME
  5. MysqL user从运行时持久化到内存数据库
  6. LOAD MysqL USERS TO MEMORY / LOAD MysqL USERS FROM DISK
  7. 将持久化的MysqL user从磁盘数据库加载到内存数据库
  8. SAVE MysqL USERS FROM MEMORY / SAVE MysqL USERS TO DISK
  9. MysqL user从内存数据库持久化到磁盘数据库
  10. LOAD MysqL USERS FROM CONFIG
  11. 配置文件中将MysqL user加载到内存数据库
  12.  
  13. MysqL server相关操作:
  14. LOAD MysqL SERVERS FROM MEMORY / LOAD MysqL SERVERS TO RUNTIME
  15. MysqL server从内存数据库加载到运行时
  16. SAVE MysqL SERVERS TO MEMORY / SAVE MysqL SERVERS FROM RUNTIME
  17. MysqL server从运行时持久化到内存数据库
  18. LOAD MysqL SERVERS TO MEMORY / LOAD MysqL SERVERS FROM DISK
  19. MysqL server从磁盘数据库加载到内存数据库
  20. SAVE MysqL SERVERS FROM MEMORY / SAVE MysqL SERVERS TO DISK
  21. MysqL server从内存数据库持久化到磁盘数据库
  22. LOAD MysqL SERVERS FROM CONFIG
  23. 配置文件将服务器加载到内存数据库
  24.  
  25. MysqL query rules相关操作:
  26. LOAD MysqL QUERY RULES FROM MEMORY / LOAD MysqL QUERY RULES TO RUNTIME
  27. MySQL查询规则从内存数据库加载到运行时数据结构
  28. SAVE MysqL QUERY RULES TO MEMORY / SAVE MysqL QUERY RULES FROM RUNTIME
  29. MySQL查询规则从运行时数据结构持久化到内存数据库
  30. LOAD MysqL QUERY RULES TO MEMORY / LOAD MysqL QUERY RULES FROM DISK
  31. MySQL查询规则从磁盘数据库加载到内存数据库
  32. SAVE MysqL QUERY RULES FROM MEMORY / SAVE MysqL QUERY RULES TO DISK
  33. MySQL查询规则从内存数据库持久化到磁盘数据库
  34. LOAD MysqL QUERY RULES FROM CONFIG
  35. 配置文件查询规则加载到内存数据库
  36.  
  37. MysqL variables相关操作:
  38. LOAD MysqL VARIABLES FROM MEMORY / LOAD MysqL VARIABLES TO RUNTIME
  39. MysqL变量从内存数据库加载到运行时数据结构
  40. SAVE MysqL VARIABLES FROM MEMORY / SAVE MysqL VARIABLES TO DISK
  41. MysqL变量从内存数据库持久化到磁盘数据库
  42. LOAD MysqL VARIABLES TO MEMORY / LOAD MysqL VARIABLES FROM DISK
  43. MysqL变量从磁盘数据库加载到内存数据库
  44. SAVE MysqL VARIABLES TO MEMORY / SAVE MysqL VARIABLES FROM RUNTIME
  45. MysqL变量从运行时数据结构持久存储到内存数据库
  46. LOAD MysqL VARIABLES FROM CONFIG
  47. 配置文件变量加载到内存数据库
  48.  
  49. admin variables相关操作:
  50. LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
  51. 将内存数据库中的管理变量加载到运行时
  52. SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
  53. 将管理变量从运行时持久化到内存数据库
  54. LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
  55. 将管理变量从磁盘数据库加载到内存数据库
  56. SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
  57. 将管理变量从内存数据库持久化到磁盘数据库
  58. LOAD ADMIN VARIABLES FROM CONFIG
  59. 配置文件管理变量加载到内存数据库
  60.  
  61.  
  62. 注意:以上命令允许使用以下快捷方式:
  63. MEM for MEMORY
  64. RUN for RUNTIME
  65.  
  66. 例如,这两个命令是等效的:
  67. SAVE ADMIN VARIABLES TO MEMORY
  68. SAVE ADMIN VARIABLES TO MEM

  提示:以上命令在配置更改加载到RUNTIME之前不会激活任何修改,也就是说只有runtime中的配置生效;

  测试:在线增加后端MysqL server的地址

  1. [root@lxc ~]# MysqL -uadmin -padmin -h127.0.0.1 -P6033
  2. Welcome to the MariaDB monitor. Commands end with ; or \g.
  3. Your MysqL connection id is 3
  4. Server version: 5.5.30 (Proxysql Admin Module)
  5.  
  6. Copyright (c) 2000,MariaDB Corporation Ab and others.
  7.  
  8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  9.  
  10. MysqL [(none)]> select * from MysqL_servers;
  11. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  12. | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  13. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  14. | 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  15. | 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  16. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  17. 2 rows in set (0.001 sec)
  18.  
  19. MysqL [(none)]> insert into MysqL_servers values (4,'192.168.0.21',3307,'ONLINE',1,1000,10,'');
  20. Query OK,1 row affected (0.001 sec)
  21.  
  22. MysqL [(none)]> select * from MysqL_servers;
  23. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  24. | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  25. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  26. | 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  27. | 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  28. | 4 | 192.168.0.21 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  29. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  30. 3 rows in set (0.001 sec)
  31.  
  32. MysqL [(none)]>

  提示:以上操作只在memory层中修改,并未加载到runtime,所以在runtime_MysqL_servers表中还是两台后端server

  加载MysqL server memory层到runtime层

  1. MysqL [(none)]> show tables;
  2. +----------------------------------------------------+
  3. | tables |
  4. +----------------------------------------------------+
  5. | global_variables |
  6. | MysqL_aws_aurora_hostgroups |
  7. | MysqL_collations |
  8. | MysqL_firewall_whitelist_rules |
  9. | MysqL_firewall_whitelist_sqli_fingerprints |
  10. | MysqL_firewall_whitelist_users |
  11. | MysqL_galera_hostgroups |
  12. | MysqL_group_replication_hostgroups |
  13. | MysqL_query_rules |
  14. | MysqL_query_rules_fast_routing |
  15. | MysqL_replication_hostgroups |
  16. | MysqL_servers |
  17. | MysqL_users |
  18. | proxysql_servers |
  19. | restapi_routes |
  20. | runtime_checksums_values |
  21. | runtime_global_variables |
  22. | runtime_MysqL_aws_aurora_hostgroups |
  23. | runtime_MysqL_firewall_whitelist_rules |
  24. | runtime_MysqL_firewall_whitelist_sqli_fingerprints |
  25. | runtime_MysqL_firewall_whitelist_users |
  26. | runtime_MysqL_galera_hostgroups |
  27. | runtime_MysqL_group_replication_hostgroups |
  28. | runtime_MysqL_query_rules |
  29. | runtime_MysqL_query_rules_fast_routing |
  30. | runtime_MysqL_replication_hostgroups |
  31. | runtime_MysqL_servers |
  32. | runtime_MysqL_users |
  33. | runtime_proxysql_servers |
  34. | runtime_restapi_routes |
  35. | runtime_scheduler |
  36. | scheduler |
  37. +----------------------------------------------------+
  38. 32 rows in set (0.001 sec)
  39.  
  40. MysqL [(none)]> select * from runtime_MysqL_servers;
  41. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  42. | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  43. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  44. | 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  45. | 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  46. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  47. 2 rows in set (0.006 sec)
  48.  
  49. MysqL [(none)]> select * from MysqL_servers;
  50. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  51. | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  52. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  53. | 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  54. | 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  55. | 4 | 192.168.0.21 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  56. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  57. 3 rows in set (0.001 sec)
  58.  
  59. MysqL [(none)]> load MysqL servers to runtime;
  60. Query OK,0 rows affected (0.015 sec)
  61.  
  62. MysqL [(none)]> select * from runtime_MysqL_servers;
  63. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  64. | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  65. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  66. | 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  67. | 4 | 192.168.0.21 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  68. | 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
  69. +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  70. 3 rows in set (0.006 sec)
  71.  
  72. MysqL [(none)]>

  提示:可以看到执行load操作后,runtime_MysqL_servers就加载进去了;此时我们就把memory层的信息加载到runtime层生效了;

  验证:通过连接proxysql所在主机的3306端口,执行写操作,看看是否能够同步到其他从节点?

  提示:可以看到在proxysql所在主机连接3306线程,是可以连接到主库,因为默认配置连接是到主库,然后在其上创建一个mydb的数据库,也能够及时的同步到其他从库;

  测试:读写操作是否分别调度到不同的节点上?

  1. [root@lxc ~]# for i in {1..10} ;do MysqL -uroot -padmin123.com -P3306 -h192.168.0.21 -e "select @@server_id" ; done
  2. +-------------+
  3. | @@server_id |
  4. +-------------+
  5. | 2 |
  6. +-------------+
  7. +-------------+
  8. | @@server_id |
  9. +-------------+
  10. | 2 |
  11. +-------------+
  12. +-------------+
  13. | @@server_id |
  14. +-------------+
  15. | 2 |
  16. +-------------+
  17. +-------------+
  18. | @@server_id |
  19. +-------------+
  20. | 3 |
  21. +-------------+
  22. +-------------+
  23. | @@server_id |
  24. +-------------+
  25. | 1 |
  26. +-------------+
  27. +-------------+
  28. | @@server_id |
  29. +-------------+
  30. | 1 |
  31. +-------------+
  32. +-------------+
  33. | @@server_id |
  34. +-------------+
  35. | 1 |
  36. +-------------+
  37. +-------------+
  38. | @@server_id |
  39. +-------------+
  40. | 3 |
  41. +-------------+
  42. +-------------+
  43. | @@server_id |
  44. +-------------+
  45. | 1 |
  46. +-------------+
  47. +-------------+
  48. | @@server_id |
  49. +-------------+
  50. | 3 |
  51. +-------------+
  52. [root@lxc ~]# for i in {1..10} ;do MysqL -uroot -padmin123.com -P3306 -h192.168.0.21 -e "select @@server_id for update" ; done
  53. +-------------+
  54. | @@server_id |
  55. +-------------+
  56. | 1 |
  57. +-------------+
  58. +-------------+
  59. | @@server_id |
  60. +-------------+
  61. | 1 |
  62. +-------------+
  63. +-------------+
  64. | @@server_id |
  65. +-------------+
  66. | 1 |
  67. +-------------+
  68. +-------------+
  69. | @@server_id |
  70. +-------------+
  71. | 1 |
  72. +-------------+
  73. +-------------+
  74. | @@server_id |
  75. +-------------+
  76. | 1 |
  77. +-------------+
  78. +-------------+
  79. | @@server_id |
  80. +-------------+
  81. | 1 |
  82. +-------------+
  83. +-------------+
  84. | @@server_id |
  85. +-------------+
  86. | 1 |
  87. +-------------+
  88. +-------------+
  89. | @@server_id |
  90. +-------------+
  91. | 1 |
  92. +-------------+
  93. +-------------+
  94. | @@server_id |
  95. +-------------+
  96. | 1 |
  97. +-------------+
  98. +-------------+
  99. | @@server_id |
  100. +-------------+
  101. | 1 |
  102. +-------------+
  103. [root@lxc ~]#

  提示:可以看到当执行有写锁时,就立刻把操作调度到server_id=1的主库上去操作;读操作是随机调度到各个节点上进行处理;

猜你在找的Mariadb相关文章