乐趣区

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

jiezi

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

6、倡议项

1）倡议设置为 GTID 模式；

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

3）StoneDB 不参加主从切换。

退出移动版