运维开发网

MySQL高可用性之Keepalived+Mysql(双主热备)

运维开发网 https://www.qedev.com 2020-11-10 12:33 出处:51CTO 作者:哦姓卞
一、安装Mysql数据库配置主从1.上传yum文件夹到/root/目录下,给予yum.sh运行权限,安装mysql。[root@master~]#chmod+xyum.sh#给予执行权限[root@master~]#./yum.sh[root@master~]#yum-yinstallmysql-servermysqlmysql-devel#安装mysql[root@master~]#servic

一、安装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;

MySQL高可用性之Keepalived+Mysql(双主热备)

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  #查看同步状态

MySQL高可用性之Keepalived+Mysql(双主热备)

注:以下在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');     #在表里插入数据

MySQL高可用性之Keepalived+Mysql(双主热备)

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

myqsl> use test;
mysql> show tables;

MySQL高可用性之Keepalived+Mysql(双主热备)

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

二、配置keepalived实现双机热备

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

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

MySQL高可用性之Keepalived+Mysql(双主热备)

[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高可用性之Keepalived+Mysql(双主热备)

三、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;
0

精彩评论

暂无评论...
验证码 换一张
取 消