StoneDB主从配置及切换实践方案

目录1、操作系统环境检查1.1 关闭防火墙1.2 关闭SELINUX1.3 设置Swap分区1.4 修改操作系统的限制1.5 创建用户2、部署MySQL2.1 下载安装包2.2 卸载mariadb2.3 上传tar包并解压2.4 创建目录2.5 配置参数文件 my.cnfmasterslave12.6 初始化实例2.7 启动实例3、部署StoneDB3.1 下载安装包3.2 上传tar包并解压3.3 检查依赖文件3.4 创建目录3.5 配置参数文件 my.cnf3.6 初始化实例3.7 启动实例4、配置主从4.1 创建复制用户4.2 备份主库4.3 传输备份文件4.4 slave1节点4.5 slave2节点4.6 建立主从复制slave1节点slave2节点5、配置Replication Manager5.1 配置hosts文件5.2 配置免密5.3 配置yum源5.4 安装Replication Manager5.5 主库创建监控用户5.6 配置config.toml5.7 启动Replication Manager5.8 WEB登录6、建议项

StoneDB 的主从切换既可以手动切换，也可以自动切换，自动切换通常需要使用第三方中间件。本文介绍的是较为常用的中间件 Replication Manager，当 master 发生宕机时，可自动切换至 slave，保证业务正常运行，故障节点恢复后再加入主从。

服务器配置说明

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/Replication Manager/192.168.30.42MySQLslaveMySQL 5.7192.168.30.46StoneDBslaveStoneDB 5.7

注：MySQL 与 StoneDB 的版本建议保持一致。

推荐采用一主两从的架构，其中 StoneDB 不参与主从切换：

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 关闭防火墙# systemctl stop firewalld # systemctl disable firewalld

1.2 关闭SELINUX# vim /etc/selinux/configSELINUX = disabled

1.3 设置Swap分区

修改vm.swappiness的值为1，表示尽量不使用Swap。

# vi /etc/sysctl.confvm.swappiness = 1

1.4 修改操作系统的限制# ulimit -acore file size (blocks, -c) 0data seg size (kbytes, -d) unlimitedscheduling priority (-e) 0file size (blocks, -f) unlimitedpending signals (-i) 1031433max locked memory (kbytes, -l) 64max memory size (kbytes, -m) unlimitedopen files (-n) 65535pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200real-time priority (-r) 0stack size (kbytes, -s) 10240cpu time (seconds, -t) unlimitedmax user processes (-u) 1024virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited修改操作系统的软硬限制# vim /etc/security/limits.conf* soft nofile 65535* hard nofile 65535mysql soft nproc 1028056mysql hard nproc 1028056

1.5 创建用户# groupadd mysql# useradd -g mysql mysql# passwd mysql

Replication Manager 节点无需创建，以上步骤执行完之后，重启操作系统。

2、部署MySQL

在 master 节点和 slave1 节点安装 MySQL。

2.1 下载安装包

https://downloads.mysql.com/archives/community/从官网下载 MySQL 5.7 的安装包。

2.2 卸载mariadb# rpm -qa|grep mariadbmariadb-5.5.56-2.el7.x86_64mariadb-server-5.5.56-2.el7.x86_64mariadb-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 = 3306socket = /mysql/data/mysql.sock[mysqld]port = 3306basedir = /usr/local/mysqldatadir = /mysql/datasocket = /mysql/data/mysql.sockpid_file = /mysql/data/mysqld.pidlog_error = /mysql/log/mysqld.loglog_bin = /mysql/log/mybinlogserver_id = 40character_set_server = utf8mb4collation_server = utf8mb4_general_cimax_connections = 1000binlog_format = rowdefault_storage_engine = innodbread_only=0innodb_buffer_pool_size = 4096000000innodb_log_file_size = 1024000000innodb_log_files_in_group = 3innodb_io_capacity = 4000innodb_io_capacity_max = 8000#开启GTID模式gtid_mode = onenforce_gtid_consistency = 1#并行复制binlog_transaction_dependency_tracking = WRITESETtransaction_write_set_extraction = XXHASH64

slave1# vim /etc/my.cnf[client]port = 3306socket = /mysql/data/mysql.sock[mysqld]port = 3306basedir = /usr/local/mysqldatadir = /mysql/datasocket = /mysql/data/mysql.sockpid_file = /mysql/data/mysqld.pidlog_error = /mysql/log/mysqld.loglog_bin = /mysql/log/mybinlogserver_id = 42character_set_server = utf8mb4collation_server = utf8mb4_general_cimax_connections = 1000binlog_format = rowdefault_storage_engine = innodbread_only=1innodb_buffer_pool_size = 4096000000innodb_log_file_size = 1024000000innodb_log_files_in_group = 3innodb_io_capacity = 4000innodb_io_capacity_max = 8000#开启GTID模式gtid_mode = onenforce_gtid_consistency = 1#并行复制slave_parallel_type = LOGICAL_CLOCKslave_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"，说明缺少文件，需要安装对应的依赖包。例如：

libsnappy.so.1 => not found

在 Ubuntu 上使用命令 "sudo apt search libsnappy" 检查，说明需要安装 libsnappy-dev。在 RedHat 或者 CentOS 上使用命令 "yum search all snappy" 检查，说明需要安装 snappy-devel、snappy。

3.4 创建目录mkdir -p /stonedb57/install/datamkdir -p /stonedb57/install/binlogmkdir -p /stonedb57/install/logmkdir -p /stonedb57/install/tmpmkdir -p /stonedb57/install/redologmkdir -p /stonedb57/install/undologchown -R mysql:mysql /stonedb57

3.5 配置参数文件 my.cnf# vim /stonedb57/install/my.cnf[client]port = 3306socket = /stonedb57/install/tmp/mysql.sock[mysqld]port = 3306basedir = /stonedb57/install/datadir = /stonedb57/install/datasocket = /stonedb57/install/tmp/mysql.sockpid_file = /stonedb57/install/data/mysqld.pidlog_error = /stonedb57/install/log/mysqld.loglog_bin = /stonedb57/install/binlog/binlogserver_id = 46character_set_server = utf8mb4collation_server = utf8mb4_general_cimax_connections = 1000binlog_format = rowdefault_storage_engine = tianmuread_only=1innodb_buffer_pool_size = 2048000000innodb_log_file_size = 1024000000innodb_log_files_in_group = 3innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_log_group_home_dir = /stonedb57/install/redolog/innodb_undo_directory = /stonedb57/install/undolog/innodb_undo_log_truncate = 1innodb_undo_tablespaces = 3innodb_undo_logs = 128#开启GTID模式gtid_mode = onenforce_gtid_consistency = 1#并行复制slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 8

3.6 初始化实例/stonedb57/install/bin/mysqld –defaults-file=/stonedb57/install/my.cnf –initialize –user=mysql

3.7 启动实例/stonedb57/install/bin/mysqld_safe –defaults-file=/stonedb57/install/my.cnf –user=mysql &

注：管理员用户的临时密码在 mysqld.log 中，第一次登陆后需要修改管理员用户的密码。

4、配置主从

4.1 创建复制用户create user \’repl\’@\’%\’ identified by \’mysql123\’;grant replication slave on *.* to \’repl\’@\’%\’;

4.2 备份主库/usr/local/mysql/bin/mysqldump -uroot -pmysql123 –single-transaction –set-gtid-purged=on -B aa > /tmp/aa.sql

4.3 传输备份文件scp /tmp/aa.sql root@192.168.30.42:/tmpscp /tmp/aa.sql root@192.168.30.43:/tmp

注：如果数据较大，建议使用 mydumper.

4.4 slave1节点/usr/local/mysql/bin/mysql -uroot -pmysql123 -S /mysqldb/data/mysql.socksource /tmp/aa.sql

注：恢复前需要确保 gtid_executed 为空。

4.5 slave2节点

在恢复前，需要修改存储引擎，注释锁表语句。

sed -i \’s/UNLOCK TABLES/– UNLOCK TABLES/g\’ /tmp/aa.sqlsed -i \’s/LOCK TABLES `/– LOCK TABLES `/g\’ /tmp/aa.sqlsed -i \’s/ENGINE=InnoDB/ENGINE=tianmu/g\’ /tmp/aa.sql/stonedb57/install/bin/mysql -uroot -pmysql123 -S /stonedb57/install/tmp/mysql.socksource /tmp/aa.sql

注：恢复前需要确保 gtid_executed 为空。

4.6 建立主从复制

slave1节点CHANGE MASTER TOMASTER_HOST=\’192.168.30.40\’,MASTER_PORT=3306,MASTER_USER=\’repl\’,MASTER_PASSWORD=\’mysql123\’,MASTER_AUTO_POSITION = 1;start slave;show slave status\\G

slave2节点CHANGE MASTER TOMASTER_HOST=\’192.168.30.40\’,MASTER_PORT=3306,MASTER_USER=\’repl\’,MASTER_PASSWORD=\’mysql123\’,MASTER_AUTO_POSITION = 1;start slave;show slave status\\G

5、配置Replication Manager

5.1 配置hosts文件

在所有节点都要配置

# vim /etc/hosts192.168.30.40 HAMI01192.168.30.41 HAMI02192.168.30.42 HAMI03192.168.30.46 HAST05

5.2 配置免密

在 Replication Manager 节点配置

ssh-keygenssh-copy-id HAMI01ssh-copy-id HAMI03ssh-copy-id HAST05ssh HAMI01ssh HAMI03ssh HAST05

注：若 ssh 免密登录表示免密配置成功。

5.3 配置yum源# vim /etc/yum.repos.d/signal18.repo[signal18]name=Signal18 repositoriesbaseurl=http://repo.signal18.io/centos/2.1/$releasever/$basearch/gpgcheck=0enabled=1

5.4 安装Replication Manager# yum install -y replication-manager-osc# rpm -qa|grep replicationreplication-manager-osc-2.2.20-1.x86_64

5.5 主库创建监控用户create user \’rep_monitor\’@\’%\’ identified by \’mysql123\’;grant reload, process, super, replication slave, replication client, event ON *.* to \’rep_monitor\’@\’%\’;grant select ON mysql.event to \’rep_monitor\’@\’%\’;grant select ON mysql.user to \’rep_monitor\’@\’%\’;grant select ON performance_schema.* to \’rep_monitor\’@\’%\’;

5.6 配置config.toml# vim /etc/replication-manager/config.toml# 集群名称[StoneDB-HA]# 主从节点db-servers-hosts = \”192.168.30.40:3306,192.168.30.42:3306,192.168.30.46:3306\”# 主节点db-servers-prefered-master = \”192.168.30.40:3306\”# 监控用户db-servers-credential = \”rep_monitor:mysql123\”db-servers-connect-timeout = 2# 复制用户replication-credential = \”repl:mysql123\”# StoneDB不被用于切换db-servers-ignored-hosts=\”192.168.30.46:3306\”################ FAILOVER ################# 故障自动切换failover-mode = \”automatic\”# 30s内再次发生故障不切换，防止硬件问题或网络问题failover-time-limit=30[Default]########### LOG ###########log-file = \”/var/log/replication-manager.log\”log-heartbeat = falselog-syslog = falsemonitoring-datadir = \”/var/lib/replication-manager\”log-level=1replication-multi-master = falsereplication-multi-tier-slave = falsefailover-readonly-state = truehttp-server = truehttp-bind-address = \”0.0.0.0\”http-port = \”10001\”

5.7 启动Replication Manager# systemctl start replication-manager# netstat -lntp|grep replicationtcp6 0 0 :::10001 :::* LISTEN 13128/replication-m tcp6 0 0 :::10005 :::* LISTEN 13128/replication-m

5.8 WEB登录

http://192.168.30.41:10001默认用户名密码为 admin/repman