ssh mysql环境搭建_搭建一个MySQL高可用架构集群环境

架构

使用一台MHA manager、一台MySQL master节点、两台MySQL slave节点

软件版本

虚拟机：Ubuntu 18

MySQL：5.7.32

MHA：0.54

环境检查

安装ssh组件

# 安装ssh

sudo apt-get install openssh-server

# 开启ssh服务

sudo service ssh start

配置ssh 免密码登录生成密钥

shell ssh-keygen -t rsa将公钥写入到 authorized_keys 文件中

可以使用scp进行将公钥复制到一个机器上，然后一起写入到authorized_keys中将authorized_keys文件分发到各个节点的 /root/.ssh/目录下

MySQL安装

# 执行更新

sudo apt-get update

# 安装 mysql

sudo apt-get install mysql-server

# 初始化MySQL

sudo mysql_secure_installation

# 检查mysql状态

systemctl status mysql.service

MySQL初始化

修改配置文件，注释掉 bind-address = 127.0.0.1

vi /etc/mysql/mysql.conf.d/mysqld.cnf

主库配置(Master)MySQL配置文件配置

vim /etc/mysql/my.cnf

mysql

[mysqld]

character-set-server=utf8mb4

default-time-zone='+8:00'

innodb_rollback_on_timeout='ON'

max_connections=500

innodb_lock_wait_timeout=500

# 开启binlog功能

log_bin=mysql-bin

# 服务唯一id，不能重复

server-id=1

# 写入日志始终同步磁盘

sync-binlog=1

# 需要忽略同步binlog的数据库

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

重启mysql服务

shell sudo service mysql restart开始授权开启主从同步

# 每个slave使用标准的MySQL用户名和密码连接master。对进行复制操作的用户授予REPLICATION SLAVE权限

mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';

# 授于远程登录访问权限

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';

# 刷新 privileges(使privileges生效)

mysql> flush privileges;安装semi

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';开启semi(半同步复制)

# 查看是否开启了esmi

mysql> show variables like '%semi%';

# 是否开启半同步复制，需要开启

mysql> set global rpl_semi_sync_master_enabled=1;

# 配置同步延迟时间(毫秒)

mysql> set global rpl_semi_sync_master_trace_level=1000;

从库配置(Slave)MySQL配置文件配置

vim /etc/mysql/my.cnf

[mysqld]

character-set-server=utf8mb4

default-time-zone='+8:00'

innodb_rollback_on_timeout='ON'

max_connections=500

innodb_lock_wait_timeout=500

# 服务唯一id，不能重复

server-id=2

# 配置relay log

relay_log=mysql-relay-bin

# 配置数据库只读

read-only=1

#--------------备用master开启binlog-------------------#

# 写入日志始终同步磁盘

sync-binlog=1

# 需要忽略同步binlog的数据库

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

重启mysql服务

shell sudo service mysql restart授权

# 授于远程登录访问权限

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';

# 刷新 privileges(使privileges生效)

mysql> flush privileges;配置同步日志

mysql> change master to master_host='172.22.0.3', master_port=3306, master_user='root', master_password='root', master_log_file='mysql-bin.000006', master_log_pos=302;安装semi

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';开启semi

mysql mysql> set global rpl_semi_sync_slave_enabled=1;查看是否开启

mysql mysql> show variables like '%semi%';启动slave

mysql> start slave;

# 查看slave状态

mysql> show slave status;

MHA环境准备

组件介绍Manager节点

masterha_check_ssh:MHA依赖的SSH环境检测工具

masterha_check_repl:MySQL复制环境检测工具

masterha_manager:MHA服务主程序

masterha_check_status:MHA运行状态检测工具

masterha_master_monitor:MySQL master节点可用性检测工具

masterha_master_switch:master节点切换工具

masterha_conf_host:添加或删除配置节点

masterha_stop:关闭MHA服务的工具

Node节点

save_binary_logs:保存和复制master的二进制日志

apply_diff_relay_logs:识别差异的中继日志事件并应用于其他slave

filter_mysqlbinlog:去除不必要的ROLLBACK事件(MHA已不在使用这个工具)

purge_relay_logs:清除中继日志(不会阻塞SQL线程)

自定义扩展

sccondary_check_script:通过多条网络路由检测master的可用性

master_ip_failover_script:更新application使用的masterrip

shutdown_script：强制关闭master节点

report_script:发送报告

init_conf_load_script:加载初始化配置参数

master_ip_online_change_script:更新master节点ip地址

安装HMA node

所有节点都需要安装mha node

# install perl

apt-get install libdbd-mysql-perl

# 下载 mha node

wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-node_0.54-0_all.deb

# 安装 mha node

dpkg -i mha4mysql-node_0.54-0_all.deb

安装MHA manager

mha manager 节点安装

# install perl

apt-get install libdbd-mysql-perl

# 下载mha manager

wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-manager_0.54-0_all.deb

# 安装mha manager

dpkg -i mha4mysql-manager_0.54-0_all.deb

配置MHA manager

vim /etc/masterha_default.cnf

[server default]

# mysql user and password

user=root

password=root

#mysql复制帐号，用来在主从机之间同步二进制日志等

repl_user=root

repl_password=root

#ping间隔，用来检测master是否正常

ping_interval=1

# working directory on the manager

manager_workdir=/masterha/app1

# manager log file

manager_log=/masterha/app1/app1.log

# working directory on MySQL servers

remote_workdir=/masterha/app1

[server1]

hostname=10.6.130.42

#候选人，master挂掉时候优先让它顶

candidate_master=1

#用防止master故障时，切换时slave有延迟，卡在那里切不过来

check_repl_delay=0

[server2]

hostname=10.6.130.43

#候选人，master挂掉时候优先让它顶

candidate_master=1

#用防止master故障时，切换时slave有延迟，卡在那里切不过来

check_repl_delay=0

[server3]

hostname=10.6.130.44

#不能成为master

no_master=1

#用防止master故障时，切换时slave有延迟，卡在那里切不过来

check_repl_delay=0

检测 ssh 通讯

masterha_check_ssh --conf=/etc/masterha_default.cnf

检测主从节点

masterha_check_repl --conf=/etc/masterha_default.cnf

启动 MHA

masterha_manager --conf=/etc/masterha_default.cnf --ignore_last_failover &

检测 MHA 状态

masterha_check_status --conf=/etc/masterha_default.cnf

检验MHA服务

service mysql stop

观察mha mannager 日志

Wed Dec 2 15:41:33 2020 - [info] Resetting slave info on the new master..

Wed Dec 2 15:41:33 2020 - [info] 10.6.130.43: Resetting slave info succeeded.

Wed Dec 2 15:41:33 2020 - [info] Master failover to 10.6.130.43(10.6.130.43:3306) completed successfully.

Wed Dec 2 15:41:33 2020 - [info]

----- Failover Report -----

masterha_default: MySQL Master failover 10.6.130.42 to 10.6.130.43 succeeded

Master 10.6.130.42 is down!

Check MHA Manager logs at NXG-AUTO-BASE:/masterha/app1/app1.log for details.

Started automated(non-interactive) failover.

The latest slave 10.6.130.43(10.6.130.43:3306) has all relay logs for recovery.

Selected 10.6.130.43 as a new master.

10.6.130.43: OK: Applying all logs succeeded.

10.6.130.44: This host has the latest relay log events.

Generating relay diff files from the latest slave succeeded.

10.6.130.44: OK: Applying all logs succeeded. Slave started, replicating from 10.6.130.43.

10.6.130.43: Resetting slave info succeeded.

Master failover to 10.6.130.43(10.6.130.43:3306) completed successfully.

表创建

position：id(int)、name(varchar)、salary(varchar)、city(varchar)

position_detail：id(int)、pid(int)、description(text)

mysql> create database lagou;

mysql> use lagou;

# 创建表positionCREATE TABLE position (

`id` int(11) NOT NULL,

`name` varchar(20) DEFAULT NULL,

`salary` decimal(10,2) DEFAULT NULL,

`city` varchar(20) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据mysql> insert into position values(2, 'tengfei', '30000', 'beijing');

# 创建表position_detailCREATE TABLE position_detail (

`id` int(0) NOT NULL,

`pid` int(0) NULL,

`description` text NULL,

PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意事项虚拟机要安装openssh组件，不然无法外部访问

各个节点之间需要将各个节点的公钥放到.ssh/authorized_keys 下

MySQL 配置文件中记得注释掉 bind-address = 127.0.0.1

修改mysql 配置文件要重启

安装hma组件之前需要安装libdbd-mysql-perl

参考文章

使用的docker compose文件

version:'3.3'services:mysql-master1:image:mysql:5.7container_name:mysql-master1ports:- "3316:3306"restart:alwaysvolumes:- ./master1/data:/var/lib/mysql- ./master1/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-master2:image:mysql:5.7container_name:mysql-master2ports:- "3326:3306"restart:alwaysvolumes:- ./master2/data:/var/lib/mysql- ./master2/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave1:image:mysql:5.7container_name:mysql-slave1ports:- "3317:3306"restart:alwaysvolumes:- ./slave1/data:/var/lib/mysql- ./slave1/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave2:image:mysql:5.7container_name:mysql-slave2ports:- "3318:3306"restart:alwaysvolumes:- ./slave2/data:/var/lib/mysql- ./slave2/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave3:image:mysql:5.7container_name:mysql-slave3ports:- "3327:3306"restart:alwaysvolumes:- ./slave3/data:/var/lib/mysql- ./slave3/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave4:image:mysql:5.7container_name:mysql-slave4ports:- "3328:3306"restart:alwaysvolumes:- ./slave4/data:/var/lib/mysql- ./slave4/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"

master 配置文件

[mysqld]

character-set-server=utf8mb4

default-time-zone='+8:00'

innodb_rollback_on_timeout='ON'

max_connections=500

innodb_lock_wait_timeout=500

# enable binary log

log_bin=mysql-bin

server-id=1

# write log always sync disk

sync-binlog=1

# ignore sync database

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

slave配置文件

[mysqld]

character-set-server=utf8mb4

default-time-zone='+8:00'

innodb_rollback_on_timeout='ON'

max_connections=500

innodb_lock_wait_timeout=500

server-id=11

relay_log=mysql-relay-log

read-only=1

机器对应角色&作用&ip地址

mysql-master1

作用：mysql 主节点1，主要做写入操作

IP地址：172.17.0.5

mysql-master2

作用：mysql 主节点2，主要做写入操作

IP地址：172.17.0.5

mysql-slave1

作用：mysql 从节点1，隶属于master1的从节点

IP地址：172.17.0.5

mysql-slave2

作用：mysql 从节点2，隶属于master1的从节点

IP地址：172.17.0.5

mysql-slave3

作用：mysql 从节点3，隶属于master2的从节点

IP地址：172.17.0.5

mysql-slave4

作用：mysql 从节点4，隶属于master2的从节点

IP地址：172.17.0.5