openeuler20.03下安装Mysql InnodbCluster 集群

最新推荐文章于 2024-09-23 19:52:00 发布

yesi330

最新推荐文章于 2024-09-23 19:52:00 发布

阅读量356

点赞数

分类专栏： openeuler 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/yesi330/article/details/132476630

版权

openeuler 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

规划：

使用四台服务器，具体如下表所示：

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:$PATH

mysqlrouter --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