StoneDB 的主從切換既可以手動切換，也可以自動切換，自動切換通常需要使用第三方中間件。本文介紹的是較為常用的中間件 Replication Manager，當 master 發生當機時，可自動切換至 slave，保證業務正常運作，故障節點恢複後再加入主從。

伺服器配置說明

IP	Memory	CPU	OS version
192.168.30.40	8G	8C	CentOS Linux release 7.9
192.168.30.41	8G	8C	CentOS Linux release 7.9
192.168.30.42	8G	8C	CentOS Linux release 7.9
192.168.30.46	16G	16C	CentOS Linux release 7.9

注：主從環境中的各個伺服器的配置一般情況下建議是一緻的，但由于 StoneDB 不管重放 binlog，還是用于 OLAP 場景的查詢，都是較消耗系統資源的，建議 StoneDB 配置略高于 MySQL。

主從環境說明

IP	DATABASE	ROLE	DB version
192.168.30.40	MySQL	master	MySQL 5.7
192.168.30.41	/	Replication Manager	/
192.168.30.42	MySQL	slave	MySQL 5.7
192.168.30.46	StoneDB	slave	StoneDB 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/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

Replication Manager 節點無需建立，以上步驟執行完之後，重新開機作業系統。

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"，說明缺少檔案，需要安裝對應的依賴包。例如：

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/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

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]:/tmp
scp /tmp/aa.sql [email protected]:/tmp

注：如果資料較大，建議使用 mydumper.

4.4 slave1節點

/usr/local/mysql/bin/mysql -uroot -pmysql123 -S /mysqldb/data/mysql.sock
source /tmp/aa.sql

注：恢複前需要確定 gtid_executed 為空。

4.5 slave2節點

在恢複前，需要修改存儲引擎，注釋鎖表語句。

sed -i 's/UNLOCK TABLES/-- UNLOCK TABLES/g' /tmp/aa.sql
sed -i 's/LOCK TABLES `/-- LOCK TABLES `/g' /tmp/aa.sql
sed -i 's/ENGINE=InnoDB/ENGINE=tianmu/g' /tmp/aa.sql

/stonedb57/install/bin/mysql -uroot -pmysql123 -S /stonedb57/install/tmp/mysql.sock
source /tmp/aa.sql

注：恢複前需要確定 gtid_executed 為空。

4.6 建立主從複制

slave1節點

CHANGE MASTER TO
MASTER_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 TO
MASTER_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/hosts
192.168.30.40 HAMI01
192.168.30.41 HAMI02
192.168.30.42 HAMI03
192.168.30.46 HAST05

5.2 配置免密

在 Replication Manager 節點配置

ssh-keygen
ssh-copy-id HAMI01
ssh-copy-id HAMI03
ssh-copy-id HAST05

ssh HAMI01
ssh HAMI03
ssh HAST05

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

5.3 配置yum源

# vim /etc/yum.repos.d/signal18.repo
[signal18]
name=Signal18 repositories
baseurl=http://repo.signal18.io/centos/2.1/$releasever/$basearch/
gpgcheck=0
enabled=1

5.4 安裝Replication Manager

# yum install -y replication-manager-osc
# rpm -qa|grep replication
replication-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 = false
log-syslog = false
monitoring-datadir = "/var/lib/replication-manager"
log-level=1

replication-multi-master = false
replication-multi-tier-slave = false
failover-readonly-state = true
http-server = true
http-bind-address = "0.0.0.0"
http-port = "10001"

5.7 啟動Replication Manager

# systemctl start replication-manager
# netstat -lntp|grep replication
tcp6       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

StoneDB主從切換實踐方案1、作業系統環境檢查2、部署MySQL3、部署StoneDB4、配置主從5、配置Replication Manager6、建議項