StoneDB主從設定及切換實踐方案

2022-10-14 14:03:59

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 root@192.168.30.42:/tmp
scp /tmp/aa.sql root@192.168.30.43:/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 statusG

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 statusG

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

6、建議項

1）建議設定為 GTID 模式；

2）建議主從設定成半同步模式；

3）StoneDB 不參與主從切換。

到此這篇關於StoneDB主從設定及切換實踐方案的文章就介紹到這了,更多相關StoneDB主從設定內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！