MySQL HA部署
环境准备
地址 说明 10.5.12.92 pro-mysql-ha01，主节点 1 10.5.12.93 pro-mysql-ha02，主节点 2 10.5.12.91 pro-mysql-vip，虚拟 Virtual IP
创建本地yum源
上传 CentOS-7-x86_64-DVD-1708.iso 到/opt/software
cd /etc/yum.repos.d/
mkdir bkp
mv *.repo bkp
sudo touch /etc/yum.repos.d/local.repo
编辑文件 local.repo 的内容
[CentOS74]
name=CentOS7.4
baseurl=file:///media
enabled=1gpgcheck=0
gpgkey=file:///media/RPM-GPG-KEY-CentOS-7
挂载并清空yum缓存
mount /opt/software/CentOS-7-x86_64-DVD-1708.iso /media -o loop
yum clean all
yum makecache
确认关闭 SELinux
1、临时关闭：输入命令setenforce 0，重启系统后还会开启。
2、永久关闭：输入命令vi /etc/selinux/config，将SELINUX=enforcing改为SELINUX=disabled，然后保存退出。
防火墙设置
使用 root 用户在 pro-mysql-ha01 和 pro-mysql-ha02 上需要将相应的端口（例如 MySQL 使用的 23306 端口）加入到防火墙的过滤规则里面
firewall-cmd --zone=public --add-port=23306/tcp –permanent
firewall-cmd –reload
firewall-cmd –list-all
MySQL安装
使用 root 用户操作创建相关的用户组和用户
groupadd mysql
useradd -r -g mysql mysql
passwd mysql
上传/解压介质
将 mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz 上传到/opt/software 目录，并解压介质到 目录/opt/mysql
tar zxvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.gz -C /opt
ln -s /opt/mysql-8.0.22-linux-glibc2.12-x86_64 /opt/mysql
yum remove mariadb-libs-5.5.56-2.el7.x86_64 #mariadb 相关内容卸载
mkdir /mysql-data #用来存放 mysql 的数据，后面的配置文件需要用到
编辑/etc/my.cnf 文件，如下
[mysqld]
basedir = /opt/mysql
datadir = /mysql-data
socket = /mysql-data/mysql.sock
skip-name-resolve
log-error = /mysql-data/error.log
pid-file = /mysql-data/mysql.pid
character-set-server=utf8mb4
port=23306
lower_case_table_names = 1
default-storage-engine=INNODB
[client]
socket = /mysql-data/mysql.sock
default-character-set=utf8mb4
port=23306
设置自启动
cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
修改/etc/init.d/mysqld 的 basedir 和 datadir 为上面指定的实际值
……
basedir=/opt/mysql
datadir=/mysql-data
……
vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL
After=syslog.target network.target remote-fs.target nss-lookup.target
Before=shutdown.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/mysql-data/mysql.pid
#Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
#Execute pre and post scripts as root
PermissionsStartOnly=true# Start|Stop main service
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d/mysqld stop
#Sets open_files_limit
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
[Install]
WantedBy=multi-user.target
Alias=mysqld.service
使用 root 用户执行以下的命令修改 basedir 和 datadir 的属主
chown mysql:mysql /etc/init.d/mysqld
chown -Rf mysql:mysql /opt/mysql /opt/mysql-8.0.22-linux-glibc2.12-x86_64
chown -Rf mysql:mysql /mysql-data
systemctl daemon-reload
chkconfig --level 345 mysqld on
chkconfig --add mysqld
Mysql初始化
cd /opt/mysql/bin
./mysqld --initialize --user=mysql --basedir=/opt/mysql --datadir=/mysql-data
cat /mysql-data/error.log #获取 root 的临时密码
启动mysql
/opt/mysql/bin/mysqld_safe --user=mysql &
进入mysql修改root密码
/opt/mysql/bin/mysql -uroot -p
alter user root@'localhost' identified with mysql_native_password by '指定的密码'; #修改root密码
/opt/mysql/bin/mysqladmin -uroot -p -S /mysql-data/mysql.sock shutdown #停止MySQL
到此，pro-mysql-ha0l 和pro-mysql-ha02上的MySQL安装并设置完毕
主主同步环境配置
1.1.3.1 基础配置
编辑 pro-mysql-ha01 服务器上的文件/etc/my.cnf 的，在 mysqld 部分添加下面的配置
server-id = 1
log_bin = mysql-bin
expire_logs_days=10
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
使用 root 用户执行 systemctl restart mysqld 重启 mysql 服务
systemctl restart mysqld
登录 mysql 执行一下操作
create user syncoper@'%' identified with mysql_native_password by '指定的密码';
grant replication slave, replication client on *.* to syncoper@'%';
flush privileges;
flush tables with read lock;
show master status;
编辑 pro-mysql-ha02 服务器上的文件/etc/my.cnf 的，在 mysqld 部分添加下面的配置
server-id = 2
log_bin = mysql-bin
expire_logs_days=10
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all
使用 root 用户执行 systemctl restart mysqld 重启 mysql 服务
systemctl restart mysqld
登录 mysql 执行一下操作
create user syncoper@'%' identified with mysql_native_password by '指定的密码';
grant replication slave, replication client on *.* to syncoper@'%';
flush privileges;
flush tables with read lock;
show master status;
pro-mysql-ha01同步操作测试
登录 mysql 命令行，执行下面的操作
unlock tables;
stop slave;
change maste master_host='10.5.15.18', master_user='syncoper',master_password= 'syncoper 的用户密码',master_log_file='mysql-bin.000001',master_log_pos=871;
start slave;
show slave status\G;
其中master_host 为 dev-mysql-ha02 的地址
master_user 为连接的用户
master_password 为连接密码
master_log_file 为上一步在 dev-mysql-ha02 执行得到 binlog 文件名
master_log_pos 为上一步在 dev-mysql-ha02 执行得到 binlog
pro-mysql-ha02同步操作测试
unlock tables;
stop slave;
change master to master_host='10.5.15.17', master_port=23306,
master_user='syncoper', master_password='syncoper
的 用 户 密 码 ',
master_log_file='mysql-bin.000002', master_log_pos=1435;
start slave;
show slave status\G;
MySQL +Keepalived配置
Keepalived安装
yum install keepalived
使用 root 用户配置两台服务器上的 keepalived, pro-mysql-ha01 上的配置内容
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
cgo-ops@qq.com
cgo-tech@qq.com
}
notification_email_from cgo-ops@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51 priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 12345678
}
virtual_ipaddress {
10.5.15.16
}
}
virtual_server virtual_server 10.5.15.16 23306 {
delay_loop 2
persistence_timeout 50
protocal TCP
real_server 10.5.15.17 23306 {
weight 3
notify_down /opt/mysql/monitor/switch_keepalived.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
pro-mysql-ha02 上的配置内容
! Configuration File for keepalived
global_defs {
notification_email {
cgo-ops@qq.com
cgo-tech@qq.com
}
notification_email_from cgo-ops@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 12345678
}
virtual_ipaddress {
10.5.15.16
}}
virtual_server virtual_server 10.5.15.16 23306 {
delay_loop 2
persistence_timeout 50
protocal TCP
real_server 10.5.15.18 23306 {
weight 3
notify_down /opt/mysql/monitor/switch_keepalived.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
修改防火墙，注意命令中要修改自己的网卡名称ens33
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload
执行一下的命令设置 keepalived 为随服务器自启动
重新加载
systemctl daemon-reload
设置开机自动启动
systemctl enable keepalived.service
查看两台vip配置情况, 若两台主机只有一个VIP,则配置正常
ip addr
至此，MySQL HA 配置完成
Original: https://www.cnblogs.com/cheng9999/p/16500925.html
Author: cheng_blog
Title: MySQL高可用安装
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/621014/
转载文章受原作者版权保护。转载请注明原作者出处！