「这是我参与2022首次更文挑战的第8天，活动详情查看：2022首次更文挑战」

主主互备

由于是主主互备，那么以下操作在两个主库中都要进行，A既是B的主库，B也是A的主库。

主库配置(my.cnf)

[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock


[mysqld]
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
server_id=2 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复
sync_binlog = 1
expire_logs_days =7   #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
log_slave_updates = 1   #【关键点】从主服务器接收到的更新同时要写入二进制日志

binlog-do-db=master_db #要同步的数据库

binlog-ignore-db = mysql  #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys


#mysql5.7以后的不兼容问题处理
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#允许最大连接数
max_connections=200
net_write_timeout=6000
wait_timeout=6000
net_read_timeout=6000

#服务端使用的字符集默认为8比特编码的latin4字符集
character-set-server=utf8mb4
collation-server = utf8mb4_unicode_ci

#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M

#设置时区
default-time_zone='+8:00'

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid


#
# include all files from the config directory
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/


bind-address = 0.0.0.0

注意：

两个主库配置相同， server_id 不能相同，需要开启 log_slave_updates ，开启 log-bin ，否则在主从备份可能存在问题。

创建授权用户

两个主库都创建用于操作备份的用户

# 创建用户
create user 'slave'@'%' IDENTIFIED BY '123456';
# 授权
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

查看binlog是否开启

show variables like 'log_bin';

查看master状态

show master status;

从库操作

# 先停止同步
stop slave;
# 建立与主库的关联及同步点
CHANGE MASTER TO
MASTER_HOST='192.168.10.212',  #主库地址
MASTER_PORT=4200,              #主库端口
MASTER_USER='slave',           # 访问主库且有同步复制权限的用户
MASTER_PASSWORD='123456',      # 密码
MASTER_LOG_FILE='mysql-bin.000001', #从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_POS=154;  # 从文件中指定位置开始读取，主库show master status返回结果（Position字段）

# 启用slave;
start slave;

# 查看同步状态
show slave status \G;
# 只有Slave_IO_Running，Slave_SQL_Running都为yes的时候才代表同步

当两个库都设置完以后通过 show slave status \G; 确认 Slave_IO_Running，Slave_SQL_Running 都为yes以后说明主主互备已经设置成功。

keepalived

通过 keepalived 可以实现 master 节点故障时自动迁移。

局限性：仅限于 MYSQL 节点都处于同一局域网下。

部署（非LVS方式）

两台服务器中都要部署

# 下载keepalived 安装包 
cd /usr/local/src/
wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
#解压
tar -zxvf keepalived-1.3.5.tar.gz
# 进入解压后的目录
cd keepalived-1.3.5
# 执行configure
./configure --prefix=/usr/local/keepalived
# 编译
make && make install
# 加入系统自启动
cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived          /etc/rc.d/init.d
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/
 echo "/etc/init.d/keepalived start" >> /etc/rc.local

修改 keepalived.conf 配置

! Configuration File for keepalived
#标识全局定义配置块 
global_defs {
 # 发送报警邮件收件地址
  notification_email { 
    xxx@xxx.com
    xxx@xxx.com
  }
  
  notification_email_from xxx@xxx.cn  #指明报警邮件的发送地址
  smtp_server 127.0.0.1    #邮件服务器地址
  smtp_connect_timeout 30   #smtp的超时时间
  router_id MASTER-HA   #物理服务器主机名
}


 #检测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 {
    state MASTER | BACKUP # 当前节点在此虚拟路由器上的初始状态，只能有一个是MASTER,其他的都是BACKUP     
    interface eth0      #绑定为当前虚拟路由器使用的物理接口
    mcast_src_ip 182.148.15.238  #指定VRRP通告的IP头的源地址
    virtual_router_id 51    #路由器标识，MASTER和BACKUP必须是一致的
    priority 101            #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来  
    advert_int 1     # 通告发出间隔，包含主机优先级、心跳登
    # 认证配置
    authentication {    
        auth_type PASS   # 认证类型，PASS表示简单字符串认证
        auth_pass 1111   # 密码,PASS密码最长为8位
    }
    # 标识VRRP VIP定义块
    virtual_ipaddress {     
        182.148.15.236 #虚拟路由IP地址，以辅助地址方式设置，主从使用相同的虚拟IP
    }
     
track_script {                
   chk_mysql_port              
  }
}

编写 chk_mysql_port 脚本

 vim /opt/chk_mysql.sh
 #!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi
# 脚本：查询3306端口，如果查询不到的话就停止当前服务的keepalived
# 赋权
chmod 755 /opt/chk_mysql.sh


# 如果提示 netstat 未找到
yum install net-tools

启动keepalived 服务

/etc/init.d/keepalived start

注意点

多个 keepalived 中只能有一个为 MASTER ，其余为 BACKUP
priority 需要设置不同的优先级
服务停止后重启的话需要重启服务跟keepalived

错误记录

[root@localhost keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking whether make supports nested variables... (cached) yes
checking for pkg-config... /usr/bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
checking for gcc... no
checking for cc... no
checking for cl.exe... no
configure: error: in `/usr/local/src/keepalived-1.3.5':
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details


解决。安装gcc yum install gcc


configure: error:
  !!! OpenSSL is not properly installed on your system. !!!
  !!! Can not include OpenSSL headers files.            !!!
 解决：yum install -y openssl-devel
 
 *** WARNING - this build will not support IPVS with IPv6. Please install libnl/libnl-3 dev libraries to support IPv6 with IPVS.
 解决：yum -y install libnl libnl-devel

MySQL双机热备