广州网站建设(信科网络),703804散讲温州论坛,网站微信认证费用多少,公司网站制作第一步是什么文章目录 mha集群搭建主从从部署基本环境准备安装mysql主从配置 mha部署故障修复 搭建完成 mha集群搭建
在 MySQL 高可用架构中#xff0c;MHA#xff08;Master High Availability#xff09;通常采用一主多从的架构。
MHA 可以提供主从复制架构的自动 master failover 功… 文章目录 mha集群搭建主从从部署基本环境准备安装mysql主从配置 mha部署故障修复 搭建完成 mha集群搭建
在 MySQL 高可用架构中MHAMaster High Availability通常采用一主多从的架构。
MHA 可以提供主从复制架构的自动 master failover 功能。当主服务器出现故障时MHA 会将从服务器提升为新的主服务器并在此期间通过与其他从服务器获取额外信息来避免一致性问题。
主从从部署
基本环境准备
yum源配置
cd /etc/yum.repos.d
mkdir bak
mv *.repo ./bak
cd /etc/yum.repos.d
wget -O CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all
yum makecache修改主机名和host文件解析
hostnamectl set-hostname master1 bash
hostnamectl set-hostname slave1 bash
hostnamectl set-hostname slave2 bashcat /etc/hosts
192.168.3.74 master1
192.168.3.75 slave1
192.168.3.76 slave2配置3台数据库主机之间ssh免密登录
ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76安装mysql
删除原有的mariadb
yum remove mariadb* -y添加源
cat EOF /etc/yum.repos.d/mysql57.repo
[mysql57]
namemysql57
baseurlhttps://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck0
enabled1
EOFyum install mysql-community-server -y启动mysql服务查看初始密码
systemctl enable mysqld --nowcat /var/log/mysqld.log |grep password
2024-09-19T08:36:50.471114Z 1 [Note] A temporary password is generated for rootlocalhost: :Q,)akd#i8U使用初始密码登录修改mysql的root账号初始密码
alter user rootlocalhost identified with mysql_native_password by aaA...111;flush privileges;也可以使用mysqladmin修改密码
mysqladmin -uroot -pe#)er)sg?39A password aaA...111主从配置
修改my.cnf配置文件主
vim /etc/my.cnfserver-id 1
log-bin mysql-bin
binlog_format row
gtid-mode on
enforce-gtid-consistency true
log-slave-updates true修改my.cnf配置文件从1
vim /etc/my.cnfserver-id 2
log-bin mysql-bin
binlog_format row
gtid-mode on
enforce-gtid-consistency true
log-slave-updates true修改my.cnf配置文件从2
vim /etc/my.cnfserver-id 3
log-bin mysql-bin
binlog_format row
gtid-mode on
enforce-gtid-consistency true
log-slave-updates true重启服务
systemctl restart mysqldmaster1创建MHA用于监控的账号mha和主从同步账号repl
# 创建主从同步账号repl
create user repl192.168.%.% identified with mysql_native_password by repl1234A...;# 授权
GRANT REPLICATION SLAVE ON *.* TO repl192.168.%.%;# 刷新权限生效
flush privileges;# 查看用户信息
select user,host from mysql.user;# 查看授权
mysql show grants for repl192.168.%.%;
--------------------------------------------------------
| Grants for repl192.168.%.% |
--------------------------------------------------------
| GRANT REPLICATION SLAVE ON *.* TO repl192.168.%.% |
--------------------------------------------------------
1 row in set (0.00 sec)# 创建用于mha高可用账号mha
create user mha192.168.%.% identified with mysql_native_password by mhA1234...;# 授权
grant all privileges on *.* to mha192.168.%.%;# 刷新权限生效
flush privileges;# 查看用户信息
mysql select user,host from mysql.user;
----------------------------
| user | host |
----------------------------
| mha | 192.168.%.% |
| repl | 192.168.%.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
----------------------------
5 rows in set (0.00 sec)# 查看用户repl授权
mysql show grants for repl192.168.%.%;
--------------------------------------------------------
| Grants for repl192.168.%.% |
--------------------------------------------------------
| GRANT REPLICATION SLAVE ON *.* TO repl192.168.%.% |
--------------------------------------------------------
1 row in set (0.00 sec)
# 查看用户mha授权
mysql show grants for mha192.168.%.%;
----------------------------------------------------
| Grants for mha192.168.%.% |
----------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO mha192.168.%.% |
----------------------------------------------------
1 row in set (0.00 sec)# 如何删除用户
drop user mha192.168.%.%;slave1、slave2从库配置主从复制
change master to master_host192.168.3.74, master_userrepl, master_passwordrepl1234A... , MASTER_AUTO_POSITION1;GTID位置点自动追踪需要同步的 positionmaster_auto_position1
开启从库的主从复制功能
mysql stop slave; #停止主从同步功能
mysql start slave; #开启主从同步功能
mysql show slave status\G #查询状态是否成功Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 02个从库会把刚才主库创建的用户同步到从库从库中都有mha repl 用户若没有检查上述步骤配置是否有误
mysql select user,host from mysql.user;
----------------------------
| user | host |
----------------------------
| mha | 192.168.%.% |
| repl | 192.168.%.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
----------------------------
5 rows in set (0.01 sec)从库设置只读模式
# 设置只读模式
mysql -uroot -paaA...111 -e set global read_only1# 查看是否为只读模式
mysql -uroot -paaA...111 -e SHOW VARIABLES LIKE read_only
mysql: [Warning] Using a password on the command line interface can be insecure.
----------------------
| Variable_name | Value |
----------------------
| read_only | ON |
----------------------mha部署
77主机作为管理主机mha manager 来管理 74主库 75从库 76从库
所有主机安装依赖软件包
yum -y install perl-DBD-MySQL perl-ExtUtils-MakeMaker perl-CPAN perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager配置ssh免密登录 77 免密登录 74 75 76 三台数据库相互免密登录
# 77免密登录 74 75 76
ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76# 74 75 76 数据库相互免密登录
ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76在所有数据库节点 安装软件包 mha4mysql-node-0.58.tar.gz
# 上传软件包
rz mha4mysql-node-0.58.tar.gz
tar zxf mha4mysql-node-0.58.tar.gz -C /usr/local/src/
cd /usr/local/src/mha4mysql-node-0.58/
perl Makefile.PL make make install# 查看安装成功后的脚本文件
ll /usr/local/bin/
总用量 48
-r-xr-xr-x 1 root root 17639 9月 19 17:01 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 9月 19 17:01 filter_mysqlbinlog
-r-xr-xr-x 1 root root 8337 9月 19 17:01 purge_relay_logs
-r-xr-xr-x 1 root root 7525 9月 19 17:01 save_binary_logs在管理节点 需要安装两个软件包mha4mysql-node-0.58.tar.gz 和 mha4mysql-manager-0.58.tar.gz
# 安装mha4mysql-node-0.58.tar.gz 和 mha4mysql-manager-0.58.tar.gzrz mha4mysql-manager-0.58.tar.gz mha4mysql-node-0.58.tar.gz
tar zxf mha4mysql-node-0.58.tar.gz -C /usr/local/src/
tar zxf mha4mysql-manager-0.58.tar.gz -C /usr/local/src/
cd /usr/local/src/mha4mysql-node-0.58/
perl Makefile.PL make make install
cd /usr/local/src/mha4mysql-manager-0.58
perl Makefile.PL make make install# 查看安装成功后的脚本文件
ll /usr/local/bin/
总用量 88
-r-xr-xr-x 1 root root 17639 9月 19 17:01 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 9月 19 17:01 filter_mysqlbinlog
-r-xr-xr-x 1 root root 1995 9月 19 17:04 masterha_check_repl
-r-xr-xr-x 1 root root 1779 9月 19 17:04 masterha_check_ssh
-r-xr-xr-x 1 root root 1865 9月 19 17:04 masterha_check_status
-r-xr-xr-x 1 root root 3201 9月 19 17:04 masterha_conf_host
-r-xr-xr-x 1 root root 2517 9月 19 17:04 masterha_manager
-r-xr-xr-x 1 root root 2165 9月 19 17:04 masterha_master_monitor
-r-xr-xr-x 1 root root 2373 9月 19 17:04 masterha_master_switch
-r-xr-xr-x 1 root root 5172 9月 19 17:04 masterha_secondary_check
-r-xr-xr-x 1 root root 1739 9月 19 17:04 masterha_stop
-r-xr-xr-x 1 root root 8337 9月 19 17:01 purge_relay_logs
-r-xr-xr-x 1 root root 7525 9月 19 17:01 save_binary_logs配置77主机mha manager
拷贝脚本文件至/usr/local/bin/
# 拷贝脚本文件至/usr/local/bin/
cp /usr/local/src/mha4mysql-manager-0.58/samples/scripts/master_ip_failover /usr/local/bin/
cp /usr/local/src/mha4mysql-manager-0.58/samples/scripts/master_ip_online_change /usr/local/bin/# 查看安装成功后的脚本文件
ll /usr/local/bin/
总用量 104
-r-xr-xr-x 1 root root 17639 9月 19 17:01 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 9月 19 17:01 filter_mysqlbinlog
-r-xr-xr-x 1 root root 1995 9月 19 17:04 masterha_check_repl
-r-xr-xr-x 1 root root 1779 9月 19 17:04 masterha_check_ssh
-r-xr-xr-x 1 root root 1865 9月 19 17:04 masterha_check_status
-r-xr-xr-x 1 root root 3201 9月 19 17:04 masterha_conf_host
-r-xr-xr-x 1 root root 2517 9月 19 17:04 masterha_manager
-r-xr-xr-x 1 root root 2165 9月 19 17:04 masterha_master_monitor
-r-xr-xr-x 1 root root 2373 9月 19 17:04 masterha_master_switch
-r-xr-xr-x 1 root root 5172 9月 19 17:04 masterha_secondary_check
-r-xr-xr-x 1 root root 1739 9月 19 17:04 masterha_stop
-rwxr-xr-x 1 root root 3648 9月 19 17:09 master_ip_failover
-rwxr-xr-x 1 root root 9870 9月 19 17:09 master_ip_online_change
-r-xr-xr-x 1 root root 8337 9月 19 17:01 purge_relay_logs
-r-xr-xr-x 1 root root 7525 9月 19 17:01 save_binary_logs77主机mha manager 创建MHA Manager配置文件
# 配置 MHA Manager
mkdir /etc/mhacat /etc/mha/app1.cnf EOF
[server default]
manager_workdir/etc/mha
manager_log/etc/mha/manager.log
master_ip_failover_script/usr/local/bin/master_ip_failover
master_ip_online_change_script/usr/local/bin/master_ip_online_changeping_interval2
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.3.75 -s 192.168.3.76ssh_userroot
ssh_port22usermha
passwordmhA1234...repl_userrepl
repl_passwordrepl1234A...[server1]
hostname192.168.3.74
candidate_master1
port3306[server2]
hostname192.168.3.75
port3306
candidate_master1[server3]
hostname192.168.3.76
candidate_master1
port3306EOF修改故障切换脚本添加vip地址的设置
vim /usr/local/bin/master_ip_failover#!/usr/bin/env perluse strict;
use warnings FATAL all;use Getopt::Long;
use MHA::DBHelper;my ($command, $ssh_user, $orig_master_host,$orig_master_ip, $orig_master_port, $new_master_host,$new_master_ip, $new_master_port, $new_master_user,$new_master_password
);##############该部分按实际修改##############
my $vip 192.168.3.177/24;
my $ifdev ens33;
my $key 1;
my $ssh_start_vip /sbin/ifconfig $ifdev:$key $vip;
my $ssh_stop_vip /sbin/ifconfig $ifdev:$key down;
##############该部分按实际修改##############GetOptions(commands \$command,ssh_users \$ssh_user,orig_master_hosts \$orig_master_host,orig_master_ips \$orig_master_ip,orig_master_porti \$orig_master_port,new_master_hosts \$new_master_host,new_master_ips \$new_master_ip,new_master_porti \$new_master_port,new_master_users \$new_master_user,new_master_passwords \$new_master_password,
);exit main();sub main {print \n\nIN SCRIPT TEST$ssh_stop_vip$ssh_start_vip\n\n;if ( $command eq stop || $command eq stopssh ) {my $exit_code 1;eval {print Disabling the VIP on old master: $orig_master_host \n;stop_vip();# updating global catalog, etc$exit_code 0;};if ($) {warn Got Error: $\n;exit $exit_code;}exit $exit_code;}elsif ( $command eq start ) {my $exit_code 10;eval {print Enabling the VIP - $vip on the new master - $new_master_host \n;start_vip();$exit_code 0;};if ($) {warn $;# If you want to continue failover, exit 10.exit $exit_code;}exit $exit_code;}elsif ( $command eq status ) {print Checking the Status of the script.. OK \n;# do nothingexit 0;}else {usage();exit 1;}
}
sub start_vip() {ssh $ssh_user\$new_master_host \ $ssh_start_vip \;
}sub stop_vip() {return 0 unless ($ssh_user);ssh $ssh_user\$orig_master_host \ $ssh_stop_vip \;
}sub usage {print
Usage: master_ip_failover --commandstart|stop|stopssh|status --orig_master_hosthost --orig_master_ipip --orig_master_portport --new_master_hosthost --new_master_ipip --new_master_portport\n;
}给mysql主服务器添加虚拟ip地址
# 创建vip地址
ifconfig ens33:1 192.168.3.177/24附
# 删除
ifconfig ens33:1 del 192.168.3.177
# 停止
ifconfig ens33:1 down启动 77主机mha manager 前验证配置是否正确
masterha_check_ssh --conf/etc/mha/app1.cnf
masterha_check_repl --conf/etc/mha/app1.cnf启动 77主机mha manager
# 前台运行
masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover# 后台运行
nohup masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /etc/mha/app1.log 21 检查运行状态
masterha_check_status --conf/etc/mha/app1.cnf主数据上创建数据库测试
# 创建数据库和表
create database db9;
create table db9.a (id int);
insert into db9.a values(111);
select * from db9.a;# 创建账号devusr
create user devusr192.168.%.% identified with mysql_native_password by devusr1234A...;# 授权devusr用户
GRANT select,insert,delete ON db9.* TO devusr192.168.%.%;
# 刷新权限生效
flush privileges;
# 查看用户devusr授权
mysql show grants for devusr192.168.%.%;
-------------------------------------------------------------------
| Grants for devusr192.168.%.% |
-------------------------------------------------------------------
| GRANT USAGE ON *.* TO devusr192.168.%.% |
| GRANT SELECT, INSERT, DELETE ON db9.* TO devusr192.168.%.% |
-------------------------------------------------------------------
2 rows in set (0.00 sec)测试高可用
# 直接把主库关机使用其它机器连接vip地址可以看到正常连接数据库。
[rootmg ~]# mysql -udevusr -pdevusr1234A... -h192.168.3.177MySQL [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| db9 |
--------------------
2 rows in set (0.00 sec)# 插入数据
insert into db9.a values(1234567);# 主库宕机后 77主机mha manager 会将 [server1] 字段被删除 且mha manager会停止运行
# 每次failover切换后会在管理目录生成文件 /etc/mha/app1.failover.complete
# 下次在切换的时候会发现有这个文件导致切换不成功需要手动清理掉
# rm -rf /masterha/app1/app1.failover.complete
# 也可以在启动时加上参数--ignore_last_failover# vip地址也会切换到其它机器上
cat /etc/mha/app1.cnf[server default]
manager_log/etc/mha/manager.log
manager_workdir/etc/mha
master_ip_failover_script/usr/local/bin/master_ip_failover
master_ip_online_change_script/usr/local/bin/master_ip_online_change
passwordmhA1234...
ping_interval2
repl_passwordrepl1234A...
repl_userrepl
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.3.75 -s 192.168.3.76
ssh_port22
ssh_userroot
usermha[server2]
candidate_master1
hostname192.168.3.75
port3306[server3]
candidate_master1
hostname192.168.3.76
port3306故障修复
# 修好宕机的机器启动mysql服务后将原来的master主库机器指向新的主数据库
change master to master_host192.168.3.75, master_userrepl, master_passwordrepl1234A... , MASTER_AUTO_POSITION1;# 开器主从同步
start slave;# 修改mha manager 配置文件
# 重新加以下入字段74主数据库宕机后以下这段配置会被删除
vim /etc/mha/app1.cnf
[server1]
hostname192.168.3.74
candidate_master1
port3306# 重新启动mha 后台运行
nohup masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /etc/mha/app1.log 21 # 重新启动mha 前台运行
masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover# 修复完成------------------# 若还需要使用原来的主服务器可以使用MHA的在线切换功能如下# 首先manager节点上停掉MHA监控
masterha_stop --conf/etc/mha/app1.cnf# 切换回原来的主库
masterha_master_switch --conf/etc/mha/app1.cnf --master_statealive --new_master_host192.168.3.74 --orig_master_is_new_slave --running_updates_limit10000 --interactive0# 如果报错编辑这个文件
vim /usr/local/bin/master_ip_online_change
# 找到下面这两条将其使用#号注释掉
FIXME_xxx_drop_app_user($orig_master_handler);
FIXME_xxx_create_app_user($new_master_handler);# 重新启动mha 后台运行
nohup masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /etc/mha/app1.log 21 # 重新启动mha 前台运行
masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover常用命令
# MHA 检测ssh状态
masterha_check_ssh --conf/etc/mha/app1.cnf# MHA 检测mysql集群主从状态
masterha_check_repl --conf/etc/mha/app1.cnf# 前台运行
masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover# 后台运行
nohup masterha_manager --conf/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /etc/mha/app1.log 21 # MHA Manager停止运行
masterha_stop --conf/etc/mha/app1.cnf# 直接kill掉也可搭建完成
