mysql-preoxy-MHA集群部署

小黄人V

于 2021-04-06 16:49:40 发布

阅读量112

点赞数

分类专栏： linux

本文链接：https://blog.csdn.net/m0_37845900/article/details/115464748

版权

linux 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

主机名	ip地址
Mysql-poxy	192.168.2.218
Mysql-master	192.168.2.219
Mysql-salve01	192.168.2.220
Mysql-slave02	192.168.2.221

Mysql-MHA 192.168.2.222

MYSQL-主从
步骤
mysql-master

安装mysql
配置server-id 开启二进制日志
创建从连接账号
Show master status 查看二进制日志文件名和pos进行同步

Slave

安装mysql
配置server-id 开启二进制日志
关闭日志
连接主提供账号
启动日志查询连接是否成功

开始操作

Mysql-master

service iptables stop
yum -y install mysql mysql-server  
vim /etc/my.cnf
log-bin=mysql-bin
server-id=1
/etc/init.d/mysqld start
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
flush privileges;
flush tables with read lock;
 ;

Slave01

service iptables stop
yum -y install mysql mysql-server     
vim /etc/my.cnf
log-bin=mysql-bin
server-id=2
/etc/init.d/mysqld start
stop slave;
CHANGE MASTER TO MASTER_HOST="192.168.2.219",MASTER_USER="slave",MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000003",MASTER_LOG_POS=406;
start slave;

show slave status\G; 显示两个yes成功

链接之后到主上DB1 进行解锁表 unlock tables

Slave02

service iptables stop
yum -y install mysql mysql-server     
vim /etc/my.cnf
log-bin=mysql-bin
server-id=2
/etc/init.d/mysqld start
stop slave;
CHANGE MASTER TO MASTER_HOST="192.168.2.219",MASTER_USER="slave",MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000003",MASTER_LOG_POS=406;
start slave;

show slave status\G; 显示两个yes成功

链接之后到主上DB1 进行解锁表 unlock tables

Mysql-proxy
安装步骤
得到安装包，解压，改名创建用户mysql-proxy
配置环境变量
启动脚本编写

开始操作

wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
useradd mysql-proxy
tar xvf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy
export PATH=$PATH:/usr/local/mysql-proxy/bin/
chown -R mysql-proxy.mysql-proxy /usr/local/mysql-proxy/

启动命令

/usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file="/var/log/mysql-proxy.log" --plugins="proxy" --proxy-backend-addresses="192.168.2.219:3306" --proxy-read-only-backend-addresses="192.168.2.220:3306" --proxy-read-only-backend-addresses="192.168.2.221:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins="admin" --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

在主master-mysql创建一个用户

mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

通过4040端口进行远程连接然后执行命令创建数据库
登录4041查询有up 证明有写如果不写则会显示unknown 读也是一个道理

已经读写分离成功
MYSQL-MHA
安装步骤
SSH 免密钥通信
配置主机名
创建mysql账号用于连接
配置主配置文件
执行自带脚本检测SSH是否通信成功
执行自带脚本检测主从复制正常
模拟宕机测试

开始操作

#!/bin/bash
rm -rf  ~/.ssh/id_rsa*

/usr/bin/expect << EOF
set timeout 10  

spawn ssh-keygen -t rsa

expect "Enter file in which to save the key (/root/.ssh/id_rsa):" { send "\r" }
expect "Enter passphrase (empty for no passphrase):" { send "\r" }
expect "Enter same passphrase again:" { send "\r" }

expect eof
EOF
./auto_key.sh

#!/usr/bin/expect  
set timeout 10  
set ip [lindex $argv 0]  

spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@$ip

expect {
           "Are you sure you want to continue connecting (yes/no)?" {
           send "yes\r"
           expect "password:"
               send "doingadmin\r"
           }
           "password:" {
               send "doingadmin\r"
           }
           "Now try logging into the machine" {
           }
       }
expect eof
./auto_ssh.sh


#!/bin/bash
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
chkconfig mysqld on
chkconfig iptables off

local_ip=$(ifconfig |grep "Bcast"|awk '{print $2}'|awk -F ':' '{print $2}')
for i in $(cat /etc/hosts|grep "192.168.2.*")
do
       ./auto_ssh.sh $(echo $i|awk '{print $1}')

       if [ $local_ip == $(echo $i|awk '{print $1}') ]

       then
       name=$(echo $i|awk '{print $2}')

       sed -i "s/HOSTNAME=.*/HOSTNAME=$name/g" /etc/sysconfig/network

       fi

done
reboot
./ auto_name.sh





cat >> /etc/hosts << EOF
192.168.2.218   mysql-poxy
192.168.2.219   mysql-master
192.168.2.220   mysql-slave01
192.168.2.221   mysql-slave02
192.168.2.222 MHA
EOF




for i in {218..221};do ./auto_ssh.sh   192.168.2.$i ;done
for i in {218..221};do /usr/bin/scp -r {auto_ssh.sh,auto_key.sh,auto_name.sh} 192.168.2.$i:/root/ ;done
for i in {218..221};do /usr/bin/scp -r /etc/hosts root@192.168.2.$i:/etc/hosts ;done



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

环境配置完成开始安装

yum install perl-DBD-MySQL –y
rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm


1
2
3
4
5	wget http://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Mail-Sender-0.8.16-1.el6.rf.noarch.rpm

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Mail-Sendmail-0.79-1.2.el6.rf.noarch.rpm

yum localinstall *.rpm -y


wget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.56.tar.gz

tar xvf mha4mysql-manager-0.56.tar.gz



[root@manager ~]# tar xf mha4mysql-manager-0.56.tar.gz 
[root@manager ~]# mkdir -p /usr/local/mha/scripts
[root@manager ~]# cp mha4mysql-manager-0.56/samples/scripts/* /usr/local/mha/scripts/
[root@manager ~]# cp mha4mysql-manager-0.56/samples/conf/app1.cnf /usr/local/mha/mha.cnf
[root@manager ~]# tree /usr/local/mha/



yum install perl
yum install cpan
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y

masterha_check_ssh --conf=/usr/local/mha/mha.cnf
[root@manager ~]# nohup masterha_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha_manager.log 2>&1 &
[root@manager ~]# ps -ef |grep masterha |grep -v 'grep'
root      1595  1140  1 23:55 pts/0    00:00:00 perl /usr/bin/masterha_mana


 


 


[root@manager ~]# vim /usr/local/mha/mha.conf #根据自己的mysql配置改动
[server default]
user=mha_manager                              #mha管理的用户名
password=123456
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
remote_workdir=/usr/local/mha
ssh_user=root                                #上面双机互信的用户
repl_user=mharep                             #mysql复制(同步)的用户名
repl_password=passwd
ping_interval=1
  
#下面是mysql检测设置
secondary_check_script= masterha_secondary_check -s 192.168.1.2 -s 192.168.1.3 -s 192.168.1.4
#master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover
#shutdown_script= /usr/local/mha/scripts/power_manager
report_script= /usr/local/mha/scripts/send_report
master_ip_online_change_script= /usr/local/mha/scripts/master_ip_online_change
  
[server1]
hostname=192.168.1.2     #写ip或者主机名都可以
ssh_port=22
master_binlog_dir=/var/mysql/log
candidate_master=1
[server2]
hostname=192.168.1.3
ssh_port=22
master_binlog_dir=/var/mysql/log
candidate_master=1
[server3]
hostname=192.168.1.4
ssh_port=22
master_binlog_dir=/var/mysql/log
no_master=1