MHA高可用搭建

1. 准备环境

4.4 GTID复制配置过程：
4.4.1 清理环境
pkill mysqld
 
m -rf /data/*
mkdir -p /data/mysql/data
mkdir -p /data/binlog
chown -R mysql.mysql /data
4.4.2 准备配置文件

主库db01：
cat > /etc/my.cnf <<'EOF'
[mysqld]
basedir=/appliction/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [d]>
EOF


slave1(db02)：
cat > /etc/my.cnf <<'EOF'
[mysqld]
basedir=/appliction/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [d]>
EOF


- slave2(db03)：
cat > /etc/my.cnf <<'EOF'
[mysqld]
basedir=/appliction/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [d]>
EOF

4.4.3 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/appliction/mysql  --datadir=/data/mysql/data 

4.4.4 启动数据库
systemctl start mysqld

4.4.5 构建主从：
master:51
slave:52,53
51:
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
5253:
change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

1.1 配置关键程序软连接

ln -s /application/mysql/bin/mysqlbinlog    /usr/bin/mysqlbinlog
ln -s /application/mysql/bin/mysql          /usr/bin/mysql

1.2 配置各节点互信

db01：
rm -rf /root/.ssh 
ssh-keygen
cd /root/.ssh 
mv id_rsa.pub authorized_keys
scp  -r  /root/.ssh  10.0.0.52:/root 
scp  -r  /root/.ssh  10.0.0.53:/root 

各节点验证
db01:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db02:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db03:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date

1.3 安装软件

下载mha软件
mha官网：https://code.google.com/archive/p/mysql-master-ha/
github下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
所有节点安装Node软件依赖包
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
在db01主库中创建mha需要的用户
grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';

8.0：
create user mha@'10.0.0.%' identified with mysql_native_password by 'mha';
grant all on *.* to mha@'10.0.0.%';
 
Manager软件安装（db03）
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

1.4 配置文件准备(db03)

创建配置文件目录
 mkdir -p /etc/mha
创建日志目录
mkdir -p /var/log/mha/app1
编辑mha配置文件
cat > /etc/mha/app1.cnf<<EOF
[server default]
manager_log=/var/log/mha/app1/manager        
manager_workdir=/var/log/mha/app1            
master_binlog_dir=/data/binlog     
user=mha                                   
password=mha                             
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root                            
[server1]                                   
hostname=10.0.0.51
port=3306                                 
[server2]            
hostname=10.0.0.52
candidate_master=1
port=3306
[server3]
hostname=10.0.0.53
port=3306
EOF

1.5 状态检查(db03)

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

1.6 开启MHA(db03)：

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 &

1.7 查看MHA状态

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf