Keepalived+MySQL 8.0.17 实现MySQL双主的高可用
一、系统环境
主机：两台CentOS7.6虚拟机
IP地址：master01(IP：192.168.27.47)，master02(IP：192.168.27.57)，VIP：192.168.27.100
软件：mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz，Keepalived(yum源)
二、实现过程
2.1、实现MySQL的安装配置
(1) 下载MySQL的安装包，下载地址: https://downloads.mysql.com/archives/community/
[ ~]# ll mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz -rw-r--r-- 1 root root 480209016 May 9 11:25 mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz [-master02 ~]# ll mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz -rw-r--r-- 1 root root 480209016 May 9 11:25 mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz
(2) 两台主机都创建mysql用户，以master01为例
[ ~]# useradd -r -s /sbin/nologin mysql
(3) 解压MySQL安装包，创建软链接
[ ~]# tar -Jxvf mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz -C /usr/local/ [-master01 ~]# ln -s /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64 /usr/local/mysql
(4) 初始化数据库
[ ~]# cd /usr/local/mysql [-master01 mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/data/mysql 2020-05-11T02:55:47.592126Z 0 [Warning] [MY-011070] [Server] ‘Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it‘ is deprecated and will be removed in a future release. 2020-05-11T02:55:47.673278Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 56424 2020-05-11T02:56:00.647204Z 5 [Note] [MY-010454] [Server] A temporary password is generated for : W:66pBoq;hkF #root连接数据库的初始密码 2020-05-11T02:56:04.547704Z 0 [System] [MY-013170] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server has completed [-master02 mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/data/mysql 2020-05-11T02:56:54.701359Z 0 [Warning] [MY-011070] [Server] ‘Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it‘ is deprecated and will be removed in a future release. 2020-05-11T02:56:54.780973Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 56410 2020-05-11T02:57:07.447552Z 5 [Note] [MY-010454] [Server] A temporary password is generated for : <ga,2Yf&2:qA #root连接数据库的初始密码 2020-05-11T02:57:11.244150Z 0 [System] [MY-013170] [Server] /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.17) initializing of server has completed
(5) 修改配置文件 /etc/my.cnf
[ mysql]# vim /etc/my.cnf
[mysqld]
port=3306
datadir=/data/mysql
socket=/data/mysql/mysql.sock
character-set-server=utf8mb4
innodb_file_per_table=on
skip_name_resolve=on
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysql.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[client]
socket=/data/mysql/mysql.sock
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
#创建日志目录与文件，防止启动报错
[-master01 ~]# mkdir /data/mysql/{log,run}
[-master01 ~]# touch /data/mysql/log/mysql.log
[-master01 ~]# chown -R mysql:mysql /data/mysql(7) 准备启动脚本，并配置环境变量
[ mysql]# cp support-files/mysql.server /etc/init.d/mysqld [-master01 mysql]# chkconfig --add mysqld [-master01 mysql]# echo ‘PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh [-master01 mysql]# . /etc/profile.d/mysql.sh
(8) 启动数据库服务，并修改root用户密码
[ ~]# service mysqld start Starting MySQL........... SUCCESS! [-master01 mysql]# mysqladmin -uroot -p"W:66pBoq;hkF" password ‘123456‘ #修改密码方式一，不安全，不推荐 [-master02 ~]# mysql -u root -p Enter password: #输入mysql-master02的数据库密码：<ga,2Yf&2:qA Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.17 Copyright (c) 2000, 2019, 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> alter user ‘root‘@‘localhost‘ identified by ‘123456‘; #修改密码方式二 Query OK, 0 rows affected (0.03 sec)
2.2、实现MySQL的双主配置
(1) 修改mysql-master01的配置文件，可以直接修改 /etc/my.cnf 文件，也可以在 /etc/my.cnf.d/ 下新建一个配置文件
[ ~]# vim /etc/my.cnf.d/mysql-master.cnf [mysqld] server_id = 1 log-bin = /data/mysql/log/log_bin binlog-format = ROW #指定二进制格式 relay-log = /data/mysql/log/relay-bin relay-log-index = relay-bin.index auto_increment_offset = 1 #起始值，一般填写第n台主机mysql auto_increment_increment = 2 #步进值auto_imcrement 。一般有n台主mysql就填n #binlog_do_db = DBNAME #设置要同步的数据库 #replicate-ignore-db = test #设置不同步的数据库 sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
(2) 修改mysql-master02的配置文件
[ ~]# vim /etc/my.cnf.d/mysql-master.cnf [mysqld] server_id = 2 log-bin = /data/mysql/log/log_bin binlog-format = ROW relay-log = /data/mysql/log/relay-bin relay-log-index = relay-bin.index auto_increment_offset = 2 auto_increment_increment = 2 #binlog_do_db = DBNAME #replicate-ignore-db = test sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
(3) 在mysql-master01与mysql-master02上分别创建授权用户
[ ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.17 MySQL Community Server - GPL mysql> create user ‘repl‘@‘192.168.27.%‘ identified with mysql_native_password by ‘repl123456‘; Query OK, 0 rows affected (0.04 sec) mysql> grant replication slave on *.* to ‘repl‘@‘192.168.27.%‘; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | log_bin.000001 | 862 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #以下为mysql-master02上的操作 [-master02 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.17 MySQL Community Server - GPL mysql> create user ‘repl‘@‘192.168.27.%‘ identified with mysql_native_password by ‘repl123456‘; Query OK, 0 rows affected (0.05 sec) mysql> grant replication slave on *.* to ‘repl‘@‘192.168.27.%‘; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | log_bin.000001 | 862 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
(4) 在mysql-master02上配置同步信息
mysql> change master to
-> master_host=‘192.168.27.47‘,
-> master_user=‘repl‘,
-> master_password=‘repl123456‘,
-> master_log_file=‘log_bin.000001‘,
-> master_log_pos=862;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave; #启动slave同步进程
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G; #查看slave状态，看是否有错误(5) 在mysql-master01上配置同步信息
mysql> change master to
-> master_host=‘192.168.27.57‘,
-> master_user=‘repl‘,
-> master_password=‘repl123456‘,
-> master_log_file=‘log_bin.000001‘,
-> master_log_pos=862;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G;(6) 测试同步
#在mysql-master01创建一个库testA mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.06 sec) mysql> create database testA; Query OK, 1 row affected (0.03 sec) #在mysql-master02查看，可以看到同步了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testA | +--------------------+ 5 rows in set (0.06 sec) #再在mysql-master02创建一个库testB mysql> create database testB; Query OK, 1 row affected (0.03 sec) #在mysql-master01上查看，可以看到也同步了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testA | | testB | +--------------------+ 6 rows in set (0.01 sec)
2.3、实现MySQL双主的高可用配置
(1) 两台主机都安装Keepalived服务，此处使用光盘自带yum源安装
[ ~]# yum install -y keepalived [-master02 ~]# yum install -y keepalived
(2) 修改mysql-master01上的keepalived配置
[ ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
}
notification_email_from
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id master01
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
#定义检查脚本
vrrp_script check_mysql_status {
script "/etc/keepalived/chk_mysql.sh"
interval 2
weight -50
fall 3
rise 5
timeout 3
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.27.100/24 dev eth0 label eth0:0
}
track_script {
check_mysql_status
}
}(3) 修改mysql-master02上的keepalived配置
[ ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
}
notification_email_from
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id master02
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script check_mysql_status {
script "/etc/keepalived/chk_mysql.sh"
interval 2
weight -50
fall 3
rise 5
timeout 3
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 80
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.27.100/24 dev eth0 label eth0:0
}
track_script {
check_mysql_status
}
}(4) 编写检查脚本，两台主机都一样
[ ~]# vim /etc/keepalived/chk_mysql.sh
#!/bin/bash
mysqlcmd="/usr/local/mysql/bin/mysql"
user="root"
password="123456"
$mysqlcmd -u$user -p$password -e "show status;" &> /dev/null
if [ $? -eq 0 ];then
echo "mysql_status=1"
exit 0
else
/usr/bin/systemctl stop keepalived
fi
[-master01 ~]# chmod +x /etc/keepalived/chk_mysql.sh
[-master01 ~]# scp /etc/keepalived/chk_mysql.sh 192.168.27.57:/etc/keepalived/(5) 启动两台主机的keepalived服务，并设为开机启动
[ ~]# systemctl start keepalived [-master01 ~]# systemctl enable keepalived [-master02 ~]# systemctl start keepalived [-master02 ~]# systemctl enable keepalived
(6) 测试
#刚开始时，VIP在mysql-master01上
[-master01 ~]# ip a|grep 192.168.27.100
inet 192.168.27.100/24 scope global secondary eth0:0
#关闭mysql-master01上的keepalived，VIP也转移到了mysql-master02上
[-master01 ~]# systemctl stop keepalived
[-master01 ~]# ip a|grep 192.168.27.100
[-master02 keepalived]# ip a|grep 192.168.27.100
inet 192.168.27.100/24 scope global secondary eth0:0
#重新启动mysql-master01的keepalived，可以看到VIP还是在mysql-master02上，因为是非抢占模式，是正确的
[-master01 ~]# systemctl start keepalived
[-master01 ~]# ip a|grep 192.168.27.100
[-master02 keepalived]# ip a|grep 192.168.27.100
inet 192.168.27.100/24 scope global secondary eth0:0
#接着，关闭mysql-master02上mysql服务，制造mysql故障
#此时，mysql-master02上的keepalived服务将被脚本控制关闭了，并且VIP应该转移至mysql-master01上
[-master02 ~]# service mysqld stop
Shutting down MySQL.......... SUCCESS!
[-master02 ~]# ip a|grep 192.168.27.100
[-master02 ~]# ps -ef|grep keepalived
root 10478 6769 0 11:37 pts/0 00:00:00 grep --color=auto keepalived
#以下可以看到，VIP已转移到mysql-master01上
[-master01 ~]# ip a|grep 192.168.27.100
inet 192.168.27.100/24 scope global secondary eth0:0 相关推荐
emmm00 2020-11-17
王艺强 2020-11-17
aydh 2020-11-12
世樹 2020-11-11
zry 2020-11-11
URML 2020-11-11
spurity 2020-11-10
yifangs 2020-10-13
Andrea0 2020-09-18
Ida 2020-09-16
ltd00 2020-09-12
tufeiax 2020-09-03
xjd0 2020-09-10
greatboylc 2020-09-10
adsadadaddadasda 2020-09-08
疯狂老司机 2020-09-08
CoderToy 2020-11-16
ribavnu 2020-11-16
bianruifeng 2020-11-16