網(wǎng)站首頁編程語言正文

StoneDB主從配置及切換實踐方案_數(shù)據(jù)庫其它

作者：來來士 ? 更新時間： 2022-11-23 編程語言

StoneDB 的主從切換既可以手動切換，也可以自動切換，自動切換通常需要使用第三方中間件。本文介紹的是較為常用的中間件 Replication Manager，當 master 發(fā)生宕機時，可自動切換至 slave，保證業(yè)務(wù)正常運行，故障節(jié)點恢復(fù)后再加入主從。

服務(wù)器配置說明

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

注：主從環(huán)境中的各個服務(wù)器的配置一般情況下建議是一致的，但由于 StoneDB 不管重放 binlog，還是用于 OLAP 場景的查詢，都是較消耗系統(tǒng)資源的，建議 StoneDB 配置略高于 MySQL。

主從環(huán)境說明

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 的版本建議保持一致。

推薦采用一主兩從的架構(gòu)，其中 StoneDB 不參與主從切換：

1）master（192.168.30.40）使用 InnoDB 引擎，可讀寫，提供 OLTP 場景的讀寫業(yè)務(wù)；

2）slave1（192.168.30.42）使用 InnoDB 引擎，只讀，同時作為 standby，當 master 發(fā)生宕機時，可切換至 slave1，保證業(yè)務(wù)正常運行；

3）slave2（192.168.30.46）使用 Tianmu 引擎，只讀，提供 OLAP 場景的讀業(yè)務(wù)。

1、操作系統(tǒng)環(huán)境檢查

操作系統(tǒng)環(huán)境檢查的步驟在四個節(jié)點均需要執(zhí)行。

1.1 關(guān)閉防火墻

# systemctl stop firewalld 
# systemctl disable firewalld

1.2 關(guān)閉SELINUX

# vim /etc/selinux/config
SELINUX = disabled

1.3 設(shè)置Swap分區(qū)

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

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

1.4 修改操作系統(tǒng)的限制

# 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

修改操作系統(tǒng)的軟硬限制
# vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
mysql soft nproc 1028056
mysql hard nproc 1028056

1.5 創(chuàng)建用戶

# groupadd mysql
# useradd -g mysql mysql
# passwd mysql

Replication Manager 節(jié)點無需創(chuàng)建，以上步驟執(zhí)行完之后，重啟操作系統(tǒng)。

2、部署MySQL

在 master 節(jié)點和 slave1 節(jié)點安裝 MySQL。

2.1 下載安裝包

https://downloads.mysql.com/archives/community/從官網(wǎng)下載 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 創(chuàng)建目錄

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

2.5 配置參數(shù)文件 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

#并行復(fù)制
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

#并行復(fù)制
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/從官網(wǎng)下載 StoneDB 5.7 的安裝包。

3.2 上傳tar包并解壓

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

用戶可根據(jù)安裝規(guī)范將安裝包上傳至服務(wù)器，解壓出來的目錄是 stonedb57，示例中的安裝路徑是 /stonedb57。

3.3 檢查依賴文件

# cd /stonedb57/install/bin
# ldd mysqld
# ldd mysql

如果檢查返回有關(guān)鍵字"not found"，說明缺少文件，需要安裝對應(yīng)的依賴包。例如：

libsnappy.so.1 => not found

在 Ubuntu 上使用命令 "sudo apt search libsnappy" 檢查，說明需要安裝 libsnappy-dev。在 RedHat 或者 CentOS 上使用命令 "yum search all snappy" 檢查，說明需要安裝 snappy-devel、snappy。

3.4 創(chuàng)建目錄

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 配置參數(shù)文件 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

#并行復(fù)制
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 創(chuàng)建復(fù)制用戶

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

注：如果數(shù)據(jù)較大，建議使用 mydumper.

4.4 slave1節(jié)點

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

注：恢復(fù)前需要確保 gtid_executed 為空。

4.5 slave2節(jié)點

在恢復(fù)前，需要修改存儲引擎，注釋鎖表語句。

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

注：恢復(fù)前需要確保 gtid_executed 為空。

4.6 建立主從復(fù)制

slave1節(jié)點

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節(jié)點

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文件

在所有節(jié)點都要配置

# 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 節(jié)點配置

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 主庫創(chuàng)建監(jiān)控用戶

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]
# 主從節(jié)點
db-servers-hosts = "192.168.30.40:3306,192.168.30.42:3306,192.168.30.46:3306"
# 主節(jié)點
db-servers-prefered-master = "192.168.30.40:3306"
# 監(jiān)控用戶
db-servers-credential = "rep_monitor:mysql123"
db-servers-connect-timeout = 2
# 復(fù)制用戶
replication-credential = "repl:mysql123"
# StoneDB不被用于切換
db-servers-ignored-hosts="192.168.30.46:3306"

##############
## FAILOVER ##
##############
# 故障自動切換
failover-mode = "automatic"
# 30s內(nèi)再次發(fā)生故障不切換，防止硬件問題或網(wǎng)絡(luò)問題
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）建議設(shè)置為 GTID 模式；

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

3）StoneDB 不參與主從切換。

原文鏈接：https://www.cnblogs.com/yangwilly/archive/2022/10/11/16778554.html

上一篇：Python數(shù)據(jù)清洗&預(yù)處理入門教程_python
下一篇：GoLang?channel底層代碼實現(xiàn)詳解_Golang

日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

網(wǎng)站首頁 編程語言 正文