ITPub博客

首页 > 数据库 > MySQL > Centenos7.4下搭建mysql集群--mysql主主互备,keepalived高可用

Centenos7.4下搭建mysql集群--mysql主主互备,keepalived高可用

原创 MySQL 作者:zy_rain 时间:2020-02-17 15:13:36 0 删除 编辑

Mysql + keepalived环境搭建

1.环境准备
系统:Centenos7.4
mysql版本 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
keepalived版本 keepalived-2.0.13.tar.gz
node1 :192.168.5.235
node2 :192.168.5.236
vip: 192.168.5.58 (最好是同一ip段,不然肯定会出现网络问题)
主机用户:具有sudo权限的test

2.mysql主主搭建
由于分配的虚拟机只有一个系统需要安装各种系统软件,磁盘也需要自己挂在
2.1磁盘分区
[test@host-192-168-5-235 ~]$sudo fdisk -l

Disk /dev/vda: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000adb11

   Device Boot      Start         End      Blocks   Id  System
/dev/vda1   *        2048     1026047      512000   83  Linux
/dev/vda2         1026048     9414655     4194304   82  Linux swap / Solaris
/dev/vda3         9414656    41943039    16264192   83  Linux

Disk /dev/vdb: 536.9 GB, 536870912000 bytes, 1048576000 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

[yaiuap_cj@host-192-168-5-236 ~]$ sudo fdisk  /dev/vdb
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xf2a1312e.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-1048575999, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-1048575999, default 1048575999):
Using default value 1048575999
Partition 1 of type Linux and of size 500 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

分区格式化
[test@host-192-168-5-235 /]$ sudo mkfs -t ext3 /dev/vdb1
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
32768000 inodes, 131071744 blocks
6553587 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
4000 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
        102400000

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

创建目录并挂载
[test@host-192-168-5-235 /]$ sudo mkdir /data
[test@host-192-168-5-235 ~]$ sudo mount /dev/vdb1 /data  

开机自动挂载按照里边的格式增加
sudo vi /etc/fstab

2.2 mysql配置node1和node2相同的操作

查看
rpm -qa | grep -i mysql
rpm -qa | grep mariadb
删除(查出来的一个个全删了)
sudo rpm -e mariadb-libs-5.5.56-2.el7.x86_64 –nodeps
解压mysql安装包
[test@host-192-168-5-236 ~]$ tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

将目录属主和组更改为test为了方便并赋予777权限
sudo chown test:test -R /data/
chmod 777 /data/
cd  /data/
mkdir  mysql
cd /data/mysql/
创建数据目录,日志目录,pid目录
mkdir data logs run

将mysql软件放在/usr/local/下
cd /usr/local/
sudo mkdir mysql
sudo chown test:test ./mysql/
cd /data
mv mysql-5.7.26-linux-glibc2.12-x86_64/* /usr/local/mysql/
删除空目录
rm mysql-5.7.26-linux-glibc2.12-x86_64/

编辑mysql配置文件node1
sudo vi /etc/my.cnf
[mysqld]
port=9060
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock

server-id=1            
log-bin=mysql-bin
symbolic-links=0
[mysqld_safe]
log-error=/data/mysql/logs/mysql.log
pid-file=/data/mysql/run/mysql.pid
[client]
default-character-set=utf8
socket=/data/mysql/data/mysql.sock

[mysql]
default-character-set=utf8
socket=/data/mysql/data/mysql.sock

vi .bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin
export PATH
source .bash_profile
初始化
mysqld --initialize --user=test --datadir=/data/mysql/data

安全启动:
mysqld_safe --user=test &

用初始化生成的root密码登录并更改密码
mysql -uroot -p

set password=password("123456");
flush privileges;


编辑mysql配置文件node2
sudo vi /etc/my.cnf
[mysqld]
port=9060
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock

server-id=2            
log-bin=mysql-bin
symbolic-links=0
[mysqld_safe]
log-error=/data/mysql/logs/mysql.log
pid-file=/data/mysql/run/mysql.pid
[client]
default-character-set=utf8
socket=/data/mysql/data/mysql.sock

[mysql]
default-character-set=utf8
socket=/data/mysql/data/mysql.sock

vi .bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin
export PATH
source .bash_profile
初始化
mysqld --initialize --user=test --datadir=/data/mysql/data

安全启动:
mysqld_safe --user=test &

用初始化生成的root密码登录并更改密码
mysql -uroot -p

set password=password("123456");
flush privileges;


配置主从
主节点(192.168.5.235)
创建同步用户
CREATE USER 'sync'@'%' IDENTIFIED  WITH mysql_native_password BY 'sync@123456';
GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';
flush privileges;
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      997 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

备节点(192.168.5.236)
CHANGE MASTER TO MASTER_HOST='192.168.5.235', MASTER_USER='sync', MASTER_PASSWORD='sync@2019#Zy',MASTER_PORT=9060,MASTER_LOG_FILE='mysql-bin.000002',  MASTER_LOG_POS=997;  
start slave;
# 停止 stop slave
# 重置 reset slave

mysql> show slave status\G

反过来配置一遍
原备节点(192.168.5.236)
创建同步用户
CREATE USER 'sync'@'%' IDENTIFIED  WITH mysql_native_password BY 'sync@123456';
GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';
flush privileges;
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      997 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
原主节点(192.168.5.235)

CHANGE MASTER TO MASTER_HOST='192.168.5.236', MASTER_USER='sync', MASTER_PASSWORD='sync@2019#Zy',MASTER_PORT=9060,MASTER_LOG_FILE='mysql-bin.000002',  MASTER_LOG_POS=997;  
start slave;
# 停止 stop slave
# 重置 reset slave

mysql> show slave status\G
看到两个YES,代表主主成功
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


赋予root用户远程访问(为了远程访问root用户)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'otn@2019#zy';
flush privileges;


测试:

创建数据库
create database test;
创建普通用户
CREATE USER 'test'@'%' IDENTIFIED  WITH mysql_native_password BY '123456';
grant all privileges on test.* to 'test'@'%';
grant all privileges on mysql.* to 'test'@'%';
flush privileges;
查看数据库
show databases;
查看用户
select user,host from mysql.user;
创建表
create table testa( Id varchar(100));
两边都能看到testa表
show tables;

插入语句
insert into testa values('1231');
insert into testa values('4567');
insert into testa values('5464');
另一个数据库都能看到
select * from testa;

delete from  testa  where Id='1231';
另一个数据库数据显也被删除

至此,mysql主主已经完全配置成功。

3.Keepalived安装
安装相关的系统环境(必须是root用户或者sudo用户)
yum -y install gcc openssl-devel openssl ipvsadm
yum -y install libnl libnl-devel    (支持ipv6)

[test@host-192-168-5-235 ~]$ tar -xvf keepalived-2.0.13.tar.gz
cd keepalived-2.0.13
sudo ./configure  --prefix=/usr/local/keepalived  --安装到/usr/local/keepalived
sudo make && sudo make install


cd /etc
mkdir keepalived
sudo cp -r /data/keepalived-2.0.13/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
sudo cp -r /data/keepalived-2.0.13/keepalived/etc/init.d /etc/rc.d/init.d/keepalived
sudo cp /data/keepalived-2.0.13/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived



keepalived.conf 配置内容:#清空默认内容,直接采用下面配置
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {                                        #全局配置标识,表明这个区域{}是全局配置
   notification_email {
        xxx@xxx                           #表示发送通知邮件时邮件源地址是谁
   }
   notification_email_from xxx@xxx     #表示keepalived在发生诸如切换操作时需要发送email通知,以及email发送给哪些邮件地址,邮件地址可以多个,每行一个notification_email_from xxx@xxx
   smtp_server 127.0.0.1                             #表示发送email时使用的smtp服务器地址,这里可以用本地的sendmail来实现
   smtp_connect_timeout 30                           #连接smtp连接超时时间
   router_id host-192-168-5-235                      #机器标识
   vrrp_skip_check_adv_addr
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_mysql_port {          #检测mysql服务是否在运行。有很多方式,比如进程,用脚本检测等等
    script "/root/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
    interface eth0                  #主机网卡     
    mcast_src_ip 192.168.5.235      #主机ip
    virtual_router_id 35            #路由器标识,MASTER和BACKUP必须是一致的
    priority 101                    #定义优先级,数字越大,优先级越高,在同一个vrrp_instance下,MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后,就可以将VIP资源再次抢回来
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
    192.168.5.58
    }
    track_script {
     chk_mysql_port
    }
}




bakcup主机上的keepalived配置

vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
       
global_defs {
   notification_email {
     xxx@xxxx
   }
   notification_email_from xxx@xxxx
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id host-192-168-5-236
   vrrp_skip_check_adv_addr
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
       
vrrp_script chk_mysql_port {
    script "/root/chk_mysql.sh"
    interval 2            
    weight -5                 
    fall 2                 
    rise 1               
}
       
vrrp_instance VI_1 {
    state BACKUP
    interface eth0    
    mcast_src_ip 192.168.5.236
    virtual_router_id 35
    priority 99          
    advert_int 1         
    authentication {   
        auth_type PASS
        auth_pass 1111     
    }
    virtual_ipaddress {    
       192.168.5.58
    }
      
track_script {               
   chk_mysql_port             
}
}


[test@host-192-168-5-227 data]$ cd /root
[test@host-192-168-5-227 root]$ vi chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "9060"|wc -l)
if [ "${counter}" -eq 0 ]; then
service keepalived stop
fi



启动
shell> sudo systemctl enable keepalived.service #设置开机自动启动
shell> sudo service keepalived start   #启动服务
shell> sudo service keepalived stop    #停止服务
shell> sudo service keepalived restart #重启服务


验证登录
mysql -h192.168.5.58 -P9060 -uroot -p123456   可以登录


mysql -h192.168.5.235 -P9060 -uroot -p123456  

mysql -h192.168.5.236 -P9060 -uroot -p123456

遇到的问题
此时发现问题使用vip在主节点可以但是再备节点和其他同段的主机不能正常访问,
在备节点(或其他节点)ping  vip 只能ping通9次
初步怀疑是网络问题,但是网络侧那边说限制放开了
我测试ping通9次,vip对应hwaddress没有获取我手动添加就能ping通,理论上应该是自动获取的,
手动添加没有意义,如果vip漂移了那还是不能访问了
最后折腾了两周在自己虚拟机同样的配置访问都没有问题,
这次找到虚拟化的同事咨询这个问题,他们从底层放开网络限制好了




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30349701/viewspace-2671854/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-07-15

  • 博文量
    3
  • 访问量
    5300