MySQL 双主复制+keepalived高可用架构

m0_72093175

已于 2024-05-06 09:15:03 修改

阅读量974

点赞数 7

文章标签：架构

于 2024-05-03 16:04:12 首次发布

本文链接：https://blog.csdn.net/m0_72093175/article/details/138387183

版权

（一）、架构介绍：

mysql双主互备加上keepalived，在其中一台机器上绑定虚拟IP（vip）。利用VIP统一对外服务有，可以避免在两个节点同时写数据造成冲突。同时，当keepalived主节点发生故障时，利用keepalived自带的服务监控功能和自定义脚本实现mysql故障自动切换，并将vip切换到备节点，实现高可用。mysql双主复制+keepalived架构是一种简单、便捷高可用方案，使用于各种业务。

（二）、环境说明

在OracleLinux 7.9环境上部署MySQL 5.7.37版本部署互为主备的高可用的双主架构，实现方法（GTID+半同步复制+keepalived）

主机	ip	Linux version	Mysql version
master1	192.168.152.146	OracleLinux-R7-U9-Server-x86_64-dvd	mysql-5.7.37
master2	192.168.152.147	OracleLinux-R7-U9-Server-x86_64-dvd	mysql-5.7.37

（三）、mysql 数据库安装

master1和master2安装mysql方法如下：

3.1、修改主机名

# hostnamectl status
# hostnamectl set-hostname master1	#主机名自定义
# su -

执行后如下：
[root@localhost ~]# hostnamectl status
   Static hostname: localhost.localdomain
         Icon name: computer-vm
           Chassis: vm
        Machine ID: 9a6db65f25694629a76ec6f53815eddd
           Boot ID: 6d438fcd30a2467f9d56ef5e617dc4d0
    Virtualization: vmware
  Operating System: Oracle Linux Server 7.9
       CPE OS Name: cpe:/o:oracle:linux:7:9:server
            Kernel: Linux 5.4.17-2102.201.3.el7uek.x86_64
      Architecture: x86-64
[root@localhost ~]# hostnamectl set-hostname master1
[root@localhost ~]# su -

Last login: Thu May  2 09:11:52 CST 2024 from 192.168.152.1 on pts/0
[root@master1 ~]#

3.2、配置/etc/hosts

[root@master1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.152.146 master1
192.168.152.147 master2

3.3、关闭防火墙

# systemctl stop firewalld.service
# systemctl disable firewalld.service 
# systemctl status firewalld.service 

执行后结果：
[root@master1 ~]# systemctl stop firewalld.service
[root@master1 ~]# systemctl disable firewalld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master1 ~]# systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

May 02 09:06:29 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
May 02 09:06:29 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
May 02 09:06:29 localhost.localdomain firewalld[792]: WARNING: AllowZoneDrifting is enabled. This is considered a...now.
May 02 09:36:49 master1 systemd[1]: Stopping firewalld - dynamic firewall daemon...
May 02 09:36:49 master1 systemd[1]: Stopped firewalld - dynamic firewall daemon.
Hint: Some lines were ellipsized, use -l to show in full.
[root@master1 ~]#

3.4、关闭NetworkManager

# systemctl stop NetworkManager.service
# systemctl disable NetworkManager.service
# systemctl status NetworkManager.service 
执行结果：
[root@master1 ~]# systemctl stop NetworkManager.service
[root@master1 ~]# systemctl disable NetworkManager.service
Removed symlink /etc/systemd/system/multi-user.target.wants/NetworkManager.service.
Removed symlink /etc/systemd/system/dbus-org.freedesktop.nm-dispatcher.service.
Removed symlink /etc/systemd/system/network-online.target.wants/NetworkManager-wait-online.service.
[root@master1 ~]# systemctl status NetworkManager.service
● NetworkManager.service - Network Manager
   Loaded: loaded (/usr/lib/systemd/system/NetworkManager.service; disabled; vendor preset: enabled)
   Active: inactive (dead) since Thu 2024-05-02 09:38:24 CST; 70ms ago
     Docs: man:NetworkManager(8)
 Main PID: 826 (code=exited, status=0/SUCCESS)

May 02 09:06:30 localhost.localdomain NetworkManager[826]: <info>  [1714611990.0860] manager: NetworkManager stat...OCAL
May 02 09:06:30 localhost.localdomain NetworkManager[826]: <info>  [1714611990.0877] manager: NetworkManager stat...SITE
May 02 09:06:30 localhost.localdomain NetworkManager[826]: <info>  [1714611990.0881] policy: set 'ens32' (ens32) ... DNS
May 02 09:06:30 localhost.localdomain NetworkManager[826]: <info>  [1714611990.0929] device (ens32): Activation: ...ted.
May 02 09:06:30 localhost.localdomain NetworkManager[826]: <info>  [1714611990.0950] manager: NetworkManager stat...OBAL
May 02 09:06:30 localhost.localdomain NetworkManager[826]: <info>  [1714611990.0960] manager: startup complete
May 02 09:25:13 master1 NetworkManager[826]: <info>  [1714613113.5079] hostname: hostname changed from "localhos...ter1"
May 02 09:25:13 master1 NetworkManager[826]: <info>  [1714613113.5081] policy: set-hostname: set hostname to 'ma...tion)
May 02 09:38:24 master1 systemd[1]: Stopping Network Manager...
May 02 09:38:24 master1 systemd[1]: Stopped Network Manager.
Hint: Some lines were ellipsized, use -l to show in full.

3.5、关闭Selinux

# sed -i "s/SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config  
# setenforce 0
# cat /etc/selinux/config

执行结果：
[root@master1 ~]# sed -i "s/SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config
[root@master1 ~]# setenforce 0
[root@master1 ~]# cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX=disabled
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

3.6、内核参数设置

##编辑/etc/security/limits.conf 添加如下内容：

vi /etc/security/limits.conf 
mysql	soft	nproc 	2047 
mysql	hard	nproc 	16384
mysql	soft	nofile	1024
mysql	hard	nofile	65536
mysql	soft	stack 	10240

##编辑/etc/pam.d/login 添加如下内容：

vi /etc/pam.d/login 
session    required     pam_limits.so

##编辑vi /etc/profile添加如下内容：

if [ /$USER = "mysql" ]; then
    if [ /$SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
    umask 022
fi

3.7、配置YUM源

# mount  /dev/cdrom  /mnt
vi /etc/yum.repos.d/dvd.repo
[dvd]
name=dvd
baseurl=file:///mnt
enabled=1
gpgcheck=0


配置后执行：
yum clean all
yum repolist

3.8、安装所需的依赖包

# yum -y install gcc* gcc-c++ ncurses* ncurses-devel* cmake* make* perl* bison* libaio-devel* libgcrypt

##安装常用故障与性能诊断工具
yum install -y unzip man screen tigervnc-server iotop dstat openssl make sysstat vsftpd ftp

3.9、mysql用户创建

# groupadd -g 1001 mysql 
# useradd -u 1001 -g mysql mysql 
# echo '123456' | passwd --stdin mysql

3.10、创建必要目录并赋权

# mkdir -p /mysql/app   #创建安装目录
# mkdir -p /mysql/product/{binlog,relaylog,log,undo,redo,data} #创建数据目录
# chown -R mysql:mysql /mysql
# chmod -R 755 /mysql

3.11、安装mysql前卸载系统自带Mariadb库

redhat7.x操作系统默认安装开源数据库MARIADB，在安装MySQL之前，请先卸载该库。
# rpm -qa|grep -i mariadb
# rpm -ql mariadb-libs-5.5.68-1.el7.x86_64
# rpm -qf /usr/lib64/mysql/
# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64		#卸载Mariadb

3.12、上传mysql安装介质并解压


# mkdir /soft
# cd /soft/
# chown -R mysql.mysql /soft
# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /mysql/app --strip-components=1

执行命令如下：
[root@master1 yum.repos.d]# mkdir /soft
[root@master1 yum.repos.d]# cd /soft/
[root@master1 soft]# ll
total 650984
-rw-r--r--. 1 root root 666603757 May  2 10:03 mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@master1 soft]# chown -R mysql.mysql /soft
[root@master1 soft]# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /mysql/app --strip-components=1

3.13、mysql初始化参数设置

必要参数：
server-id = 1
log_bin
binlog_format= ROW

# GTID
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=on
注：
1.必须都开启binlog
2. server-id 两主不能相同
3.自增量根据主机数量确定
主1：
auto-increment-increment= 2：表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。
auto-increment-offset= 1：用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突。

[root@master ~]# vi /etc/my.cnf
[mysqld]
user=mysql
port=3306
basedir=/mysql/app
datadir=/mysql/product/data
symbolic-links=0
open_files_limit = 65535
server_id=1
 
#开启GTID
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=on
 
#会话参数
max_connections=4532
max_user_connections=4000
max_connect_errors=50000
wait_timeout=7200
interactive_timeout=7200
thread_cache_size=256
 
#binlog日志参数
max_binlog_size = 1073741824
log-bin=/mysql/product/binlog/mysql-bin
relay_log=/mysql/product/relaylog/mysql-relay-bin
binlog_cache_size=131072
binlog_format=ROW
expire_logs_days=30
sync_binlog=1

##设置ID自增初始值1，每次增量为2，即都是偶数1,3,5,7...
auto-increment-increment = 2        #每次增长2
auto-increment-offset = 1            #初始值
#慢日志参数
slow_query_log=0
long_query_time=10
log_output=FILE
log_error=/mysql/product/log/myerror.log
slow_query_log_file=/mysql/product/log/mysql-slow.log
general_log = OFF
general_log_file=/mysql/product/log/mysql-general.log
 
#Redo日志参数
innodb_log_group_home_dir=/mysql/product/redo
innodb_log_file_size=1048576000
innodb_log_files_in_group=3
#缓存参数
sort_buffer_size=720896
read_buffer_size=720896
read_rnd_buffer_size=360448
join_buffer_size=360448
max_allowed_packet=1073741824
max_heap_table_size=16777216
tmp_table_size=16777216
#innodb参数
innodb_buffer_pool_size=134217728  #改为内存的50%-80%
innodb_data_file_path=ibdata1:200M:autoextend
innodb_file_per_table=ON
innodb_log_buffer_size=16777216
innodb_flush_method=O_DIRECT
innodb_undo_directory=/mysql/product/undo
innodb_undo_tablespaces=2
innodb_flush_log_at_trx_commit=1
innodb_temp_data_file_path=ibtmp1:200M:autoextend
#事务隔离级别
transaction-isolation=READ-COMMITTED
#字符集
character_set_server=utf8mb4
#查询缓存
query_cache_size=0
query_cache_type=OFF
#设置时区
default_time_zone='+8:00'
log_timestamps=SYSTEM
#大小写不敏感
lower_case_table_names=1
log_bin_trust_function_creators=1
 
 
#多源复制必须的参数
master-info-repository=table # 这个参数是必须的
relay-log-info-repository=table # 这个参数是必须的
relay_log_recovery=1

 
#以下参数根据服务器性能调整
sync_relay_log=1 #默认值是10000
sync_relay_log_info=1 #默认值是10000
sync_master_info = 1 #默认值是10000 



-------------------以下为部分参数解释部分-------------------------------------------

1、master_info_repository有两个值，分别是file和table，该参数决定了slave记录master的状态，如果参数是file，就会创建master.info文件，如果参数值是table，就在mysql中创建slave_master_info的表。
sync_master_info参数决定slave刷新master的状态的方式。并且master_info_repository的参数不同，刷新方式也不同。
1）如果master_info_repository=file,sync_master_info=N，其中N>0，那么slave就会在每N个事件后，使用fdatasync()方式同步到master.info文件中。如果sync_master_info=N，其中N=0，那么MySQL就会把状态信息写入到OS Cache中，需要等待操作系统同步。
2）如果master_info_repository=table,sync_master_info=N,如果N>0，那么slave就会在，每N个事件后，更新mysql.slave_master_info表，如果N=0，那么mysql.slave_master_info表将永远不会更新。
mysql sync_master_info与sync_relay_info参数
本参数用于主从库中配置从库
大于0作用为每个命令之后刷盘
小与0作为为永不刷盘
默认均为1000
刷盘:把数据从内存记录到磁盘中，总所周知linux系统操作文件都是在内存中，会在CPU空闲的时候更新到磁盘中


2、log_bin_trust_function_creators:
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;

3、innodb_flush_log_at_trx_commit和sync_binlog参数详解:
innodb_flush_log_at_trx_commit和sync_binlog是MySQL innodb引擎的两个重要的参数，其中innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer写入到redo log中，sync_binlog是将二进制日志文件刷新到磁盘上。

innodb事务日志redo,binlog逻辑过程如下:
1.事务写入redo log buffer中；
2.将log buffer刷新到redo log中，不过会先写一个TX PREPARE标记；
3.写binlog
4.在redo log中写入TX COMMIT标记；
5.将写binlog成功的标记写入redo log。

参数解析如下： 
innodb_flush_log_at_trx_commit = N： 

N=0    每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
   log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
   
N=1    每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上； 
    当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。

N=2    每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度； 
    当取值为 2 时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。 
上面说到的「最后 1s」并不是绝对的，有的时候会丢失 更多数据。有时候由于调度的问题，每秒刷写（once-per-second flushing）并不能保证 100% 执行。对于一些数据一致性和完整性要求不高的应用，配置为 2 就足够了；如果为了最高性能，可以设置为 0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为 1. 
      当我们设置为2 的时候，Log Thread 会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log Thread 的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。所以，当设置为2 的时候，MySQL Crash 并不会造成数据的丢失，但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样，大家可以自行参阅相关的手册。 
     
sync_binlog =  N： 

N>0    每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上； 
N=0    不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定； 

推荐配置组合： 

N=1,1  — 适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统； 
N=1,0  — 适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制； 
N=2,0或2,m(0<m<100)  — 适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受； 
N=0,0  — 磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务； 
  当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。 

数据安全性
 
当innodb_flush_log_at_trx_commit和sync_binlog  都为1时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，都为1会导致频繁的IO操作，因此该模式也是最慢的一种方式。 
当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。 
当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。 

双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，推荐的做法是innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

4、innodb_flush_method
https://blog.csdn.net/h106140873/article/details/125701485

5、symbolic-links

3.14、初始化MySQL5.7

# cd /mysql/app
# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize 
# [root@master1 product]# grep 'temporary password' /mysql/product/log/myerror.log
2024-05-02T10:30:07.432151+08:00 1 [Note] A temporary password is generated for root@localhost: gmgeWw+Uk6Hv
获取数据库初始登录临时密码：qdcf_56>k9L3

3.15、配置数据库服务

# cp /mysql/app/support-files/mysql.server /etc/init.d/mysqld
# vi /etc/init.d/mysqld
basedir=/mysql/app  #与上面创建的目录一致
datadir=/mysql/product/data #与上面创建的目录一致

3.16、添加环境变量

# vi /root/.bash_profile
export PATH=/mysql/app/bin:$PATH
# source /root/.bash_profile

3.17、启停MySQL

# service mysqld start     //启动mysql
# service mysqld status    //查看启动状态
# service mysqld stop      //停止mysql   ----做下面的操作不要停库

3.18、修改root用户密码

# mysqladmin -uroot -p password "123456"
Enter password:gmgeWw+Uk6Hv
# mysql -uroot -p123456

3.19、设置远程登录

mysql> create user root@'%' identified by '123456';
mysql> grant all privileges on *.* to root@'%';

（四）半同步复制配置

master1和master2节点配置：

4.1、添加半同步复制plugin：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
9 rows in set (0.00 sec)


mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+    |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
46 rows in set (0.00 sec)

mysql>

4.2、添加半同步配置参数

rpl_semi_sync_master_enabled=1  #开启半同步
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=1000       #设置超时时间

4.3、重启mysql

[root@master1 product]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@master1 product]# service mysqld start
Starting MySQL. SUCCESS!

查看：
mysql>  show global variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 1000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

（五）、配置双主复制

5.1、创建复制用户

master1创建：

create user 'repl'@'192.168.152.147' identified by '123456';
grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.152.147';
flush privileges;

master2创建：

create user 'repl'@'192.168.152.146' identified by '123456';
grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.152.146';
flush privileges;

5.2、配置复制链接

master1：

change master to master_host='192.168.152.146',master_user='repl',master_password='123456',master_port=3306,master_auto_position = 1;

master2：

change master to master_host='192.168.152.147',master_user='repl',master_password='123456',master_port=3306,master_auto_position = 1;

主1，主2启动复制，并查看

start slave;
show slave status\G;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……


确认Slave_IO_Running和Slave_SQL_Running均为yes，则复制正常。

（六）、keepalived安装与配置

6.1、使用yum安装方式，安装keepalived

#rpm -qa|grep keepalived
#yum -y install keepalived

6.2、在master1和master2上配置mysql.sh脚本

并赋权 a+x 执行权限，脚本命令是mysql服务器出现异常，通过强制杀死keepalived 进程进行切换操作。

#vi /etc/keepalived/mysql.sh

#!/bin/bash
killall keepalived

#chmod a+x /etc/keepalived/mysql.sh

命令结果如下：
[root@master1 yum.repos.d]# vi /etc/keepalived/mysql.sh
[root@master1 yum.repos.d]# chmod a+x /etc/keepalived/mysql.sh
[root@master1 yum.repos.d]# cat /etc/keepalived/mysql.sh
#!/bin/bash
killall keepalived

6.3、分别在master1和master2上修改keepalived配置文件

Master2 主机的 keepalived.conf 文件配置与 master1 基本相同，只是router_id， priority， real_server 三处不同，其他配置都相同

[root@master1 yum.repos.d]# cd /etc/keepalived/
[root@master1 keepalived]# ll
total 8
-rw-r--r-- 1 root root 3598 Oct  1  2020 keepalived.conf
-rwxr-xr-x 1 root root   31 May  3 14:03 mysql.sh
[root@master1 keepalived]# cp keepalived.conf keepalived.conf.bak
[root@master1 keepalived]# echo > keepalived.conf
[root@master1 keepalived]# vi keepalived.conf

master1参数设置如下：
! Configuration File for keepalived
 
global_defs {

   router_id MYSQL-1  #表示运行 Keepalived 服务器的一个标识，是发邮件时显示在邮件主题中的信息
}
 
vrrp_instance VI_1 {
    state BACKUP  #指定keepalived的角色,两台配置此处均是BACKUP
    interface ens32  #
	
    #同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。
    #如server-1、server-2为一组，virtual_router_id=51
    #server-3、server-4为一组，则virtual_router_id不能为51
    virtual_router_id 51 
	
    priority 100 #master1设置100，master2设置90。字数越大，优先级越高，将根据优先级决定主或从
    advert_int 3 #用于设定 MASTER1 与 master2 主机之间同步检查的时间间隔，单位是秒。
	nopreempt	#不抢占，即允许一个 priority 比较低的节点作为 master，即使有 priority 更高的节点启动。设置 nopreempt 可以实现主节点故障恢复后不再切回到主节点，让服务一直在备用节点工作，直到备用节点出现故障才会进行切换。
				#在使用不抢占时，只能在 state 状态为 BACKUP 的节点上设置，而且这个节点的优先级必须高于其他节点。
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
       192.168.152.150  #该ip为虚拟出来的vip地址
    }
}

virtual_server 192.168.152.150 3306 { 		#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
    delay_loop 2  							#设置健康检查的时间间隔，单位是秒。
    persistence_timeout 50					#有了这个会话保持功能，用户的请求会一直分发到某个服务节点，直到超过这个会话的保持时间
    protocol TCP							#指定转发协议类型，有 TCP 和 UDP 两种可选。
    real_server 192.168.152.146 3306 {      #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
	    weight 3 							#配置服务节点的权值，权值大小用数字表示，数字越大，权值越高，设置权值大小为了区分不同性能的服务器
        notify_down /etc/keepalived/mysql.sh #当mysql服务down时，执行此脚本，杀死keepalived实现切换
        TCP_CHECK {
            connect_timeout 3		#连接超时
            nb_get_retry 3          #重试次数
            delay_before_retry 3	#重试时间间隔
        }
    }
}


master2参数设置如下：
! Configuration File for keepalived
 
global_defs {

   router_id MYSQL-2  #表示运行 Keepalived 服务器的一个标识，是发邮件时显示在邮件主题中的信息
}
 
vrrp_instance VI_1 {
    state BACKUP  #指定keepalived的角色,两台配置此处均是BACKUP
    interface ens32  #
	
    #同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。
    #如server-1、server-2为一组，virtual_router_id=51
    #server-3、server-4为一组，则virtual_router_id不能为51
    virtual_router_id 51 
	
    priority 90 #master1设置100，master2设置90。字数越大，优先级越高，将根据优先级决定主或从
    advert_int 3 #用于设定 MASTER1 与 master2 主机之间同步检查的时间间隔，单位是秒。
	nopreempt	#不抢占，即允许一个 priority 比较低的节点作为 master，即使有 priority 更高的节点启动。设置 nopreempt 可以实现主节点故障恢复后不再切回到主节点，让服务一直在备用节点工作，直到备用节点出现故障才会进行切换。
				#在使用不抢占时，只能在 state 状态为 BACKUP 的节点上设置，而且这个节点的优先级必须高于其他节点。
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
       192.168.152.150  #该ip为虚拟出来的vip地址
    }
}

virtual_server 192.168.152.150 3306 { 		#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
    delay_loop 2  							#设置健康检查的时间间隔，单位是秒。
    persistence_timeout 50					#有了这个会话保持功能，用户的请求会一直分发到某个服务节点，直到超过这个会话的保持时间
    protocol TCP							#指定转发协议类型，有 TCP 和 UDP 两种可选。
    real_server 192.168.152.147 3306 {      #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
	    weight 3 							#配置服务节点的权值，权值大小用数字表示，数字越大，权值越高，设置权值大小为了区分不同性能的服务器
        notify_down /etc/keepalived/mysql.sh #当mysql服务down时，执行此脚本，杀死keepalived实现切换
        TCP_CHECK {
            connect_timeout 3		#连接超时
            nb_get_retry 3          #重试次数
            delay_before_retry 3	#重试时间间隔
        }
    }
}

6.4、启动2台机子上keepalived进程

#启动和配置开机启动
# systemctl enable keepalived
# systemctl start keepalived
# systemctl status keepalived



[root@master1 init.d]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2024-05-03 15:17:36 CST; 8s ago
  Process: 9471 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 9472 (keepalived)
    Tasks: 3
   CGroup: /system.slice/keepalived.service
           ├─9472 /usr/sbin/keepalived -D
           ├─9473 /usr/sbin/keepalived -D
           └─9474 /usr/sbin/keepalived -D

May 03 15:17:36 master1 Keepalived_vrrp[9474]: Using LinkWatch kernel netlink reflector...
May 03 15:17:36 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) Entering BACKUP STATE
May 03 15:17:36 master1 Keepalived_vrrp[9474]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: Opening file '/etc/keepalived/keepalived.conf'.
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: WARNING - default user 'keepalived_script' for script execution does not exist - please create.
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: Unknown keyword 'nb_get_retry'
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: SECURITY VIOLATION - check scripts are being executed but script_security not enabled.
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: IPVS (cmd 1154, errno 2): Scheduler or persistence engine not found
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: IPVS (cmd 1159, errno 2): No such file or directory
May 03 15:17:36 master1 Keepalived_healthcheckers[9473]: Activating healthchecker for service [192.168.152.150]:3306


查看进程：
[root@master1 ~]# ps -ef|grep keepalived
root      9472     1  0 15:17 ?        00:00:00 /usr/sbin/keepalived -D
root      9473  9472  0 15:17 ?        00:00:00 /usr/sbin/keepalived -D
root      9474  9472  0 15:17 ?        00:00:00 /usr/sbin/keepalived -D
root      9696  9677  0 15:34 pts/2    00:00:00 grep --color=auto keepalived

查看启动日志的输出结果，master1的优先级高，keepalived为master，并且发送了一个广播协议
May  3 15:17:36 master1 Keepalived_healthcheckers[9473]: Activating healthchecker for service [192.168.152.150]:3306
May  3 15:17:46 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) Transition to MASTER STATE
May  3 15:17:49 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) Entering MASTER STATE
May  3 15:17:49 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) setting protocol VIPs.
May  3 15:17:49 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:49 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens32 for 192.168.152.150
May  3 15:17:49 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:49 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:49 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:49 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:54 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:54 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens32 for 192.168.152.150
May  3 15:17:54 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:54 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:54 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150
May  3 15:17:54 master1 Keepalived_vrrp[9474]: Sending gratuitous ARP on ens32 for 192.168.152.150

查看vip；
[root@master1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:54:dc:34 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.146/24 brd 192.168.152.255 scope global ens32
       valid_lft forever preferred_lft forever
    inet 192.168.152.150/32 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe54:dc34/64 scope link
       valid_lft forever preferred_lft forever

[root@master1 ~]# tail -1000f /var/log/messages

（七）、vip测试

在另外一台机子上登陆测试，发现可以通过vip 192.168.152.150正常连接

[root@master ~]# mysql -uroot -p123456 -h192.168.152.150
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 821
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.37, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          821
Current database:
Current user:           root@192.168.152.140
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.37-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             192.168.152.150 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 day 1 hour 42 min 1 sec

Threads: 6  Questions: 776  Slow queries: 0  Opens: 345  Flush tables: 1  Open tables: 339  Queries per second avg: 0.008
--------------

mysql>

（八）、模拟主库故障宕机切换，master1主库关闭，cat /var/log/messages查看日志输出，显示vip移走，并且停掉keepalived服务

停止mysql服务：
[root@master1 log]# service mysqld stop
Shutting down MySQL............ SUCCESS!

vip漂移到master2：
[root@master1 log]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:54:dc:34 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.146/24 brd 192.168.152.255 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe54:dc34/64 scope link
       valid_lft forever preferred_lft forever
[root@master2 keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:53:5b:37 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.147/24 brd 192.168.152.255 scope global ens32
       valid_lft forever preferred_lft forever
    inet 192.168.152.150/32 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe53:5b37/64 scope link
       valid_lft forever preferred_lft forever

查看日志输出信息：
[root@master1 ~]# cat  /var/log/messages

May  3 15:46:39 master1 Keepalived_healthcheckers[9473]: TCP connection to [192.168.152.146]:3306 failed.
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: TCP connection to [192.168.152.146]:3306 failed.
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: Check on service [192.168.152.146]:3306 failed after 1 retry.
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: Removing service [192.168.152.146]:3306 from VS [192.168.152.150]:3306
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: IPVS (cmd 1160, errno 2): No such destination
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: Executing [/etc/keepalived/mysql.sh] for service [192.168.152.146]:3306 in VS [192.168.152.150]:3306
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: Lost quorum 1-0=1 > 0 for VS [192.168.152.150]:3306
May  3 15:46:42 master1 Keepalived[9472]: Stopping
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: IPVS (cmd 1156, errno 2): No such file or directory
May  3 15:46:42 master1 Keepalived_healthcheckers[9473]: Stopped
May  3 15:46:42 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) sent 0 priority
May  3 15:46:42 master1 Keepalived_vrrp[9474]: VRRP_Instance(VI_1) removing protocol VIPs.
May  3 15:46:43 master1 Keepalived_vrrp[9474]: Stopped
May  3 15:46:43 master1 Keepalived[9472]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
May  3 15:46:49 master1 systemd-logind: Removed session 1.

keepalived被杀死：
[root@master1 log]# ps -ef|grep keepalived
root      9905  7056  0 15:50 pts/1    00:00:00 grep --color=auto keepalived

再次启动mysql，master1也不会抢占资源将主节点切回到master1：
[root@master1 log]# service mysqld start
Starting MySQL. SUCCESS!
[root@master1 log]# systemctl start keepalived
[root@master1 log]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2024-05-03 15:52:13 CST; 6s ago
  Process: 10894 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 10895 (keepalived)
    Tasks: 3
   CGroup: /system.slice/keepalived.service
           ├─10895 /usr/sbin/keepalived -D
           ├─10896 /usr/sbin/keepalived -D
           └─10897 /usr/sbin/keepalived -D

May 03 15:52:13 master1 Keepalived_vrrp[10897]: Registering Kernel netlink command channel
May 03 15:52:13 master1 Keepalived_vrrp[10897]: Registering gratuitous ARP shared channel
May 03 15:52:13 master1 Keepalived_vrrp[10897]: Opening file '/etc/keepalived/keepalived.conf'.
May 03 15:52:13 master1 Keepalived_vrrp[10897]: VRRP_Instance(VI_1) removing protocol VIPs.
May 03 15:52:13 master1 Keepalived_vrrp[10897]: Using LinkWatch kernel netlink reflector...
May 03 15:52:13 master1 Keepalived_vrrp[10897]: VRRP_Instance(VI_1) Entering BACKUP STATE
May 03 15:52:13 master1 Keepalived_vrrp[10897]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
May 03 15:52:13 master1 Keepalived_healthcheckers[10896]: IPVS (cmd 1154, errno 2): Scheduler or persistence engine not found
May 03 15:52:13 master1 Keepalived_healthcheckers[10896]: IPVS (cmd 1159, errno 2): No such file or directory
May 03 15:52:13 master1 Keepalived_healthcheckers[10896]: Activating healthchecker for service [192.168.152.150]:3306

[root@master1 log]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:54:dc:34 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.146/24 brd 192.168.152.255 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe54:dc34/64 scope link
       valid_lft forever preferred_lft forever

也可以通过uuid，确定具体连接到那一台库：

[root@master ~]# mysql -uroot -p123456 -h 192.168.152.150
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1133
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like 'uuid';
Empty set (0.01 sec)

mysql>  show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 6b514603-0831-11ef-a943-000c29535b37 |
+---------------+--------------------------------------+
1 row in set (0.01 sec)

参考文献：

keepalived是通过高可用vrrp,竞选机制实现，

mysql实现是通过配置keepalived优先级，优先级高的为主吗master，优先级低的为辅backup

https://blog.csdn.net/huxiang19851114/article/details/113846059

https://blog.csdn.net/nasen512/article/details/126973119

https://blog.csdn.net/qq_37067752/article/details/120054140

https://baijiahao.baidu.com/s?id=1761063519243311104&wfr=spider&for=pc

https://blog.csdn.net/chengxuyuanyonghu/article/details/56498706

下面重点参考：

https://blog.csdn.net/a360284634/article/details/89892028

https://blog.csdn.net/D1179869625/article/details/126198495

https://blog.csdn.net/jxjdhdnd/article/details/136297338

m0_72093175

关注

7
点赞
踩
19

收藏

觉得还不错? 一键收藏
2
评论
MySQL 双主复制+keepalived高可用架构

mysql双主互备加上keepalived，在其中一台机器上绑定虚拟IP（vip）。利用VIP统一对外服务有，可以避免在两个节点同时写数据造成冲突。同时，当keepalived主节点发生故障时，利用keepalived自带的服务监控功能和自定义脚本实现mysql故障自动切换，并将vip切换到备节点，实现高可用。mysql双主复制+keepalived架构是一种简单、便捷高可用方案，使用于各种业务。
复制链接

扫一扫