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漂移了那还是不能访问了
最后折腾了两周在自己虚拟机同样的配置访问都没有问题，
这次找到虚拟化的同事咨询这个问题，他们从底层放开网络限制好了
