|
目录
一、环境准备
二、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 |
2.虚拟VIP
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配置文件的网卡类型, 修改后重启访问成功.
| 
转播
