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 配置完成