mysql高可用集群搭建_mysql高可用配置

机器作用
10.10.20.147  master
10.10.20.148  slave1
10.10.20.230  slave2
10.10.20.179  mha(负载读写分离控制)

一：搭建mysql

1：环境准备，更改主机名，配置host文件

hostnamectl set-hostname centos77
vim /etc/host

2：配置防火墙

firewall-cmd --state           #查看防火墙状态
service firewalld start        #开启防火墙
service firewalld restart      #重启防火墙
service firewalld stop         #关闭防火墙
firewall-cmd --list-all        #查看防火墙规则
firewall-cmd --query-port=8080/tcp    # 查询端口是否开放
firewall-cmd --permanent --add-port=80/tcp     # 开放80端口
firewall-cmd --permanent --remove-port=8080/tcp  # 移除端口
firewall-cmd --reload      #保存防火墙

3：将相应的部署包上传到相应的目录

/home/app/

4：解压部署包

cd /home/app 
tar -xvf mysql-8.0.26-el7-x86_64.tar
tar -xzvf mysql-8.0.26-el7-x86_64.tar.gz
mv mysql-8.0.26-el7-x86_64 mysql

5：创建tmp和data文件夹

mkdir {data,tmp}

6：创建用户和数组

groupadd mysql
useradd -r -g mysql mysql

7：给mysql目录赋权

chown -R mysql:mysql /home/app/mysql

8：初始化mysql

cd /home/app/mysql/bin
./mysqld --initialize --user=mysql  --datadir=/home/app/mysql/data --basedir=/home/app/mysql

9：查看初始化状态

10：配置/etc/my.cnf ###########主节点##############

[client]
port=3306
socket=/home/app/mysql/tmp/mysql.sock
 
[mysqld]
port=3306
user=mysql
socket=/home/app/mysql/tmp/mysql.sock
basedir=/home/app/mysql
datadir=/home/app/mysql/data
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id = 1
gtid_mode=ON
enforce-gtid-consistency=true
log_slave_updates=ON
log_bin=binlog

11：配置/etc/my.cnf ###########从1节点##############

[client]
port=3306
socket=/home/app/mysql/tmp/mysql.sock
 
[mysqld]
port=3306
user=mysql
socket=/home/app/mysql/tmp/mysql.sock
basedir=/home/app/mysql
datadir=/home/app/mysql/data
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#log-bin=mysql-bin
server-id = 2
gtid_mode=ON
enforce-gtid-consistency=true
log_slave_updates=ON
log_bin=binlog

12：配置/etc/my.cnf ###########从2节点##############

[client]
port=3306
socket=/home/app/mysql/tmp/mysql.sock
 
[mysqld]
port=3306
user=mysql
socket=/home/app/mysql/tmp/mysql.sock
basedir=/home/app/mysql
datadir=/home/app/mysql/data
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#log-bin=mysql-bin
server-id = 3
gtid_mode=ON
enforce-gtid-consistency=true
log_slave_updates=ON
log_bin=binlog

13：创建相应的日志目录和pid目录，并赋权

mkdir -p /var/log
mkdir -p /var/run/mysqld
chown -R mysql:mysql /var/log
chown -R mysql:mysql /var/run/mysqld

14：创建mysqld服务

cp /home/app/mysql/support-files/mysql.server /etc/init.d/mysqld

15：配置环境变量

vim /etc/profile
PATH=/home/app/mysql/bin:/home/app/mysql/lib:$PATH
export PATH
source /etc/profile   #生效环境变量

16：尝试启动mysql

/etc/init.d/mysqld start

17：登录mysql并更改密码和权限

mysql -uroot -p
#create user 'admin'@'10.10.20.%' identified with mysql_native_password by 'Mysql2021!';
#grant replication slave on *.* to 'admin'@'10.10.20.%';
alter user 'root'@'localhost' identified by 'Mysql2021!';
 
alter user 'root'@'localhost' identified with mysql_native_password by 'Mysql2021!';
 
 
use mysql;
select host,user,plugin from user;
update user set host='%' where user='root';
 
flush privileges;

二：配置主从

1：主节点配置从节点的账号及权限

create user 'repl'@'10.10.20.%' identified with mysql_native_password by 'Mysql2021!';
grant replication slave on *.* to 'repl'@'10.10.20.%';
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000000000000;

2：从1节点配置

create user 'repl'@'10.10.20.147' identified with mysql_native_password by 'Mysql2021!';
grant replication slave on *.* to 'repl'@'10.10.20.%' master_auto_position=1;
change master to master_host='10.10.20.147',master_user='repl',master_password='Mysql2021!',master_auto_position=1;
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
stop slave io_thread;
start slave io_thread;
start slave;
show slave status\G;

显示一下内容，说明配置成功。

3：从2节点配置

change master to master_host='10.10.20.147',master_user='repl',master_password='Mysql2021!',master_auto_position=1;
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
stop slave io_thread;
start slave io_thread;
start slave;
show slave status\G;

三：在server4上安装管理节点

1：安装依赖包（四个节点都装）

 
yum install -y epel-release perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

2：安装mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm（四个节点都装）

yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

3.生成server4的ssh密钥，并发送给server1、server2、server3(机器名)

ssh-keygen
ssh-copy-id server1
ssh-copy-id server2
ssh-copy-id server3 

4.创建masterha文件夹

mkdir /etc/masterha
cd /etc/masterha

5.编辑mha配置文件,vim master.cnf

[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log
master_binlog_dir=/etc/masterha
#imaster_ip_failover_script= /usr/local/bin/master_ip_failover
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change
password=**********
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=Mysql2021!
repl_user=repl
ssh_user=root
#ssh_port=22
#user=admin
#password=########
 
[server1]
hostname=slave0
ssh_port=22
port=3306
#candidate_master=1
#check_repl_delay=0
 
[server2]
hostname=slave1
ssh_port=22
port=3306
candidate_master=1
check_repl_delay=0
candidate_master=1
check_repl_delay=0
 
[server3]
hostname=slave2
ssh_port=22
port=3306
#no_master=1

6.密钥互相传递(server1/2/3 是主机名)

scp -r ~/.ssh server1:
scp -r ~/.ssh server2:
scp -r ~/.ssh server3:

7.检查ssh是否出错

 masterha_check_ssh --conf=/etc/masterha/master.cnf
 grant all on *.* to root@'%' identified by 'Mysql2021!';#server1执行（mysql8 执行一下操作代替：CREATE USER 'root'@'%' IDENTIFIED BY 'Mysql2021!';再给用户授权grant all privileges on *.* to 'root'@'%';）
 set global read_only=1;#server2执行
 set global read_only=1;#server3执行

8.查看mysql的复制情况

masterha_check_repl --conf=/etc/masterha/master.cnf

四：MHA测试

（1）手动测试

1.关闭server1的mysql

/etc/init.d/mysqld stop

2.手动将master节点转换到server2上

masterha_master_switch --master_state=dead --conf=/etc/masterha/master.cnf --dead_master_host=10.10.20.147 --dead_master_port=3306 --new_master_host=10.10.20.148 --new_master_port=3306

3：server2查看slave状态为空

show slave status\G;

4.server3查看slave状态（master的ip转到server2）

show slave status\G;

5.打开server1的mysql将slave添加进群组

/etc/init.d/mysqld start
mysql -u root -p
change master to master_host='10.10.20.147',master_user='repl',master_password='Mysql2021!',master_auto_position=1;
start slave;
show slave status\G;

6.手动将matser转到server1上

masterha_master_switch --master_state=alive --conf=/etc/masterha/master.cnf --new_master_host=10.10.20.147 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=1000;

(2)自动转换

1.在server4下创建一个检测进程，来创建监控master的进程并查看进程，即执行自动转换命令

nohup masterha_manager -conf=/etc/masterha/master.cnf &> /etc/masterha/manager.log &
查看进程
ps a

2.关掉server1的mysql并查看其他节点的状态

/etc/init.d/mysqld stop
show slave status\G;

五：mysql配置报错

报错1：MySQL的slave_msq_running状态为no

解决办法：

首先，我们需要停止从服务器。登录mysql服务器并执行以下命令。

mysql> RESET MASTER;mysql> FLUSH TABLES WITH READ LOCK;

使用以下命令复制数据库转储。

mysqldump -uroot -pMysql2021! --set-gtid-purged=OFF --all-databases > dump.sql

在备份后解锁主服务器上的表。

mysql> UNLOCK TABLES;

登录mysql并执行以下命令以重置从服务器状态。

mysql> RESET SLAVE;

重置从服务器后启动从服务器复制

mysql> START SLAVE;

复制已与新配置重新同步,可以使用以下命令进行验证。

mysql> show slave status\G;

报错2：mysql的slave_IO_running状态为no

解决办法：

1：在从节点上执行  reset master;   重置从节点上的masterUUID
2：stop slave;   停止slave会话
3：reset slave;  重置slave信息
4：start slave;   重启slave会话
5：reset master; 重启master
6：show slave status\G;  查看状态

报错3：mha环境检查报错

原因：root全系不对

解决办法：

update user set host='%' where user='root'; #更改权限
flush privileges;  # 刷新配置

报错4：mha环境检查报错

解决办法：

grant REPLICATION CLIENT ON *.* TO 'root';