2.26 mysql--主从切换，虚拟IP的实现

外部存储：
emc商用存储，非常安全，对方工程师无法查看
mysql的MMM架构
MHA的管理员：可以管控多个主从切换实例
后台api

实验环境：

server1：172.25.85.1    主数据库

server2：172.25.85.2        从1数据库

server3：172.25.85.3        从2数据库

server4：172.25.85.4        HMA，实现主从切换的服务器。

注意：虚拟机的内存最好设定为500M

1、实现sever1，server2和server3的gtid的主从复制

1）编辑配置文件

server1

vim /etc/my.cnf

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog

server2

vim /etc/my.cnf

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog

server3

vim /etc/my.cnf

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog

2)启动数据库，完成初始化，这里只作一个server3的示例

[root@server3 ~]# systemctl start mysqld
[root@server3 ~]# cat /var/log/mysqld.log |grep password
2019-02-27T02:08:53.688078Z 1 [Note] A temporary password is generated for root@localhost: UQgdlbXGg2*b
[root@server3 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

 

3)给server1，server2和sever3建立gtid主从复制

server1

mysql> grant replication slave on *.* to repl@'172.25.85.%' identified by 'CCJloveu.123';
Query OK, 0 rows affected, 1 warning (0.42 sec)

 

server2

mysql> change master to master_host='172.25.85.1',master_user='repl', master_password='CCJloveu.123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.20 sec)

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

查看状态：IO和SQL都为yes

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1003
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 1210
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

server3

mysql> change master to master_host='172.25.85.1',master_user='repl', master_password='CCJloveu.123',master_auto_position=1;

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

查看状态：IO和SQL都为yes

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1003
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 1210
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试：查看是否实现gtid的主从复制

server1：

mysql> create database westos;
Query OK, 1 row affected (0.06 sec)

mysql> use westos;
Database changed
mysql> create table usertb ( username varchar(10) not null, password varchar(15) not null);
Query OK, 0 rows affected (0.28 sec)

mysql> insert into usertb values ('user1','111');
Query OK, 1 row affected (0.20 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

server2查看数据

mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

server3查看数据

mysql> select * from westos.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

2、准备一台sever4，作为主从切换的调度机，并给sever1-4安装相应的软件

server1-3安装节点包

yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

Installed:
  mha4mysql-node.noarch 0:0.58-0.el7.centos

server4安装全部的MHA包

yum install *.rpm -y

Installed:
  mha4mysql-manager.noarch 0:0.58-0.el7.centos                                  
  mha4mysql-node.noarch 0:0.58-0.el7.centos                                     
  perl-Config-Tiny.noarch 0:2.14-7.el7                                          
  perl-Email-Date-Format.noarch 0:1.002-15.el7                                  
  perl-Log-Dispatch.noarch 0:2.41-1.el7.1                                       
  perl-MIME-Lite.noarch 0:3.030-1.el7                                           
  perl-MIME-Types.noarch 0:1.38-2.el7                                           
  perl-Mail-Sender.noarch 0:0.8.23-1.el7                                        
  perl-Mail-Sendmail.noarch 0:0.79-21.el7                                       
  perl-Parallel-ForkManager.noarch 0:1.18-2.el7  

3、编辑配置文件

[root@server4 ~]# mkdir /etc/masterha    ##创建配置文件的目录
[root@server4 ~]# vim /etc/masterha/app1.cnf  ##每一个app.cnf为一个主从切换的实例，可以有多个

编辑如下

[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log
master_binlog_dir=/etc/masterha
#master_ip_failover_script= /usr/local/bin/master_ip_failover   
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change
password=CCJloveu.123
user=root
ping_interval=1
remote_workdir=/tmp     
repl_password=CCJloveu.123
repl_user=repl
#report_script=/usr/local/send_report   
#secondary_check_script=/usr/local/bin/masterha_secondary_check -s server03 -s server02
#shutdown_script=""     
ssh_user=root

[server1]
hostname=172.25.85.1
port=3306

[server2]
hostname=172.25.85.2
port=3306
candidate_master=1  ##候选主数据库第一位，意思是主数据库荡机后，server2将成为主数据库
check_repl_delay=0

[server3]
hostname=172.25.85.3
port=3306
no_master=1   ##sever3不会作为主数据库

4、实现server4免密登陆server1-3，server1-3可以互相免密登陆,并测试

server4

ssh-keygen
ssh-copy-id server1:
ssh-copy-id server2:
ssh-copy-id server3:
scp -r .ssh/ server1:
scp -r .ssh/ server2:
scp -r .ssh/ server3:

测试：

[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

Wed Feb 27 11:00:34 2019 - [info] All SSH connection tests passed successfully.

 

5、授权server4，可以访问server1-3的数据库,server2，3只可读，检查

server1

mysql> grant all on *.* to root@'%' identified by 'CCJloveu.123';
Query OK, 0 rows affected, 1 warning (0.05 sec)

server2,3

mysql> set global read_only=1;
Query OK, 0 rows affected (0.09 sec)

检查登陆是否可以

[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

MySQL Replication Health is OK.

 

6、手动切换主数据库

1）关闭server1数据库，（模拟数据库荡机）

[root@server1 ~]# systemctl stop mysqld

2）手动切换

server4

[root@server4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.85.1 --dead_master_port=3306 --new_master_host=172.25.85.2 --new_master_port=3306

提示信息：

Selected 172.25.85.2(172.25.85.2:3306) as a new master.
172.25.85.2(172.25.85.2:3306): OK: Applying all logs succeeded.
172.25.85.3(172.25.85.3:3306): OK: Slave started, replicating from 172.25.85.2(172.25.85.2:3306)
172.25.85.2(172.25.85.2:3306): Resetting slave info succeeded.
Master failover to 172.25.85.2(172.25.85.2:3306) completed successfully.

查看server3数据库的主从状态，发现server3的主数据库为server2

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 2491
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 961
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3)恢复server1作为从库

systemctl start mysqld

mysql> CHANGE MASTER TO MASTER_HOST='172.25.85.2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='CCJloveu.123';
Query OK, 0 rows affected, 2 warnings (0.40 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 2491
               Relay_Log_File: server1-relay-bin.000002
                Relay_Log_Pos: 961
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

7、热切换

server4

1)删除之前切换时生成的文件，若有此文件一段时间内是不可以切换的。

[root@server4 ~]# cd /etc/masterha/
[root@server4 masterha]# ls
app1.cnf  app1.failover.complete
[root@server4 masterha]# rm -rf app1.failover.complete

2）热切换

[root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.85.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

提示：

Wed Feb 27 11:18:55 2019 - [info]  172.25.85.1: Resetting slave info succeeded.
Wed Feb 27 11:18:55 2019 - [info] Switching master to 172.25.85.1(172.25.85.1:3306) completed successfully.

查看server2和server3的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 750
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

8、自动切换

1)自动切换监听打开打入后台

[root@server4 masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &
[1] 12279

2)关闭server1，模拟荡机

systemctl stop mysqld

3)查看sever3的状态，主数据库为server2

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 2491
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4）恢复server1作为从库

systemctl start mysqld

mysql> CHANGE MASTER TO MASTER_HOST='172.25.85.2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='CCJloveu.123';
Query OK, 0 rows affected, 2 warnings (0.40 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 2491
               Relay_Log_File: server1-relay-bin.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

9、实现虚拟IP（vip）

server4

1）编辑配置文件

[root@server4 masterha]# vim /etc/masterha/app1.cnf

编辑如下

  4 master_binlog_dir=/var/lib/mysql
  5 master_ip_failover_script= /usr/local/bin/master_ip_failover
  6 master_ip_online_change_script= /usr/local/bin/master_ip_online_change

 

 

2）从安装包中，提取测试文件并修改

[root@server4 ~]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@server4 conf]# cd ~/mha4mysql-manager-0.58/samples/scripts/
[root@server4 scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@server4 scripts]# cp master_ip_*  /usr/local/bin/
[root@server4 scripts]# cd /usr/local/bin/
[root@server4 bin]# vim master_ip_failover

#!/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 = '172.25.85.100/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

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

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {

        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &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 \n";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    `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=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

 
[root@server4 bin]# vim master_ip_online_change

#!/usr/bin/env perl
use strict;  
use warnings FATAL =>'all';  
 
use Getopt::Long;  
 
my $vip = '172.25.85.100/24';  # Virtual IP  
my $key = "1";  
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";  
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";  
my $exit_code = 0;  
 
my (  
  $command,              $orig_master_is_new_slave, $orig_master_host,  
  $orig_master_ip,       $orig_master_port,         $orig_master_user,  
  $orig_master_password, $orig_master_ssh_user,     $new_master_host,  
  $new_master_ip,        $new_master_port,          $new_master_user,  
  $new_master_password,  $new_master_ssh_user,  
);  
GetOptions(  
  'command=s'                => \$command,  
  'orig_master_is_new_slave' => \$orig_master_is_new_slave,  
  'orig_master_host=s'       => \$orig_master_host,  
  'orig_master_ip=s'         => \$orig_master_ip,  
  'orig_master_port=i'       => \$orig_master_port,  
  'orig_master_user=s'       => \$orig_master_user,  
  'orig_master_password=s'   => \$orig_master_password,  
  'orig_master_ssh_user=s'   => \$orig_master_ssh_user,  
  'new_master_host=s'        => \$new_master_host,  
  'new_master_ip=s'          => \$new_master_ip,  
  'new_master_port=i'        => \$new_master_port,  
  'new_master_user=s'        => \$new_master_user,  
  'new_master_password=s'    => \$new_master_password,  
  'new_master_ssh_user=s'    => \$new_master_ssh_user,  
);  
 
 
exit &main();  
 
sub main {  
 
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";  
 
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 "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\n";  
&stop_vip();  
            $exit_code = 0;  
        };  
        if ($@) {  
            warn "Got Error: $@\n";  
            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 "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\n";  
&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 \n";  
        `ssh $orig_master_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 $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;  
}  
# A simple system call that disable the VIP on the old_master  
sub stop_vip() {  
`ssh $orig_master_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=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";  
}

3)增加执行权限

[root@server4 bin]# ll
total 8
-rw-r--r-- 1 root root 2168 Feb 27 12:38 master_ip_failover
-rw-r--r-- 1 root root 3843 Feb 27 12:39 master_ip_online_change
[root@server4 bin]# chmod +x *
[root@server4 bin]# ll
total 8
-rwxr-xr-x 1 root root 2168 Feb 27 12:38 master_ip_failover
-rwxr-xr-x 1 root root 3843 Feb 27 12:39 master_ip_online_change

4）给主数据库增加虚拟ip

[root@server2 ~]# ip addr add 172.25.85.100/24 dev eth0
[root@server2 ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:b1:7b:e2 brd ff:ff:ff:ff:ff:ff
    inet 172.25.85.2/24 brd 172.25.85.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.25.85.100/24 scope global secondary eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::5054:ff:feb1:7be2/64 scope link
       valid_lft forever preferred_lft forever

测试

1、首先授权一个外部用户可以访问，虚拟ip

server2(主数据库)

mysql> grant insert,update,select on *.* to ccj@'%' identified by 'CCJloveu.123';
Query OK, 0 rows affected, 1 warning (0.65 sec)

测试：使用其他shell（172.25.85.250）

[kiosk@foundation85 ~]$ mysql -uccj -pCCJloveu.123 -h 172.25.85.100
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.02 sec)

2、删除之前切换时生成的文件，若有此文件一段时间内是不可以切换的。

[root@server4 ~]# cd /etc/masterha/
[root@server4 masterha]# ls
app1.cnf  app1.failover.complete
[root@server4 masterha]# rm -rf app1.failover.complete

3、热切换

[root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.85.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

提示：

Wed Feb 27 13:34:43 2019 - [info]  172.25.85.1: Resetting slave info succeeded.
Wed Feb 27 13:34:43 2019 - [info] Switching master to 172.25.85.1(172.25.85.1:3306) completed successfully.

查看server2和server3的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.85.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 521
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

查看172.25.85.250的shell，发现访问虚拟ip的数据库没有发生变化，（内部主从切换，外部无法得知）

MySQL [(none)]> select * from westos.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)