mysql 双机主备 -keepalived 版

IP
192.168.105.11	master	主
192.168.105.13	slave	备

主备机安装 MySQL 数据库 

1 修改配置文件

1. my.cnf(主)

[mysqld]

symbolic-links=0

log-bin=mysql-bin

server-id=117

binlog_format=MIXED

user=mysql

  2 .my.cnf (备)

 

symbolic-links=0

log-bin=mysql-bin

server-id=190

user=mysql

2. 分别重启 master,slave

systemctl start mysql

3.创建主从复制账户 

登录 master 执行:

GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.%' IDENTIFIED BY 'mysync';

FLUSH PRIVILEGES;

(mysync 用户用于备机复制主机数据，ip 支持通配符)

1 .查看主机binlog

show master status:

2. 若主机之前已有数据，需要先备份数据到备机

mysqldump --all-databases  --master-data -uxxxx -pxxxx -P 3306 > /XXXX/node01-master.db

scp  node01-master.db 到备机

4.备机同步

登录slave 执行

stop salve；

source /xxxx/node01-master.db;(同步前，加载主机数据)

change master to

>master_host='192.168.105.11',

>master_port=3306,

>master_user='mysync',

>master_password='mysync',

>master_log_file='mysql-bin.000007',

>master_log_pos=6556622; （show master status file/pos的值）

start slave;      (启动同步命令)

1.Show slave status （备机状态） 

复制过程关键进程：Slave_IO_Running --> 负责与主机通信（拷贝主机binlog二进制文件）

Slave_SQL_Running ------>备机mysql 进程（同步执行二进制文件）

5 keepalived 配置

版本：keepalived-2.0.18.tar.gz

1.编译

tar -zxvf keepalived-2.0.18.tar.gz

Cd  keepalived-2.0.18

./configure --prefix=/usr/local/keepalived

make && make install

cp  keepalived-2.0.18/keepalived/etc/init.d   /etc/init.d

cp /usr/local/keepalived/etc/keepalived/keepalived.conf   /etc/keepalived/

cp /usr/local/keepalived/etc/sysconfig/keepalived   /etc/sysconfig/

Chkconfig --add  keepalived

Chkconfig  keepalived on

 2. 修改配置文件 keepalived.conf

    主机：

global_defs {

    router_id HA_MYSQL

}

vrrp_script chk_mysql_port {

    script "/xxxx/keepalived_down"

    interval 2

    weight -5

    fall 2

    rise 1

}

vrrp_instance VI_1 {

    state MASTER

    interface eno1    ---》 网卡

    virtual_router_id 51

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.105.199

    }

   track_script{

   chk_mysql_port

   }

}

 virtual_server 192.168.105.199 3306 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    persistence_timeout 60

    protocol TCP

    real_server 192.168.105.11 3306 {

        weight 3

        notify_down /xxxx/keepalived

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

}

 备机 ：

global_defs {

    router_id HA_MYSQL

}

vrrp_script chk_mysql_port {

    script "/xxxx/keepalived_down"

    interval 2

    weight -5

    fall 2

    rise 1

}

vrrp_instance VI_1 {

    state BACKUP

    interface eno1

    virtual_router_id 51

    priority 90

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.105.199    -----》 设置虚地址

    }

    track_script{

 chk_mysql_port

}

}

virtual_server 192.168.105.199 3306 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    persistence_timeout 60

    protocol TCP

    real_server 192.168.105.13 3306 {

        weight 3

        notify_down /xxxx/keepalived_down

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

}

3. keepalived_down

#!/bin/bash

counter=$(ps -C mysqld --no-heading|wc -l)

if [ ${counter} -eq 0 ]; then

     systemctl restart mysqld

     counter1=$(ps -C keepalived --no-heading|wc -l)

     if [ ${counter1} -ne 0 ]; then

        /etc/init.d/keepalived stop

     fi

fi

4.  设定周期性监测keepalived状态：

#!/bin/bash

counter=$(ps -C keepalived --no-heading|wc -l)

if [ ${counter} -ne 0 ]; then

     /etc/init.d/keepalived restart

fi

 5  查看 主机状态 ip addr 

 其中 192.68.105.199 为虚拟地址

6.测试

1. 用 Navicat 工具 链接数据库 192.168.105.199:3306
2. 修改主库，检查备库
3. 关闭主库(备库升级为主库)，navicat 访问正常

7. 问题 

1. 主机执行 mysqldump 前，执行 FLUSH TABLES WITH READ LOCK 防止修改主库数据，但是发现表依旧被修改（貌似FLUSH TABLES WITH READ LOCK 没起作用），导致同步前导入备机的数据与主机不一致  。

======》检查，停掉所有写主机应用。

2 备机同步时，应用写备机。

=====》停掉备机同步，手动同步；或  set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

3 .在发生一次切换后，备机(x.x.105.13)变为主机；此时若备机(x.x.105.11) 恢复，备机上升为主机，若(x.x.105.13) 为主机的过程中有更新，导致 (x.x.105.13) 与 (x.x.105.11)数据不一致。  ======= >  (1) 在检测脚本中不对keepalived做周期拉起，一次主备切换后，需手工处理，（2） 配置主主双活模式。