关于数据库:StoneDB主从切换实践方案

StoneDB 的主从切换既能够手动切换，也能够主动切换，主动切换通常须要应用第三方中间件。本文介绍的是较为罕用的中间件 Replication Manager，当 master 产生宕机时，可主动切换至 slave，保障业务失常运行，故障节点复原后再退出主从。

服务器配置阐明

注：主从环境中的各个服务器的配置个别状况下倡议是统一的，但因为 StoneDB 不论重放 binlog，还是用于 OLAP 场景的查问，都是较耗费系统资源的，倡议 StoneDB 配置略高于 MySQL。

主从环境阐明

注：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

6、倡议项

1）倡议设置为 GTID 模式；

2）倡议主从配置成半同步模式；

3）StoneDB 不参加主从切换。