MYSQL系列---MHA集群的完整搭建

超级完整的MHA集群搭建

环境准备：

10.10.199.1（主库）

10.10.199.2（从库）

10.10.199.3（从库，管理节点，备份节点）

一、关闭防火墙和selinux

这一步需要我们对3台机器的防火墙和selinux进行关闭，一般在公司里面交付机器的时候会关闭，如果没有关闭，自己手动关闭即可

systemctl  stop  firewalld   |     vi   /etc/selinux/config    修改为disabled即可

二、3台机器之间做免密操作

MHA集群搭建的前提就是机器之间可以相关登陆，故我们需要对其进行免密登陆操作！！！

 

1、ssh-keygen -t rsa  一路回车即可

2、将本地/root/.ssh/id_rsa.pub的内容复制到对方的/root/.ssh/authorized_keys，配置完成在每个节点ssh测试登录

3、3台机器都需要做1和2步骤，完成后测试通过即可

三、建立主从关系

1、3台机器安装mysql，安装的方式很多，可以根据自己的喜爱来安装，这里就不具体赘述了

2、安装完成后，做个软连接 ln  -s   /usr/local/mysql/bin/mysql   /usr/bin

3、三台机器都安装完成后按照gtid的方式修改/etc/my.cnf,重启数据库（当然脚本已经设置，此处我们不做任何操作）

4、查看主库的GTID：

Show master status\G

7b294792-1040-11ec-9832-005056ba4889:1-2

5、创建用户并授权

create user 'repl'@'10.%' identified  by '#13$*3yzac%HJ';

grant  replication  slave on *.*  to 'repl'@'10.%';

6、在从库建立主从关系

 

Reset   master；

 

set@@global.gtid_purged='7b294792-1040-11ec-9832-005056ba4889:1-2';

 

change   master  to

 

master_host='10.10.199.1',

 

master_port=13306,

 

master_user='repl',

 

master_password='#13$*3yzac%HJ',

 

master_auto_position=1;

 

Start  slave;

这样我们3台机器的主从关系就建立好了，我们可以登陆数据库去查看主从关系。确保没问题后，我们在主库进行root密码的修改，这样我们三台主机的root密码就一致了。

 

set password=password("新密码")

四、MHA的搭建

1、配置好yum源，安装如下依赖包（三台）

 

yum install -y perl-DBD-MySQL

 

yum install -y perl-Config-Tiny

 

yum install -y perl-Log-Dispatch

 

yum install -y perl-Parallel-ForkManager

 

yum -y install perl-CPAN

 

2、安装MHA node  (每个节点以root身份安装)

 

tar -zxf mha4mysql-node-0.56.tar.gz

 

cd mha4mysql-node-0.56/

 

perl Makefile.PL

 

make && make install

 

建立目录：/app/sh、conf、tools、sharedstorage

 

cp /soft/mha4mysql-manager-master/samples/scripts/master_ip_online_change /apps/sh/mha/

 

cp /soft/mha4mysql-manager-master/samples/scripts/master_ip_failover /apps/sh/mha/

 

3、配置文件配置

Vi  master_ip_failover

###Begin Variables define###

ssh_port=777

        cmd=/sbin/ifconfig

arpcmd=/sbin/arping

        vip=10.10.199.250

        device=eth0:1

        netmk=255.255.255.0

        start_vip="${cmd} ${device} ${vip} netmask ${netmk} up"

        stop_vip="${cmd} ${device} ${vip} netmask ${netmk} down"

arp_vip="${arpcmd} -c 3 -I eth0 -A ${vip}"

        ###End Variables define###

 

        ###Begin Status Funciont###

        status()

        {

        exit 0

        }

        ###End Status Funciont###

 

        ###Begin Stop Or Stopssh Funciont###

        stop()

        {

        exit 0

        }

        ###End Stop Or Stopssh Funciont###

 

        ###Begin Start Funciont###

        start()

        {

        /usr/bin/ssh -p ${ssh_port} ${ssh_user}@${orig_master_host} ""${stop_vip}""

        /usr/bin/ssh -p ${ssh_port} ${ssh_user}@${new_master_host} ""${start_vip}""

/usr/bin/ssh -p ${ssh_port} ${ssh_user}@${new_master_host} ""${arp_vip}""

        exit 0

        }

        ###End Start Funciont###

 

        ###Begin Get The Command-Line Parameters###

 

        eval set -- "`getopt -a -q -o n -l command::,ssh_user:,orig_master_host:,orig_master_ip:,new_master_host:,new_master_ip: -- "$@"`"

 

        if [ $? != 0 ] ; then echo "Terminating..." >&2 ;exit 1;fi

 

        while true

        do

                case "$1" in

                        --command)

                                command="${2}";

                                shift;;

                        --ssh_user)

                                ssh_user="${2}";

                                shift;;

                        --orig_master_host)

                                orig_master_host="${2}";

                                shift;;

                        --orig_master_ip)

                                orig_master_ip="${2}";

                                shift;;

                        --new_master_host)

                                new_master_host="${2}";

                                shift;;

                        --new_master_ip)

                                new_master_ip="${2}";

                                shift;;

                        --)

                                shift;

                                break;;

                esac

        shift

        done

        ###End Get The Command-Line Parameters###

 

        if [ "${command}" == "status" ];

        then

        status;

        fi

 

        if [ "${command}" == "stop" ] || [ "${command}" == "stopssh" ] ;

        then

        stop;

        fi

 

        if [ "${command}" == "start" ];

        then

        start;

        fi

 

Vi  master_ip_online_change

#/bin/bash  

source /root/.bash_profile  

  

vip=`echo '10.10.199.250/24'`  # Virtual IP  

vip2=`echo '10.10.199.250'`

key=`echo '1'`  

  

command=`echo "$1" | awk -F = '{print $2}'`  

orig_master_host=`echo "$2" | awk -F = '{print $2}'`  

new_master_host=`echo "$7" | awk -F = '{print $2}'`  

        orig_master_ssh_user=`echo "${12}" | awk -F = '{print $2}'`  

        new_master_ssh_user=`echo "${13}" | awk -F = '{print $2}'`  

          

        stop_vip=`echo "ssh -p 777 root@$orig_master_host /sbin/ifconfig  eth0:$key  down"`  

        start_vip=`echo "ssh -p 777 root@$new_master_host /sbin/ifconfig  eth0:$key  $vip"`  

arp_vip=`echo "ssh -p 777 root@$new_master_host /sbin/arping -c 3 -I eth0 -A $vip2"`

          

        if [ $command = 'stop' ]  

           then  

           echo -e "\n\n\n***************************************************************\n"  

           echo -e "Disabling the VIP - $vip on old master: $orig_master_host\n"  

           $stop_vip  

           if [ $? -eq 0 ]  

              then  

              echo "Disabled the VIP successfully"  

           else  

              echo "Disabled the VIP failed"  

           fi  

           echo -e "***************************************************************\n\n\n\n"  

        fi  

          

        if [ $command = 'start' -o $command = 'status' ]  

           then  

           echo -e "\n\n\n***************************************************************\n"  

           echo -e "Enabling the VIP - $vip on new master: $new_master_host \n"  

           $start_vip

   $arp_vip

           if [ $? -eq 0 ]  

              then  

              echo "Enabled the VIP successfully"  

           else  

              echo "Enabled the VIP failed"  

           fi  

           echo -e "***************************************************************\n\n\n\n"  

        fi

 

4、配置启动参数文件：/app/conf/mha/app.cnf，并创建对应目录

 

[server default]

 

manager_workdir=/app/tools/mha

 

manager_log=/app/sharedstorage/logs/mha/appmanager.log

 

remote_workdir=/data/binlog/

 

master_binlog_dir=/data/binlog/

 

 

 

user=mha_root

 

password=9n9*KMruWfN

 

ssh_user=root

 

ssh_port=777

 

repl_user=repl

 

repl_password=#13$*3yzac%HJ

 

ping_interval=4

 

 

 

#secondary_check_script=/usr/local/bin/masterha_secondary_check -s 10.208.124.3 -s 10.208.124.4 --user=root --port=777 --master_host=vbu00144p1d02 --master_ip=10.208.124.2 --master_port=3306

 

 

 

master_ip_failover_script=/app/sh/mha/master_ip_failover

 

master_ip_online_change_script=/app/sh/mha/master_ip_online_change

 

 

 

[server1]

 

hostname=10.10.199.1

 

port=13306

 

candidate_master=1

 

check_repl_delay=0

 

 

 

[server2]

 

hostname=10.10.199.2

 

port=13306

 

candidate_master=1

 

check_repl_delay=0

 

 

 

[server3]

 

hostname=10.10.199.3

 

port=13306

 

candidate_master=0

 

check_repl_delay=0

 

5、在master机器挂在VIP

 

/sbin/ifconfig eth0:1 $vip netmask 255.255.255.0 up   挂载

 

/sbin/ifconfig eth0:1 $vip netmask 255.255.255.0 down 卸载

 

6、验证

 

检查ssh：masterha_check_ssh --conf=/app/conf/mha/app.cnf

 

检查主从同步：masterha_check_repl --conf=/app/conf/mha/app.cnf

 

启动:/usr/local/bin/masterha_manager --conf=/app/conf/mha/app.cnf --ignore_fail_on_start --ignore_last_failover &  启动完按enter

 

查看状态：masterha_check_status --conf=/app/conf/mha/app.cnf

7、切换主从，看VIP是否飘逸，主从节点身份是否发生变化，以上都可以就说明我们的MHA集群搭建OK！！！