利用keepalived部署mysql双主高可用

alenas

1.服务器配置
    mysql1   192.168.47.131
    mysql2   192.168.47.132
    vip      192.168.47.130
2.初始化系统
    2.1 操作系统 centos7
    2.2 关闭selinux
        sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
        setenforce 0
    2.3 卸载防火墙firewalld
        yum -y remove firewalld
    2.4 安装基础工具包
        yum install -y net-tools vim iptables-services
3.安装mariadb
    yum install -y mariadb mariadb-server
    mysql_secure_installation   #初始化mariadb数据库
    systemctl start mariadb     #启动mariadb数据库
    systemctl enable mariadb    #开机启动mariadb数据库
4.配置两台数据库互为主从
    4.1 mysql1下数据库配置
        修改mysql配置文件/etc/my.conf,在mysqld下增加以下内容
        [mysqld]
        server-id =  131
        log-bin = mysql-bin
        auto-increment-offset = 1
        auto-increment-increment = 2
        log-slave-updates = true
        systemctl restart mariadb  #修改完配置文件后重启mariadb

        mysql -uroot -p     #登陆数据库
        show master status;  #查看binlog日志文件和pos点
            **mysql-bin.000001 |      399**
        grant replication slave on *.* to repl@'192.168.47.%' identified by 'repl';   #授权同步用户

        CHANGE MASTER TO
            MASTER_USER='repl',
            MASTER_HOST='192.168.47.132',
            MASTER_PASSWORD='repl',
            MASTER_PORT=3306,
            MASTER_LOG_FILE='mysql-bin.000001',
            MASTER_LOG_POS=399;   #设置主服务器为mysql2
        start slave;  #启动主从
        show slave status\G
            **Slave_IO_Running: Yes**
            **Slave_SQL_Running: Yes**
    4.2 mysql2下数据库配
        修改mysql配置文件/etc/my.conf,在mysqld下增加以下内容
        [mysqld]
        server-id =  132
        log-bin = mysql-bin
        auto-increment-offset = 2
        auto-increment-increment = 2
        log-slave-updates = true
        systemctl restart mariadb  #修改完配置文件后重启mariadb

        mysql -uroot -p     #登陆数据库
        show master status;  #查看binlog日志文件和pos点
            **mysql-bin.000001 |      399**
        grant replication slave on *.* to repl@'192.168.47.%' identified by 'repl';   #授权同步用户

        CHANGE MASTER TO
            MASTER_USER='repl',
            MASTER_HOST='192.168.47.131',
            MASTER_PASSWORD='repl',
            MASTER_PORT=3306,
            MASTER_LOG_FILE='mysql-bin.000001',
            MASTER_LOG_POS=399;   #设置主服务器为mysql1
        start slave;  #启动主从
        show slave status\G
            **Slave_IO_Running: Yes**
            **Slave_SQL_Running: Yes**
5.安装keepalived
    yum -y install keepalived
    systemctl enable keepalived  #开机启动keepalived
6.配置keepalive高可用
    6.1 配置mysql为MASTER节点
        vim /etc/keepalived/keepalived.conf
        ! Configuration File for keepalived

        global_defs {
           notification_email {
             acassen@firewall.loc
             failover@firewall.loc
             sysadmin@firewall.loc
           }
           notification_email_from Alexandre.Cassen@firewall.loc
           smtp_server 192.168.200.1
           smtp_connect_timeout 30
           router_id LVS_DEVEL
           vrrp_skip_check_adv_addr
           vrrp_strict
           vrrp_garp_interval 0
           vrrp_gna_interval 0
        }     #配置文件默认部分，不用修改

        vrrp_script check_mysql {

           script "/etc/keepalived/check_mysql.sh"
           interval 2
           weight  2

        }

        vrrp_instance MYSQL {

           state MASTER
           interface ens33
           virtual_router_id 130
           priority 100
           advert_int 1

           authentication {
              auth_type PASS
              auth_pass 666666
           }

           track_script {
              check_mysql
           }

           virtual_ipaddress {
              192.168.47.130
           }

        }

    6.2 配置mysql为BACKUP节点
        vim /etc/keepalived/keepalived.conf
        ! Configuration File for keepalived

        global_defs {
           notification_email {
             acassen@firewall.loc
             failover@firewall.loc
             sysadmin@firewall.loc
           }
           notification_email_from Alexandre.Cassen@firewall.loc
           smtp_server 192.168.200.1
           smtp_connect_timeout 30
           router_id LVS_DEVEL
           vrrp_skip_check_adv_addr
           vrrp_strict
           vrrp_garp_interval 0
           vrrp_gna_interval 0
        }     #配置文件默认部分，不用修改

        vrrp_script check_mysql {

           script "/etc/keepalived/check_mysql.sh"
           interval 2
           weight  2

        }

        vrrp_instance MYSQL {

           state BACKUP      #与mysql1不同
           interface ens33
           virtual_router_id 130
           priority 90       #与mysql1不同
           advert_int 1

           authentication {
              auth_type PASS
              auth_pass 666666
           }

           track_script {
              check_mysql
           }

           virtual_ipaddress {
              192.168.47.130
           }

        }
    6.3 启动keepalived
        systemctl start keepalived
7.修改防火墙配置文件
    vim /etc/sysconfig/iptables
    -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
    -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
    -A INPUT -s 192.168.47.0/24 -p vrrp -j ACCEPT
    systemctl restart iptables   #重启防火墙
    systemctl enable iptables    #开机启动防火墙
8.查看vip是否生效
    mysql1：
    ip addr
    inet 192.168.47.131/24 brd 192.168.47.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.47.130/32 scope global ens33
       valid_lft forever preferred_lft forever
    mysql2：
    ip addr
    inet 192.168.47.132/24 brd 192.168.47.255 scope global ens33
       valid_lft forever preferred_lft forever
9.mysql_check脚本
    vim /etc/keepalived/check_mysql.sh
    #!/bin/bash
    MYSQL=/usr/bin/mysql            
    MYSQL_HOST=localhost
    MYSQL_USER=root
    MYSQL_PASSWORD=mysql

    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1

    if [ $? == 0 ]
    then
        echo " $host mysql login successfully "
        exit 0
    else
        #echo " $host mysql login faild"
        systemctl stop keepalived  
        exit 2
    fi

    chmod +x check_mysql.sh  #给脚本添加可执行权限
10.高可用测试
    在mysql1上关闭mysql
    systemctl stop mariadb
    查看vip是否漂移到mysql2

    在mysql1上重新启动mysql，并启动keepalived
    systemctl start mariadb
    systemctl start keepalived
    查看vip是否回到mysql1

mayiwen123456

不是很明白楼主为啥把firewalld卸载了，改为iptables。难道他两功能不一样嘛。楼主写的很用心，我会按照楼主的步骤做一遍

mayiwen123456

180311 18:31:34 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; thesesame-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code:
180311 18:31:34 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4


[iyunv@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
server-id=131
log-bin=mysql-bin
auto-increment-offset=1
auto-increment-increment=2
log-slave-updates=true

socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d


[mysqld]
datadir=/var/lib/mysql
server-id=132
log-bin=mysql-bin
auto-increment-offset=2
auto-increment-increment=2
log-slave-updates=true
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

alenas

mayiwen123456 发表于 2018-3-11 09:06
不是很明白楼主为啥把firewalld卸载了，改为iptables。难道他两功能不一样嘛。楼主写的很用心，我会按照楼 ...

很多人还不习惯用firewalld，所以就用iptables了。另外，我试过卸载firewalld，不安装iptables，启动keepalived的时候还是默认会启动防火墙规则，导致vip无法ping通。

alenas

mayiwen123456 发表于 2018-3-11 11:48
180311 18:31:34 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave  ...

你是不是修改完配置文件没有重启mysql，报错说的是主从有一样的server id

jzaos

10.高可用测试
    在mysql1上关闭mysql
    systemctl stop mariadb
    查看vip是否漂移到mysql2

怎么测试 vip是否漂移到mysql2   ???

jzaos

10.高可用测试
    在mysql1上关闭mysql
    systemctl stop mariadb
怎么测试    查看vip是否漂移到mysql2   ？