MHA双主双从

洪栋梁

已于 2023-02-27 18:10:44 修改

阅读量268

点赞数

文章标签： linux 服务器 ubuntu

于 2023-02-27 17:47:35 首次发布

本文链接：https://blog.csdn.net/unito/article/details/129239262

版权

该文详细介绍了如何在Linux环境下配置MySQL主从同步，包括设置主机名、IP地址，关闭SELinux和防火墙，实现SSH免密登录，以及安装和配置MySQL服务。接着，文章演示了如何启用半同步复制，并利用MHA（MySQLHighAvailability）工具进行高可用性设置，包括MHAManager和Node的安装，以及处理主从切换的过程。

摘要由CSDN通过智能技术生成

主机名	ip
manager	192.168.86.100
master01	192.168.86.10
master02	192.168.86.20
slave01	192.168.86.30
slave02	192.168.86.40

1、安装前的准备

设置主机名(vim /etc/hostname)

[root@manager ~]# cat /etc/hostname
manager

修改ip(vim /etc/sysconfig/network-scripts/ifcfg-ens33)

[root@manager ~]# tail -3 /etc/sysconfig/network-scripts/ifcfg-ens33
IPADDR=192.168.86.100
GATEWAY=192.168.86.2
DNS1=223.5.5.5

关闭seliunx

[root@manager ~]# head -7 /etc/selinux/config | tail -5
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled

修改hosts文件(vim /etc/hosts)

[root@manager ~]# tail -5 /etc/hosts
192.168.86.10   master01
192.168.86.20   master02
192.168.86.30   slave01
192.168.86.40   slave02
192.168.86.100   manager

关闭防火墙systemctl stop firewalld

实现ssh免密，所有服务器都要

for i in 10 20 30 40 100;do ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.86.$i;done

安装mysql

在每台主机上安装mysql服务，manager主机不需要启动服务

使用二进制的方式
#卸载mariadb

rpm -e mariadb --nodeps
#切换到mysql安装包的目录

cd /usr/local/src
#解压安装包

tar -zxf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/src
#重定向mysql

mv mysql-5.7.35-linux-glibc2.12-x86_64/ /usr/local/mysql
#切换到工作目录

cd /usr/local/mysql
#创建用户组和用户

groupadd -r mysql && useradd -r -g mysql -s /bin/false -M mysql
#在工作目录创建data库

mkdir ./data
#赋予权限

chown -R mysql:mysql /usr/local/mysql
#写入环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
#执行

source /etc/profile

#编写主配置文件

#cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
log-error=/usr/local/mysql/data/mysql.err
socket=/tmp/mysql.sock

#初始化mysql

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#设置root密码
mysqladmin -uroot -p$mima password 123456

#切换到启动文件的目录下
cd support-files/

#复制启动文件到init下
cp mysql.server /etc/init.d/

#启动mysql
service mysql.server start

将同步方式修改为半同步，登录到mysql

install plugin rpl_semi_sync_master soname 'semisync_master.so';

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

使用show plugins查看是否成功

修改主配置文件开启二进制日志和中继日志和半同步，其他服务器修改server_id即可

[root@master01 ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
log-error=/usr/local/mysql/data/mysql.err
socket=/tmp/mysql.sock
log_bin=mysql-bin.log
server_id=1
relay_log=mysql-relay.log
relay_log_index=/usr/local/mysql/data/relay-bin.index
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_enabled=1

在10和20创建主从用户和管理用户，30和40只创建管理用户,配置主从

master01：

grant all on *.* to rep@'%' identified by '123456';

grant all on *.* to manager@'%' identified by '123456';

slave01：

grant all on *.* to manager@'%' identified by '123456';

change master to master_host='192.168.86.10',master_port=3306,master_user='rep',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=154;

配置mha

1 、在所有主机上安装 mha 所依赖的软件包（需要系统自带的yum 源并联网）

wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo

yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles ncftp perl-Params-Validate perl-CPAN perl-Test-Mock-LWP.noarch perl-LWP-Authen-Negotiate.noarch perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

2、以下操作管理节点需要两个都安装，在其他四台数据库节点只要安装MHA的node节点：

软件下载 https://github.com/yoshinorim

master01

[root@master1 src]# tar zxf mha4mysql-node-0.58.tar.gz

[root@master1 src]# cd mha4mysql-node-0.58

[root@master1 mha4mysql-node-0.58]# perl Makefile.PL

*** Module::AutoInstall version 1.06

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.627)

- DBD::mysql ...loaded. (4.023)

*** Module::AutoInstall configuration finished.

Checking if your kit is complete..

Looks good

Writing Makefile for mha4mysql::node

[root@master1 mha4mysql-node-0.58]# make && make install

manager

[root@manager mha4mysql-manager-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...loaded. (2.14)
- Log::Dispatch ...loaded. (2.70)
- Parallel::ForkManager ...loaded. (2.02)
- MHA::NodeConst ...loaded. (0.58)
*** Module::AutoInstall configuration finished.
Generating a Unix-style Makefile
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
[root@manager mha4mysql-manager-0.58]# make && make install

如果报错

sudo wget http://xrl.us/cpanm -O /usr/bin/cpanm

chmod +x /usr/bin/cpanm

安装好cpanm后就可以用他直接进行安装包了会把相应依赖一起装了的

cpanm Log::Dispatch

cpanm Parallel::ForkManager

重新perl Makefile.PL

创建所需要的目录

mkdir /etc/masterha /masterha/app1 /scripts -p

复制模板过去在刚才创建的目录里

在manager解压后的目录

cp samples/conf/* /etc/masterha

cp samples/scripts/* /scripts

修改配置文件vim /etc/masterha/app1.cnf

[root@manager ~]# cat /etc/masterha/app1.cnf
[server default]
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
user=manager
password=123456
ssh_user=root
repl_user=rep
repl_password=123456
ping_interval=1
[server1]
hostname=192.168.86.10
port=3306
master_binlog_dir=/usr/local/mysql/data
candidate_master=1
[server2]
hostname=192.168.86.20
port=3306
master_binlog_dir=/usr/local/mysql/data
candidate_master=1
[server3]
hostname=192.168.86.30
port=3306
master_binlog_dir=/usr/local/mysql/data
no_master=1
[server4]
hostname=192.168.86.40
port=3306
master_binlog_dir=/usr/local/mysql/data
no_master=1

检查ssh连接和repl连接

masterha_check_ssh --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.cnf

masterha_check_repl --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.cnf

如果repl报错

上传libmysqlclient包

wget http://launchpadlibrarian.net/212189159/libmysqlclient18_5.6.25-0ubuntu1_amd64.deb

安装alien工具

yum install alien

把deb格式转换为rpm

alien -r libmysqlclient18_5.6.25-0ubuntu1_amd64.deb

rpm安装

rpm -ivh --nodeps --force libmysqlclient18-5.6.25-1.x86_64.rpm

如果都ok

启动mha

nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/tmp/mha_manager.log &

查看状态

masterha_check_status --conf=/etc/masterha/app1.cnf

[root@manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:20521) is running(0:PING_OK), master:192.168.86.10

如果主发生宕机

修复后启动并连接到备主的服务器上

change master to master_host='192.168.86.20',master_port=3306,master_user='rep',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=593;

然后再启动mha

nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/tmp/mha_manager.log &

发生主从切换后， MHAmanager 服务会自动停掉，且在

manager_workdir （ /masterha/app1 ）目录下面生成文件 app1.failover.complete ，若要启动 MHA ，必须先确保无此文件) 如果有这个提示，那么删除此文件 / masterha/app1/app1.failover.complete