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

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

[test@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)

备节点（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 -h292.168.5.58 -P9060 -uroot -p123456   可以登录
mysql -h292.168.5.235 -P9060 -uroot -p123456 
mysql -h292.168.5.236 -P9060 -uroot -p123456

MySQL启动与关闭

1、查看mysql服务的两种方式

[root@localhost bin]ps -ef|grep mysql

[root@localhost bin]netstat -nlp

2、启动服务的两种方式

命令行方式

[root@localhost bin]cd /usr/bin

[root@localhost bin]./mysqld_safe &

服务方式

[root@localhost ~]service mysql start

如果服务在启动状态，直接重启服务用以下命令：

[root@localhost ~]service mysql restart

3、关闭服务的两种方式

命令行方式：

[ root@localhost ~]mysqladmin -u root shutdown

服务方式：

[root@localhost ~]service mysql stop

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