StoneDB 的主从切换既能够手动切换，也能够主动切换，主动切换通常须要应用第三方中间件。本文介绍的是较为罕用的中间件 Replication Manager，当 master 产生宕机时，可主动切换至 slave，保障业务失常运行，故障节点复原后再退出主从。

服务器配置阐明

IPMemoryCPUOS version
192.168.30.408G8CCentOS Linux release 7.9
192.168.30.418G8CCentOS Linux release 7.9
192.168.30.428G8CCentOS Linux release 7.9
192.168.30.4616G16CCentOS Linux release 7.9

注：主从环境中的各个服务器的配置个别状况下倡议是统一的，但因为 StoneDB 不论重放 binlog，还是用于 OLAP 场景的查问，都是较耗费系统资源的，倡议 StoneDB 配置略高于 MySQL。

主从环境阐明

IPDATABASEROLEDB version
192.168.30.40MySQLmasterMySQL 5.7
192.168.30.41/Replication Manager/
192.168.30.42MySQLslaveMySQL 5.7
192.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 [email protected]:/tmpscp /tmp/aa.sql [email protected]:/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

6、倡议项

1）倡议设置为 GTID 模式；

2）倡议主从配置成半同步模式；

3）StoneDB 不参加主从切换。