高可靠myql配置-双MysqlRouter+MHA

普通的主备方案

双MysqlRouter + MHA

3.1资源
角色 主机IP 软件版本 备注
RouterMaster 192.10.13.203 8.0.16 192.10.13.205
RouterSlave 192.10.13.204 8.0.16 192.10.13.205
MHAManager 192.10.13.206 MHA0.56
MHANode&Mysql主 192.10.13.201 MHA0.56&Mysql5.7.26
MHANode&Mysql备主 192.10.13.202 MHA0.56&Mysql5.7.26
MHANode&Mysql从 192.20.24.16 MHA0.56&Mysql5.7.26

3.2实施步骤和配置
3.2.1MysqlRouter
包括192.10.13.203 , 192.10.13.204

第一步安装
rpm -ivh mysql-router-community-8.0.16-2.el6.x86_64.rpm

可执行文件： /usr/bin/mysqlrouter
配置文件: /etc/mysqlrouter/mysqlrouter.conf

第二步配置防火墙
配置防火墙端口:
vi /etc/sysconfig/iptables
-A INPUT -s 192.10.13.0/24 -m state --state NEW -m tcp -p tcp --dport 7001 -j ACCEPT
-A INPUT -s 192.10.13.0/24 -m state --state NEW -m tcp -p tcp --dport 7002 -j ACCEPT
service iptables restart

第三步启动服务
/usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

3.2.2keepalive
第一步解压
tar -zxvf keepalived-2.0.15.tar.gz
第二步配置编译文件(mkfile)
cd keepalived-2.0.15
./configure --prefix=/usr/local/keepalived

提示缺少的包，需要安装，这个看具体的环境需要
yum install openssl openssl-devel
yum install libnl libnl-devel

第三步编译&安装
Make
make install

第四步配置服务
拷贝相关文件到服务需要的目录
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived-2.0.15/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
创建放置脚本的目录
mkdir -p /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
(之后用上传的文件替换 按照master 192.10.13.203和 slave192.10.13.204替换)
加入服务
chkconfig --add keepalived
chkconfig keepalived on

第五步启动服务
service keepalived start
3.3.2MHA从节点
第一步 安装Perl组件
yum install perl-DBD-MySQL
第二步安装MHA 节点软件
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

第三步配置网络
包括3306端口和ssh互信，在防火墙上配置,并且需要互联设置

第四步 配置mysql主从
主节点创建复制用户(192.10.13.201, 192.10.13.202)
grant replication slave on *.* to 'slaveuser'@'192.%' identified by 'xxxx';
flush privileges;
查看当前mysql 主节点信息
show master status;

从节点设置同步
change master to
master_host='192.10.13.201',
master_port=3306,
master_user='slaveuser',
master_password='xxxx',
master_log_file='mysql-bin.000002',
master_log_pos=598,
master_connect_retry=5;
开启主从同步
start slave;

第四步配置mysql从节点
设置手工清理中继日志
mysql -uroot -p -e 'set global relay_log_purge=0';
从节点设置只读
mysql -uroot -p -e 'set global read_only=1';

主节点创建root监控用户
grant all privileges on *.* to 'root'@'192.%' identified by 'xxxx';
flush privileges;

因为mysql 是单独安装的，所以需要建立软连接
每个mysql节点需要执行
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
3.2.3MHA主节点
第一步安装所需Perl组件
yum install perl-DBD-MySQL
rpm -ivh perl-Config-Tiny-2.12-1.el6.rfx.noarch.rpm
rpm -ivh perl-Email-Date-Format-1.002-5.el6.noarch.rpm
rpm -ivh perl-MIME-Types-1.28-2.el6.noarch.rpm
rpm -ivh perl-Email-Address-1.912-1.el6.noarch.rpm
rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
rpm -ivh perl-TimeDate-1.16-13.el6.noarch.rpm
rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm
rpm -ivh perl-MIME-Lite-3.027-2.el6.noarch.rpm
rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
rpm -ivh perl-Params-Validate-0.92-3.el6.x86_64.rpm
rpm -ivh perl-Log-Dispatch-2.27-1.el6.noarch.rpm
rpm -ivh perl-Parallel-ForkManager-1.20-1.el6.noarch.rpm

yum install perl-Time-HiRes
yum install perl-devel

第二步安装节点包和管理包
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

---中间因为安装MYSQL数据库客户端,不小心删除了MHA,重装需要的包
rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpm
rpm -ivh MySQL-shared-compat-5.6.42-1.el6.x86_64.rpm

第三步配置网络
包括3306端口和ssh互信，在防火墙上配置,并且需要互联设置

3.创建
mkdir -p /var/log/masterha/app1
检查ssh是否联通
/usr/bin/masterha_check_ssh --conf=/etc/mha/conf/app1.cnf
检查集群复制环境
/usr/bin/masterha_check_repl --conf=/etc/mha/conf/app1.cnf
查看MHA状态
/usr/bin/masterha_check_status --conf=/etc/mha/conf/app1.cnf

启动MHA服务
nohup /usr/bin/masterha_manager --conf=/etc/mha/conf/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

查看MHA状态
/usr/bin/masterha_check_status --conf=/etc/mha/conf/app1.cnf