StoneDB读写分离实践方案

商道如狼道  论坛元老 | 2022-10-10 20:23:19 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1032|帖子 1032|积分 3096

在 StoneDB 1.0 版本中，InnoDB 引擎处理 OLTP 的事务型业务，Tianmu 引擎处理 OLAP 的分析型业务。因此，需要在主从复制环境的基础上做读写分离，所有的写操作和部分读操作走 InnoDB 引擎，所有的分析类查询走 Tianmu 引擎。读写分离方案既可以使用第三方中间件，也可以在业务前端实现。本文介绍的是较为常用的中间件 ProxySQL。
服务器配置说明
IPMemoryCPUOS version192.168.30.408G8CCentOS Linux release 7.9192.168.30.418G8CCentOS Linux release 7.9192.168.30.428G8CCentOS Linux release 7.9192.168.30.4616G16CCentOS Linux release 7.9注：主从环境中的各个服务器的配置一般情况下建议是一致的，但由于 StoneDB 不管重放 binlog，还是用于 OLAP 场景的查询，都是较消耗系统资源的，建议 StoneDB 配置略高于 MySQL。
主从环境说明
IPDATABASEROLEDB version192.168.30.40MySQLmasterMySQL 5.7192.168.30.41/ProxySQL/192.168.30.42MySQLslaveMySQL 5.7192.168.30.46StoneDBslaveStoneDB 5.7注：MySQL 与 StoneDB 的版本建议保持一致。
架构图说明

推荐采用一主两从的架构，上层的 ProxySQL 用于读写分离：
1）master（192.168.30.40）使用 InnoDB 引擎，可读写，提供 OLTP 场景的读写业务；
2）slave1（192.168.30.42）使用 InnoDB 引擎，只读，同时作为 standby，当 master 发生宕机时，可切换至 slave1，保证业务正常运行；
3）slave2（192.168.30.46）使用 Tianmu 引擎，只读，提供 OLAP 场景的读业务。
1、操作系统环境检查

操作系统环境检查的步骤在四个节点均需要执行。
1.1 关闭防火墙
  1. # systemctl stop firewalld
  2. # systemctl disable firewalld
复制代码
1.2 关闭SELINUX
  1. # vim /etc/selinux/config
  2. SELINUX = disabled
复制代码
1.3 设置Swap分区

修改vm.swappiness的值为1，表示尽量不使用Swap。
  1. # vi /etc/sysctl.conf
  2. vm.swappiness = 1
复制代码
1.4 修改操作系统的限制
  1. # ulimit -a
  2. core file size          (blocks, -c) 0
  3. data seg size           (kbytes, -d) unlimited
  4. scheduling priority             (-e) 0
  5. file size               (blocks, -f) unlimited
  6. pending signals                 (-i) 1031433
  7. max locked memory       (kbytes, -l) 64
  8. max memory size         (kbytes, -m) unlimited
  9. open files                      (-n) 65535
  10. pipe size            (512 bytes, -p) 8
  11. POSIX message queues     (bytes, -q) 819200
  12. real-time priority              (-r) 0
  13. stack size              (kbytes, -s) 10240
  14. cpu time               (seconds, -t) unlimited
  15. max user processes              (-u) 1024
  16. virtual memory          (kbytes, -v) unlimited
  17. file locks                      (-x) unlimited
  19. 修改操作系统的软硬限制
  20. # vim /etc/security/limits.conf
  21. * soft nofile 65535
  22. * hard nofile 65535
  23. mysql soft nproc 1028056
  24. mysql hard nproc 1028056
复制代码
1.5 创建用户
  1. # groupadd mysql
  2. # useradd -g mysql mysql
  3. # passwd mysql
复制代码
ProxySQL 节点无需创建，以上步骤执行完之后，重启操作系统。
2、部署MySQL

在 master 节点和 slave1 节点安装 MySQL。
2.1 下载安装包

https://downloads.mysql.com/archives/community/
从官网下载 MySQL 5.7 的安装包。
2.2 卸载mariadb
  1. # rpm -qa|grep mariadb
  2. mariadb-5.5.56-2.el7.x86_64
  3. mariadb-server-5.5.56-2.el7.x86_64
  4. mariadb-libs-5.5.56-2.el7.x86_64
  5. # yum remove mariadb*
  6. # rpm -qa|grep mariadb
复制代码
2.3 上传tar包并解压
  1. # tar -zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
  2. # cd /usr/local/
  3. # mv mysql-5.7.36-linux-glibc2.12-x86_64 mysql
复制代码
2.4 创建目录
  1. # mkdir -p /mysql/data/
  2. # mkdir -p /mysql/log
  3. # chown -R mysql:mysql /mysql/
复制代码
2.5 配置参数文件 my.cnf

master
  1. # vim /etc/my.cnf
  2. [client]
  3. port    = 3306
  4. socket  = /mysql/data/mysql.sock
  6. [mysqld]
  7. port      = 3306
  8. basedir   = /usr/local/mysql
  9. datadir   = /mysql/data
  10. socket    = /mysql/data/mysql.sock
  11. pid_file  = /mysql/data/mysqld.pid
  12. log_error = /mysql/log/mysqld.log
  13. log_bin   = /mysql/log/mybinlog
  14. server_id = 40
  15. character_set_server = utf8mb4
  16. collation_server = utf8mb4_general_ci
  17. max_connections = 1000
  18. binlog_format = row
  19. default_storage_engine = innodb
  20. read_only=0
  22. innodb_buffer_pool_size = 4096000000
  23. innodb_log_file_size = 1024000000
  24. innodb_log_files_in_group = 3
  25. innodb_io_capacity = 4000
  26. innodb_io_capacity_max = 8000
  28. #开启GTID模式
  29. gtid_mode = on
  30. enforce_gtid_consistency = 1
  32. #并行复制
  33. binlog_transaction_dependency_tracking = WRITESET
  34. transaction_write_set_extraction = XXHASH64
复制代码
slave1
  1. # vim /etc/my.cnf
  2. [client]
  3. port    = 3306
  4. socket  = /mysql/data/mysql.sock
  6. [mysqld]
  7. port      = 3306
  8. basedir   = /usr/local/mysql
  9. datadir   = /mysql/data
  10. socket    = /mysql/data/mysql.sock
  11. pid_file  = /mysql/data/mysqld.pid
  12. log_error = /mysql/log/mysqld.log
  13. log_bin   = /mysql/log/mybinlog
  14. server_id = 42
  15. character_set_server = utf8mb4
  16. collation_server = utf8mb4_general_ci
  17. max_connections = 1000
  18. binlog_format = row
  19. default_storage_engine = innodb
  20. read_only=1
  22. innodb_buffer_pool_size = 4096000000
  23. innodb_log_file_size = 1024000000
  24. innodb_log_files_in_group = 3
  25. innodb_io_capacity = 4000
  26. innodb_io_capacity_max = 8000
  28. #开启GTID模式
  29. gtid_mode = on
  30. enforce_gtid_consistency = 1
  32. #并行复制
  33. slave_parallel_type = LOGICAL_CLOCK
  34. slave_parallel_workers = 4
复制代码
2.6 初始化实例
  1. /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
复制代码
2.7 启动实例
  1. /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
复制代码
注：管理员用户的临时密码在 mysqld.log 中，第一次登陆后需要修改管理员用户的密码。
3、部署StoneDB

3.1 下载安装包

https://stonedb.io/zh/docs/download/
从官网下载 StoneDB 5.7 的安装包。
3.2 上传tar包并解压
  1. # cd /
  2. # tar -zxvf stonedb-ce-5.7-v1.0.0.el7.x86_64.tar.gz
复制代码
用户可根据安装规范将安装包上传至服务器，解压出来的目录是 stonedb57，示例中的安装路径是 /stonedb57。
3.3 检查依赖文件
  1. # cd /stonedb57/install/bin
  2. # ldd mysqld
  3. # ldd mysql
复制代码
如果检查返回有关键字"not found"，说明缺少文件，需要安装对应的依赖包。例如：
libsnappy.so.1 => not found
在 Ubuntu 上使用命令 "sudo apt search libsnappy" 检查，说明需要安装 libsnappy-dev。在 RedHat 或者 CentOS 上使用命令 "yum search all snappy" 检查，说明需要安装 snappy-devel、snappy。
3.4 创建目录
  1. mkdir -p /stonedb57/install/data
  2. mkdir -p /stonedb57/install/binlog
  3. mkdir -p /stonedb57/install/log
  4. mkdir -p /stonedb57/install/tmp
  5. mkdir -p /stonedb57/install/redolog
  6. mkdir -p /stonedb57/install/undolog
  7. chown -R mysql:mysql /stonedb57
复制代码
3.5 配置参数文件 my.cnf
  1. # vim /stonedb57/install/my.cnf
  2. [client]
  3. port    = 3306
  4. socket  = /stonedb57/install/tmp/mysql.sock
  6. [mysqld]
  7. port      = 3306
  8. basedir   = /stonedb57/install/
  9. datadir   = /stonedb57/install/data
  10. socket    = /stonedb57/install/tmp/mysql.sock
  11. pid_file  = /stonedb57/install/data/mysqld.pid
  12. log_error = /stonedb57/install/log/mysqld.log
  13. log_bin   = /stonedb57/install/binlog/binlog
  14. server_id = 46
  15. character_set_server = utf8mb4
  16. collation_server = utf8mb4_general_ci
  17. max_connections = 1000
  18. binlog_format = row
  19. default_storage_engine = tianmu
  20. read_only=1
  22. innodb_buffer_pool_size = 2048000000
  23. innodb_log_file_size = 1024000000
  24. innodb_log_files_in_group = 3
  25. innodb_io_capacity = 4000
  26. innodb_io_capacity_max = 8000
  27. innodb_log_group_home_dir   = /stonedb57/install/redolog/
  28. innodb_undo_directory       = /stonedb57/install/undolog/
  29. innodb_undo_log_truncate    = 1
  30. innodb_undo_tablespaces     = 3
  31. innodb_undo_logs            = 128
  33. #开启GTID模式
  34. gtid_mode = on
  35. enforce_gtid_consistency = 1
  37. #并行复制
  38. slave_parallel_type = LOGICAL_CLOCK
  39. slave_parallel_workers = 8  
复制代码
3.6 初始化实例
  1. /stonedb57/install/bin/mysqld --defaults-file=/stonedb57/install/my.cnf --initialize --user=mysql
复制代码
3.7 启动实例
  1. /stonedb57/install/bin/mysqld_safe --defaults-file=/stonedb57/install/my.cnf --user=mysql &
复制代码
注：管理员用户的临时密码在 mysqld.log 中，第一次登陆后需要修改管理员用户的密码。
4、配置主从

4.1 创建复制用户
  1. create user 'repl'@'%' identified by 'mysql123';
  2. grant replication slave on *.* to 'repl'@'%';
复制代码
4.2 备份主库
  1. /usr/local/mysql/bin/mysqldump -uroot -pmysql123 --single-transaction --set-gtid-purged=on -B aa > /tmp/aa.sql
复制代码
4.3 传输备份文件
  1. scp /tmp/aa.sql root@192.168.30.42:/tmp
  2. scp /tmp/aa.sql root@192.168.30.43:/tmp
复制代码
注：如果数据较大，建议使用 mydumper.
4.4 slave1节点
  1. /usr/local/mysql/bin/mysql -uroot -pmysql123 -S /mysqldb/data/mysql.sock
  2. source /tmp/aa.sql
复制代码
注：恢复前需要确保 gtid_executed 为空。
4.5 slave2节点

在恢复前，需要修改存储引擎，注释锁表语句。
  1. sed -i 's/UNLOCK TABLES/-- UNLOCK TABLES/g' /tmp/aa.sql
  2. sed -i 's/LOCK TABLES `/-- LOCK TABLES `/g' /tmp/aa.sql
  3. sed -i 's/ENGINE=InnoDB/ENGINE=tianmu/g' /tmp/aa.sql
  5. /stonedb57/install/bin/mysql -uroot -pmysql123 -S /stonedb57/install/tmp/mysql.sock
  6. source /tmp/aa.sql
复制代码
注：恢复前需要确保 gtid_executed 为空。
4.6 建立主从复制

slave1节点
  1. CHANGE MASTER TO
  2. MASTER_HOST='192.168.30.40',
  3. MASTER_PORT=3306,
  4. MASTER_USER='repl',
  5. MASTER_PASSWORD='mysql123',
  6. MASTER_AUTO_POSITION = 1;
  8. start slave;
  9. show slave status\G
复制代码
slave2节点
  1. CHANGE MASTER TO
  2. MASTER_HOST='192.168.30.40',
  3. MASTER_PORT=3306,
  4. MASTER_USER='repl',
  5. MASTER_PASSWORD='mysql123',
  6. MASTER_AUTO_POSITION = 1;
  8. start slave;
  9. show slave status\G
复制代码
5、配置ProxySQL

5.1 安装ProxySQL
  1. # mkdir -p /home/ProxySQL
  2. # cd /home/ProxySQL
  3. # yum install proxysql-2.2.0-1-centos7.x86_64.rpm
  4. # rpm -qa|grep proxysql
  5. # rpm -ql proxysql
复制代码
注：/etc/proxysql.cnf 为 ProxySQL 的配置文件。
5.2 启动ProxySQL
  1. # systemctl start proxysql
  2. # netstat -lntp|grep proxysql
  3. tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      17957/proxysql      
  4. tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      17957/proxysql
复制代码
注：6032是管理端口，6033是服务端口。
5.3 管理员登录ProxySQL
  1. # mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='ProxySQL>'
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 1
  5. Server version: 5.5.30 (ProxySQL Admin Module)
  7. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. ProxySQL>show databases;
  16. +-----+---------------+-------------------------------------+
  17. | seq | name          | file                                |
  18. +-----+---------------+-------------------------------------+
  19. | 0   | main          |                                     |
  20. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
  21. | 3   | stats         |                                     |
  22. | 4   | monitor       |                                     |
  23. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
  24. +-----+---------------+-------------------------------------+
  25. 5 rows in set (0.00 sec)
复制代码
注：ProxySQL 的用户名和密码默认都是 admin，加参数 prompt 是为了便于区分环境。
5.4 创建ProxySQL所需用户

在 master 上创建 ProxySQL 的监控用户和对外访问用户，其中监控用户只需要有 replication client 权限即可。
  1. ###监控用户
  2. create user 'monitor'@'%' identified by 'MySQL_123';
  3. grant replication client on *.* to 'monitor'@'%';
  5. ###对外访问用户（用于连接ProxySQL）
  6. create user 'proxysql'@'%' identified by 'MySQL_123';
  7. grant select,delete,update,insert on *.* to 'proxysql'@'%';
复制代码
5.5 配置ProxySQL主从分组信息

1）创建分组

表 mysql_replication_hostgroups 的字段 writer_hostgroup、reader_hostgroup 分别代表写组和读组，都要大于0且不能相同，该环境中定义写组为10，读组为20。
ProxySQL 会根据 read_only 的取值将 server 进行分组，read_only=0为 master，被分到编号为10的写组，read_only=1为 slave，被分到编号为20的读组。
  1. ###创建分组
  2. insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
  3. load mysql servers to runtime;
  4. save mysql servers to disk;
  6. ###查看三层配置系统是否都写入数据
  7. ProxySQL>select * from mysql_replication_hostgroups;
  8. +------------------+------------------+------------+---------+
  9. | writer_hostgroup | reader_hostgroup | check_type | comment |
  10. +------------------+------------------+------------+---------+
  11. | 10               | 20               | read_only  | proxy   |
  12. +------------------+------------------+------------+---------+
  13. 1 row in set (0.00 sec)
  15. ProxySQL>select * from runtime_mysql_replication_hostgroups;
  16. +------------------+------------------+------------+---------+
  17. | writer_hostgroup | reader_hostgroup | check_type | comment |
  18. +------------------+------------------+------------+---------+
  19. | 10               | 20               | read_only  | proxy   |
  20. +------------------+------------------+------------+---------+
  21. 1 row in set (0.00 sec)
  23. ProxySQL>select * from mysql_replication_hostgroups;
  24. +------------------+------------------+------------+---------+
  25. | writer_hostgroup | reader_hostgroup | check_type | comment |
  26. +------------------+------------------+------------+---------+
  27. | 10               | 20               | read_only  | proxy   |
  28. +------------------+------------------+------------+---------+
  29. 1 row in set (0.00 sec)
复制代码
2）添加主从节点
  1. insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.30.40',3306);
  2. insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.30.46',3306);
  3. load mysql servers to runtime;
  4. save mysql servers to disk;
  6. ###查看状态
  7. ProxySQL>select * from mysql_servers;
  8. +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  9. | hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  10. +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  11. | 20           | 192.168.30.40 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  12. | 20           | 192.168.30.46 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  13. +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  14. 2 rows in set (0.00 sec)
复制代码
3）为ProxySQL创建监控用户
  1. ###创建监控用户（在ProxySQL创建）
  2. set mysql-monitor_username='monitor';
  3. set mysql-monitor_password='MySQL_123';
  4. load mysql variables to runtime;
  5. save mysql variables to disk;
  7. ###对连接用户监控
  8. ProxySQL>select * from monitor.mysql_server_connect_log;
  9. +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
  10. | hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
  11. +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
  12. | 192.168.30.40 | 3306 | 1664183920198998 | 0                       | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  13. | 192.168.30.46 | 3306 | 1664183920926981 | 0                       | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  14. | 192.168.30.46 | 3306 | 1664183970671663 | 1616                    | NULL                                                                   |
  15. | 192.168.30.40 | 3306 | 1664183971672625 | 2089                    | NULL                                                                   |
  16. +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
  17. 4 rows in set (0.00 sec)
  19. 注：在没有创建监控用户前，会有很多的connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL，则表示正常。
  21. ###对心跳信息的监控
  22. ProxySQL>select * from mysql_server_ping_log limit 10;
  23. +---------------+------+------------------+----------------------+------------------------------------------------------------------------+
  24. | hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             |
  25. +---------------+------+------------------+----------------------+------------------------------------------------------------------------+
  26. | 192.168.30.40 | 3306 | 1664183880229349 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  27. | 192.168.30.46 | 3306 | 1664183880427787 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  28. | 192.168.30.40 | 3306 | 1664183890229405 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  29. | 192.168.30.46 | 3306 | 1664183890336793 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  30. | 192.168.30.40 | 3306 | 1664183900229529 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  31. | 192.168.30.46 | 3306 | 1664183900357491 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  32. | 192.168.30.46 | 3306 | 1664183910229710 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  33. | 192.168.30.40 | 3306 | 1664183910406115 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  34. | 192.168.30.46 | 3306 | 1664183920229740 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  35. | 192.168.30.40 | 3306 | 1664183920346638 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  36. +---------------+------+------------------+----------------------+------------------------------------------------------------------------+
  37. 10 rows in set (0.00 sec)
  39. ###对read_only值监控
  40. ProxySQL>select * from mysql_server_read_only_log limit 10;
  41. +---------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------------------------+
  42. | hostname      | port | time_start_us    | success_time_us | read_only | error                                                                                                      |
  43. +---------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------------------------+
  44. | 192.168.30.46 | 3306 | 1664183876942878 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  45. | 192.168.30.40 | 3306 | 1664183876961694 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  46. | 192.168.30.46 | 3306 | 1664183878441697 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  47. | 192.168.30.40 | 3306 | 1664183878461063 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  48. | 192.168.30.46 | 3306 | 1664183879941587 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  49. | 192.168.30.40 | 3306 | 1664183879961993 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  50. | 192.168.30.46 | 3306 | 1664183881441750 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  51. | 192.168.30.40 | 3306 | 1664183881461890 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  52. | 192.168.30.40 | 3306 | 1664183882942044 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
  53. | 192.168.30.46 | 3306 | 1664183882958866 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
  54. +---------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------------------------+
  55. 10 rows in set (0.00 sec)
  57. 注：monitor就会开始监控后端的read_only值，然后按照read_only的值将某些节点自动移到读写组。
复制代码
4）为ProxySQL配置对外访问用户
  1. insert into mysql_users(username,password,default_hostgroup) values('proxysql','MySQL_123',10);
  2. insert into mysql_users(username,password,default_hostgroup) values('proxysql2','*0815E74A768849A6CCF0E9C1C5B940FB4D9F839E',20);
  3. load mysql users to runtime;
  4. save mysql users to disk;
  6. ProxySQL>select * from mysql_users\G
  7. *************************** 1. row ***************************
  8.               username: proxysql
  9.               password: MySQL_123
  10.                 active: 1
  11.                use_ssl: 0
  12.      default_hostgroup: 10
  13.         default_schema: NULL
  14.          schema_locked: 0
  15. transaction_persistent: 1
  16.           fast_forward: 0
  17.                backend: 1
  18.               frontend: 1
  19.        max_connections: 10000
  20.             attributes:
  21.                comment:
  22. *************************** 2. row ***************************
  23.               username: proxysql2
  24.               password: *0815E74A768849A6CCF0E9C1C5B940FB4D9F839E
  25.                 active: 1
  26.                use_ssl: 0
  27.      default_hostgroup: 20
  28.         default_schema: NULL
  29.          schema_locked: 0
  30. transaction_persistent: 1
  31.           fast_forward: 0
  32.                backend: 1
  33.               frontend: 1
  34.        max_connections: 10000
  35.             attributes:
  36.                comment:
  37. 2 rows in set (0.00 sec)
复制代码
mysql_users 表最主要的三个字段 username、password、default_hostgroup 解释。
username：前端连接 ProxySQL 的数据库用户
password：用户对应的密码，即可以是明文密码，也可以是 hash 密码，如果想使用 hash 密码，可以先在某个节点上执行 select password('password')，然后将加密结果复制到该字段。
default_hostgroup：用户默认的路由目标，例如：若用户 proxysql2 是个只读用户，则该字段值可以设置为20，表示所有的SQL语句默认情况下将路由到 hostgroup_id=20 的组；若用户不是只读用户，则该字段必须设置为10，表示 DML 语句会路由到 hostgroup_id=10 的组，查询语句即会路由到 hostgroup_id=10 的组，也会路由到 hostgroup_id=20 的组。
5.6 配置读写分离策略

ProxySQL 的路由规则配置比较灵活，可以基于用户级别，数据库级别等。由于 StoneDB 提供的是 OLAP 分析型查询业务场景，建议将聚合类查询、即席查询、复杂查询等分发到 StoneDB。由于只是测试，因此只配置了几个简单的路由规则。
与查询规则有关的表有两个：mysql_query_rules 和 mysql_query_rules_fast_routing，表mysql_query_rules_fast_routing 是 mysql_query_rules 的扩展，并在以后评估快速路由策略和属性（仅在ProxySQL 1.4.7+中可用）。
mysql_query_rules 表的几个字段解释。
active：是否启用这个规则，1表示启用，0表示禁用
match_pattern：设置规则
destination_hostgroup：默认指定的分组
apply：真正执行应用规则
  1. ###创建规则
  2. 这里我创建两个规则：
  3. 1）把所有以select开头的语句分配到编号为20的读组中；
  4. 2）把select...for update语句分配到编号为10的写组中，其他所有操作都会默认路由到写组。
  5. insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select.*for update$',10,1);
  6. insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',20,1);
  7. insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(3,1,'^select*sum',20,1);
  8. load mysql query rules to runtime;
  9. save mysql query rules to disk;
  11. ###查看规则
  12. ProxySQL>select * from mysql_query_rules\G
  13. *************************** 1. row ***************************
  14.               rule_id: 1
  15.                active: 1
  16.              username: NULL
  17.            schemaname: NULL
  18.                flagIN: 0
  19.           client_addr: NULL
  20.            proxy_addr: NULL
  21.            proxy_port: NULL
  22.                digest: NULL
  23.          match_digest: NULL
  24.         match_pattern: ^select.*for update$
  25. negate_match_pattern: 0
  26.          re_modifiers: CASELESS
  27.               flagOUT: NULL
  28.       replace_pattern: NULL
  29. destination_hostgroup: 10
  30.             cache_ttl: NULL
  31.    cache_empty_result: NULL
  32.         cache_timeout: NULL
  33.             reconnect: NULL
  34.               timeout: NULL
  35.               retries: NULL
  36.                 delay: NULL
  37.     next_query_flagIN: NULL
  38.        mirror_flagOUT: NULL
  39.      mirror_hostgroup: NULL
  40.             error_msg: NULL
  41.                OK_msg: NULL
  42.           sticky_conn: NULL
  43.             multiplex: NULL
  44.   gtid_from_hostgroup: NULL
  45.                   log: NULL
  46.                 apply: 1
  47.            attributes:
  48.               comment: NULL
  49. *************************** 2. row ***************************
  50.               rule_id: 2
  51.                active: 1
  52.              username: NULL
  53.            schemaname: NULL
  54.                flagIN: 0
  55.           client_addr: NULL
  56.            proxy_addr: NULL
  57.            proxy_port: NULL
  58.                digest: NULL
  59.          match_digest: NULL
  60.         match_pattern: ^select
  61. negate_match_pattern: 0
  62.          re_modifiers: CASELESS
  63.               flagOUT: NULL
  64.       replace_pattern: NULL
  65. destination_hostgroup: 20
  66.             cache_ttl: NULL
  67.    cache_empty_result: NULL
  68.         cache_timeout: NULL
  69.             reconnect: NULL
  70.               timeout: NULL
  71.               retries: NULL
  72.                 delay: NULL
  73.     next_query_flagIN: NULL
  74.        mirror_flagOUT: NULL
  75.      mirror_hostgroup: NULL
  76.             error_msg: NULL
  77.                OK_msg: NULL
  78.           sticky_conn: NULL
  79.             multiplex: NULL
  80.   gtid_from_hostgroup: NULL
  81.                   log: NULL
  82.                 apply: 1
  83.            attributes:
  84.               comment: NULL
  85. *************************** 3. row ***************************
  86.               rule_id: 3
  87.                active: 1
  88.              username: NULL
  89.            schemaname: NULL
  90.                flagIN: 0
  91.           client_addr: NULL
  92.            proxy_addr: NULL
  93.            proxy_port: NULL
  94.                digest: NULL
  95.          match_digest: NULL
  96.         match_pattern: ^select*sum
  97. negate_match_pattern: 0
  98.          re_modifiers: CASELESS
  99.               flagOUT: NULL
  100.       replace_pattern: NULL
  101. destination_hostgroup: 20
  102.             cache_ttl: NULL
  103.    cache_empty_result: NULL
  104.         cache_timeout: NULL
  105.             reconnect: NULL
  106.               timeout: NULL
  107.               retries: NULL
  108.                 delay: NULL
  109.     next_query_flagIN: NULL
  110.        mirror_flagOUT: NULL
  111.      mirror_hostgroup: NULL
  112.             error_msg: NULL
  113.                OK_msg: NULL
  114.           sticky_conn: NULL
  115.             multiplex: NULL
  116.   gtid_from_hostgroup: NULL
  117.                   log: NULL
  118.                 apply: 1
  119.            attributes:
  120.               comment: NULL
  121. 3 rows in set (0.00 sec)
复制代码
注：select...for update 规则的 rule_id 必须要小于普通的 select 规则的 rule_id，因为 ProxySQL 是根据 rule_id的顺序进行规则匹配的。
5.7 读写分离测试

1）读操作
  1. # mysql -uproxysql -pMySQL_123 -h127.0.0.1 -P6033
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 6
  5. Server version: 5.5.30 (ProxySQL)
  7. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. proxysql@HAMI02 17:21:  [(none)]> select sum(money) from aa.ttt;
  16. +------------+
  17. | sum(money) |
  18. +------------+
  19. |    88888.8 |
  20. +------------+
  21. 1 row in set (0.01 sec)
  23. proxysql@HAMI02 17:21:  [(none)]> select @@server_id;
  24. +-------------+
  25. | @@server_id |
  26. +-------------+
  27. |          46 |
  28. +-------------+
  29. 1 row in set (0.00 sec)
复制代码
2）写操作
  1. # mysql -uproxysql -pMySQL_123 -h127.0.0.1 -P6033
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 19
  5. Server version: 5.5.30 (ProxySQL)
  7. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. proxysql@HAMI02 19:53:  [(none)]> begin;
  16. Query OK, 0 rows affected (0.00 sec)
  18. proxysql@HAMI02 19:54:  [(none)]> insert into aa.t1 values(7);
  19. Query OK, 1 row affected (0.00 sec)
  21. proxysql@HAMI02 19:54:  [(none)]> select @@server_id;
  22. +-------------+
  23. | @@server_id |
  24. +-------------+
  25. |          40 |
  26. +-------------+
  27. 1 row in set (0.00 sec)
复制代码
6、建议项

1）建议设置为 GTID 模式，好处是从库发生 crash 后，不需要去找位点，系统会自动拉起复制线程；
2）在从库的并行 worker 相同的情况下，从库 StoneDB 相比从库 InnoDB 会消耗更多的CPU资源，如果未来上生产环境，建议 StoneDB 的配置略比 InnoDB 的配置高；
3）若从库 StoneDB 的延迟较高，可临时关闭参数 tianmu_enable_rowstore，待追上主库后再开启参数。关闭该参数带来的风险是如果在同步过程中 StoneDB 发生重启，重启后会出现丢数据的情况；
4）建议将聚合类查询、即席查询、复杂查询等分发到 StoneDB。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

商道如狼道

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表