Mysql高可用集群--MHA

systemctl stop firewalld && yum -y install net-tools vim wget git make cmake gcc gcc-c++

[root@localhost ~]# vim /etc/hosts
#添加
192.168.1.10 master
192.168.1.20 slave1
192.168.1.30 slave2
[root@localhost ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:lU9My4J5QGSrEV8wvMfN61Fxu9jIyCX6+WdxjYTykp8 root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|      .+B.. .    |
|       +.B = .. .|
|      . =o=o=. o.|
|       o.o+=+.o. |
|      . S.o+*++.o|
|         .oo++.+o|
|          .+.o  o|
|           oE  o |
|            ..o  |
+----[SHA256]-----+
[root@master ~]# ssh-copy-id -i slave1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host 'slave1 (192.168.1.20)' can't be established.
ECDSA key fingerprint is SHA256:dnnDcAA2qVnA31i7mtr9LYJmH2veu2+r4t+19qUSqqw.
ECDSA key fingerprint is MD5:0e:f3:c1:3c:dc:5f:12:66:ae:c9:01:51:66:db:bb:02.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@slave1's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'slave1'"
and check to make sure that only the key(s) you wanted were added.
[root@master ~]# ssh-copy-id -i slave2
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host 'slave2 (192.168.1.30)' can't be established.
ECDSA key fingerprint is SHA256:xltTmad7PHB4XORvNjSpTaA4hdjQmix+o3VHfYgwuRE.
ECDSA key fingerprint is MD5:52:f6:2c:cc:d4:75:ce:06:c6:c0:1e:18:7c:4e:2c:73.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@slave2's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'slave2'"
and check to make sure that only the key(s) you wanted were added.

[root@localhost ~]# hostname master
[root@localhost ~]# bash
[root@master ~]# 
[root@master ~]# scp /etc/hosts slave1:/etc/hosts
hosts                                                    100%  218   174.0KB/s   00:00    
[root@master ~]# scp /etc/hosts slave2:/etc/hosts
hosts                                                    100%  218   117.7KB/s   00:00    

[root@localhost ~]# hostname slave1
[root@localhost ~]# bash
[root@slave1 ~]# ssh-copy-id -i master
[root@slave1 ~]# ssh-copy-id -i slave2

[root@localhost ~]# hostname slave2
[root@localhost ~]# bash
[root@slave2 ~]# ssh-copy-id -i master
[root@slave2 ~]# ssh-copy-id -i slave1

systemctl stop firewalld && setenforce 0

wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

yum -y install mysql57-community-release-el7-11.noarch.rpm

yum repolist enabled | grep mysql.*

yum install mysql-community-server

systemctl start mysqld.service

grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p
password：刚获取的随机密码
修改默认密码复杂难度
mysql>set global validate_password_policy=0;#设置密码检查策略为0
mysql>set global validate_password_length=1;#设置密码的长度为1

set password for 用户名 @localhost = password('新密码');

vim /etc/my.cnf

systemctl restart mysqld

mysql> grant replication slave on *.* to 'slave'@'192.168.1.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

slave1

slave2

systemctl restart mysqld

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000006 |     1049 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.10',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123.com',
    -> MASTER_LOG_FILE='master-bin.000006',
    -> MASTER_LOG_POS=1049;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

start slave;

show slave status\G;

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

链接：https://pan.baidu.com/s/1QOgJrkHItlCjwMX60h6s4A 
提取码：e9bi 
复制这段内容后打开百度网盘手机App，操作更方便哦

server-id=1
log-bin=master-bin
log-slave-updates=true
relay-log = relay-log-bin

server-id=2
log-bin=master-bin
log-slave-updates=true
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
read-only=1	# 启动只读属性
relay-log-purge=0	# 高可用进行切换时，从升为主时，保留之前的中继日志

grant all on *.* to 'mha'@'192.168.1.%' identified by '123.com';

grant replication slave on *.* to 'slave'@'192.168.1.%' identified by '123.com';

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      1021 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

change master to master_host='192.168.1.10',master_user='slave',master_password='123.comm',master_log_file='master-bin.000001',master_log_pos=1021;
start slave;
show slave status\G  # 出现以下则成功
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

ssh-keygen
ssh-copy-id -i 192.168.1.10
ssh-copy-id -i 192.168.1.20
ssh-copy-id -i 192.168.1.30

[root@localhost ~]# rpm -ivh epel-release-latest-7.noarch.rpm

yum -y install perl-DBD-mysql perl-DBI

[root@localhost ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

yum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

[root@localhost ~]# vim /etc/masterha_default.cnf
[server default]    # 全局配置
user=mha    # 经过数据库授权的给管理节点的管理用户
password=123.com  # 经过数据库授权的给管理节点的管理用户的密码
repl_user=slave		# 主从复制使用的用户
repl_password=123.com   # 主从复制使用的用户密码
ssh_user=root    # 管理节点与节点之间使用的ssh用户
master_binlog_dir=/var/lib/mysql  # mysql主存放二进制日志的目录
remote_workdir=/data/login	# 当获取到mysql主的二进制日志是存放在本机的此目录
ping_interval=2  # 管理节点与mysql主的通讯时间（秒），超过时间，则认为mysql主宕机了
shutdown_script=""   # 当mysql主宕机时执行的脚本，可以为空

[root@localhost ~]# mkdir /etc/mha
[root@localhost ~]# vim /etc/mha/app1.cnf
[server default]  # 全局配置
manager_workdir=/var/lib/manager   # 工作目录
manager_log=/var/log/manager/manager.log	# 日志目录，manager需要创建
[server1]   # 节点配置
hostname=192.168.1.10
port=3306
ssh_port=22
 
[server2]
hostname=192.168.1.20
port=3306
ssh_port=22
candidate_master=1  # 表示mysql主宕机后，有server2的节点来接替主
 
[server3]
hostname=192.168.1.30
port=3306
ssh_port=22

mkdir /var/log/manager

[root@localhost ~]# masterha_check_ssh --global-conf=/etc/masterha_default.cnf --conf=/etc/mha/app1.cnf
# 最后显示ok

[root@localhost ~]# masterha_check_repl --global-conf=/etc/masterha_default.cnf --conf=/etc/mha/app1.cnf
# 最后显示ok

[root@localhost ~]# masterha_manager --global-conf=/etc/masterha_default.cnf --conf=/etc/mha/app1.cnf
# 阻塞状态

systemctl stop mysqld