StoneDB 读写分离实践方案
在 StoneDB 1.0 版本中,InnoDB 引擎处理 OLTP 的事务型业务,Tianmu 引擎处理 OLAP 的分析型业务。因此,需要在主从复制环境的基础上做读写分离,所有的写操作和部分读操作走 InnoDB 引擎,所有的分析类查询走 Tianmu 引擎。读 写分离方案既可以使用第三方中间件,也可以在业务前端实现。本文以第三方中间件 ProxySQL 为例,介绍 StoneDB 如何实现读写分离。
- 主从环境说明
IP | Memory | CPU | OS version | ROLE | Version |
---|---|---|---|---|---|
192.168.30.40 | 8GB | 8C | CentOS Linux release 7.9 | master | MySQL 5.7 |
192.168.30.41 | 8GB | 8C | CentOS Linux release 7.9 | ProxySQL | ProxySQL 2.2 |
192.168.30.42 | 8GB | 8C | CentOS Linux release 7.9 | slave | MySQL 5.7 |
192.168.30.46 | 16GB | 16C | CentOS Linux release 7.9 | slave | StoneDB 5.7 |
主从环境中的各个服务器的配置、数据库版本建议保持一致,但由于 StoneDB 不管重放 binlog,还是处理 OLAP 场景的查询,都是较消耗系统资源的,建议 StoneDB 配置略高于 MySQL。
- 架构图说明
推荐采用一主两从的架构,上层的 ProxySQL 用于读写分离:
- master(192.168.30.40)使用 InnoDB 引擎,可读写,提供 OLTP 场景的读写业务;
- slave1(192.168.30.42)使用 InnoDB 引擎,只读,同时 作为 standby,当 master 发生宕机时,可切换至 slave1,保证业务正常运行;
- slave2(192.168.30.46)使用 Tianmu 引擎,只读,提供 OLAP 场景的读业务。
1、操作系统环境检查
操作系统环境检查的步骤在四个节点均需要执行。
1.1 关闭防火墙
# systemctl stop firewalld
# systemctl disable firewalld
1.2 关闭SELINUX
# vim /etc/selinux/config
SELINUX = disabled
1.3 设置Swap分区
修改vm.swappiness的值为1,表示不使用Swap。
# vi /etc/sysctl.conf
vm.swappiness = 1
1.4 修改操作系统的限制
# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 1031433
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 65535
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 1024
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
修改操作系统的软硬限制
# vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
mysql soft nproc 1028056
mysql hard nproc 1028056
1.5 创建用户
# groupadd mysql
# useradd -g mysql mysql
# passwd mysql
ProxySQL 所在的节点无需创建用户 mysql,以上步骤执行完之后,重启操作系统。
2、部署MySQL
在 master 节点和 slave1 节点安装 MySQL。
2.1 下载安装包
https://downloads.mysql.com/archives/community/
从官网下载 MySQL 5.7 的安装包。
2.2 卸载mariadb
# rpm -qa|grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
# yum remove mariadb*
# rpm -qa|grep mariadb
2.3 上传tar包并解压
# tar -zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# mv mysql-5.7.36-linux-glibc2.12-x86_64 mysql
2.4 创建目录
# mkdir -p /mysql/data/
# mkdir -p /mysql/log
# chown -R mysql:mysql /mysql/
2.5 配置参数文件 my.cnf
master
# vim /etc/my.cnf
[client]
port = 3306
socket = /mysql/data/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /mysql/data
socket = /mysql/data/mysql.sock
pid_file = /mysql/data/mysqld.pid
log_error = /mysql/log/mysqld.log
log_bin = /mysql/log/mybinlog
server_id = 40
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
max_connections = 1000
binlog_format = row
default_storage_engine = innodb
read_only=0
innodb_buffer_pool_size = 4096000000
innodb_log_file_size = 1024000000
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#开启GTID模式
gtid_mode = on
enforce_gtid_consistency = 1
#并行复制
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave1
# vim /etc/my.cnf
[client]
port = 3306
socket = /mysql/data/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /mysql/data
socket = /mysql/data/mysql.sock
pid_file = /mysql/data/mysqld.pid
log_error = /mysql/log/mysqld.log
log_bin = /mysql/log/mybinlog
server_id = 42
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
max_connections = 1000
binlog_format = row
default_storage_engine = innodb
read_only=1
innodb_buffer_pool_size = 4096000000
innodb_log_file_size = 1024000000
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#开启GTID模式
gtid_mode = on
enforce_gtid_consistency = 1
#并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
2.6 初始化实例
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
2.7 启动实例
/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包并解压
# cd /
# tar -zxvf stonedb-ce-5.7-v1.0.0.el7.x86_64.tar.gz
用 户可根据安装规范将安装包上传至服务器,解压出来的目录是 stonedb57,示例中的安装路径是 /stonedb57。
3.3 检查依赖文件
# cd /stonedb57/install/bin
# ldd mysqld
# ldd mysql
如果检查返回有关键字"not found",说明缺少文件,需要安装对应的依赖包。例如:
-
在 Ubuntu 上使用命令 "sudo apt search libsnappy" 检查,说明需要安装 libsnappy-dev。
-
在 RedHat 或者 CentOS 上使用命令 "yum search all snappy" 检查,说明需要安装 snappy-devel、snappy。
3.4 创建目录
mkdir -p /stonedb57/install/data
mkdir -p /stonedb57/install/binlog
mkdir -p /stonedb57/install/log
mkdir -p /stonedb57/install/tmp
mkdir -p /stonedb57/install/redolog
mkdir -p /stonedb57/install/undolog
chown -R mysql:mysql /stonedb57
3.5 配置参数文件 my.cnf
# vim /stonedb57/install/my.cnf
[client]
port = 3306
socket = /stonedb57/install/tmp/mysql.sock
[mysqld]
port = 3306
basedir = /stonedb57/install/
datadir = /stonedb57/install/data
socket = /stonedb57/install/tmp/mysql.sock
pid_file = /stonedb57/install/data/mysqld.pid
log_error = /stonedb57/install/log/mysqld.log
log_bin = /stonedb57/install/binlog/binlog
server_id = 46
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
max_connections = 1000
binlog_format = row
default_storage_engine = tianmu
read_only=1
innodb_buffer_pool_size = 2048000000
innodb_log_file_size = 1024000000
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_log_group_home_dir = /stonedb57/install/redolog/
innodb_undo_directory = /stonedb57/install/undolog/
innodb_undo_log_truncate = 1
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
#开启GTID模式
gtid_mode = on
enforce_gtid_consistency = 1
#并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
3.6 初始化实例
/stonedb57/install/bin/mysqld --defaults-file=/stonedb57/install/my.cnf --initialize --user=mysql