mysql主主复制+keepalived部署

环境：

mysql-master-01：10.0.0.30

mysql-master-02：10.0.0.35

vip：10.0.0.39

安装mysql5.7.20

可参考：https://soulchild.cn/266.html

开始配置主主环境

一、修改mysql配置

master-01：

[mysqld]
basedir=/application/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=1
port=3306
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%

[mysql]
socket=/tmp/mysql.sock
prompt=master-01[\\d]>

[mysqld_safe]
log-error=/var/log/mysql.log

master-02：

[mysqld]
basedir=/application/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=11
port=3306
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%

[mysql]
socket=/tmp/mysql.sock
prompt=master-02[\\d]>

[mysqld_safe]
log-error=/var/log/mysql.log

二、配置msater-01主,master-02从

1.添加主从复制用户，master-01执行

grant replication slave on *.* to 'repl'@'10.0.0.%' identified by 'replpass';
grant all on blog.* to 'blog'@'10.0.0.%' identified by 'blog123';
#记录两个值File和Position
show master status;

2.master-02中执行，指定master-01服务器作为主服务器

#mysql-bin.000001和704为上面获取的值
change master to master_host='10.0.0.30',master_user='repl',master_password='replpass',master_log_file='mysql-bin.000001',master_log_pos=704;
strart slave;
show slave status\G

三、配置msater-02主,master-01从

1.添加主从复制用户，master-02执行

grant replication slave on *.* to 'repl'@'10.0.0.%' identified by 'replpass';
grant all on blog.* to 'blog'@'10.0.0.%' identified by 'blog123';
#记录两个值File和Position
show master status;

2.master-01中执行，指定master-02服务器作为主服务器

#mysql-bin.000003和704为上面获取的值
change master to master_host='10.0.0.35',master_user='repl',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=704;
strart slave;
show slave status\G

测试数据同步：

master-01[blog]>create database blog;

master-01[blog]>use blog;

master-01[blog]>create table user(

`username` char(10),

`password` char(10)

);

master-02[blog]>show databases;

master-02[blog]>use blog;

master-02[blog]>show tables;

master-02[blog]>insert into `user` (username,password) values('li','123');

master-01[blog]>select * from user;

四、安装配置keepalived

1.两个节点安装

yum install -y keepalived

master-01的keepalived配置文件（此配置未考虑脑裂问题）：

global_defs {
   notification_email {
     742899387@qq.com
   }
   notification_email_from keepalived@local.com
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id mysql-master-01
}

vrrp_script check_mysql {
    script "/server/scripts/keepalived/check_mysql.pl"
    interval 2
}

vrrp_instance mysql {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.0.0.39
    }
    track_script {
        check_mysql
    }
}

master-02的keepalived配置文件

global_defs {
   notification_email {
     742899387@qq.com
   }
   notification_email_from keepalived@local.com
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id mysql-master-02
}

vrrp_script check_mysql {
    script "/server/scripts/keepalived/check_mysql.pl"
    interval 2
}

vrrp_instance mysql {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 80
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.0.0.39
    }
    track_script {
        check_mysql
    }
}

2.编写状态检测脚本

mkdir /server/scripts/keepalived/ -p
cd /server/scripts/keepalived/
vim check_mysql.pl

脚本

#!/usr/bin/perl -w

use DBI;
use DBD::mysql;

# CONFIG VARIABLES
$SBM = 120;
$db = "mysql";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "123456";

# SQL query
$query = "show slave status";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });

if (!defined($dbh)) {
    exit 1;
}

$sqlQuery = $dbh->prepare($query);

$sqlQuery->execute;
$Slave_IO_Running =  "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master = "";

while (my $ref = $sqlQuery->fetchrow_hashref()) {
    $Slave_IO_Running = $ref->{'Slave_IO_Running'};
    $Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
    $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
}

$sqlQuery->finish;
$dbh->disconnect();

if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
    exit 1;
} else {
    if ( $Seconds_Behind_Master > $SBM ) {
        exit 1;
    } else {
        exit 0;
    }
}

chmod +x check_mysql.pl

3. 开启keepalived

systemctl start keepalived
systemctl enable keepalived

4.连接测试

mysql -ublog -p -h 10.0.0.39

show variables like ‘%hostname%’;

关闭master-01

mysql -ublog -p -h 10.0.0.39