Mariadb之主从复制的读写分离

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相关文章

Mariadb之主从复制的读写分离
首先我们来回顾下代理的概念，所谓代理就是指的是一端面向客户端，另外一端面向服务端，代...
Mariadb之复制过滤器
mariadb的主从复制集群，默认情况下是把主库上的所有库进行复制，只要在主库上产生写操作，...
Mariadb之半同步复制集群配置
首先我们来了解下在mariadb/mysql数据库主从复制集群中什么是同步，什么是异步，什么是半同...
Mariadb之显式使用表锁和行级锁
首先我们来看看mariadb的锁定概念，所谓锁就是当一个进程或事务在操作某一资源时，为了防止...
Mariadb之日志相关配置
前面我们聊到了mariadb的事务，以及事务隔离级别，回顾请参考https://www.cnblogs.com/qiu...
xtrabackup备份还原mariadb数据库
一、xtrabackup 简介 xtrabackup 是由percona公司开源免费的数据库热备软件，它能对InnoDB...
MariaDB表查询以及用户管理实践
1、&#160;导入hellodb.sql生成数据库 [root@test-centos7-node1 ~]# rz rz waiting to...
MariaDB的备份与主从以及高可用实践
1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。 [root@test-cent...
Ubuntu下安装完mariadb后使用root用户登录不上，如何解决？
问题描述vagrant@homestead:~/Code/zookeeper-3.4.9/bin$ sudo mysql -uro...
Mariadb的安装及相关配置
## 1 安装Mariadb > 使用默认的 `yum install mariadb-server`时，默认安装的是和Mysq...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问