MySQL之高可用集群部署及故障切换实现

systemctl stop firewalld
systemctl disable firewalld
setenforce 0
netstat -natp | grep 3306

hostnamectl set-hostname mysql1
su -
hostnamectl set-hostname mysql2
su -
hostnamectl set-hostname mysql3
su -

MySQL1
vim /etc/my.cnf
[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true

systemctl restart mysqld
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

MySQL2
vim /etc/my.cnf
server-id = 2  
#server-id = 3  MySQL3则为3，三台服务器 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
systemctl restart mysqld

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

(1)所有MySQL服务器进行MySQL授权
mysql1（192.168.221.20）
mysql2（192.168.221.100）
mysql3（192.168.221.110）

3台都要配置
mysql -uroot -p123
grant replication slave on *.* to 'myslave'@'192.168.221.%' identified by '123';
grant all privileges on *.* to 'mha'@'192.168.221.%' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql1' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql2' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql3' identified by 'manager';
flush privileges;
show master status;

(2)在 Slave1、Slave2 节点执行同步操作
change master to master_host='192.168.221.20',master_user='myslave',master_password='123',master_log_file='master-bin.000005',master_log_pos=1991;

start slave;

show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#一般 Slave_IO_Running: No 的可能性：
#网络不通
#my.cnf配置有问题
#密码、file文件名、pos偏移量不对
#防火墙没有关闭

(3)Slave1、Slave2 节点设置为只读模式
mysql2（192.168.221.100）
mysql3（192.168.221.110）

set global<a>本文来源gao($daima.com搞@代@#码(网</a> read_only=1;
#改回读写状态set global read_only=0;

(4)主从复制验证
mysql1（192.168.221.20）
创建库

create database srs;
use test;
create table test(id int);
insert into test values(1);

mysql2（192.168.221.100）
mysql3（192.168.221.110）
查询库验证

show databases;

（1）所有服务器上都安装 MHA 依赖的环境
MHAmanager（192.168.221.30）
mysql1（192.168.221.20）
mysql2（192.168.221.100）
mysql3（192.168.221.110）

首先安装 epel 源，需要在线源安装
再在所有服务器上安装 node 组件
#安装在线源
mv /etc/yum.repos.d/repos.bak/CentOS-* /etc/yum.repos.d/
yum list

yum install epel-release --nogpgcheck -y

yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN