mysql 8.0双主_Keepalived+MySQL 8.0.17 实现MySQL双主的高可用

最新推荐文章于 2024-05-22 15:20:28 发布
最新推荐文章于 2024-05-22 15:20:28 发布
阅读量126 收藏
点赞数
文章标签： mysql 8.0双主
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33988332/article/details/113168111
版权

一、系统环境

主机：两台CentOS7.6虚拟机

IP地址：master01(IP：192.168.27.47)，master02(IP：192.168.27.57)，VIP：192.168.27.100

软件：mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz，Keepalived(yum源)

二、实现过程

2.1、实现MySQL的安装配置

[root@mysql-master01 ~]# ll mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz-rw-r--r-- 1 root root 480209016 May 9 11:25 mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

[root@mysql-master02 ~]# ll mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz-rw-r--r-- 1 root root 480209016 May 9 11:25 mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

(2) 两台主机都创建mysql用户，以master01为例

[root@mysql-master01 ~]# useradd -r -s /sbin/nologin mysql

(3) 解压MySQL安装包，创建软链接

[root@mysql-master01 ~]# tar -Jxvf mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz -C /usr/local/[root@mysql-master01 ~]# ln -s /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64 /usr/local/mysql

(4) 初始化数据库

[root@mysql-master01 ~]# cd /usr/local/mysql

[root@mysql-master01 mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/data/mysql2020-05-11T02:55:47.592126Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed ina future release.2020-05-11T02:55:47.673278Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 56424

2020-05-11T02:56:00.647204Z 5 [Note] [MY-010454] [Server] A temporary password is generated forroot@localhost: W:66pBoq;hkF #root连接数据库的初始密码2020-05-11T02:56:04.547704Z 0 [System] [MY-013170] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server has completed

[root@mysql-master02 mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/data/mysql2020-05-11T02:56:54.701359Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed ina future release.2020-05-11T02:56:54.780973Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 56410

2020-05-11T02:57:07.447552Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost:

(5) 修改配置文件 /etc/my.cnf

[root@mysql-master01 mysql]# vim /etc/my.cnf

[mysqld]

port=3306datadir=/data/mysql

socket=/data/mysql/mysql.sock

character-set-server=utf8mb4

innodb_file_per_table=on

skip_name_resolve=on

log-error=/data/mysql/log/mysql.log

pid-file=/data/mysql/run/mysql.pid

# 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 unitfile formariadb according to the

# instructionsin http://fedoraproject.org/wiki/Systemd

[client]

socket=/data/mysql/mysql.sock

#

# include all files from the config directory

#!includedir /etc/my.cnf.d

#创建日志目录与文件，防止启动报错

[root@mysql-master01 ~]# mkdir /data/mysql/{log,run}

[root@mysql-master01 ~]# touch /data/mysql/log/mysql.log

[root@mysql-master01 ~]# chown -R mysql:mysql /data/mysql

(7) 准备启动脚本，并配置环境变量

[root@mysql-master01 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@mysql-master01 mysql]# chkconfig --add mysqld

[root@mysql-master01 mysql]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh[root@mysql-master01 mysql]# . /etc/profile.d/mysql.sh

(8) 启动数据库服务，并修改root用户密码

[root@mysql-master01 ~]# service mysqld start

Starting MySQL........... SUCCESS![root@mysql-master01 mysql]# mysqladmin -uroot -p"W:66pBoq;hkF" password '123456'#修改密码方式一，不安全，不推荐

[root@mysql-master02 ~]# mysql -u root -p

Enter password: #输入mysql-master02的数据库密码：

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 8Server version:8.0.17Copyright (c)2000, 2019, Oracle and/or its affiliates. All rights reserved.

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 clearthe current input statement.

mysql> alter user 'root'@'localhost' identified by '123456'; #修改密码方式二

Query OK,0 rows affected (0.03 sec)

2.2、实现MySQL的双主配置

(1) 修改mysql-master01的配置文件，可以直接修改 /etc/my.cnf 文件，也可以在 /etc/my.cnf.d/ 下新建一个配置文件

[root@mysql-master01 ~]# vim /etc/my.cnf.d/mysql-master.cnf

[mysqld]

server_id= 1log-bin = /data/mysql/log/log_bin

binlog-format =ROW　　#指定二进制格式

relay-log = /data/mysql/log/relay-bin

relay-log-index = relay-bin.index

auto_increment_offset= 1　　#起始值，一般填写第n台主机mysqlauto_increment_increment= 2　　#步进值auto_imcrement 。一般有n台主mysql就填n#binlog_do_db= DBNAME　　#设置要同步的数据库#replicate-ignore-db =test　　#设置不同步的数据库

sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

(2) 修改mysql-master02的配置文件

[root@mysql-master02 ~]# vim /etc/my.cnf.d/mysql-master.cnf

[mysqld]

server_id= 2log-bin = /data/mysql/log/log_bin

binlog-format =ROW

relay-log = /data/mysql/log/relay-bin

relay-log-index = relay-bin.index

auto_increment_offset= 2auto_increment_increment= 2#binlog_do_db= DBNAME#replicate-ignore-db =test

sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

(3) 在mysql-master01与mysql-master02上分别创建授权用户

[root@mysql-master01 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 10Server version:8.0.17 MySQL Community Server -GPL

mysql> create user 'repl'@'192.168.27.%' identified with mysql_native_password by 'repl123456';

Query OK,0 rows affected (0.04sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.27.%';

Query OK,0 rows affected (0.02sec)

mysql>flush privileges;

Query OK,0 rows affected (0.02sec)

mysql>show master status;+----------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+-------------------+

| log_bin.000001 | 862 | | | |

+----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00sec)

#以下为mysql-master02上的操作

[root@mysql-master02 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 9Server version:8.0.17 MySQL Community Server -GPL

mysql> create user 'repl'@'192.168.27.%' identified with mysql_native_password by 'repl123456';

Query OK,0 rows affected (0.05sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.27.%';

Query OK,0 rows affected (0.02sec)

mysql>flush privileges;

Query OK,0 rows affected (0.02sec)

mysql>show master status;+----------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+-------------------+

| log_bin.000001 | 862 | | | |

+----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

(4) 在mysql-master02上配置同步信息

mysql>change master to-> master_host='192.168.27.47',-> master_user='repl',-> master_password='repl123456',-> master_log_file='log_bin.000001',-> master_log_pos=862;

Query OK,0 rows affected, 2 warnings (0.11sec)

mysql>start slave; #启动slave同步进程

Query OK,0 rows affected (0.05sec)

mysql> show slave status\G; #查看slave状态，看是否有错误

(5) 在mysql-master01上配置同步信息

mysql>change master to-> master_host='192.168.27.57',-> master_user='repl',-> master_password='repl123456',-> master_log_file='log_bin.000001',-> master_log_pos=862;

Query OK,0 rows affected, 2 warnings (0.12sec)

mysql>start slave;

Query OK,0 rows affected (0.05sec)

mysql> show slave status\G;

(6) 测试同步

#在mysql-master01创建一个库testA

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.06sec)

mysql>create database testA;

Query OK,1 row affected (0.03sec)

#在mysql-master02查看，可以看到同步了

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| testA |

+--------------------+

5 rows in set (0.06sec)

#再在mysql-master02创建一个库testB

mysql>create database testB;

Query OK,1 row affected (0.03sec)

#在mysql-master01上查看，可以看到也同步了

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| testA |

| testB |

+--------------------+

6 rows in set (0.01 sec)

2.3、实现MySQL双主的高可用配置

(1) 两台主机都安装Keepalived服务，此处使用光盘自带yum源安装

[root@mysql-master01 ~]# yum install -y keepalived

[root@mysql-master02 ~]# yum install -y keepalived

(2) 修改mysql-master01上的keepalived配置

[root@mysql-master01 ~]# vim /etc/keepalived/keepalived.conf! Configuration File forkeepalived

global_defs {

notification_email {

root@localhost

}

notification_email_from root@localhost

smtp_server127.0.0.1smtp_connect_timeout30router_id master01

vrrp_skip_check_adv_addr

vrrp_iptables

vrrp_strict

vrrp_garp_interval0vrrp_gna_interval0}

#定义检查脚本

vrrp_script check_mysql_status {

script"/etc/keepalived/chk_mysql.sh"interval2weight-50fall3rise5timeout3}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id51priority100nopreempt

advert_int1authentication {

auth_type PASS

auth_pass1111}

virtual_ipaddress {192.168.27.100/24 dev eth0 label eth0:0}

track_script {

check_mysql_status

}

}

(3) 修改mysql-master02上的keepalived配置

[root@mysql-master02 ~]# vim /etc/keepalived/keepalived.conf! Configuration File forkeepalived

global_defs {

notification_email {

root@localhost

}

notification_email_from root@localhost

smtp_server127.0.0.1smtp_connect_timeout30router_id master02

vrrp_skip_check_adv_addr

vrrp_iptables

vrrp_strict

vrrp_garp_interval0vrrp_gna_interval0}

vrrp_script check_mysql_status {

script"/etc/keepalived/chk_mysql.sh"interval2weight-50fall3rise5timeout3}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id51priority80nopreempt

advert_int1authentication {

auth_type PASS

auth_pass1111}

virtual_ipaddress {192.168.27.100/24 dev eth0 label eth0:0}

track_script {

check_mysql_status

}

}

(4) 编写检查脚本，两台主机都一样

[root@mysql-master01 ~]# vim /etc/keepalived/chk_mysql.sh#!/bin/bash

mysqlcmd="/usr/local/mysql/bin/mysql"user="root"password="123456"$mysqlcmd-u$user -p$password -e "show status;" &> /dev/null

if [ $? -eq 0 ];then

echo "mysql_status=1"exit0

else

/usr/bin/systemctl stop keepalivedfi[root@mysql-master01 ~]# chmod +x /etc/keepalived/chk_mysql.sh[root@mysql-master01 ~]# scp /etc/keepalived/chk_mysql.sh 192.168.27.57:/etc/keepalived/

(5) 启动两台主机的keepalived服务，并设为开机启动

[root@mysql-master01 ~]# systemctl start keepalived

[root@mysql-master01 ~]# systemctl enable keepalived

[root@mysql-master02 ~]# systemctl start keepalived

[root@mysql-master02 ~]# systemctl enable keepalived

(6) 测试

#刚开始时，VIP在mysql-master01上

[root@mysql-master01 ~]# ip a|grep 192.168.27.100inet192.168.27.100/24 scope global secondary eth0:0#关闭mysql-master01上的keepalived，VIP也转移到了mysql-master02上

[root@mysql-master01 ~]# systemctl stop keepalived

[root@mysql-master01 ~]# ip a|grep 192.168.27.100[root@mysql-master02 keepalived]# ip a|grep 192.168.27.100inet192.168.27.100/24 scope global secondary eth0:0#重新启动mysql-master01的keepalived，可以看到VIP还是在mysql-master02上，因为是非抢占模式，是正确的

[root@mysql-master01 ~]# systemctl start keepalived

[root@mysql-master01 ~]# ip a|grep 192.168.27.100[root@mysql-master02 keepalived]# ip a|grep 192.168.27.100inet192.168.27.100/24 scope global secondary eth0:0#接着，关闭mysql-master02上mysql服务，制造mysql故障

#此时，mysql-master02上的keepalived服务将被脚本控制关闭了，并且VIP应该转移至mysql-master01上

[root@mysql-master02 ~]# service mysqld stop

Shutting down MySQL.......... SUCCESS![root@mysql-master02 ~]# ip a|grep 192.168.27.100[root@mysql-master02 ~]# ps -ef|grepkeepalived

root10478 6769 0 11:37 pts/0 00:00:00 grep --color=auto keepalived

#以下可以看到，VIP已转移到mysql-master01上

[root@mysql-master01 ~]# ip a|grep 192.168.27.100inet192.168.27.100/24 scope global secondary eth0:0

KTunstal
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql8高可用之双主+keepalived
m0_61388193的博客
06-28 5602
利用keepalived实现Mysql数据库的高可用，Keepalived+Mysql双主来实现MYSQL-HA，我们必须保证两台Mysql数据库的数据完全一致，实现方法是两台Mysql互为主从关系，通过keepalived配置VIP，实现当其中的一台Mysql数据库宕机后，应用能够自动切换到另外一台Mysql数据库上去，保证系统的高可用。1、 采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，避免因为意外情况下相互抢占导致两个节点内写入相同的数据而引发冲突；
mysql 双主单活+keepalived 实现高可用
11-20
mysql 双主单活+keepalived 实现高可用指导文档，按步骤操作，接完成数据库高可用的自动切换部署
mysql实战——Mysql8.0高可用之双主+keepalived
最新发布
zyjzyjjyzjyz的博客
05-22 1442
1、 采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，避免因为意外情况下相互抢占导致两个节点内写入相同的数据而引发冲突；2、 把两个节点的auto_increment_increment（自增步长）和auto_increment_offset（字增起始值）设置成不同值，其目的是为了避免master节点意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原master上冲突，因此一开始就错开；
mysql 8.0双主_鲲鹏mysql8.0.18高可用双主架构部署要点汇总
weixin_35998762的博客
01-27 194
云主机：鲲鹏云服务器Linux系统版本：NeoKylin Linux Advanced Server release V7Update6 (Chromium)MySQL源码版本：mysql-8.0.18keepalived版本：keepalived-1.3.5-6.el7.aarch64数据库架构元素IP地址DB1主机10.0.20.10DB2主机10.0.20.20Keepalived-VIP1...
配置Keepalived实现Mysql双主高可用配置（完整配置）
Jecci
06-08 558
随着公司的发展，数据显得尤为重要，上次写了服务器的安全操作知之外，今天给大家写一篇针对mysql的双主高可用配置 。 本文讲述配置Keepalived实现Mysql双主高可用配置，其中所需知识包含iptables，mysql，sendmail，Keepalived，shell或者其它脚本语言均可。差不多就这么多吧。自己也百度了许多文章，很多都没有讲全，今天我就把整个过程给大家讲述清楚，由于本次
mysql8 keepalived_MySQL8 Keepalived+双主
weixin_28790465的博客
02-02 177
1. 配置双主：双机名win89.inno.comwin88.inno.com准备账号SET SQL_LOG_BIN=0;set passwordfor ‘root‘@‘localhost‘=‘123456‘;grant all privileges on*.* to root@‘localhost‘with grant option;create user‘repl‘@‘win88.inno.c...
mysql8 keepalived_mysql+keepalived
weixin_29505163的博客
02-18 216
mysql+keepalived1.安装# yum install -y openssl-devel popt-devel# tar zxf keepalived-1.2.8.tar.gz# cd keepalived-1.2.8# ./configure --prefix=/usr/local/keepalived# make && make install2.将keepaliv...
MariaDB+Keepalived双主高可用配置MySQL-HA
01-30
利用keepalived构建高可用MySQL-HA，保证两台MySQL数据的一致性，然后用keepalived实现虚拟VIP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。硬件拓扑如下：操作系统：CentOSrelease6.3（32位）...
MySQL 高可用性—keepalived+mysql双主.docx
06-27
在这个场景中，我们利用Keepalived和MySQL的双主复制来构建高可用集群。Keepalived是一个开源工具，主要用于提供虚拟IP地址和健康检查，确保在主服务器故障时，服务能够快速切换到备用服务器。 首先，我们要配置两...
mysql双主同步+keepalived高可用群集
02-17
mysql【1】主从同步之人机交互自动化配置脚本-CSDN博客 ...mysql【3】keepalived+mysql双主同步，实现故障转移群集 https://blog.csdn.net/u014332402/article/details/136137707 这3篇文章所使用的所有自动化配置脚本
Mysql双主热备+LVS+Keepalived高可用操作记录-精华版
01-06
本篇文档为Mysql双主热备+LVS+Keepalived高可用操作记录，可作为线上实操手册，有需要的朋友可以拿走，希望能帮助到有用到的人～
mysql-8.0.11-linux-glibc2.5-x86_64.tar.gz(安装步骤)
07-02
mysql-8.0.11-linux-glibc2.5-x86_64.tar.gz(安装步骤)
【环境部署】keepAlive mysql8主备
清风唱诗人的博客
11-17 265
直接贴两个比较好的博客。博主参考并且实践过了，特此记录 mysql主从之keepalive+MySQL高可用 - 梦中泪 - 博客园 Centos7安装配置Keepalived_cuiyaoqiang的博客-CSDN博客 实践环境 centos7 + mysql8 + keepalive 1.3 ...
mysql8 keepalived_mysql学习-mysql8.0配置双主复制+keepalived实现高可用架构
weixin_39548438的博客
02-07 694
1、mysql双主复制介绍双主复制，就是相互做主从复制，每个master既是master又是另外一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。主从复制涉及三个线程，一个运行到主节点(log dump thread)，其余两个(I/O thread,SQL thread)运行在从节点，如下所示：i/o线程去请求主库 的binlog，并将得到的binlog日志...
mysql8 keepalived_mysql8+keepalived 双主高可用搭建
weixin_39727863的博客
01-19 222
配置keepalived注意：记得关闭防火墙或者增加vrrp策略1、安装注意：先创建 /usr/local/keepalived(新建文件夹)# tar -zxf keepalived-2.1.5.tar.gz# cd keepalived-2.0.0# ./configure --prefix=/usr/local/keepalived# make&&make install...
docker部署mysql8主从集群（互为主从），keepalived保证高可用
qq_41369135的博客
07-14 1830
docker部署mysql8主从集群（互为主从），keepalived保证高可用
【MySQL8入门到精通】高可用-双主+Keepalived故障转移
IT邦德
05-30 3825
keepalived是集群管理中保证集群高可用的一个服务软件，其功能类似于heartbeat，用来防止单点故障
Keepalived+MySql 8.0（双主） HA搭建
out_of_it_farmer的博客
11-05 1651
操作系统：ubuntu 16.04 两台服务器ip分别为： 192.168.0.105 192.168.0.106 虚拟ip为：192.168.0.250 一. 离线安装mysql8.0.18 1. 下载mysql安装包 百度网盘 提取码：22th 2. 安装mysql 依次执行下面11条命令 1. sudo dpkg -i mysql-community-client-core_8.0.18-1...
Mysql主从之keepalived保证高可用搭建详细教程
qq_33549942的博客
03-13 1809
在很早之前搭建过Mysql的主从，但是当时年幼无知，没有想到过高可用怎么保证，这两天在复习以前的笔记的时候，无意中看到了Mysql之keepalived的文章，自己就想着实践一下，于是就有了这篇文章。文章大体分为两部分：1.主从搭建，2.keepalived集成。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值