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
file

6、建議項

1）建議設定為 GTID 模式；

2）建議主從配置成半同步模式；

3）StoneDB 不參與主從切換，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/513727.html

標籤：其他

上一篇：[Raft共識演算法] Dragonboat Log Replication 代碼走讀

下一篇：[Raft共識演算法] Dragonboat Log Replication 代碼走讀

StoneDB主從切換實踐方案