# mysql-MHA

**Repository Path**: linestyle007/mysql-mha

## Basic Information

- **Project Name**: mysql-MHA
- **Description**: mysql-MHA
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-07-27
- **Last Updated**: 2021-11-02

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# mysql-MHA

[参考网址]: https://blog.csdn.net/weixin_44796239/article/details/118679179?utm_source=app&app_version=4.11.0&code=app_1562916241&uLinkId=usr1mkqgl919blen



## 安装

## root启动

* 开机按‘e’
* 找到 US.UTF-8 那一行，追加 init=/bin/sh
* Ctrl + x
* $ mount -o remount,rw / 
* $ passwd root
* $ touch /.autorelabel
* exec /sbin/init

## 安装mysql

```scala
tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
# 卸载系统自带的
rpm -qa | grep mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

# 安装依赖
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm 
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

# 初始化用户
mysqld --initialize --user=mysql
# 查看初始密码 => 2oVMd/i/01sL
cat /var/log/mysqld.log | grep password
# 启动mysql服务
systemctl start mysqld
# 配置开始启动
systemctl enable mysqld

# 登录
mysql -uroot -p'2oVMd/i/01sL'
# 设置密码
mysql> SET PASSWORD = PASSWORD('root');
```

## 关闭防火墙

```scala
systemctl stop firewalld
# 永久关闭防火墙：（可选）
systemctl disable firewalld

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-ports
```

## mysql集群

```scala
master: 172.16.65.139
slave1: 172.16.65.137
slave2: 172.16.65.141
MHA: 172.16.65.140

# 重启mysql
systemctl restart mysqld

# 主库给从库授权
# master
mysql -uroot -p'root';

grant replication slave on *.* to root@'%' identified by 'root';
grant all privileges on *.* to root@'%' identified by 'root';
flush privileges;
show master status;
```

### master

* 172.16.65.139

```scala
$ vim /etc/my.cnf

#bin_log配置 
log_bin=mysql-bin 
server-id=1 
sync-binlog=1 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema 
binlog-ignore-db=sys 
#relay_log配置 
relay_log=mysql-relay-bin 
log_slave_updates=1 
relay_log_purge=0

$ systemctl restart mysqld
```

### slave1/slave2

* 172.16.65.137
* 172.16.65.141

```scala
1.修改mysql配置
$ vim /etc/my.cnf
#bin_log配置 
log_bin=mysql-bin 
#服务器ID,从库1是2,从库2是3 
server-id=2 
sync-binlog=1 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema 
binlog-ignore-db=sys 
#relay_log配置 
relay_log=mysql-relay-bin 
log_slave_updates=1 
relay_log_purge=0 
read_only=1

2.重启服务
$ systemctl restart mysqld

3.开启同步
mysql -uroot -p'root';
change master to master_host='172.16.65.139',master_port=3306,master_user='root',master_password ='root',master_log_file='mysql-bin.000001',master_log_pos=869; 

start slave; // 开启同步
```

## 配置半同步复制

### master

```scala
1.安装插件
$ mysql -uroot -p'root';
$ install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
show variables like '%semi%';

2.修改配置
$ vim /etc/my.cnf
# 自动开启半同步复制 
rpl_semi_sync_master_enabled=ON 
rpl_semi_sync_master_timeout=1000

3.重启
$ systemctl restart mysqld
```

### Slave节点

* slave1
* slave2

```scala
1.安装插件
$ mysql -uroot -p'root';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

2.修改MySQL配置文件
$ vim /etc/my.cnf
# 自动开启半同步复制
rpl_semi_sync_slave_enabled=ON

3.重启
$ systemctl restart mysqld

4.测试
mysql -uroot -p'root';
show variables like '%semi%';
```

## 其它

```scala
1.将3台mysql服务器ssh copy到管理服务器
ssh-copy-id 172.16.65.140

2.将MHA密钥也添加进去
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys

3.向其他三台MySQL服务器分发公钥信息
scp /root/.ssh/authorized_keys root@172.16.65.139:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@172.16.65.137:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@172.16.65.141:/root/.ssh/authorized_keys

4.测试互通性
ssh 172.16.65.139
ssh 172.16.65.137
ssh 172.16.65.141

master: 172.16.65.139
slave1: 172.16.65.137
slave2: 172.16.65.141
MHA: 172.16.65.140
```

## MHA下载安装

### MHA node安装

* master
* slave1
* slave2
* MHA

```scala
yum install perl-DBD-MySQL -y 

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
```

### MHA manager安装

* MHA Manager服务器

```scala
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
```

## MHA 配置文件

```scala
#目录说明 
#/var/log 									(CentOS目录) 
# 		/mha 								(MHA监控根目录) 
# 			/app1 							(MHA监控实例根目录)
#				/manager.log   				(MHA监控实例日志文件) 

mkdir -p /var/log/mha/app1 
touch /var/log/mha/app1/manager.log

2.在master库创建用户
mysql -uroot -p'root';
set global read_only=1;

create user 'mha'@'%' identified by 'root';
grant all on *.* to mha@'%' identified by 'root';
flush privileges;

3.修改MHA配置
vim /etc/masterha_default.cnf

[server default] 
#主库用户名，在master mysql的主库执行下列命令建一个新用户 
#create user 'mha'@'%' identified by 'root'; 
#grant all on *.* to mha@'%' identified by 'root'; 
#flush privileges; 
user=mha 
password=root 
port=3306 
#ssh登录账号 
ssh_user=root
#从库复制账号和密码 
repl_user=root 
repl_password=root 
port=3306 
#ping次数 
ping_interval=1 
#二次检查的主机 
secondary_check_script=masterha_secondary_check -s 172.16.65.139 -s 172.16.65.137 -s 172.16.65.141

#master: 172.16.65.139
#slave1: 172.16.65.137
#slave2: 172.16.65.141
#MHA: 172.16.65.140
4.配置监控实例配置文件
$ mkdir -p /etc/mha

$ vim /etc/mha/app1.cnf
[server default]
#MHA监控实例根目录 
manager_workdir=/var/log/mha/app1 
#MHA监控实例日志文件 
manager_log=/var/log/mha/app1/manager.log 

#[serverx] 			服务器编号 
#hostname 			主机名 
#candidate_master 	可以做主库 
#master_binlog_dir 	binlog日志文件目录 

[server1] 
hostname=172.16.65.139
candidate_master=1 
master_binlog_dir="/var/lib/mysql" 

[server2] 
hostname=172.16.65.137
candidate_master=1 
master_binlog_dir="/var/lib/mysql"

[server3] 
hostname=172.16.65.141
candidate_master=1 
master_binlog_dir="/var/lib/mysql"

5.MHA 配置检测
masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

6.启动
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

# 查看监控状态
masterha_check_status --conf=/etc/mha/app1.cnf

tail -f /var/log/mha/app1/manager.log
tail -200f /var/log/mha/app1/manager.log

masterha_check_repl --conf=/etc/mha/app1.cnf
```

## 测试

```scala
1.DDL
CREATE DATABASE test_db;

create TABLE position ( 
	id int(20), name varchar(50), 
	salary varchar(20), 
	city varchar(50) ) ENGINE=innodb charset=utf8;
insert into position values(1, 'Java', 13000, 'shanghai'); 
insert into position values(2, 'DBA', 20000, 'beijing');

create TABLE position_detail (
	id int(20), 
	pid int(20), 
	description text ) ENGINE=innodb charset=utf8;
insert into position_detail values(1, 1, 'Java Developer'); 
insert into position_detail values(2, 2, 'Database Administrator');

2.从库查看
mysql -uroot -p'root';
```

## 问题记录

### 数据库防火墙问题

```scala
systemctl stop firewalld
# 永久关闭防火墙：（可选）
systemctl disable firewalld

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-ports
```

### 从数据库报错

https://www.cnblogs.com/Courage129/p/14169251.html