Centos7安装mysql+keepalived 高可用环境[通俗易懂]

全栈程序员站长

发布于 2022-09-06 11:46:20

60400

代码可运行

文章被收录于专栏：全栈程序员必看全栈程序员必看

运行总次数：0

代码可运行

大家好，又见面了，我是你们的朋友全栈君。

一、环境准备

二、mysql下载和安装

三、keepalived下载和安装

四、配置mysql双主+keepalived 高可用环境

五、遇到的问题及解决方法

一、环境准备

1.节点信息

节点IP	节点名称	系统	软件及版本
192.168.51.187	node187	CentOS 7	keepalived-1.3.5 mysql-5.7.24
192.168.51.226	node226	CentOS 7	keepalived-1.3.5 mysql-5.7.24

2.虚拟VIP

虚拟VIP	192.168.51.170

3.初始化, 在两个节点上进行常用工具的安装

yum install gcc gcc-c++ vim-enhanced glibc make unzip openssl openssl-devel openssh-server openssh-clients wget -y

二、mysql下载和安装

* 除标明特定节点执行外, 其他步骤均在两个节点执行, 在两个节点安装mysql

1.官网下载

链接:https://downloads.mysql.com/archives/community/

选择版本并下载

2.检查是否已经安装了mysql或者卸载系统自带的Mariadb，如果已经安装，则先把安装的卸载

[root@node187 ~]# rpm -qa | grep mysql
[root@node187 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64
[root@node187 ~]# yum -y remove mariadb-libs-5.5.64-1.el7.x86_64

3.将安装包传输至服务器, 并解压

[root@node187 home]# tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
重命名mysql安装目录
[root@node187 home]# mv mysql-5.7.24-linux-glibc2.12-x86_64  mysql

4.创建新用户组和用户

[root@node187 home]# groupadd mysql
禁止mysql登陆
[root@node187 home]# useradd -s /sbin/nologin mysql

5.在mysql下新建data目录和log目录

[root@node187 home]# cd mysql
[root@node187 mysql]# mkdir data
[root@node187 mysql]# mkdir log

6.将mysql文件夹的拥有权限改为mysql用户

[root@node187 mysql]# chown -R mysql:mysql ./

7.数据库初始化, 并记住初始化密码

[root@node187 mysql]# ./bin/mysqld --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data --initialize

8.创建my.cnf配置文件, 添加如下配置

在节点一中修改配置文件

[root@node187 mysql]# vim /etc/my.cnf
[mysql]
default-character-set=utf8
[mysqld]
skip-name-resolve
port = 3306
basedir=/home/mysql
datadir=/home/mysql/data
socket=/home/mysql/mysql.sock
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1 #不区分大小写
max_allowed_packet=100M

# 开启ip绑定
bind-address = 0.0.0.0
server-id = 1         
log-bin = mysql-bin     
#sync_binlog = 1
#binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 1 

[mysqld_safe]
log-error=/home/mysql/log/mysqld.log
pid-file=/home/mysql/data/mysqld.pid
#指定客户端连接mysql时的socket通信文件路径
[client]
socket=/home/mysql/mysql.sock
default-character-set=utf8

在节点二中修改配置文件

[root@node226 mysql]# vim /etc/my.cnf
[mysql]
default-character-set=utf8
[mysqld]
skip-name-resolve
port = 3306
basedir=/home/mysql
datadir=/home/mysql/data
socket=/home/mysql/mysql.sock
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1 #不区分大小写
max_allowed_packet=100M

# 开启ip绑定
bind-address = 0.0.0.0
server-id = 2        
log-bin = mysql-bin    
#sync_binlog = 1
#binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 2    
slave-skip-errors = all

[mysqld_safe]
log-error=/home/mysql/log/mysqld.log
pid-file=/home/mysql/data/mysqld.pid
#指定客户端连接mysql时的socket通信文件路径
[client]
socket=/home/mysql/mysql.sock
default-character-set=utf8

9.将mysql添加至开机启动

[root@node187 mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld

修改mysqld以下代码部分

[root@node187 mysql]# vim /etc/init.d/mysqld 
修改以下内容
basedir=/home/mysql
datadir=/home/mysql/data

设置开机启动

[root@node187 mysql]# chkconfig --add mysqld

10.添加环境变量

[root@node187 mysql]# vim  /etc/profile
添加以下内容
export PATH=$PATH:/home/mysql/bin
使配置文件立即生效
[root@node187 mysql]# source /etc/profile

11.启动服务

[root@node187 mysql]# service mysqld start

12.登录mysql, 初始密码为上述第7步红色框选部分

[root@node187 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

13.修改mysql数据库的root用户的密码, 此处根据自己需求设置, 本例中密码为root.

mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

14. 此时使用数据库连接工具连接数据库报错, 需进行以下设置

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set user.Host='%' where user.User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

测试正常连接,至此数据库安装完成

15.配置mysql主主

配置节点一mysql 主主

[root@node187 mysql]# systemctl stop mysqld
[root@node187 mysql]# systemctl start mysqld
登陆mysql 创建同步用户
[root@node187 mysql]# mysql  -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log MySQL Community Server (GPL)

mysql> grant replication slave,replication client on *.* to repl@'192.168.51.%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#锁表，查看binlog日志节点 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      622 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

记录下 mysql-bin.000001 和 622

配置节点二mysql主主

[root@node226 mysql]# systemctl stop mysqld
[root@node226 mysql]# systemctl start mysqld
登陆mysql 创建同步用户
[root@node226 data]# mysql  -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log MySQL Community Server (GPL)

mysql> grant replication slave,replication client on *.* to repl@'192.168.51.%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#锁表，查看binlog日志节点 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      622 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

记录下 mysql-bin.000001 和622

16.开启同步

开启节点一

#解锁
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change  master to master_host='192.168.51.226',master_user='repl',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=622;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

#查看主从状态 如下图两个Yes表示主从正常
mysql> show slave status \G;

开启节点二

#解锁
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change  master to master_host='192.168.51.187',master_user='repl',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=622;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

#查看主从状态  如下图两个Yes表示主从正常
mysql> show slave status \G;

17.测试

此时已经实现了mysql主主同步，可以在两个数据库里创建表来验证一下是否相互同步。

在节点一上创建表

在节点二上查看表

三、keepalived下载和安装

1.keepalived简介

官网地址: https://www.keepalived.org/

安装参考: https://www.keepalived.org/doc/installing_keepalived.html

2.下载并安装keepalived (两个节点均安装keepalived)

[root@node187 ~]# yum install -y openssl-devel
[root@node187 ~]# cd /home
[root@node187 home]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[root@node187 home]# tar -zvxf keepalived-1.3.5.tar.gz
[root@node187 home]# cd keepalived-1.3.5
[root@node187 keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived
[root@node187 keepalived-1.3.5]# make && make install
     
[root@node187 keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@node187 keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@node187 keepalived-1.3.5]# mkdir /etc/keepalived/
[root@node187 keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@node187 keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@node187 keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local
 
更改PIDFile地址，不然启动会报错
[root@node187 keepalived-1.3.5]#vi /lib/systemd/system/keepalived.service
[Unit]
Description=LVS and VRRP High Availability Monitor
After=syslog.target network-online.target
 
[Service]
Type=forking
PIDFile=/var/run/keepalived.pid
KillMode=process
EnvironmentFile=-/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID
 
[Install]
WantedBy=multi-user.target

四、配置mysql双主+keepalived 高可用环境

1.节点一 keepalived.conf配置修改主机IP并设置虚拟IP, 并启动keepalived

[root@node187 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@node187 local]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
       
global_defs {
notification_email {
dzp@126.com   #邮箱随便写，这里没配置发送邮件
}
       
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {     #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/opt/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        
    state BACKUP     
    nopreempt    #非抢占模式
    interface enp0s3      #指定虚拟ip的网卡接口, 通过ifconfig查看以确定
    mcast_src_ip 192.168.51.187 
    virtual_router_id 51    #路由器标识，MASTER和BACKUP必须是一致的
    priority 101            #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级 
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.51.170
    }
      
track_script {               
   chk_mysql_port             
}
}
 
#编写心跳检测脚本
[root@node187 ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi
 
[root@node187 ~]# chmod 755 /opt/chk_mysql.sh
     
启动keepalived服务
[root@node187 ~]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]

2.节点二 keepalived.conf配置修改主机IP并设置虚拟IP, 并启动keepalived

[root@node226 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@node226 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
       
global_defs {
notification_email {
dzp@126.com
}
       
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {
    script "/opt/chk_mysql.sh"
    interval 2            
    weight -5                 
    fall 2                 
    rise 1               
}
       
vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface enp0s3
    mcast_src_ip 192.168.51.226
    virtual_router_id 51    
    priority 99          
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.51.170
    }
      
track_script {               
   chk_mysql_port             
}
}
#编写心跳检测脚本
[root@node226 ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi
 
[root@node226 ~]# chmod 755 /opt/chk_mysql.sh
     
启动keepalived服务
[root@node226 ~]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]

3.测试

(1)通过VIP连接，看是否连接成功。通过客户端和Navicat、以及在项目中配置均连接成功

mysql -uroot -proot -h192.168.51.170

(2)关闭vip所在机器的mysql服务，观察vip是否转移

通过命令可以看出, 当前VIP在节点一上

节点一
[root@node187 keepalived-1.3.5]# ip addr | grep 192.168
    inet 192.168.51.187/24 brd 192.168.51.255 scope global noprefixroute dynamic enp0s3
    inet 192.168.51.170/32 scope global enp0s3
[root@node187 keepalived-1.3.5]# 

节点二
[root@node226 keepalived-1.3.5]# ip addr | grep 192.168
    inet 192.168.51.226/24 brd 192.168.51.255 scope global noprefixroute dynamic enp0s3
[root@node226 keepalived-1.3.5]#

停止节点一上的mysql服务，根据配置中的脚本，mysql服务停了，keepalived也会停，从而vip资源将会切换到节点二上。（mysql服务没有起来的时候，keepalived服务也无法顺利启动！）

节点一
[root@node187 opt]# systemctl stop mysqld
[root@node187 opt]# netstat -na|grep "LISTEN"|grep "3306"|wc -l
0
[root@node187 opt]# ps -ef|grep mysql
root      1783 29952  0 10:49 pts/0    00:00:00 grep --color=auto mysql
[root@node187 opt]# ps -ef|grep keepalived
root      1647 29952  0 10:40 pts/0    00:00:00 grep --color=auto keepalived
[root@node187 opt]# ip addr | grep 192.168
    inet 192.168.51.187/24 brd 192.168.51.255 scope global noprefixroute dynamic enp0s3

节点二
[root@node226 ~]# ip addr | grep 192.168
    inet 192.168.51.226/24 brd 192.168.51.255 scope global noprefixroute dynamic enp0s3
    inet 192.168.51.170/32 scope global enp0s3
[root@node226 ~]# ps -ef|grep keepalived
root     18963     1  0 10:36 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root     18964 18963  0 10:36 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root     18965 18963  0 10:36 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root     20988 17221  0 10:44 pts/0    00:00:00 grep --color=auto keepalived
[root@node226 ~]# ps -ef|grep mysql
root     18594     1  0 10:36 ?        00:00:00 /bin/sh /home/mysql/bin/mysqld_safe --datadir=/home/mysql/data --pid-file=/home/mysql/data/node226.pid
mysql    18917 18594  0 10:36 ?        00:00:00 /home/mysql/bin/mysqld --basedir=/home/mysql --datadir=/home/mysql/data --plugin-dir=/home/mysql/lib/plugin --user=mysql --log-error=/home/mysql/log/mysqld.log --pid-file=/home/mysql/data/node226.pid --socket=/home/mysql/mysql.sock --port=3306
root     21004 17221  0 10:44 pts/0    00:00:00 grep --color=auto mysql

(3)重新开启mysql和keepalived 需要先开启mysql,keepalived才能顺利启动

五、遇到的问题及解决方法

1.描述: 因为没有路径也没有权限，所以创建此路径并授权给mysql用户

[root@node226 mysql]# service mysqld start
Starting MySQL.2020-12-17T08:53:50.054618Z mysqld_safe error: log-error set to '/home/mysql/log/mysqld.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/home/mysql/data/node226.pid).

解决方法: 创建log目录, 并进行授权

mkdir log

chown -R mysql:mysql /home/mysql

2.报错

[root@node187 mysql]# systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: failed (Result: exit-code) since 五 2020-12-18 12:29:39 CST; 50min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 19580 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 21732 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/FAILURE)

12月 18 12:27:56 node187 systemd[1]: Starting LSB: start and stop MySQL...
12月 18 12:28:29 node187 mysqld[21732]: Starting MySQL................................./etc/rc.d/init.d/mysqld: 行 146: 22117 已杀死               sleep 1
12月 18 12:29:39 node187 systemd[1]: mysqld.service: control process exited, code=exited status=1
12月 18 12:29:39 node187 mysqld[21732]: ..................................................................... ERROR! The server quit without updating PID file (/home/mysql/d...mysqld.pid).
12月 18 12:29:39 node187 systemd[1]: Failed to start LSB: start and stop MySQL.
12月 18 12:29:39 node187 systemd[1]: Unit mysqld.service entered failed state.
12月 18 12:29:39 node187 systemd[1]: mysqld.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

解决方法:

加入配置
[mysqld_safe]
log-error=/home/mysql/log/mysqld.log
pid-file=/home/mysql/data/mysqld.pid

3.描述: 安装keepalived时报错

configure: error: 
  !!! OpenSSL is not properly installed on your system. !!!
  !!! Can not include OpenSSL headers files.            !!!
[root@node226 keepalived-1.3.5]# make && make install
make: *** 没有指明目标并且找不到 makefile。 停止。

解决方法:安装openssl-devel

yum -y install openssl-devel

4.描述: 通过VIP无法访问mysql服务

[root@node226 keepalived-1.3.5]# mysql -uroot -proot -h192.168.51.170
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.51.170' (113)

解决方法: 经排查,发现网卡类型设置错误, 通过ifconfig确认网卡类型, 修改keepalived配置文件的网卡类型, 修改后重启访问成功.

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/134704.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022年6月6，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server