MySQL高可用性之Keepalived+Mysql（双主热备）

2021-09-30 02:43:06

一、安装Mysql数据库配置主从

1.上传yum文件夹到 /root/目录下，给予yum.sh 运行权限，安装mysql。

[root@master ~]# chmod +x yum.sh    #给予执行权限
[root@master ~]# ./yum.sh               
[root@master ~]# yum -y install mysql-server mysql mysql-devel   #安装mysql
[root@master ~]# service mysqld start                                 #启动mysql
[root@master ~]# mysqladmin -u root password zhuoshi  #修改密码
[root@master ~]# vi /etc/my.cnf   #开启二进制日志，设置id

[mysqld]         #删掉其他配置，保留以下配置
server-id = 1       #1和2区分主备
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all

[root@master ~]# service mysqld restart        #重启mysql服务

2.先查看下log bin日志和pos值位置

[root@master ~]#myqsl -uroot -pzhuoshi    #进入mysql数据库
mysql >show master status;

master配置如下：（配置对方信息）

[root@ master ~]# mysql -uroot -pzhuoshi
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication‘@‘23.20.4.%‘ IDENTIFIED BY ‘zhuoshi‘; 
mysql> flush privileges;
mysql> change master to
    -> master_host=‘23.20.4.221‘,     #host：需要写对面的
    -> master_user=‘replication‘,
    -> master_password=‘zhuoshi‘,
    -> master_log_file=‘mysql-bin.000002‘,     #file：需要写对面的
    -> master_log_pos=106;               #pos：需要写对面的值
mysql> start slave;     #启动同步
mysql> stop  slave;       #如果配置错误执行stop slave从新配置填写对方的信息

backup配置如下：（配置对方信息）

[root@backup ~]#  mysql -u root -pzhuoshi
mysql> GRANT  REPLICATION SLAVE ON *.* TO ‘replication‘@‘23.20.4.%‘ IDENTIFIED  BY zhuoshi;
mysql> flush  privileges;
mysql> change  master to
 ->  master_host=‘23.20.4.220‘,   # host：需要写对面的
 ->master_user=‘replication‘,
 ->master_password=zhuoshi,
 ->master_log_file=‘mysql-bin.000002‘,    # file：需要写对面的
 ->master_log_pos=106;             #pos：需要写对面的值
mysql> start  slave;
mysql> stop  slave;       #如果配置错误执行stop slave从新配置填写对方的信息

3.主从同步配置完毕，查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功如下：

mysql>show slave status\G  #查看同步状态

注：以下在master插入数据测试一下查看主从数据是否同步...

mysql> use test;     #进入test库
mysql> show tables;
mysql> create table user (number INT(10),name VARCHAR(255));   #创建表
mysql> insert info user values(01,‘zhangshan‘);     #在表里插入数据

注：在backup查看数据是否同步成功...

myqsl> use test;
mysql> show tables;

注：查看数据同步成功后，也可以在backup插入数据测试一下可以不做。

二、配置keepalived实现双机热备

1、安装keepalived，修改配置文件

[root@master ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包

[root@master ~]# tar zxvf keepalived-1.2.7.tar.gz -C /opt/ #将上传的tar包解压
[root@master ~]# cd keepalived-1.2.7
[root@master ~]#./configure --prefix=/usr/local/keepalived && make && make install   #编译安装
#将keepalived配置成系统服务
[root@master]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived    /etc/init.d/
[root@master]# cp /usr/local/keepalived/etc/sysconfig/keepalived    /etc/sysconfig/
[root@master]# mkdir /etc/keepalived/     #创建/etc/keepalived
[root@master]#cp /usr/local/keepalived/etc/keepalived/keepalived.conf   /etc/keepalived/
[root@master]# cp /usr/local/keepalived/sbin/keepalived    /usr/sbin/

注：配置文件 #backup服务器只修改priority为90、nopreempt不设置、real_server设置本地IP

[root@master ~]# vi /etc/keepalived/keepalived.conf  #修改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 MYSQL_HA
}

vrrp_instance VI_1 {
    state BACKUP        #此处改为BACKUP
    interface eno3
    virtual_router_id 51
    priority 100            #backup服务器只修改priority为90、nopreempt不设置、real_server设置本地IP
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
     23.20.4.227       #此处为漂移IP
    }
}

virtual_server 23.20.4.220 3306 {       #此处为主mysql数据库的IP地址
    delay_loop 6
    nat_mask 255.255.255.0               
    persistence_timeout 50                 #同一IP的连接60秒内被分配到同一台真实服务器
    protocol TCP

    real_server 23.20.4.220 3306 {
        weight 3
        notify_down /user/local/keepalived/mysql.sh
        TCP_CHECK {
            connect_timeout 3     #连接超时
            nb_get_retry 3           #重试次数
            delay_before_retry 3  #重试间隔时间
          }
     }
}
virtual_server 23.20.4.221 3306 {     #此处为从mysql数据库的IP地址
    delay_loop 6
    nat_mask 255.255.255.0
    persistence_timeout 50        #同一IP的连接60秒内被分配到同一台真实服务器
    protocol TCP

    real_server 23.20.4.221 3306 {
        weight 3
        notify_down /user/local/keepalived/mysql.sh
        TCP_CHECK {
            connect_timeout 3    #连接超时
            nb_get_retry 3           #重试次数
            delay_before_retry 3   #重试间隔时间
          }
     }
}

2、配置keepalived的脚本文件

[root@master ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[root@master ~]# chmod +x /usr/local/keepalived/mysql.sh   #给予执行权限
[root@master ~]# /etc/init.d/keepalived start          #启动keepalived   start启动、 stop停止、restart重启

注：登录两台mysql数据库授权两台Mysql服务器允许root远程登录，用于在其他服务器登陆测试！

mysql> grant all on *.* to ‘root‘@‘23.20.4.%‘ identified by ‘zhuoshi‘;
mysql> flush privileges;

**注：以下为测试停掉主节点的 /etc/init.d/keepalived stop查看漂移IP是否能正常切换过去

[root@master ~]# /etc/init.d/keepalived/  stop  #停掉keepalived
[root@backup ~]# ip addr  #查看漂移IP是否正常切换

三、Mysql

1、安装完mysql数据库后，连接mysql数据库

[root@master ~]# mysql -uroot -pzhuoshi   # 进入mysql数据库

2、创建bigdata数据库

mysql >CREATE database bigdata DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
mysql>create database bigdata DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
#以上两条命令都为创建bigdata库，头一条创建bigdata不行，就用第二条！！！！

mysql>show database             
   #查看创建bigdata数据库是否成功                             
    #exit退出mysql数据库

3、将数据库脚本bigdata.sql文件拷贝到root目录下
1.1、执行导入命令

[root@master ~]#mysql -uroot -pzhuoshi --default-character-set=utf8 bigdata < /root/bigdata.sql

1.2、连接mysql数据库查看数据是否导入成功，依次执行以下命令，（注：bigdata库里有表即为导入成功）

mysql>[root@master ~]# mysql -uroot -pzhuoshi;
mysql> use bigdata;
mysql>show tables;

码农公寓

二、配置keepalived实现双机热备

三、Mysql

相关文章