1.环境软件版本
| 环境 | 软件 版本 | |
|---|---|---|
| 虚拟机&VMware Fusion | 11 Pro | |
| 服务器&CentOS | 7.9 | |
| 数据库&Mysql | 5.7.28 |
2.环境架构介绍
| 机器名称 | IP | 角色 | 权限 |
|---|---|---|---|
| Mysql_Master0 | 172.16.122.137 | 数据库Master | 可读写、主库 |
| Mysql_Slave0 | 172.16.122.144 | 数据库Slave 只读 | 从库 |
| Mysql_Slave1 | 172.16.122.145 | 数据库Slave | 只读、从库 |
| Mysql_MHA | 172.16.122.146 | MHA Manager | 高可用监控 |
架构如图所示，4台机器的IP和角色如下表：
3. MySQL主从搭建
3.1 RPM 使用记录
RPM是Red Hat公司随Redhat Linux推出的一个软件包管理器，通过它能够更加方便地实现软件的安
装。rpm常用的命令有以下几个:
-i, --install 安装软件包
-v, --verbose 可视化，提供更多的详细信息的输出
-h, --hash 显示安装进度
-U, --upgrade=<packagefile>+ 升级软件包
-e, --erase=<package>+ 卸载软件包
--nodeps 不验证软件包的依赖
组合可得到几个常用命令：
安装软件：rpm -ivh rpm包名
升级软件：rpm -Uvh rpm包名
卸载软件：rpm -e rpm包名
查看某个包是否被安装 rpm -qa | grep 软件名称
3.2 MySQL 安装
- 下载 'wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar'
- 解压 tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 包含如下
mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-test-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm
检查删除 mariadb
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
[root@localhost ~]# rpm -qa | grep mariadb
强制安装 --nodeps --force
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
初始化 MySQL
mysqld --initialize --user=mysql
查看默认密码
cat /var/log/mysqld.log
2021-02-28T15:00:18.447380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-02-28T15:00:19.895801Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-02-28T15:00:20.077652Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-02-28T15:00:20.147587Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: acf21eb8-79d5-11eb-a406-00163e0c5e60.
2021-02-28T15:00:20.150256Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-02-28T15:00:21.071202Z 0 [Warning] CA certificate ca.pem is self signed.
2021-02-28T15:00:21.268681Z 1 [Note] A temporary password is generated for root@localhost: 7kpxr<L6/Eru
启动 MySQL
systemctl start mysqld.service
查看启动状态
[root@hc-aliyun-cloud ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2021-02-28 23:01:46 CST; 26s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 98081 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 98055 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 98084 (mysqld)
Tasks: 27 (limit: 11498)
Memory: 238.1M
CGroup: /system.slice/mysqld.service
└─98084 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Feb 28 23:01:05 hc-aliyun-cloud systemd[1]: Starting MySQL Server...
Feb 28 23:01:46 hc-aliyun-cloud systemd[1]: Started MySQL Server.
登录 MySQL 修改密码
[root@hc-aliyun-cloud ~]# mysql -uroot -p
mysql> set password=password('root')
关闭防火墙
[root@hc-aliyun-cloud ~]# systemctl stop iptables;
Failed to stop iptables.service: Unit iptables.service not loaded.
[root@hc-aliyun-cloud ~]# systemctl stop firewalld
[root@hc-aliyun-cloud ~]# systemctl disable firewalld.service
3.3 Master 配置
修改 /etc/my.cnf 开启 bin-log
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
bin-log 部分
log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
重启
[root@hc-aliyun-cloud ~]# systemctl restart mysqld.service
授权 MySQL 主库库权限、刷新并验证
mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
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 | 869 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
3.4 Slave 配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
server-id=2
# 开启 relay log
relay_log=mysql-relay-bin
# 设定只读
read_only=1
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
查看冲库状态被设置同步
mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to master_host='39.105.125.98' ,master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
-- 开启从库
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
--- 查看重库状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 39.105.125.98
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 869
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 869
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
3.5 问题
- 克隆的虚拟机 server_uuid 可能相同
查看 server_id/server_uuid
show variables like '%server%id%';
删除 auto.cnf 文件 再 my.cnf 文件 datadir=/var/lib/mysql 目录下
3.4 配置半同步复制
Master 节点
安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
show variables like '%semi%'
修改配置文件
# 自动开启半同步复制
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=1000
重启服务
systemctl restart mysqld
Slave 节点
两台Slave节点都执行以下步骤。
安装插件
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
修改配置文件
# 自动开启半同步复制
rpl_semi_sync_slave_enabled=ON
重启服务
systemctl restart mysqld
测试半同步状态
首先通过MySQL命令行检查参数的方式，查看半同步是否开启。
show variables like '%semi%'
然后通过MySQL日志再次确认。
cat /var/log/mysqld.log
可以看到日志中已经启动半同步：
Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000005, 154)
4. MHA 高可用搭建
四台机器 ssh 互通
在四台服务器上分别执行下面命令，生成公钥和私钥，换行回车采用默认值
ssh-keygen -t rsa
在三台MySQL服务器分别执行下面命令，将公钥拷到MHA Manager服务器上
ssh-copy-id 192.168.31.126
之后可以在MHA Manager服务器上检查下，看看.ssh/authorized_keys文件是否包含3个公钥
cat /root/.ssh/authorized_keys
从MHA Manager服务器执行下面命令，向其他三台MySQL服务器分发公钥信息。
scp authorized_keys hadoop25-02:$PWD
scp /root/.ssh/authorized_keys 192.168.31.199:$PWD
scp /root/.ssh/authorized_keys 192.168.31.165:$PWD
scp /root/.ssh/authorized_keys 192.168.31.142:$PWD
可以MHA Manager执行下面命令，检测下是否实现ssh互通。
ssh master0；
4.1 MHA 下载安装
MHA 下载
MySQL5.7 对应的 MHA 版本是 0.5.8，所以在 GitHub 上找到对应的 rpm 包进行下载，MHA manager和
node 的安装包需要分别下载：
https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
下载后，将Manager和Node的安装包分别上传到对应的服务器。（可使用WinSCP等工具）
三台MySQL服务器需要安装node
MHA Manager服务器需要安装manager和node
提示：也可以使用wget命令在linux系统直接下载获取，例如
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
MHA node 安装
在四台服务器上安装 mha4mysql-node。
MHA 的 Node 依赖于perl-DBD-MySQL，所以要先安装 perl-DBD-MySQL。
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
MHA manager 安装
在 MHA Manage r服务器安装 mha4mysql-node 和 mha4mysql-manager。
MHA 的 manager 又依赖了**perl-Confifig-Tiny、perl-Log-Dispatch、perl-Parallel-ForkManager **，也分别
进行安装。
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel- ForkManager -y
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
注：仍不能下载perl-Parallel-ForkManager 依赖 尝试链接解决方案 链接： https://centos.pkgs.org/7/epel-aarch64/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm.html
提示：由于 perl-Log-Dispatch 和 perl-Parallel-ForkManager 这两个被依赖包在 yum 仓库找不到，
因此安装 epel-release-latest-7.noarch.rpm。在使用时，可能会出现下面异常：Cannot
retrieve metalink for repository: epel/x86_64。可以尝试使
用/etc/yum.repos.d/epel.repo，然后注释掉mirrorlist，取消注释baseurl。MHA 配置文件
MHA Manager服务器需要为每个监控的 Master/Slave 集群提供一个专用的配置文件，而所有的
Master/Slave 集群也可共享全局配置。
4.2 初始化配置目录
配置监控全局配置文件
vim /etc/masterha_default.cnf
配置监控实例配置文件
[root@mha-manager1 ~]# cat /etc/mha/app1.cnf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
[server1]
hostname=172.16.122.137
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]
hostname=172.16.122.144
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=172.16.122.145
candidate_master=1
master_binlog_dir="/var/lib/mysql"
vim /etc/mha/app1.cnf
#目录说明
#/var/log (CentOS目录)
# /mha (MHA监控根目录)
# /app1 (MHA监控实例根目录)
# /manager.log (MHA监控实例日志文件)
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
[server default]
#用户名
user=root
#密码
password=root
#ssh登录账号
ssh_user=root
#主从复制账号
repl_user=root
#主从复制密码
repl_password=root
#ping次数
ping_interval=1
#二次检查的主机
secondary_check_script=masterha_secondary_check -s 192.168.31.199 -s
192.168.31.165 -s 192.168.31.142
[server default]
#MHA监控实例根目录
manager_workdir=/var/log/mha/app1
#MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log
#[serverx] 服务器编号
#hostname 主机名
#candidate_master 可以做主库
#master_binlog_dir binlog日志文件目录
[server1]
hostname=192.168.31.199
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]hostname=192.168.31.165
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=192.168.31.142
candidate_master=1
master_binlog_dir="/var/lib/mysql"
配置文件 vim /etc/mha/app1.cnf
#目录说明
#/var/log (CentOS目录)
# /mha (MHA监控根目录)
# /app1 (MHA监控实例根目录)
# /manager.log (MHA监控实例日志文件)
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
[server default]
#用户名
user=root
#密码
password=root
#ssh登录账号
ssh_user=root
#主从复制账号
repl_user=root
#主从复制密码
repl_password=root
#ping次数
ping_interval=1
#二次检查的主机
secondary_check_script=masterha_secondary_check -s 172.16.122.137 -s
172.16.122.133 -s 172.16.122.145
[server default]
#MHA监控实例根目录
manager_workdir=/var/log/mha/app1
#MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log
#[serverx] 服务器编号
#hostname 主机名
#candidate_master 可以做主库
#master_binlog_dir binlog日志文件目录
[server1]
hostname=172.16.122.137
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]hostname=172.16.122.144
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=172.16.122.145
master_binlog_dir="/var/lib/mysql"
MHA 配置检测
执行 ssh 通信检测
在MHA Manager服务器上执行：
masterha_check_ssh --conf=/etc/mha/app1.cnf
检测 MySQL 主从复制
在MHA Manager服务器上执行：
masterha_check_repl --conf=/etc/mha/app1.cnf
出现**“MySQL Replication Health is OK.”证明MySQL**复制集群没有问题。
MH Manager 启动
在MHA Manager服务器上执行：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
查看监控状态命令如下：
masterha_check_status --conf=/etc/mha/app1.cnf
查看监控日志命令如下：
tail -f /var/log/mha/app1/manager.log
5. 遇到的问题
- 问题一 从配置文件和主配置文件不一致、修改从配置文件和主保持一致
Sat Mar 6 09:31:15 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln359] Slave configurations is not valid.
- 问题二 克隆的虚拟机 server_uuid 可能相同
查看 server_id/server_uuid
show variables like '%server%id%';
删除 auto.cnf 文件 再 my.cnf 文件 datadir=/var/lib/mysql 目录下
-
perl-Parallel-ForkManager 安装失败
安装 epel 仍不能下载 perl-Parallel-ForkManager 依赖 尝试单独下载 rpm 包手动安装、解决方案 链接： https://centos.pkgs.org/7/epel-aarch64/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm.html
本文由 chaoohuua 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为:
2021/04/23 15:20