MySQL数据库双机热备 keepalived+mysql主主复制

1. Mysql5.7安装(安装环境centos7.5下安装mysql5.7)

 

yum -y install gcc gcc-c++ ncurses ncurses-devel cmake

 

groupadd mysql

useradd -r -g mysql -s /sbin/nologin mysql

mkdir -p /mysql/mysql

mkdir -p /mysql/datadir

mkdir -p /mysql/logdir

chown -R mysql.mysql /mysql/

 

tar zxvf mysql-5.7.24-el7-x86_64.tar.gz -C /usr/local

 

mv /usr/local/mysql-5.7.24-el7-x86_64/ /usr/local/mysql

 

cd /usr/local/mysql

 

groupadd mysql

 

useradd -r -g mysql mysql

 

chown -R mysql:mysql ./

 

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

 

bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

 

chown -R mysql:mysql data

 

vim /etc/my.cnf         修改配置文件

 

cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld

 

vim /etc/init.d/mysqld      添加basedir和datadir的路径

 

chkconfig --add mysqld

 

ln -s /usr/local/mysql/bin/mysql /usr/bin

 

/etc/init.d/mysqld start

 

 

 

 

（修改mysql密码  

# vim /etc/my.cnf
在[mysqld]的段中加上一句： skip-grant-tables

重启数据库后无密码登陆

mysql> update mysql.user set authentication_string=password('XXXXXXXX') where user='root'；

mysql> flush privileges;

quit

再次修改mysql的登陆设置

将第一步中加上去skip-grant-tables的删掉

重启mysql即可

）

 

 

 

 

 

2.Mysql主主备份

修改my.cnf

开启binlog

输入vi /etc/my.cnf 进入配置文件，按Insert键进入编辑模式，添加如下参数

第一台：

server-id=1

log-bin=/var/lib/mysql/mysql-bin

auto_increment_increment=2

auto_increment_offset=1

 

第二台：
server-id=3
log-bin=/var/lib/mysql/mysql-bin
auto_increment_increment=2
auto_increment_offset=2

 

重启 service mysqld restart

 

创建用户并授权2台都有如下操作：

登录MySQL mysql -uroot -p

创建用户：create user ‘repl’ identified by ‘repl’;

授权:grant replication slave on . to ‘repl’@’192.168.1.%’ identified by ‘epl’;

 

建立关系
第二台关联第一台
a.打开第一台机器登录mysql
输入 show master status; 查看状态

 

b.打开第二台机器登录mysql，执行如下操作主服务器信息写入的是另一台服务器

change master to

master_host=’192.168.1.193’, // 主节点

master_port=3306, //主节点的端口号

master_user=’repl’, // 账号

master_password=’repl’, // 密码

master_log_file=’mysql-bin.000002’, // show master status 对应的的日志

master_log_pos=2072; // show master status 对应的

 

第一台关联第二台
a.打开第二台机器登录mysql
输入 show master status; 查看状态

 

b.打开第一台机器登录mysql，执行如下操作

change master to

master_host=’192.168.1.195’, // 主节点

master_port=3306, //主节点的端口号

master_user=’repl’, // 账号

master_password=’repl’, // 密码

master_log_file=’mysql-bin.000001’, // show master status 对应的的日志

master_log_pos=702; // show master status 对应的

 

6.启动库，执行start slave

 

Show slave status\G;    #查看

 

#表示同步的文件和位置

Master_Log_File: mysql-bin.000035

Read_Master_Log_Pos: 895597105

#显示下面表示工作正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

 

 

 

3.Keepalived安装

Keepalived下载地址http://www.keepalived.org/download.html

1、安装keepalived

# tar -xvf keepalived-2.0.11.tar.gz

# cd keepalived-2.0.11

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

#yum -y install openssl* 如果没有安装openssl请安装

#yum -y install libnl libnl-devel

# make && make install

#cp /root/keepalived-2.0.11/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/keepalived 移动启动脚本

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

# mkdir /etc/keepalived

# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ 复制配置文件

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ 修改可执行二进制文件路径

 

2、修改配置文件master:

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_bak #备份配置文件

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 127.0.0.1

   smtp_connect_timeout 30

   router_id mysqlmaster

}

vrrp_script chk_mysql {

        script "/usr/local/mysql/check_mysql.sh"

        interval 2

        weight 2

}

 

vrrp_instance VI_1 {

    state MASTER

    interface enp2s0f0

    virtual_router_id 51

priority 100

advert_int 5

    nopreempt

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        210.14.119.200/24

    }

    track_script {

        chk_mysql

        }

}

 

修改配置文件 salve:

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_bak #备份配置文件

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 127.0.0.1

   smtp_connect_timeout 30

   router_id mysqlmaster

}

vrrp_script chk_mysql {

        script "/usr/local/mysql/check_mysql.sh"   #检测mysql运行情况脚本

        interval 2

        weight 2

}

 

vrrp_instance VI_1 {

    state BACKUP

    interface enp2s0f0

    virtual_router_id 51

priority 80

advert_int 5

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        210.14.119.200/24

    }

    track_script {

        chk_mysql

        }

}

 

3、监测mysql运行脚本：

vim /usr/local/mysql/check_mysql.sh

 

#!/bin/bash

# auto check mysql process

NUM=`ps -ef|grep mysql|grep -v grep|wc -l`

if

[[ $NUM -eq 0 ]];then

        /etc/init.d/keepalived stop

fi