MHA+GTID-526互联

MHA+GTID
MHA+GTID

# 主库配置文件
cat >/etc/my.cnf<< EOF
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
socket=/app/mysql/data/mysql.sock
server_id=1
gtid_mode=on
enforce_gtid_consistency
log-bin=mysql-bin
[mysql]
user=root
password=123
socket=/app/mysql/data/mysql.sock
EOF

# 从库配置
cat >/etc/my.cnf<< EOF
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
socket=/app/mysql/data/mysql.sock
log-bin=mysql-bin
server_id=2
gtid_mode=on
enforce_gtid_consistency
log-slave-updates          // 级联复制
[mysql]
user=root
password=123
socket=/app/mysql/data/mysql.sock
prompt=\u@\h[\d]>
EOF

# 1.创建主从用户
grant replication slave on *.* to rep@'%' identified by '123';

# 2.从库执行change master
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_auto_position=1;

# 3.开启主从复制
mysql> start slave;

# 3.5.检查主从状态
mysql> show slave status\G
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---有报错自己解决---俩yes就对了

# 4.所有库，要禁用自动删除relay log的功能
## 临时生效
root@localhost [(none)] >set global relay_log_purge = 0;
## 永久生效
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
relay_log_purge=0

# 5.从库只读（不能配置在配置文件中）
只有从库操作
set global read_only=1;

# 6.创建MHA用户和主从用户（全部都创建）
grant replication slave on *.* to rep@'%' identified by '123';
grant all on *.* to mha@'%' identified by 'mha';

# 7.全部安装node
yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm 

# 7.5.安装包下载地址
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

# 8.安装manager
[root@db04 ~]# yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm 

# 9.建立软连接（全部都创建）
软链接mysql和mysqlbinlog
ln -s /app/mysql/bin/mysql /usr/bin/mysql
ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

# 10.创建密钥对（所有数据库之间免密）
ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
ssh-copy-id -i ~/.ssh/id_dsa.pub 172.16.1.51
ssh-copy-id -i ~/.ssh/id_dsa.pub 172.16.1.52
ssh-copy-id -i ~/.ssh/id_dsa.pub 172.16.1.53
ssh-copy-id -i ~/.ssh/id_dsa.pub 172.16.1.54

# 10.5检测ssh
for i in 51 52 53 54;do ssh 172.16.1.$i "echo $i OK";done
51 OK
52 OK
53 OK
54 OK

# 11.创建mha配置文件目录
[root@db04 ~]# mkdir /etc/mha

# 12.创建mha配置文件
cat >/etc/mha/app1.cnf<<EOF
[server default]
manager_log=/etc/mha/logs/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/app/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
ssh_port=22
[server1]
hostname=172.16.1.51
port=3306
[server2]
#candidate_master=1
#check_repl_delay=0
hostname=172.16.1.52
port=3306
[server3]
hostname=172.16.1.53
port=3306
[server4]
hostname=172.16.1.54
port=3306
EOF

# 4.检测ssh
[root@db04 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Tue Aug  8 16:24:57 2023 - [info] All SSH connection tests passed successfully.
# 5.检测主从复制
[root@db04 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
# 6.启动mha
mkdir /etc/mha/logs
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --
ignore_last_failover < /dev/null > /etc/mha/logs/manager.log 2>&1 &
[root@db04 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:21986) is running(0:PING_OK), master:172.16.1.51
GTID