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

mysql + keepalived環(huán)境搭建

1.環(huán)境準備
系統(tǒng)：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段，不然肯定會出現(xiàn)網(wǎng)絡(luò)問題）
主機用戶：具有sudo權(quán)限的test

2.mysql主主搭建
由于分配的虛擬機只有一個系統(tǒng)需要安裝各種系統(tǒng)軟件，磁盤也需要自己掛在
2.1磁盤分區(qū)
[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.

分區(qū)格式化
[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

創(chuàng)建目錄并掛載
[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權(quán)限
sudo chown test:test -R /data/
chmod 777 /data/
cd  /data/
mkdir  mysql
cd /data/mysql/
創(chuàng)建數(shù)據(jù)目錄，日志目錄，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;

配置主從
主節(jié)點（192.168.5.235）
創(chuàng)建同步用戶
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)

備節(jié)點（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

反過來配置一遍
原備節(jié)點（192.168.5.236）
創(chuàng)建同步用戶
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)
原主節(jié)點（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;

測試：
創(chuàng)建數(shù)據(jù)庫
create database test;
創(chuàng)建普通用戶
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;
查看數(shù)據(jù)庫
show databases;
查看用戶
select user,host from mysql.user;
創(chuàng)建表
create table testa( Id varchar(100));
兩邊都能看到testa表
show tables;
插入語句
insert into testa values('1231');
insert into testa values('4567');
insert into testa values('5464');
另一個數(shù)據(jù)庫都能看到
select * from testa;
delete from  testa  where Id='1231';
另一個數(shù)據(jù)庫數(shù)據(jù)顯也被刪除
至此，mysql主主已經(jīng)完全配置成功。

3.Keepalived安裝
安裝相關(guān)的系統(tǒng)環(huán)境（必須是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 配置內(nèi)容：＃清空默認內(nèi)容，直接采用下面配置
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {                                        #全局配置標識，表明這個區(qū)域{}是全局配置
   notification_email {
        xxx@xxx                           #表示發(fā)送通知郵件時郵件源地址是誰
   }
   notification_email_from xxx@xxx     #表示keepalived在發(fā)生諸如切換操作時需要發(fā)送email通知，以及email發(fā)送給哪些郵件地址，郵件地址可以多個，每行一個notification_email_from xxx@xxx
   smtp_server 127.0.0.1                             #表示發(fā)送email時使用的smtp服務(wù)器地址，這里可以用本地的sendmail來實現(xiàn)
   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服務(wù)是否在運行。有很多方式，比如進程，用腳本檢測等等
    script "/root/chk_mysql.sh"        #這里通過腳本監(jiān)測
    interval 2                       #腳本執(zhí)行間隔，每2s檢測一次
    weight -5                        #腳本結(jié)果導(dǎo)致的優(yōu)先級變更，檢測失?。_本返回非0）則優(yōu)先級 -5
    fall 2                          #檢測連續(xù)2次失敗才算確定是真失敗。會用weight減少優(yōu)先級（1-255之間）
    rise 1                         #檢測1次成功就算成功。但不修改優(yōu)先級
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0                  #主機網(wǎng)卡     
    mcast_src_ip 192.168.5.235      #主機ip
    virtual_router_id 35            #路由器標識，MASTER和BACKUP必須是一致的
    priority 101                    #定義優(yōu)先級，數(shù)字越大，優(yōu)先級越高，在同一個vrrp_instance下，MASTER的優(yōu)先級必須大于BACKUP的優(yōu)先級。這樣MASTER故障恢復(fù)后，就可以將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 #設(shè)置開機自動啟動
shell> sudo service keepalived start   #啟動服務(wù)
shell> sudo service keepalived stop    #停止服務(wù)
shell> sudo service keepalived restart #重啟服務(wù)

驗證登錄
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啟動與關(guān)閉

1、查看mysql服務(wù)的兩種方式

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

[root@localhost bin]netstat -nlp

2、啟動服務(wù)的兩種方式

命令行方式

[root@localhost bin]cd /usr/bin

[root@localhost bin]./mysqld_safe &

服務(wù)方式

[root@localhost ~]service mysql start

如果服務(wù)在啟動狀態(tài)，直接重啟服務(wù)用以下命令：

[root@localhost ~]service mysql restart

3、關(guān)閉服務(wù)的兩種方式

命令行方式：

[ root@localhost ~]mysqladmin -u root shutdown

服務(wù)方式：

[root@localhost ~]service mysql stop

遇到的問題
此時發(fā)現(xiàn)問題使用vip在主節(jié)點可以但是再備節(jié)點和其他同段的主機不能正常訪問，
在備節(jié)點（或其他節(jié)點）ping  vip 只能ping通9次
初步懷疑是網(wǎng)絡(luò)問題，但是網(wǎng)絡(luò)側(cè)那邊說限制放開了
我測試ping通9次，vip對應(yīng)hwaddress沒有獲取我手動添加就能ping通，理論上應(yīng)該是自動獲取的，
手動添加沒有意義，如果vip漂移了那還是不能訪問了
最后折騰了兩周在自己虛擬機同樣的配置訪問都沒有問題，
這次找到虛擬化的同事咨詢這個問題，他們從底層放開網(wǎng)絡(luò)限制好了