Linux-MySQL5.7部署MHA高可用集群

异步复制：
    MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后立即将结果返回给客户端，并不关心从库是否已经接受并处理，这样就会导致一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整
全同步复制：
    指当主库执行完一个事务，所有的从库都执行该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会受到严重的影响
半同步复制：
    结余异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写道relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
总结：
    异步与半同步的异同：
        默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到Slave之上。异步操作虽然效率极高，但是在Master/Slave出现问题的时候，存在很大的数据不同步风险，甚至可能丢失数据。
        Mysql5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据时完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台Slave赶上之后，继续切换到半同步模式
实验环境：
    192.168.1.105   Centos7.3   master  mha4mysql-manager、mha4mysql-node
    192.168.1.106   Centos7.3   node1   mha4mysql-node
    192.168.1.107   Centos7.3   node2   mha4mysql-node
    软件包下载：
        wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
        wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz
    部署：
        1.关闭selinux和防火墙或者添加对应的防火墙规则，开放端口
            systemctl stop firewalld
            setenforce 0
        2.配置解析：
            vim /etc/hosts      # 末尾追加以下三行
            192.168.1.105   master
            192.168.1.106   node1
            192.168.1.107   node2
        3.配置三台服务器可以相互免密登录(以master为例：)
            ssh-keygen -t rsa
            ssh-copy-id node1
            ssh-copy-id node2
        4.三台服务器同时安装mysql5.7，上传软件包到服务器
            解压：
                tar xf mysql-5.7.22-el7-x86_64.tar.gz -C /usr/local/
            重命名：
                mv /usr/local/mysql-5.7.22-el7-x86_64 /usr/local/mysql
            创建用户和用户组：
                groupadd mysql && useradd -r -g mysql mysql
            给mysql目录指定专有用户和用户组：
                首先创建data目录
                    cd /usr/local/mysql/
                    mkdir data
                指定用户和组：
                    chown -R mysql. /usr/local/mysql
            初始化mysql：
                cd /usr/local/mysql/bin/
                ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --lc_messages_dir=/usr/local/mysql/share --lc_messages=en_US
                注：记住生成的临时密码，如果忘记或者想重新初始化，可以先将/usr/local/mysql/data目录中的文件删除，然后再执行初始化命令
            配置my.cnf：
                从5.7.17后mysql就没有默认的my_default.cnf文件，需手动创建
                精简版示例：
                    [mysqld]
                    basedir=/usr/local/mysql/
                    datadir=/usr/local/mysql/data/
            启动：
                cd /usr/local/mysql/bin
                ./mysqld_safe --user=mysql &
            设置为开机自启动：
                cd /usr/local/mysql/support-files/
                cp mysql.server /etc/init.d/mysqld
                vi /etc/init.d/mysqld
                将mysql目录填上：
                    basedir=/usr/local/mysql/
                    datadir=/usr/local/mysql/data/
                chkconfig --add mysqld
            登录mysql修改密码授权远程登录：
                cd /usr/local/mysql/bin
                ./mysql -uroot -p       # 输入临时密码
                修改密码：
                    set password=password("123456");
                登录授权：
                    grant all privileges on *.* to 'root'@'localhost' identified by '123456';
                授权生效：
                    flush privileges;
        5.搭建主从环境：
            本次实验搭建的事一主两从环境，使用的是MySQL5.7版本，基于GRID+row模式
                开始GTID：
                    vim /etc/my.cnf
                        log-bin=mysql-bin
                        binlog_format=row
                        log_slave_updates=1
                        gtid_mode=ON
                        enforce_gtid_consistency=ON
            在所有服务器上执行以下操作：
                创建主从复制账号：
                    grant replication slave on *.* to 'gtid'@'192.168.1.%' identified by 'gtid123';
                    flush privileges;
                创建管理账号：
                    grant all privileges on *.* to 'manager'@'192.168.1.%' identified by 'manager123';
                    flush privileges;
            在主库上复制数据到所有从库，完成在某个时刻GTID的同步
                mysqldump --single-transaction -uroot -p -A > all.sql
                scp all.sql node1:/root/  
                scp all.sql node2:/root/
            在各从库上恢复备份并配置主从复制，开启主从同步
                mysql -uroot -p < all.sql
                change master to master_host='192.168.1.105',master_user='gtid',master_password='gtid123',master_log_file='mysql-bin.000001',master_log_pos=154;
                start slave;
                show slave statusG
        6.安装MHA-Node节点：
            在所有节点上安装数据节点：
                首先安装MySQL依赖的perl环境：
                    yum install perl-DBD-MySQL.x86_64 -y
                解压mha4mysql-node包，并安装perl-cpan
                    tar -zxf mha4mysql-node-0.58.tar.gz
                    cd mha4mysql-node-0.58/
                    yum install perl-CPAN* -y
                    perl Makefile.PL
                    make && make install
        7.安装配置MHA-Manager管理节点：
            注：以下操作都是在master(192.168.1.105)上完成的
            安装环境所需要的介质包：
                注意：我的操作系统是centos7，下载软件时注意软件版本问题
                    yum install perl-DBD-MySQL*

                    wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/matthewdva:/build:/RedHat:/RHEL-7/complete/x86_64/perl-Params-Validate-1.08-4.el7.x86_64.rpm
                    rpm -ivh perl-Params-Validate-1.08-4.el7.x86_64.rpm

                    wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/csbuild:/Perl/RHEL_7/noarch/perl-Config-Tiny-2.20-1.2.noarch.rpm
                    rpm -ivh perl-Config-Tiny-2.20-1.2.noarch.rpm

                    wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/csbuild:/Perl/RHEL_7/noarch/perl-Log-Dispatch-2.41-2.2.noarch.rpm
                    rpm -ivh perl-Log-Dispatch-2.41-2.2.noarch.rpm
                    注：如果安装失败，请配置epel源，并将perl-Log-Dispatch这个软件包创建一个单独的yum源：
                        示例：
                            createrepo /root/perl/
                            vim /etc/yum.repos.d/perl-patch.repo
                                [perl-patch]
                                name=perl patch
                                baseurl=file:///root/perl
                                gpgcheck=0
                                enabled=1
                        使用yum安装：
                            yum install perl-Log-Dispatch

                    wget ftp://ftp.pbone.net/mirror/download.fedora.redhat.com/pub/fedora/epel/7/aarch64/Packages/p/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
                    rpm -ivh perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
            安装管理节点：
                tar -zxf mha4mysql-manager-0.58.tar.gz
                cd mha4mysql-manager-0.58
                perl Makefile.PL
                make && make install
            配置MHA：
                mkdir /etc/mha
                mkdir -p /usr/local/mha
                vim /etc/mha/mha.conf
                ##################################################写入配置
                [server default]
                user=manager
                password=manager123
                manager_log=/usr/local/mha/manager.log
                manager_workdir=/usr/local/mha
                master_binlog_dir=/mvtech/mysql/logs
                remote_workdir=/usr/local/mha
                ssh_user=root
                repl_user=gtid
                repl_password=gtid123
                master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover
                master_ip_online_change_script=/usr/local/mha/scripts/master_ip_online_change
                ping_interval=1

                [server1]
                hostname=192.168.1.105
                ssh_port=22
                master_binlog_dir=/usr/local/mysql/data/
                candidate_master=1
                port=3306

                [server2]
                candidate_master=1
                hostname=192.168.1.106
                ssh_port=22
                master_binlog_dir=/usr/local/mysql/data/
                port=3306

                [server3]
                hostname=192.168.1.107
                ssh_port=22
                master_binlog_dir=/usr/local/mysql/data/
                no_master=1
                port=3306
                ###########################################################
            编辑failover切换脚本：
                mkdir /usr/local/mha/scripts
                cd /usr/local/mha/scripts
                vim master_ip_failover
                # 脚本内部的VIP和网卡根据自己的实际要求更改
                ############################################################
                #!/usr/bin/env perl  
                use strict;  
                use warnings FATAL =>'all';  

                use Getopt::Long;  

                my (  
                $command,          $ssh_user,        $orig_master_host, $orig_master_ip,  
                $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port  
                );  

                my $vip = '192.168.1.250/24';  # Virtual IP 这里需要根据自己的环境修改
                my $key = "1";  
                my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";   #注意网卡  
                my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";  
                my $exit_code = 0;  

                GetOptions(  
                'command=s'          => $command,  
                'ssh_user=s'         => $ssh_user,  
                'orig_master_host=s' => $orig_master_host,  
                'orig_master_ip=s'   => $orig_master_ip,  
                'orig_master_port=i' => $orig_master_port,  
                'new_master_host=s'  => $new_master_host,  
                'new_master_ip=s'    => $new_master_ip,  
                'new_master_port=i'  => $new_master_port,  
                );  

                exit &main();  

                sub main {  

                #print " IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip=== ";  

                if ( $command eq "stop" || $command eq "stopssh" ) {  

                        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.  
                        # If you manage master ip address at global catalog database,  
                        # invalidate orig_master_ip here.  
                        my $exit_code = 1;  
                        eval {  
                            print " *************************************************************** ";  
                            print "Disabling the VIP - $vip on old master: $orig_master_host ";  
                            print "*************************************************************** ";  
                &stop_vip();  
                            $exit_code = 0;  
                        };  
                        if ($@) {  
                            warn "Got Error: $@ ";  
                            exit $exit_code;  
                        }  
                        exit $exit_code;  
                }  
                elsif ( $command eq "start" ) {  

                        # all arguments are passed.  
                        # If you manage master ip address at global catalog database,  
                        # activate new_master_ip here.  
                        # You can also grant write access (create user, set read_only=0, etc) here.  
                my $exit_code = 10;  
                        eval {  
                            print " *************************************************************** ";  
                            print "Enabling the VIP - $vip on new master: $new_master_host ";  
                            print "*************************************************************** ";  
                &start_vip();  
                            $exit_code = 0;  
                        };  
                        if ($@) {  
                            warn $@;  
                            exit $exit_code;  
                        }  
                        exit $exit_code;  
                }  
                elsif ( $command eq "status" ) {  
                        print "Checking the Status of the script.. OK ";  
                        `ssh $ssh_user@$orig_master_host " $ssh_start_vip "`;  
                        exit 0;  
                }  
                else {  
                &usage();  
                        exit 1;  
                }  
                }  

                # A simple system call that enable the VIP on the new master  
                sub start_vip() {  
                `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;  
                }  
                # A simple system call that disable the VIP on the old_master  
                sub stop_vip() {  
                `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;  
                }  

                sub usage {  
                print  
                "Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po  
                rt –new_master_host=host –new_master_ip=ip –new_master_port=port ";  
                }
                ###################################################
                chmod +x master_ip_failover
            编辑online_change脚本：
                vim master_ip_online_change
                # 注意VIP
                ##########################################################
                #/bin/bash  
                source /root/.bash_profile  

                vip=`echo '172.25.5.100/24'`  # Virtual IP  
                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 root@$orig_master_host /sbin/ifconfig  eth0:$key  down"`  
                start_vip=`echo "ssh root@$new_master_host /sbin/ifconfig  eth0:$key  $vip"`  

                if [ $command = 'stop' ]  
                   then  
                   echo -e " *************************************************************** "  
                   echo -e "Disabling the VIP - $vip on old master: $orig_master_host "  
                   $stop_vip  
                   if [ $? -eq 0 ]  
                      then  
                      echo "Disabled the VIP successfully"  
                   else  
                      echo "Disabled the VIP failed"  
                   fi  
                   echo -e "*************************************************************** "  
                fi  

                if [ $command = 'start' -o $command = 'status' ]  
                   then  
                   echo -e " *************************************************************** "  
                   echo -e "Enabling the VIP - $vip on new master: $new_master_host "  
                   $start_vip  
                   if [ $? -eq 0 ]  
                      then  
                      echo "Enabled the VIP successfully"  
                   else  
                      echo "Enabled the VIP failed"  
                   fi  
                   echo -e "*************************************************************** "  
                fi
                ############################################################
                chmod +x master_ip_online_change
            检测所有主机的连通性：
                /usr/local/bin/masterha_check_ssh --conf=/etc/mha/mha.conf
            检测复制状态：
                /usr/local/bin/masterha_check_repl --conf=/etc/mha/mha.conf
        8.在主库上添加VIP
             ip addr add 192.168.1.250/24 dev ens33
        9.在管理节点启动MHA服务
            启动MHA：
                nohup masterha_manager --conf=/etc/mha/mha.conf > /tmp/mha_manager.log < /dev/null 2>&1 &
            检测MHA是否启动：
                masterha_check_status --conf=/etc/mha/mha.conf
    验证：
        1.模拟主库(192.168.1.105)故障
            kill掉主库的MySQL服务：
                killall mysqld
        2.MHA自动切换主库
            VIP漂移：
                [root@node1 /]# ip a | grep ens33
                2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
                    inet 192.168.1.106/24 brd 192.168.1.255 scope global ens33
                    inet 192.168.1.250/24 brd 192.168.1.255 scope global secondary ens33:1
            在node2(192.168.1.107)上看主从信息：
                [root@node2 /]# mysql -uroot -p123456 -e 'show slave statusG'
                mysql: [Warning] Using a password on the command line interface can be insecure.
                *************************** 1. row ***************************
                               Slave_IO_State: Waiting for master to send event
                                  Master_Host: 192.168.1.106
                                  Master_User: gtid
            切换master后，MHA进程会自动关闭，并生成文件mha.failover.complete
                masterha_check_status --conf=/etc/mha/mha.conf
                mha.failover.complete文件：该文件生成后，将不在允许主库故障后自动切换
        3.手动切换主库
            手动failover，这种场景意味着在业务上没有启用MHA自动切换功能，当主服务器故障时，人工手动调用MHA来进行故障切换操作
            环境介绍：
                由于刚才MHA自动切换成功，所以现在主库是node1(192.168.1.106)，重启宕掉的主库后成为新的slave
                    mysql> change master to master_host='192.168.1.106',master_user='gtid',master_password='gtid123',master_log_file='mysql-bin.000002',master_log_pos=154;
                    Query OK, 0 rows affected, 2 warnings (0.01 sec)

                    mysql>
                    mysql> start slave;
                    Query OK, 0 rows affected (0.00 sec)

                    mysql> show slave statusG
                    *************************** 1. row ***************************
                                   Slave_IO_State: Waiting for master to send event
                                      Master_Host: 192.168.1.106
                                      Master_User: gtid
                                      Master_Port: 3306
                                    Connect_Retry: 60
                                  Master_Log_File: mysql-bin.000002
                              Read_Master_Log_Pos: 154
                                   Relay_Log_File: master-relay-bin.000002
                                    Relay_Log_Pos: 320
                            Relay_Master_Log_File: mysql-bin.000002
                                 Slave_IO_Running: Yes
                                Slave_SQL_Running: Yes
                为了确保不会自动切换，停掉MHA进程：
                    rm -rf mha.failover.complete
                    masterha_stop --conf=/etc/mha/mha.conf
                    MHA Manager is not running on mha(2:NOT_RUNNING).
                将主库kill掉：
                    killall mysqld
                主库切换失败：
                    mysql> show slave statusG
                    *************************** 1. row ***************************
                                   Slave_IO_State: Reconnecting after a failed master event read
                                      Master_Host: 192.168.1.106
                                      Master_User: gtid
                MHA自动切换主库失败后，可以用手动切换
                    在管理节点操作：
                        masterha_master_switch --master_state=dead --conf=/etc/mha/mha.conf --dead_master_host=192.168.1.106 --dead_master_port=3306 --new_master_ip=192.168.1.107 --new_master_port=3306
                    将宕机的MySQL主库恢复起来：
                        systemctl start mysqld
                    在node1上面操作：
                        mysql> change master to master_host='192.168.1.107',master_user='gtid',master_password='gtid123',master_auto_position=1;
                        Query OK, 0 rows affected, 2 warnings (0.01 sec)

                        mysql> start slave;
                        Query OK, 0 rows affected (0.00 sec)

                        mysql> show slave statusG
                        *************************** 1. row ***************************
                                       Slave_IO_State: Waiting for master to send event
                                          Master_Host: 192.168.1.107
                                          Master_User: gtid
                    至此，新的一主两从结构完成！
        4.让宕掉的主库重新成为新的主库
            手动回切需要关闭MHA监控，使用以下命令来查看MHA监控是否开启
                masterha_check_status --conf=/etc/mha/mha.conf
            将宕掉的MySQL主库重启并成为新的slave后，在管理节点操作：
                masterha_master_switch --conf=/etc/mha/mha.conf --master_state=alive --new_master_host=192.168.1.105 --orig_master_is_new_slave
            在node2节点上可以看到：node2已经成为了slave，而主库是192.168.1.106

来源参考：https://blog.csdn.net/weixin_44297303/article/details/100854872