MySQL主主复制-虚机实践

一、概述

keepalived采用arp广播模式,无法跨网段虚拟IP。所以只能在同一网段,并且所有Master的端口和账号密码要保持一致,否则使用VIP的时候,还需要修改账号密码。

方案:
  • 两台mysql都可读写,互为主备,默认只使用一台负责写入,另一台备用;

  • 两台主服务器之间可用keepalived做高可用;

  • 可多个从服务器与备用机主从同步。

架构:
IP角色软件
172.16.227.129Master1Mysql5.7.35、keepalived-2.2.4
172.16.227.130Master2Mysql5.7.35、keepalived-2.2.4
172.16.227.131VIP
软件:

Centos7.6

Mysql5.7

keepalived

二、搭建主主架构

1.配置2台Mysql

详见:《CentOS安装Mysql 5.7步骤_root.md》

0.卸载MariaDB
rpm -qa | grep Maria*
yum -y remove mari*
# 查看遗留文件(卸载以后就没了)
rm /etc/my.cnf
# 清除文件
rm -rf /var/lib/mysql/*
1.配置Mysql
groupadd mysql
useradd -r -g mysql mysql
cd /root/software
tar zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local
mv mysql-5.7.35-linux-glibc2.12-x86_64 mysql
cd /usr/local/mysql
# 创建data目录,作为数据库存储位置
mkdir /usr/local/mysql/data
# 修改目录所属
cd /usr/local
chown -R mysql.mysql mysql
vi /etc/my.cnf
# 添加
[mysqld]
port=60000
socket=/tmp/mysql.sock 
init_connect='SET collation_connection = utf8_general_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_general_ci 
skip-character-set-client-handshake
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2.安装依赖
  • 安装依赖

    yum install -y libaio numactl
    
3.初始化Mysql

如果初始化失败,则删除datadir中的数据。

# 注意参数顺序
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --initialize
# 记录初始密码
4.启动停止

本章只测试主主,注册服务等,都省略,详见:《CentOS安装Mysql 5.7步骤_root.md》

cd /usr/local/mysql
ps aux | grep mysql
# 启动
bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
# 停止
bin/mysqladmin -u root -p shutdown
5.修改密码
/usr/local/mysql/bin/mysql -uroot -p
> alter user 'root'@'localhost' identified by '<password>';
# 添加远程访问权限
> use mysql;
> grant all privileges on *.* to root@'%' identified by '<password>';
> flush privileges;
6.验证端口通讯
# Master1节点
telnet 172.16.227.130 60000
# Master2节点
telnet 172.16.227.129 60000

###############################
# CentOS6.5查看防火墙的状态:
service iptable status
# CentOS 6.5 临时关闭防火墙
servcie iptables stop
# 永久关闭防火墙
chkconfig iptables off

# CentOS 7查看防火墙的状态:
firewall-cmd --state
# 停止firewall
systemctl stop firewalld.service
# 禁止firewall开机启动
systemctl disable firewalld.service
# 开启端口
firewall-cmd --zone=public --add-port=80/tcp --permanent
#重启 firewall
firewall-cmd --reload
# 命令含义:
–zone # 作用域
–add-port=80/tcp # 添加端口,格式为:端口/通讯协议
–permanent # 永久生效,没有此参数重启后失效
2.配置主主复制
  • 1、Master1 配置:

    vi /etc/my.cnf
    port=60000
    # server唯一标识符
    server-id=1
    # 自增ID起始值
    auto_increment_offset=1
    # 步长(保持主键是奇数)
    auto_increment_increment=2
    # 开启二进制功能,主服务器必须打开
    log-bin=mysql-bin
    # binlog单文件最大值
    max_binlog_size=1024M
    # 忽略不同步主从数据库
    replicate-ignore-db=mysql
    replicate-ignore-db=informaton_schema
    replicate-ignore-db=performance_schema
    replicate-ignore-db=test
    
  • 2、Master2配置:

    vi /etc/my.cnf
    port=60000
    # server唯一标识符
    server-id=2
    # 自增ID起始值
    auto_increment_offset=2
    # 步长(保持主键是偶数)
    auto_increment_increment=2
    # 开启二进制功能,主服务器必须打开(这里是主主架构)
    log-bin=mysql-bin
    # binlog单文件最大值
    max_binlog_size=1024M
    # 忽略不同步主从数据库
    replicate-ignore-db=mysql
    replicate-ignore-db=informaton_schema
    replicate-ignore-db=performance_schema
    replicate-ignore-db=test
    
  • 3、Mysql重启:

    cd /usr/local/mysql
    # 停止
    bin/mysqladmin -u root -p shutdown
    # 启动
    bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
    
  • 4、主库创建同步用户:(Master1和Master2都创建)

    /usr/local/mysql/bin/mysql -uroot -p
    mysql> CREATE USER 'sync_dba'@'%' IDENTIFIED BY '<password>';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync_dba'@'%' IDENTIFIED BY '<password>';
    mysql> FLUSH PRIVILEGES;
    
  • 5、在主库查看主库信息

    mysql> show master status;
    
  • 5、在从库指定主库信息

    # SUPER privilege(s) for this operation
    # /usr/local/mysql/bin/mysql -uroot -p
    # 在Master1上配置
    mysql> change master to master_host='172.16.227.130',master_port=60000,master_user='sync_dba',master_password='<password>',master_log_file='mysql-bin.000001',master_log_pos=857;
    
    # 在Master2上配置
    mysql> change master to 
    master_host='172.16.227.129',master_port=60000,master_user='sync_dba',master_password='<password>',master_log_file='mysql-bin.000001',master_log_pos=857;
    
    mysql> start slave;
    mysql> show slave status\G;
    # 查看主从复制是否配置成功
    Slave_IO_Running: Yes # 从库与主库IO通信 : 正常运行
    Slave_SQL_Running: Yes # 从库进程 : 正常运行
    

主主架构已经配置完成,都可读写,互为主备。

三、安装Keepalived

keepalived 的核心就是将IPVS配置成高可用,生成ipvs规则来完成负载均衡效果。

1.安装Keepalived
  • 1、安装 Keepalived 依赖

    yum install -y openssl openssl-devel gcc
    
  • 2、编译安装

    cd /root/software
    tar zxvf keepalived-2.2.4.tar.gz
    cd keepalived-2.2.4
    ./configure --prefix=/usr/local/keepalived
    make && make install
    
    # 拷贝相关目录和文件的到系统目录
    mkdir -p /etc/keepalived/
    # 编译生成文件
    cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
    cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
    cp /usr/local/keepalived/sbin/keepalived /usr/local/sbin/
    
  • 3、自启动

    # 复制启动脚本(etc/rc.d/init.d包含各种软件脚本,系统从初始化的时候有选择的运行init.d里的脚本)
    cp /root/software/keepalived-2.2.4/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
    # 将启动脚本写入系统启动
    echo "/etc/init.d/keepalived start" >> /etc/rc.local
    
2.配置Keepalived
1.配置说明

keepalived 配置文件 keepalived.conf,主要包括以下几个配置区域:

global_defs:配置故障发生时的通知对象以及机器标识。

static_ipaddress:本节点的IP。(一般情况下机器都会有IP地址和路由信息的,因此没必要再配置。)

static_routes:本节点的路由。(一般情况下机器都会有IP地址和路由信息的,因此没必要再配置。)

vrrp_script:用来做健康检查的,当时检查失败时会将vrrp_instancepriority减少相应的值。

vrrp_instance:用来定义对外提供服务的VIP区域及其相关属性。

virtual_server:用来定义vrrp_intance组,使得这个组内成员动作一致。

# 清空ggdG
echo > /etc/keepalived/keepalived.conf
vi /etc/keepalived/keepalived.conf
####################################################
! Configuration File for keepalived
global_defs {
	notification_email {# 指定keepalived在发生切换时需要发送email到的对象,一行一个
    keepalived@mysql.com
  }
  notification_email_from keepalived@mysql.com # 指定发件人
  smtp_server 127.0.0.1 # 指定smtp服务器地址
  smtp_connect_timeout 30 # 指定smtp连接超时时间
  router_id mysql_ha # 运行keepalived机器的一个标识
  
  vrrp_skip_check_adv_addr
   #vrrp_strict # 将严格遵守vrrp协议这一项关闭,否则会因为不是组播而无法启动keepalived
   vrrp_iptables
   vrrp_garp_interval 0
   vrrp_gna_interval 0
   
}

# 检测mysql服务是否在运行。有很多方式,比如进程,用脚本检测等等
vrrp_script chk_mysql_port { 
	script "/opt/chk_mysql.sh" # 通过脚本监测
	interval 2 # 脚本执行间隔,每2s检测一次
	weight -5 # 脚本结果导致的优先级变更,检测失败(脚本返回非0)则优先级 -5
	fall 2 # 检测连续2次失败才算确定是真失败。会用weight减少优先级(1-255之间)
	rise 1 # 检测1次成功就算成功。但不修改优先级
}

vrrp_instance VI_1 {
  ######## 节点1 ########
  state BACKUP
  interface ens33 # 指定自己的网卡接口
  mcast_src_ip 172.16.227.129 # 指定自己的IP
  virtual_router_id 51 # 路由器标识,MASTER和BACKUP必须是一致的
  priority 101 # 优先级
  # advert_int 1 # 组播信息发送间隔时间。两个节点必须一样
  nopreempt # 不抢占资源

  ######## 节点2 ########
  state BACKUP
  interface ens33 # 指定自己的网卡接口
  mcast_src_ip 172.16.227.130 # 指定自己的IP
  virtual_router_id 51 # 路由器标识,MASTER和BACKUP必须是一致的
  priority 100 # 优先级
  # advert_int 1 # 组播信息发送间隔时间。两个节点必须一样
  nopreempt # 不抢占资源
  
  authentication {
    auth_type PASS
    auth_pass 1111
  }

  virtual_ipaddress {
    172.16.227.131  # VIP
  }

	track_script {
  	chk_mysql_port
  }
}
2.配置keepaived.conf

非抢占模式说明:

当 Master 宕机后,Backup 接管服务。当Master恢复后,VIP重新漂移到Master上,避免VIP切换造成的延迟。当原先的 Master 恢复后,状态变为 Backup,不接管服务,这是非抢占模式。

非抢占模式配置:

1、两个节点的state都必须配置为BACKUP;

2、两个节点都必须加上配置 nopreempt;

3、其中一个节点的优先级必须要高于另外一个节点的优先级。(根据优先级选举一个Master)

# 清空ggdG
echo > /etc/keepalived/keepalived.conf
vi /etc/keepalived/keepalived.conf
####################################################
! Configuration File for keepalived
global_defs {
	notification_email {
    keepalived@mysql.com
  }
  notification_email_from keepalived@mysql.com
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id mysql_ha
}

vrrp_script chk_mysql_port { 
	script "/opt/chk_mysql.sh"
	interval 2
	weight -5
	fall 2
	rise 1
}

vrrp_instance VI_1 {
  # 节点1和节点2非抢占模式都设置为 BACKUP
  state BACKUP 
  # 设置当前节点的网卡接口ifconfig
  interface ens33 
  # 设置当前节点的IP(节点1 : 172.16.227.129,节点2 : 172.16.227.130)
  mcast_src_ip 172.16.227.129
  # 设置优先级(节点1: 101 , 节点2 : 100,根据优先级选举一个Master)
  priority 101
  virtual_router_id 51
  advert_int 1
  nopreempt
  authentication {
    auth_type PASS
    auth_pass milk_chocolate
  }

  virtual_ipaddress {
    172.16.227.131
  }

	track_script {
  	chk_mysql_port
  }
}
3.监测Mysql

KeepAlived使用脚本做心跳检测,如果Master的MySQL挂了,则会将Keepalived停止。BACKUP的KeepAlived通过心跳检测发现这个情况,就会将VIP的请求接管。

vi /opt/chk_mysql.sh
################### Mysql监测脚本 ###################
# wc -l : 显示行数
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "60000"|wc -l)
if [ "${counter}" -eq 0 ]; then
/etc/init.d/keepalived stop
fi
################### Mysql监测脚本 ###################
# 给脚本添加执行权限
chmod 755 /opt/chk_mysql.sh
4.重启测试
  • 重启

    systemctl start keepalived
    systemctl status keepalived
    systemctl stop keepalived
    systemctl restart keepalived
    
  • 测试

    # 测试1 : 服务都启动
    # 查看网卡上虚拟IP
    ip addr show ens33
    # Master1
    [root@localhost keepalived-2.2.4]# ip addr show ens33
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
        link/ether 00:0c:29:cf:db:9d brd ff:ff:ff:ff:ff:ff
        inet 172.16.227.129/24 brd 172.16.227.255 scope global dynamic ens33
           valid_lft 1364sec preferred_lft 1364sec
        inet 172.16.227.131/32 scope global ens33    # VIP在Master1:因为竞选Master优先级高
           valid_lft forever preferred_lft forever
        inet6 fe80::1483:12b2:5fc0:f209/64 scope link
           valid_lft forever preferred_lft forever
           
    # Master2
    [root@localhost keepalived-2.2.4]# ip addr show ens33
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
        link/ether 00:0c:29:f0:00:f2 brd ff:ff:ff:ff:ff:ff
        inet 172.16.227.130/24 brd 172.16.227.255 scope global dynamic ens33
           valid_lft 1519sec preferred_lft 1519sec
        inet6 fe80::f5f8:feaf:d8cc:6190/64 scope link
           valid_lft forever preferred_lft forever
    
    # Navicat 连接VIP : 172.16.227.131
    # 插入数据自增主键为奇数
    #############################################################
    # 测试2 : 停掉Master1节点Mysql服务
    # 查看keepalived状态
    systemctl status keepalived
    Master1 上 keepalived 挂掉。
    # Master1 
    [root@localhost mysql]# ip addr show ens33
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
        link/ether 00:0c:29:cf:db:9d brd ff:ff:ff:ff:ff:ff
        inet 172.16.227.129/24 brd 172.16.227.255 scope global dynamic ens33
           valid_lft 1492sec preferred_lft 1492sec
        inet6 fe80::1483:12b2:5fc0:f209/64 scope link
           valid_lft forever preferred_lft forever
           
    # Master2
    [root@localhost mysql]# ip addr show ens33
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
        link/ether 00:0c:29:f0:00:f2 brd ff:ff:ff:ff:ff:ff
        inet 172.16.227.130/24 brd 172.16.227.255 scope global dynamic ens33
           valid_lft 1706sec preferred_lft 1706sec
        inet 172.16.227.131/32 scope global ens33 # VIP转移到Master2 
           valid_lft forever preferred_lft forever
        inet6 fe80::f5f8:feaf:d8cc:6190/64 scope link
           valid_lft forever preferred_lft forever
    
    # Navicat 连接VIP : 172.16.227.131   无感知
    # 插入数据自增主键为偶数
    #############################################################
    # 测试3 :重新启动Master1上Mysql,keepalived
    Master1上的Mysql数据同步
    
    抢占模式:
    VIP重新漂移到Master1节点上。
    
    非抢占模式:
    VIP保持不动,在Master2节点上。
    
7.查看日志
# 默认日志路径/var/log/messages
tail -500f /var/log/messages
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码上富贵

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值