mysql主备搭建_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 -h192.168.5.58 -P9060 -uroot -p123456   可以登录

mysql -h192.168.5.235 -P9060 -uroot -p123456

mysql -h192.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漂移了那还是不能访问了

最后折腾了两周在自己虚拟机同样的配置访问都没有问题，

这次找到虚拟化的同事咨询这个问题，他们从底层放开网络限制好了