openeuler20.03下安装Mysql InnodbCluster 集群
在openeuler20.03下安装Mysql InnodbCluster 集群，通过Keepalived实现高可用
·
规划：
使用四台服务器，具体如下表所示：
| IP | Mysql | MysqlShell | Keepalived | MysqlRouter |
|---|---|---|---|---|
| 192.168.210.172 | 主 | 安装 | ||
| 192.168.210.173 | 备 | 安装 | ||
| 192.168.210.171 | 备 | 安装 | 主 | 安装 |
| 192.168.210.200 | 备 | 安装 |
说明：171、172、173分别安装Mysql、MysqlShell,作为数据库服务器节点，MysqlShell可以简单理解为MysqlRouter通过MysqlShell来操作Mysql数据库的shell脚本工具；在171和200上分别安装MysqlRouter和Keepalived，后面通过访问MysqlRouter来访问数据库集群，Keepalived虚拟出192.168.210.175为对外的vip。
安装步骤：
1、开放相关的端口
171、172、173三个服务器节点分别执行
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --add-port=33061/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all说明 ：3306为数据库端口，33061为MGR节点通信接口，若关闭防火墙可忽略此操作
171、200还需执行
firewall-cmd --add-port=6446-6449/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all说明 ：mysqlrouter对外相关端口
## MySQL Classic protocol
- Read/Write Connections:6446
- Read/Only Connections:6447
## MySQL X protocol
- Read/Write Connections:6448
- Read/Only Connections:6449
若关闭防火墙可忽略此操作，一般使用6446读写接口
2、安装Mysql数据库
在171、172、173三台服务器上安装Mysql
2.1基础环境设置
下面为这三台服务器的操作系统openEuler 20.03 (LTS-SP3)，建议操作系统不要用最新的（个人经验，保守点好）
# cat /etc/os-release
NAME="openEuler"
VERSION="20.03 (LTS-SP3)"
ID="openEuler"
VERSION_ID="20.03"
PRETTY_NAME="openEuler 20.03 (LTS-SP3)"
ANSI_COLOR="0;31"
# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.210.172 dbm1
192.168.210.173 dbm2
192.168.210.171 dbm3
192.168.210.200 dbcc
说明：可通过vim /etc/hosts 修改，将IP地址映射到主机名，方便后续配置，主机名（hostname ）根据自己情况更改。
2.2下载安装mysql8
下载地址
https://dev.mysql.com/downloads/mysql/
MySQL Community Server 8.0.33
Linux-Generic
mysql-8.0.33-linux-glibc2.28-x86_64根据cpu的指令集下载相应的版本
mkdir -p /disk/db/mysql/data
mv mysql-8.0.33-linux-glibc2.28-x86_64/* /disk/db/mysql/
说明：解压后直接剪切，不需要安装
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql /disk/db/mysql/
chmod 750 -R /disk/db/mysql/data
说明：设置mysql用户和用户组，并赋予mysql安装目录相应的权限
vim /etc/profile
PATH=/disk/db/mysql/bin:/disk/db/mysql/lib:$PATH
export PATH
source /etc/profile
说明：设置相关环境变量，source /etc/profile使得环境变量生效
vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
[client]
port=3306
socket=/disk/db/mysql/mysql.sock
[mysqld]
port=3306
server-id=172
gtid_mode=ON
enforce_gtid_consistency=ON
#存储引擎设置
##Group Replication 数据必须存储在InnoDB 事务引擎中， 所以要设置禁用其他引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#Group Replication 设置
#将 Group Replication 插件添加到服务器在启动时加载的插件列表中。这在生产部署中比手动安装插件更可取。
plugin_load_add='group_replication.so'
#组名称,必须是uuid
group_replication_group_name="8e62ccc8-0e47-11ee-a3a5-a4bf010731e4"
#插件在系统启动时是否自动启动，它确保可以在手动启动插件之前配置服务器
group_replication_start_on_boot=off
#本服务通信地址,三个节点根据情况更改
group_replication_local_address= "dbm1:33061"
#成员以及内部通信地址
group_replication_group_seeds= "dbm1:33061,dbm2:33061,dbm3:33061"
#指示插件是否引导group
group_replication_bootstrap_group=off
user=mysql
general_log = 1
general_log_file=/disk/db/mysql/mysql.log
socket=/disk/db/mysql/mysql.sock
basedir=/disk/db/mysql
datadir=/disk/db/mysql/data
log-bin=/disk/db/mysql/data/mysql-bin
innodb_data_home_dir=/disk/db/mysql/data
innodb_log_group_home_dir=/disk/db/mysql/data/
character-set-server=utf8mb4
lower_case_table_names=1
autocommit=1
default_authentication_plugin=mysql_native_password
symbolic-links=0
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default-time-zone = '+8:00'
wait_timeout=31536000
interactive_timeout=31536000
max_connections=1000
[mysqld_safe]
log-error=/disk/db/mysql/data/mysql.log
pid-file=/disk/db/mysql/data/mysql.pid
说明：server-id、group_replication_local_address、group_replication_group_seeds 根据三个具体安装的服务器做相应的修改
mysql初始化
cd /disk/db/mysql/bin
./mysqld --user=mysql --basedir=/disk/db/mysql/ --datadir=/disk/db/mysql/data/ --initialize
会输出初始化的结果，里面包含密码，保存到本地，后面需要使用到
复制 mysql.server 文件,先进入安装目录
cp ./support-files/mysql.server /etc/init.d/mysqld
chown 777 /etc/my.cnf
chmod +x /etc/init.d/mysqld
启动数据库
service mysqld start修改密码,并创建用户
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123_a456';
flush privileges;
use mysql;
update user set host='%' where user='root';
flush privileges;
ALTER USER 'root'@'%' IDENTIFIED BY '123_a456' PASSWORD EXPIRE NEVER;
flush privileges;
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
CREATE user 'ys'@'%';
alter user 'ys'@'%' identified with mysql_native_password by '123_a456';
grant all privileges on *.* to 'ys'@'%';
flush privileges;3、安装Mysql shell
安装到 /usr/local/下面
tar -zxvf mysql-shell-8.0.33-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-shell-8.0.33-linux-glibc2.12-x86-64bit mysql-shell
mv mysql-shell /usr/local/
chown -R mysql:mysql /usr/local/mysql-shell
添加环境变量 vim /etc/profile,source /etc/profile使得环境变量生效
export PATH=/disk/db/mysql/bin:/disk/db/mysql/lib:/usr/local/mysql-shell/bin:$PATH创建集群，172主节点执行
mysqlsh --mysql -uroot -h 127.0.0.1 -P 3306 -p
dba.configureInstance();
var cluster = dba.createCluster('ysCluster');
cluster.addInstance('root@dbm2:3306');
cluster.addInstance('root@dbm3:3306');
cluster.status()
如报错group_replication is stopped，删除报错的实例重新加入
cluster.removeInstance('root@dbm2:3306')
cluster.addInstance('root@dbm2:3306');
mysql> select * from performance_schema.replication_group_members;
dba.checkInstanceConfiguration("root@hostname:3306") #检查节点配置实例，用于加入cluster之前
dba.rebootClusterFromCompleteOutage('myCluster'); #重启
dba.dropMetadataSchema(); #删除schema
var cluster = dba.getCluster() #获取当前集群
cluster.status()
cluster.checkInstanceState("root@hostname:3306") #检查cluster里节点状态
cluster.rejoinInstance("root@hostname:3306") #重新加入节点
addcluster.dissolve({force：true}) #删除集群
cluster.addInstance("root@hostname:3306") #增加节点
cluster.removeInstance("root@hostname:3306") #删除节点
cluster.removeInstance('root@host:3306',{force:true}) #强制删除节点
cluster.dissolve({force:true}) #解散集群
cluster.describe(); #集群描述
4、安装MySQLRouter路由
171和200分别部署route的主节点和从节点
tar -zxvf mysql-router-8.0.33-linux-glibc2.28-x86_64.tar.gz
mv mysql-router-8.0.33-linux-glibc2.28-x86_64 mysql-router
mv mysql-router /usr/local/
chown -R mysql:mysql /usr/local/mysql-router
添加环境变量,source /etc/profile使得环境变量生效
export PATH=/disk/db/mysql/bin:/disk/db/mysql/lib:/usr/local/mysql-shell/bin:/usr/local/mysql-router/bin:$PATHmysqlrouter --bootstrap root@127.0.0.1:3306 --directory /usr/local/mysql-router/myrouter --user root
mysqlrouter -c /usr/local/mysql-router/myrouter/mysqlrouter.conf
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448
- Read/Only Connections: localhost:6449
启动路由
cd /usr/local/mysql-router/myrouter
./start.sh
至此，可通过访问171或者200服务器的6446端口访问数据库集群。
5、安装KeepAlived
下载后，解压安装到指定目录
tar -zxvf keepalived-2.2.8.tar.gz
cd keepalived-2.2.8
./configure --prefix=/soft/keepalived --sysconf=/etc
make && make install
cd /etc/keepalived
cp keepalived.conf.sample keepalived.conf
vim keepalived.conf
global_defs {
router_id mysql_master
}
vrrp_script check_mysqlrouter{
script /soft/keepalived/check_mysqlrouter.sh
interval 3
weight -20
rise 2 # 表示需要连续成功2次才能认为是成功的
fall 2 # 表示需要连续失败2次才能认为是失败的
user root # 指定哪个用户指定脚本
init_fail #设置默认脚本最初为失败状态，监测成功之后再转换为成功状态
}
vrrp_instance VI_1 {
state SLAVE
interface enp2s0
virtual_router_id 61
priority 51
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.210.175
}
track_script {
check_mysqlrouter
}
}
192.168.210.175 为虚拟ip，根据实际情况配置，不能和原有ip重复
使用到的检测脚本check_mysqlrouter.sh
HECK_TIME=3
STATUS_OK=1
function check_mysqlrouter_health (){
MYROUTER_PROCESS=`ps -ef |grep -w /usr/local/mysql-router/bin/mysqlrouter|grep -v grep|wc -l`
if [[ ${MYROUTER_PROCESS} -eq 1 ]] ;then
STATUS_OK=1
else
STATUS_OK=0
fi
return $STATUS_OK
}
while [[ $CHECK_TIME -ne 0 ]]
do
let "CHECK_TIME-=1"
check_mysqlrouter_health
if [[ $STATUS_OK = 1 ]] ; then
CHECK_TIME=0
exit 0
fi
if [[ $STATUS_OK -eq 0 ]] && [[ $CHECK_TIME -eq 0 ]]
then
systemctl stop keepalived.service
exit 1
fi
sleep 1
done
启动、并查看状态
systemctl start keepalived.service
systemctl status keepalived.service
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)