# StoneDB 主從切換實踐方案

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 關閉防火牆

```shell
# systemctl stop firewalld 
# systemctl disable firewalld
```

## 1.2 關閉 SELINUX

```shell
# vim /etc/selinux/config
SELINUX = disabled
```

## 1.3 設定 Swap 分割槽

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

```shell
# vi /etc/sysctl.conf
vm.swappiness = 1
```

## 1.4 修改作業系統的限制

```shell
# 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 建立使用者

```shell
# groupadd mysql
# useradd -g mysql mysql
# passwd mysql
```

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

# 2、部署 MySQL

在 master 節點和 slave1 節點安裝 MySQL。

## 2.1 下載安裝包

從官網下載 MySQL 5.7 的安裝包。

## 2.2 解除安裝 mariadb

```shell
# 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 包並解壓

```shell
# 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 建立目錄

```shell
# mkdir -p /mysql/data/
# mkdir -p /mysql/log
# chown -R mysql:mysql /mysql/
```

## 2.5 配置引數檔案 my.cnf

### master

```shell
# 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

```shell
# 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 初始化例項

```shell
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
```

## 2.7 啟動例項

```shell
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
```

注：博主的環境都是部署在[cnaaa伺服器](https://www.cnaaa.com/)上的。管理員使用者的臨時密碼在 mysqld.log 中，第一次登陸後需要修改管理員使用者的密碼。

# 3、部署 StoneDB

## 3.1 下載安裝包

從官網下載 StoneDB 5.7 的安裝包。

## 3.2 上傳 tar 包並解壓

```shell
# cd /
# tar -zxvf stonedb-ce-5.7-v1.0.0.el7.x86_64.tar.gz
```

使用者可根據安裝規範將安裝包上傳至伺服器，解壓出來的目錄是 stonedb57，示例中的安裝路徑是 /stonedb57。

## 3.3 檢查依賴檔案

```shell
# 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 建立目錄

```shell
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

```shell
# 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 初始化例項

```shell
/stonedb57/install/bin/mysqld --defaults-file=/stonedb57/install/my.cnf --initialize --user=mysql
```

## 3.7 啟動例項

```shell
/stonedb57/install/bin/mysqld_safe --defaults-file=/stonedb57/install/my.cnf --user=mysql &
```

注：管理員使用者的臨時密碼在 mysqld.log 中，第一次登陸後需要修改管理員使用者的密碼。

# 4、配置主從

## 4.1 建立複製使用者

```sql
create user 'repl'@'%' identified by 'mysql123';
grant replication slave on *.* to 'repl'@'%';
```

## 4.2 備份主庫

```shell
/usr/local/mysql/bin/mysqldump -uroot -pmysql123 --single-transaction --set-gtid-purged=on -B aa > /tmp/aa.sql
```

## 4.3 傳輸備份檔案

```shell
scp /tmp/aa.sql root@192.168.30.42:/tmp
scp /tmp/aa.sql root@192.168.30.43:/tmp
```

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

## 4.4 slave1 節點

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

注：恢復前需要確保 gtid_executed 為空。

## 4.5 slave2 節點

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

```shell
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 節點

```sql
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 節點

```sql
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 檔案

在所有節點都要配置

```shell
# 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 節點配置

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

ssh HAMI01
ssh HAMI03
ssh HAST05
```

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

## 5.3 配置 yum 源

```shell
# 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

```shell
# yum install -y replication-manager-osc
# rpm -qa|grep replication
replication-manager-osc-2.2.20-1.x86_64
```

## 5.5 主庫建立監控使用者

```sql
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

```shell
# 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

```shell
# 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 登入

預設使用者名稱密碼為 admin/repman ![file](https://oscimg.oschina.net/oscnet/up-fbc6ddce6ccfe9e62902c4badc98c287d7c.png)

# 6、建議項

1）建議設定為 GTID 模式；

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

3）StoneDB 不參與主從切換。

StoneDB 主從切換實踐方案

相關文章