当前位置： 代码网 > it编程>数据库>Mysql > MySQL双主高可用详解

MySQL双主高可用详解

2025年08月14日 • Mysql •我要评论
主机规划：主机名称ipkeepalived操作系统安装服务安装路径mysql01192.168.91.61192.168.91.100centos 7.9mysql 8.0.28 keepalived

主机规划：

主机名称ipkeepalived操作系统安装服务安装路径
mysql01192.168.91.61192.168.91.100centos 7.9mysql 8.0.28 keepalived v1.3.5/usr/local/mysql
mysql02192.168.91.62192.168.91.100centos 7.9mysql 8.0.28 keepalived v1.3.5/usr/local/mysql

架构图：

一、系统初基础优化（mysql01和mysql02都操作）

systemctl stop firewalld
systemctl disable firewalld
sed -i 's@selinux=enforcing@selinux=disabled@g' /etc/selinux/config
grep -i "^selinux=" /etc/selinux/config
setenforce 0
getenforce

cat >> /etc/security/limits.conf << eof
*   soft    nproc    65535
*   hard    nproc    65535
*   soft    nofile    65535
*   hard    nofile    65535
eof

cat >> /etc/hosts << eof
192.168.91.61  mysql01
192.168.91.62  mysql02
eof

二、脚本自动安装mysql二进制软件包（mysql01和mysql02都操作）

1、下载mysql二进制软件包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz -p /mnt/

2、创建自动安装mysql二进制软件包脚本

注意：此脚本只适合centos 7.9版本操作系统和mysql 8.0版本数据库安装，mysql二进制软件包一定要与自动安装脚本放在同一个目录上执行，否则执行会报错。

[root@mysql01 ~]# vim /mnt/mysql_install.sh

#!/bin/bash
# description: mysql binary pack install
# version: 1.0

#################################################################################
# prompt:  mysql binary pack must put same directory with scripts.           
# 提示:    把mysql 二进制软件包与该脚本放在同一个目录下，再执行该脚本进行安装.  
#################################################################################

. /etc/profile

export path=$path
red='\033[1;31;5m'
res='\033[0m'
mysql_count=$(ss -tlunp | grep mysqld | wc -l)
mysql_pwd=$(pwd)
mysql_pack="mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz"    ### 只需要把这个软件包名称替换成你所下载的软件包名称一致
mysql_path="/usr/local/${mysql_pack//-linux*/}"
mysql_link="/usr/local/mysql"


### check mysql if installed
if [ $mysql_count -ne 0 ];then
   echo -e "${red} mysql program is running ${res}"
   exit 1
fi
### check mysql directory is exits
if [ -d /usr/local/mysql* ];then 
   echo -e "${red} mysql directory is exits ${res}"
   exit 1
fi
### check mysql binary pack if exist
if [ ! -f ${mysql_pwd}/${mysql_pack} ];then
   echo -e "${red} current directory is not mysql binary pack,please copy it ${res}"
   exit 1
fi

### install mysql depend pack
yum -y install ncurses-devel libaio-devel gcc gcc++ net-tools > /dev/null 2>&1
if [ $? -ne 0 ];then
   echo -e "${red} yum install is failure,please check network if surf the internet ${res}"
   exit 1
fi

### discompress mysql binary pack
tar xf ${mysql_pwd}/${mysql_pack} -c /usr/local/
mv /usr/local/mysql-* ${mysql_path}
ln -s ${mysql_path} ${mysql_link}
mkdir -p ${mysql_link}/data
mkdir -p ${mysql_link}/logs


###judge my.cnf configuration file if exits
if [ -f /etc/my.cnf ];then
   mv /etc/my.cnf /tmp/
fi

### compile my.cnf configuration file
cat << eof > /etc/my.cnf
[mysqld]
#server-id=1
mysqlx_socket=/tmp/mysqlx.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
pid_file=/tmp/mysqld.pid
log_error=/usr/local/mysql/logs/error.log
log-bin=/usr/local/mysql/logs/mysql.bin
character-set-server=utf8mb4

max_connections=500
max_connect_errors=1000
port=3306
mysqlx_port=33060
eof

### create mysql virtual user
id mysql > /dev/null 2>&1
if [ $? -ne 0 ];then
  useradd -s /bin/nologin -m mysql
fi

### mysql directory to mandate mysql user
chown -r mysql:mysql /usr/local/mysql*

### initialize mysql
${mysql_link}/bin/mysqld --initialize --user=mysql --basedir=${mysql_link} --datadir=${mysql_link}/data

### copy mysql boot scripts
cp ${mysql_link}/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
sed -i 's@^basedir=@basedir=/usr/local/mysql@g' /etc/init.d/mysqld
sed -i 's@^datadir=@datadir=/usr/local/mysql/data@g' /etc/init.d/mysqld

### mysql take all command add system environment variable
cat << eof >> /etc/profile
export path=$path:/usr/local/mysql/bin/
eof
source /etc/profile

### mysql start
/etc/init.d/mysqld start > /dev/null 2>&1
if [ $? -ne 0 ];then
   echo -e "${red} mysql start is failure,please check mysql error-log ${res}"
   exit 1
fi

### grep mysql initialize root password and modify root password is 123456
mysql_pass=$(grep "a temporary password" /usr/local/mysql/logs/error.log | awk '{print $nf}')
${mysql_link}/bin/mysql --connect-expired-password -uroot   -p${mysql_pass}   -e  "alter user 'root'@'localhost' identified by '123456'"

### setup mysql service auto start
cat << eof >> /etc/rc.local
/etc/init.d/mysqld start
eof

chmod +x /etc/rc.local

### echo mysql password
echo -e "\n\n###############################################\n"
echo "  mysql root 密码 123456"
echo "  mysql 服务启动命令  /etc/init.d/mysqld start"
echo "  mysql 命令生效请执行命令 source /etc/profile"
echo -e "\n###############################################\n"

3、执行自动安装mysql二进制软件包脚本

chmod +x /mnt/mysql_install.sh
cd /mnt/
sh mysql_install.sh

4、测试登录mysql数据库

source /etc/profile
mysql -uroot -p123456

三、配置mysql01和mysql02相互主从同步（mysql01和mysql02都操作）

1、登录mysql01主机修改/etc/my.cnf配置文件

cat > /etc/my.cnf << eof             
[client]
# 设置mysql客户端连接端口
port=3306
# 设置mysql客户端连接sock
socket=/usr/local/mysql/logs/mysql.sock
# 设置mysql客户端编码
default-character-set=utf8mb4


[mysqld]
# 设置mysql服务端tcp连接端口
port=3306
# 设置mysql服务端ssl连接端口
mysqlx_port=33060
# 设置mysql服务端编码
character-set-server=utf8mb4
# mysql服务端安装目录
basedir=/usr/local/mysql
# mysql服务端数据存放目录
datadir=/usr/local/mysql/data
# mysql服务端socket文件地址
socket=/usr/local/mysql/logs/mysql.sock
# mysql服务端mysqlx.socket文件地址
mysqlx_socket=/usr/local/mysql/logs/mysqlx.sock
# mysql服务端pid文件地址
pid_file=/usr/local/mysql/logs/mysqld.pid
# mysql服务端错误日志存储地址
log_error=/usr/local/mysql/logs/error.log
# 设置mysql主从同步的server-id，每台mysql设置的server-id不能相同
server-id=100
# 设置mysql主从同步bin-log存放路径及名称
log-bin=/usr/local/mysql/logs/mysql-bin
# 设置mysql主从同步方式，默认row
binlog_format=row
# 保留最近604800秒（7天）bin-log日志（默认秒为单位），默认保留最近30天
binlog_expire_logs_seconds=604800
# 设置主键自增起始值，两个master主库不能设置相同的值，否则容易引起主键自增值冲突
auto_increment_offset=1
# 控制主键自增长每次递增的量，用于防止master主库与master主库之间复制出现重复自增字段值，例如auto_increment_increment值与auto_increment_offset值组合使用，例如auto_increment_offset值等于1, auto_increment_increment值等于2，那么每次主键自增长值就是 1 3 5 7 9 11每次加2递增的主键值
auto_increment_increment=2
eof

2、登录mysql02主机修改/etc/my.cnf配置文件

cat > /etc/my.cnf << eof             
[client]
# 设置mysql客户端连接端口
port=3306
# 设置mysql客户端连接sock
socket=/usr/local/mysql/logs/mysql.sock
# 设置mysql客户端编码
default-character-set=utf8mb4


[mysqld]
# 设置mysql服务端tcp连接端口
port=3306
# 设置mysql服务端ssl连接端口
mysqlx_port=33060
# 设置mysql服务端编码
character-set-server=utf8mb4
# mysql服务端安装目录
basedir=/usr/local/mysql
# mysql服务端数据存放目录
datadir=/usr/local/mysql/data
# mysql服务端socket文件地址
socket=/usr/local/mysql/logs/mysql.sock
# mysql服务端mysqlx.socket文件地址
mysqlx_socket=/usr/local/mysql/logs/mysqlx.sock
# mysql服务端pid文件地址
pid_file=/usr/local/mysql/logs/mysqld.pid
# mysql服务端错误日志存储地址
log_error=/usr/local/mysql/logs/error.log
# 设置mysql主从同步的server-id，每台mysql设置的server-id不能相同
server-id=200
# 设置mysql主从同步bin-log存放路径及名称
log-bin=/usr/local/mysql/logs/mysql-bin
# 设置mysql主从同步方式，默认row
binlog_format=row
# 保留最近604800秒（7天）bin-log日志（默认秒为单位），默认保留最近30天
binlog_expire_logs_seconds=604800
# 设置主键自增起始值，两个master主库不能设置相同的值，否则容易引起主键自增值冲突
auto_increment_offset=2
# 控制主键自增长每次递增的量，用于防止master主库与master主库之间复制出现重复自增字段值，例如auto_increment_increment值与auto_increment_offset值组合使用，例如auto_increment_offset值等于2, auto_increment_increment值等于2，那么每次主键自增长值就是 2 4 6 8 10 12每次加2递增的主键值
auto_increment_increment=2
eof

3、登录mysql01和mysql02分别重启mysql数据库

/etc/init.d/mysqld restart

4、登录mysql01和mysql02分别创建mysql主从同步用户

mysql -uroot -p123456
create user 'slaveuser'@'%' identified with mysql_native_password by '123456';
grant replication slave on *.* to 'slaveuser'@'%';
flush privileges;
select user,host from mysql.user;

5、登录mysql01查看master状态下的file文件和position位置点信息

注意：本教程安装的两个mysql数据库都是初始化的数据库，没有任何生产数据

mysql> show master status\g;
*************************** 1. row ***************************
             file: mysql-bin.000001
         position: 1951
     binlog_do_db: 
 binlog_ignore_db: 
executed_gtid_set: 
1 row in set (0.00 sec)

6、登录mysql02配置从库同步mysql01主库信息

mysql -uroot -p123456

mysql> change master to master_host='192.168.91.61', master_port=3306,master_user='slaveuser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1951; 
query ok, 0 rows affected, 9 warnings (0.01 sec)

mysql> start slave;
query ok, 0 rows affected, 1 warning (0.00 sec)

### 主要看以下三个参数指标即可
mysql> show slave status\g;
slave_io_running: yes
slave_sql_running: yes
seconds_behind_master: 0

###  slave_io_running：yes，这个表示i/o的线程状态，i/o线程负责从主库中读取binlog日志，并将binlog日志写入从库的中继日志中，状态为yes表示i/o线程工作正常，否则异常。
###  slave_sql_running：yes，这个表示sql的线程状态，sql线程负责读取中继日志（relay-log）中的数据并转换为sql语句应用到从数据库中，状态为yes表示i/o线程工作正常，否则异常。
###  seconds_behind_master：0，这个表示在复制过程中，从库比主库延迟的秒数。

### change master to 
### master_host='192.168.91.61',             #<==主库的ip
### master_port=3306,                      #<==主库的端口
### master_user='slaveuser',                 #<==主库创建主从同步用户
### master_password='123456',            #<==主库创建主从同步用户密码
### master_log_file='mysql-bin.000001',     #<==主库show master status状态的file值
### master_log_pos=1092;                  #<==主库show master status状态的position值

7、登录mysql02查看master状态下的file文件和position位置点信息

注意：本教程安装的两个mysql数据库都是初始化的数据库，没有任何生产数据

mysql> show master status\g;
*************************** 1. row ***************************
             file: mysql-bin.000001
         position: 1725
     binlog_do_db: 
 binlog_ignore_db: 
executed_gtid_set: 
1 row in set (0.00 sec)

8、登录mysql01配置从库同步mysql02主库信息

mysql -uroot -p123456

mysql> change master to master_host='192.168.91.62', master_port=3306,master_user='slaveuser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1725; 
query ok, 0 rows affected, 9 warnings (0.01 sec)

mysql> start slave;
query ok, 0 rows affected, 1 warning (0.01 sec)

### 主要看以下三个参数指标即可
mysql> show slave status\g;
slave_io_running: yes
slave_sql_running: yes
seconds_behind_master: 0

9、mysql数据库双主测试

### 登录mysql01主机数据库创建一个测试用户testuser
[root@mysql01 ~]# mysql -uroot -p123456
mysql> create user testuser@'%' identified by '123456';
mysql> select user,host from mysql.user where user='testuser';
+----------+------+
| user     | host |
+----------+------+
| testuser | %    |
+----------+------+
1 row in set (0.00 sec)

### 登录mysql02主机数据库查看testuser测试用户是否同步创建
[root@mysql02 ~]# mysql -uroot -p123456
mysql> select user,host from mysql.user where user='testuser';
+----------+------+
| user     | host |
+----------+------+
| testuser | %    |
+----------+------+
1 row in set (0.00 sec)

### 登录mysql02主机数据库删除testuser测试用户
[root@mysql02 ~]# mysql -uroot -p123456
mysql> drop user testuser@'%';
query ok, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user where user='testuser';
empty set (0.00 sec)

### 登录mysql01主机数据库查看testuser测试用户是否同步删除
[root@mysql01 ~]# mysql -uroot -p123456
mysql> select user,host from mysql.user where user='testuser';
empty set (0.00 sec)

四、登录mysql01和mysql02安装配置keepalived服务

1、登录mysql01安装配置keepalived服务

yum -y install keepalived
mv /etc/keepalived/keepalived.conf /tmp/

### 修改keepalived服务配置文件，根据实际情况修改ip地址、网卡名称、state参数值名称要修改成master、priority参数值越大，优先级就越高，master优先级要高于backup
cat > /etc/keepalived/keepalived.conf << 'eof'
! configuration file for keepalived
global_defs {
   script_user root
   notification_email {
      lishi666@qq.com
   }
   notification_email_from xiaoming@qq.com
   smtp_server mail.qq.com
   smtp_connect_timeout 30
   router_id lvs_master
   enable_script_security
}

vrrp_script check_mysql_port {
   script "/data/scripts/check.sh"
   interval 1
   weight -5
}

vrrp_instance vi_1 {
   state master
   interface ens33
   virtual_router_id 92
   priority 100
   advert_int 1

   notify_master "/data/scripts/notify.sh master"
   notify_slave "/data/scripts/notify.sh backup"
   notify_fault "/data/scripts/notify.sh fault"

   unicast_src_ip 192.168.91.61
   unicast_peer {
       192.168.91.62
   }

   authentication {
       auth_type pass
       auth_pass 123456
   }

   virtual_ipaddress {
      192.168.91.100
   }

   track_script {
      check_mysql_port
   }
}
eof

### 配置smtp发送邮件功能，设置个人的外网邮箱账号，此邮件账号主要用来发送邮件使用
cat > /etc/mail.rc << eof
set from=xiaoming@qq.com
set smtp=mail.qq.com
set smtp-auth-user=xiaoming@qq.com
set smtp-auth-password=abc123456
set smtp-auth=login
eof

### 创建keepalived服务检测mysql状态是否正常的脚本
mkdir -p /data/scripts
cat > /data/scripts/check.sh << 'eof'
#!/bin/bash
source /etc/profile
mysql_pid=`ps -ef|grep mysql |grep -v grep | awk '{print $2}' | wc -l`
if [ ${mysql_pid} -eq 0 ];then
systemctl stop keepalived
fi
eof

### 创建keepalived故障邮件通知脚本，根据实际情况修改ip、主机名称、收件人邮件地址
cat > /data/scripts/notify.sh << 'eof'
#!/bin/bash
#接收者邮箱，多个以空格分隔
contact=(lishi666@qq.com)
#本机ip
host_ip=192.168.91.61
host_name=mysql01
notify() {
    #邮件主题
    mailsubject="mysql keepalived负载均衡vip地址发生了转移"
    #邮件正文
    description="事件描述：${host_ip}  keepalived changed to be $1"
    datebody="发生时间：$(date +'%f %t')"
    hostnamebody="主机名称：${host_ip}"
    ipbody="主机ip： ${host_name}"
    for receiver in ${contact[*]}
    do
        #发送邮件
        echo -e "${description} ${hostbody} \n ${datebody} \n ${hostnamebody} \n ${ipbody}" | mail -s "$mailsubject" $receiver
    done
}
case $1 in
master)
    notify master
    ;;
backup)
    notify backup
    ;;
fault)
    notify fault
    ;;
*)
    echo "usage:{master|backup|fault}"
    exit 1
    ;;
esac
eof

chmod +x /data/scripts/*.sh

2、登录mysql02安装配置keepalived服务

yum -y install keepalived
mv /etc/keepalived/keepalived.conf /tmp/

### 修改keepalived服务配置文件，根据实际情况修改ip地址、网卡名称、state参数值名称要修改成backup、priority参数值越大，优先级就越高，backup优先级要低于master
cat > /etc/keepalived/keepalived.conf << 'eof'
! configuration file for keepalived
global_defs {
   script_user root
   notification_email {
      lishi666@qq.com
   }
   notification_email_from xiaoming@qq.com
   smtp_server mail.qq.com
   smtp_connect_timeout 30
   router_id lvs_master
   enable_script_security
}

vrrp_script check_mysql_port {
   script "/data/scripts/check.sh"
   interval 1
   weight -5
}

vrrp_instance vi_1 {
   state backup
   interface ens33
   virtual_router_id 92
   priority 60
   advert_int 1

   notify_master "/data/scripts/notify.sh master"
   notify_slave "/data/scripts/notify.sh backup"
   notify_fault "/data/scripts/notify.sh fault"

   unicast_src_ip 192.168.91.62
   unicast_peer {
       192.168.91.61
   }

   authentication {
       auth_type pass
       auth_pass 123456
   }

   virtual_ipaddress {
      192.168.91.100
   }

   track_script {
      check_mysql_port
   }
}
eof

### 配置smtp发送邮件功能，设置个人的外网邮箱账号，此邮件账号主要用来发送邮件使用
cat > /etc/mail.rc << eof
set from=xiaoming@qq.com
set smtp=mail.qq.com
set smtp-auth-user=xiaoming@qq.com
set smtp-auth-password=abc123456
set smtp-auth=login
eof

### 创建keepalived服务检测mysql状态是否正常的脚本
mkdir -p /data/scripts
cat > /data/scripts/check.sh << 'eof'
#!/bin/bash
source /etc/profile
mysql_pid=`ps -ef|grep mysql |grep -v grep | awk '{print $2}' | wc -l`
if [ ${mysql_pid} -eq 0 ];then
systemctl stop keepalived
fi
eof

### 创建keepalived故障邮件通知脚本，根据实际情况修改ip、主机名称、收件人邮件地址
cat > /data/scripts/notify.sh << 'eof'
#!/bin/bash
#接收者邮箱，多个以空格分隔
contact=(lishi666@qq.com)
#本机ip
host_ip=192.168.91.62
host_name=mysql02
notify() {
    #邮件主题
    mailsubject="mysql keepalived负载均衡vip地址发生了转移"
    #邮件正文
    description="事件描述：${host_ip}  keepalived changed to be $1"
    datebody="发生时间：$(date +'%f %t')"
    hostnamebody="主机名称：${host_ip}"
    ipbody="主机ip： ${host_name}"
    for receiver in ${contact[*]}
    do
        #发送邮件
        echo -e "${description} ${hostbody} \n ${datebody} \n ${hostnamebody} \n ${ipbody}" | mail -s "$mailsubject" $receiver
    done
}
case $1 in
master)
    notify master
    ;;
backup)
    notify backup
    ;;
fault)
    notify fault
    ;;
*)
    echo "usage:{master|backup|fault}"
    exit 1
    ;;
esac
eof

chmod +x /data/scripts/*.sh

3、登录mysql01和mysql02启动keepalived服务

### 先登录mysql01主机启动keepalived服务
systemctl start keepalived
systemctl status keepalived
ip a | grep 192.168.91.100

### 再登录mysql02主机启动keepalived服务
systemctl start keepalived
systemctl status keepalived

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com