基于keepalived实现mariadb的高可用

提示:

上一篇博文己经介绍过了keepalived是什么,有那些参数,也介绍过基于corosync+pacemaker实现mairadb高可用,这次我将介绍一下如何利用keepalived对mariadb实现高可用。

----本文大纲

前言

主机环境

配置过程

测试

-----------

一、前言

说到对mariadb实现高可用,也就是就说,当有任何一个mariadb挂掉之后在还有其它mariadb主机接管业务,完全不会影响到线上的业务,当挂掉的主机修复后重新上线,周而复始的工作,这就要对maridb做主主复制,无论那一个主机重新上线,那可以做到从当前工作主机上同步数据;在以前,要对mariadb实现出现故障自动切换,主要是在主机之间发送心跳信息做健康检测,而keepalived则要做的是,如果当前主机的服务不可用,那么就要对当前的keepalived的优先级先降级,而从其它从节点中选举出一个新的keepalived从做为主,将vip转移到此主机上。

二、主机环境

系统角色

IP地址

安装的软件
Centos 6.5 x86_64
DBMASTER1

VIP:192.168.1.200

IP:192.168.1.112

maridb-10(通用二进制格式)、ipvsadm、keepalived
Centos 6.5 x86_64
DBMASTER2

VIP:192.168.1.200

IP:192.168.1.113

maridb-10(通用二进制格式)、ipvsadm、keepalived

三、配置过程

1、DBMASTER1

数据库mariadb10通用二进制格式的安装略过(与mysql5.6安装方式完全相同)。

  • 配置文件/etc/my.cnf

[root@essun ~]# grep -v "#" /etc/my.cnf |grep -v "^$"
[client]
port        = 3306
socket      = /tmp/maria.sock
[mysqld]
port        = 3306
socket      = /tmp/maria.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
datadir=/mydata/data
server-id=1
log-bin=mysql-bin
binlog_format=ROW
log-slave-updates
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
  • 安装ipvsamd、keepalived

#yum install -y ipvsadm keepalived
  • 修改配置文件/etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
   notification_email {
     root@localhost
     essun@localhost
   }
   notification_email_from essun@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 90
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.200
    }
}
virtual_server 192.168.1.200 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind dr
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.112 3306 {
        weight 1
    notify_down /etc/keepalived/mari.sh #当maridb停止时,执行的脚本
    TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
  • 事件脚本

[root@essun keepalived]# cat mari.sh
#!/bin/bash
killall -9 keepalived
[root@essun keepalived]# chmod +x /etc/keepalived/mari.sh
  • 修改内核参数

[root@essun ~]# tail -5 /etc/sysctl.conf
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
[root@essun ~]# sysctl -p |tail -5
error: "net.bridge.bridge-nf-call-ip6tables" is an unknown key
error: "net.bridge.bridge-nf-call-iptables" is an unknown key
error: "net.bridge.bridge-nf-call-arptables" is an unknown key
kernel.shmall = 4294967296
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
  • 启动服务(mariadb、keepalived)

[root@essun keepalived]# service mari start
Starting MySQL.........                                    [  OK  ]
[root@essun keepalived]# service keepalived start
Starting keepalived:                                       [  OK  ]
[root@essun keepalived]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:98:b0:ac brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.112/24 brd 255.255.255.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::20c:29ff:fe98:b0ac/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun keepalived]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50
  -> 192.168.1.112:3306           Local   1      0          0
  • 查看当前主机上的binlog日志

MariaDB [(none)]> show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000021 |      358 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
  • 授权复制用户与测试用户

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘192.168.1.113‘ identified by ‘replpass‘;
MariaDB [(none)]> grant replication slave,replication client on *.* to ‘root‘@‘192.168.1.%‘ identified by ‘mari‘;
  • 查看一下授权表记录

MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+------------------+-------------------------------------------+
| user        | host             | password                                  |
+-------------+------------------+-------------------------------------------+
| root        | localhost        |                                           |
| root        | master.mysql.com |                                           |
| root        | 127.0.0.1        |                                           |
| root        | ::1              |                                           |
|             | localhost        |                                           |
| repluser    | 192.168.1.113    | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |
| root        | 192.168.1.%      | *CA3CBE479AD72908BED6733E9D9695CC2DAC4BC5 |
+-------------+------------------+-------------------------------------------+
12 rows in set (0.00 sec)

2、DBMASTER2 (192.168.1.113)

  • 修改/etc/my.cnf

[root@essun ~]# grep -v "#" /etc/my.cnf |grep -v "^$"
[client]
port        = 3306
socket      = /tmp/maria.sock
[mysqld]
port        = 3306
socket      = /tmp/maria.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
datadir=/mydata/data
binlog_format=row
server-id=30
log-bin=mysql-bin
log-slave-updates
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

此处的配置文件只有server-id与DBMASTER是不同的,其它的都一样

  • 安装ipvsadm、keepalived

#yum install -y ipvsadm keepalived
  • 修改keepalived配置文件

[root@essun ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     root@localhost
     essun@localhost
   }
   notification_email_from essun@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 90
    priority 95
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.200
    }
}
virtual_server 192.168.1.200 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind dr
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.113 3306 {
        weight 1
    notify_down /etc/keepalived/mari.sh
    TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
  • 事件脚本

[root@essun keepalived]# cat mari.sh
#!/bin/bash
killall -9 keepalived
[root@essun keepalived]# chmod +x /etc/keepalived/mari.sh
  • 修改内核参数

[root@essun ~]# tail -5 /etc/sysctl.conf
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
[root@essun ~]# sysctl -p |tail -5
error: "net.bridge.bridge-nf-call-ip6tables" is an unknown key
error: "net.bridge.bridge-nf-call-iptables" is an unknown key
error: "net.bridge.bridge-nf-call-arptables" is an unknown key
kernel.shmall = 4294967296
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
  • 启动服务

[root@essun ~]# service mari start
Starting MySQL....                                         [  OK  ]
[root@essun ~]# service keepalived start
Starting keepalived:                                       [  OK  ]
[root@essun ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:d2:39:cb brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.113/24 brd 255.255.255.255 scope global eth0
    inet6 fe80::20c:29ff:fed2:39cb/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun ~]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50
  -> 192.168.1.113:3306           Local   1      0          0

由于vip192.168.1.200并不在当前主机,所以当前节点是不会工作的。

  • 查看当前主机的binlog日志

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000017 |      374 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
  • 添加复制与测试用户

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘192.168.1.112‘ identified by ‘replpass‘;
MariaDB [(none)]> grant replication slave,replication client on *.* to ‘root‘@‘192.168.1.%‘ identified by ‘mari‘;
  • 在DBMASTER1使用change连接DBMASTER2

MariaDB [(none)]> change master to master_host=‘192.168.1.113‘,master_user=‘repluser‘,master_password=‘replpass‘,master_log_file=‘mysql-bin.000017‘,master_log_pos=374;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.113
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 374
               Relay_Log_File: essun-relay-bin.000029
                Relay_Log_Pos: 661
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 374
              Relay_Log_Space: 1245
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/slave/cacert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/slave/mysql.crt
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/slave/mysql.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 30
               Master_SSL_Crl: /etc/slave/cacert.pem
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)
  • 在DBMASTER2连接DBMASTER1

MariaDB [(none)]> change master to master_host=‘192.168.1.112‘,master_user=‘repluser‘,master_password=‘replpass‘,master_log_file=‘mysql-bin.000021‘,master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.112
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000021
          Read_Master_Log_Pos: 358
               Relay_Log_File: essun-relay-bin.000026
                Relay_Log_Pos: 535
        Relay_Master_Log_File: mysql-bin.000021
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 358
              Relay_Log_Space: 832
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/slave/cacert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/slave/mysql.crt
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/slave/mysql.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl: /etc/slave/cacert.pem
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)

四、测试

1、数据库复制测试

  • 在DBMASTER1中建立一个数据库DBMASTER

MariaDB [(none)]> create database DBMASTER;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| DBMASTER           |
| a                  |
| b                  |
| ceshidb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| xxyy               |
| yydb               |
+--------------------+
11 rows in set (0.00 sec)
  • 在DBMASTER2,也建立一个DBMASTER1,并查看一下。

MariaDB [(none)]> create database DBMASTER1
    -> ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| DBMASTER           |
| DBMASTER1          |
| a                  |
| b                  |
| ceshidb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| xxyy               |
+--------------------+
11 rows in set (0.00 sec)
  • DBMASTER1建立的数据库己经复制到了DBMASTER2上,在DBMASTER2上建立的数据库己在DBMASTER1中.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| DBMASTER           |
| DBMASTER1          |
| a                  |
| b                  |
| ceshidb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| xxyy               |
| yydb               |
+--------------------+
12 rows in set (0.00 sec)

2、测试事件脚本(DBMASTER1)

[root@essun keepalived]# service mari status
MySQL running (3501)                                       [  OK  ]
[root@essun keepalived]# service keepalived status
keepalived (pid  3583) is running...
[root@essun keepalived]# ps aux |grep keepalived
root      3583  0.0  0.3 110148  1092 ?        Ss   04:21   0:00 /usr/sbin/keepalived -D
root      3585  0.0  0.8 112376  2580 ?        S    04:21   0:00 /usr/sbin/keepalived -D
root      3586  0.0  0.6 112252  2008 ?        S    04:21   0:01 /usr/sbin/keepalived -D
root      3724  0.0  0.2 103252   828 pts/0    S+   05:10   0:00 grep keepalived
[root@essun keepalived]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:98:b0:ac brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.112/24 brd 255.255.255.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::20c:29ff:fe98:b0ac/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun keepalived]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50
  -> 192.168.1.112:3306           Local   1      0          0
[root@essun keepalived]# service mari stop
Shutting down MySQL..                                      [  OK  ]
[root@essun keepalived]# ps aux |grep keepalived
root      3763  0.0  0.2 103252   820 pts/0    S+   05:11   0:00 grep keepalived
[root@essun keepalived]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:98:b0:ac brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.112/24 brd 255.255.255.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::20c:29ff:fe98:b0ac/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun keepalived]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50

只要一停止maradb,vip就转移了

  • 查看一下DBMASTER2上的ip信息

[root@essun ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:d2:39:cb brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.113/24 brd 255.255.255.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::20c:29ff:fed2:39cb/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun ~]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50
  -> 192.168.1.113:3306           Local   1      0          0
  • 登陆数据库测试下,将vip切换到DBMASRTER1上

[root@essun keepalived]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:98:b0:ac brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.112/24 brd 255.255.255.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::20c:29ff:fe98:b0ac/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun keepalived]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50
  -> 192.168.1.112:3306           Local   1      0          0     
[root@essun keepalived]#
  • 使用vip登录测试

root@essun ~]# mysql -uroot -pmari -h192.168.1.200
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.0.10-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| DBMASTER           |
| DBMASTER1          |
| a                  |
| b                  |
| ceshidb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| xxyy               |
| yydb               |
+--------------------+
12 rows in set (0.00 sec)
MariaDB [(none)]>

这是DBMASTER1上的数据库,此时不退出登录状态,将DBMASRER1上的mariadb关闭,看一下能否切换到。

  • DBMASRTER2上的数据库上

[root@essun keepalived]# service mari stop
Shutting down MySQL.                                       [  OK  ]
[root@essun keepalived]#
##################DBMASTER2####################
[root@essun ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:d2:39:cb brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.113/24 brd 255.255.255.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::20c:29ff:fed2:39cb/64 scope link tentative dadfailed
       valid_lft forever preferred_lft forever
[root@essun ~]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.200:3306 wrr persistent 50
  -> 192.168.1.113:3306           Local   1      0          0
#######################由此发现地址己经切换过到DBMASTER2上了######
#再show databases;
unknown [(none)]> show databases;
No connection. Trying to reconnect...
Connection id:    11
Current database: *** NONE ***
+--------------------+
| Database           |
+--------------------+
| DBMASTER           |
| DBMASTER1          |
| a                  |
| b                  |
| ceshidb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| xxyy               |
+--------------------+
11 rows in set (0.00 sec)
MariaDB [(none)]>
#这己经是DBMASTER2的数据库了




==============================完=============================================

本文出自 “和风细雨” 博客,请务必保留此出处http://essun.blog.51cto.com/721033/1405880

基于keepalived实现mariadb的高可用,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。