shell脚本

#!/bin/bash

# 两个变量
variable1=("value1" "value2" "value3")
variable2=("foo" "bar" "baz")

# 合并两个变量的值
combined=$(paste -d ' ' <(printf "%s\n" "${variable1[@]}") <(printf "%s\n" "${variable2[@]}"))

# 使用 for 循环遍历合并后的值
IFS=' ' # 设置 IFS（内部字段分割符）为空格，以便正确分隔合并后的值
for values in ${combined[@]}; do
    echo "变量1: ${values% *}, 变量2: ${values#* }"

执行 sed -i '/\[mysqld\]/a relay_log_purge=off' $Mysql_Conf 命令会在 $Mysql_Conf 文件中的 [mysqld] 配置段后面插入一行 relay_log_purge=off。

请确保 $Mysql_Conf 变量指向正确的 MySQL 配置文件路径，并注意备份配置文件以防意外。执行此命令后，再次启动 MySQL 服务时将应用新的配置。

sed -i '/\[mysqld\]/a relay_log_purge=0' /etc/my.cnf
relay_log_purge=0

cat >/root/mha_peizhi.cnf<< EOC
# 设置主机列表
hosts=("172.16.1.51" "172.16.1.52" "172.16.1.53" "172.16.1.54")

# 主从的变量设置
zhu_user="rep"
zhu_password="123"

mha_user="mha"
mha_password="mha"

# 主库配置内容
primary_host="172.16.1.51"
primary_user="root"
primary_password="123"

slave_user="root"
slave_password="123"

slave1_host="172.16.1.52"
slave2_host="172.16.1.53"
slave3_host="172.16.1.54"


slave_ip=("172.16.1.52" "172.16.1.53" "172.16.1.54")
master_ip="172.16.1.51"
manager_ip="172.16.1.54"
EOC


cat >install_mha.sh 
#!/bin/bash

. /etc/init.d/functions

#-------------- 变量 ------------------
check_ip="[1-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
mha_worker_dir=/etc/mha
mha_log_dir=/etc/mha/logs
binlog_dir=/app/mysql/data
mha_manager_user=mha
mha_manager_password=mha
repl_user=rep
repl_password=123


# ------------ 函数 -------------------
function push_public_key(){
  yum install -y sshpass &>/dev/null
  if [ ! -f ~/.ssh/id_dsa.pub ];then
    ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
  fi
  for ip in $@;do
    ping -W1 -c1 $ip &>/dev/null
    if [ $? -eq 0 ];then
      sshpass -p '1' ssh-copy-id -o StrictHostKeyChecking=no -i ~/.ssh/id_dsa.pub root@$ip &>/dev/null
      action "主机：$ip" /bin/true
    else
      action "主机：$ip" /bin/false
    fi
  done
}


function check_ipaddress(){
if [[ $1 =~ $check_ip ]];then
  echo "$1 ok" &>/dev/null
else
  echo "Usage：请输入正确的IP地址。"
  exit 2
fi
}


#------------------- 程序 -----------------------

read -p "请输入集群个数：" num

expr 1 + $num &>/dev/null
if [ $? -ne 0 ];then
  echo "Usage：集群个数必须是一个整形"
  exit 1
fi

read -p "请输入master的IP地址：" master_ip
#check_ipaddress $master_ip



read -p "请输入slave的IP地址（多台使用空格开）：" -a slave_ip
#check_ipaddress ${slave_ip[0]}
#push_public_key $master_ip ${slave_ip[*]}

for id in ${slave_ip[*]};do
  echo ${id##*.}
done

read -p "请输入manager的IP地址：" manager_ip
#check_ipaddress $manager_ip
if [ $manager_ip == $master_ip ];then
  echo -e  "\e[1;4;5;31mFBI Warning： 尽量不要把mha装在主库上\e[0m"
fi

cat > app1.cnf <<EOF
[server default]
manager_log=${mha_log_dir}/manager.log
manager_workdir=${mha_worker_dir}/app1
master_binlog_dir=$binlog_dir
user=$mha_manager_user
password=$mha_manager_password
ping_interval=2
repl_password=$repl_password
repl_user=$repl_user
ssh_user=root
ssh_port=22

[server100]
hostname=$master_ip
port=3306
EOF

for((n=1;n<=$num-1;n++));do
cat >> app1.cnf <<EOF
[server$n]
hostname=${slave_ip[(($n-1))]}
port=3306
EOF
done

#ssh $manager_ip "mkdir -p $mha_worker_dir $mha_log_dir"
#scp app1.cnf $manger_ip:$mha_worker_dir

cat >/root/mha_peizhi.cnf<< EOC
# 设置主机列表
hosts=("172.16.1.51" "172.16.1.52" "172.16.1.53" "172.16.1.54")
lave_hosts=("172.16.1.52" "172.16.1.53" "172.16.1.54")
# 主从的变量设置
zhu_user="rep"
zhu_password="123"

mha_user="mha"
mha_password="mha"

# 主库配置内容
primary_host="172.16.1.51"
primary_user="root"
primary_password="123"

slave_user="root"
slave_password="123"

slave1_host="172.16.1.52"
slave2_host="172.16.1.53"
slave3_host="172.16.1.54"


slave_ip=("172.16.1.52" "172.16.1.53" "172.16.1.54")
master_ip="172.16.1.51"
manager_ip="172.16.1.54"
EOC

cat >/root/mha.sh<< EOF
#!/bin/bash
source /root/mha_peizhi.cnf
source /etc/init.d/functions
#------------------------函数----------------------------
#本地推密钥函数
function push_public_key(){
yum install -y sshpass &>/dev/null
if [ ! -f ~/.ssh/id_dsa.pub ];then
        ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
fi
for ip in $@;do
        ping -W1 -c1 $ip &>/dev/null
        if [ $? -eq 0 ];then
                sshpass -p '1' ssh-copy-id -o StrictHostKeyChecking=no -i ~/.ssh/id_dsa.pub root@$ip &>/dev/null
                action "主机：$ip" /bin/true
        else
                action "主机：$ip" /bin/false
        fi
done
}
#全部推密钥函数
function hhh(){
for ipa in $@;do
scp ~/.ssh/* $ipa:~/.ssh/
done
}
#从新启动全部主机数据库
chongqi(){
        for host in "${hosts[@]}";do
        	ssh "$host" "/etc/init.d/mysqld restart"
		done
}
qqq(){
ssh $hosts "yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm"
if [$? -nq 0 ];then
	qqq
fi
}
www(){
ssh "$slave3_user@$slave3_host" "yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm"
if [$? -nq 0 ];then
        www
fi
}
#----------------------------主配置--------------------------
#主机ip
#read -p "请输入master的IP地址：" master_ip
#read -p "请输入slave的IP地址（多台使用空格开）：" -a slave_ip

#read -p "请输入manager的IP地址：" manager_ip
#if [ $manager_ip == $master_ip ];then
#  echo -e  "\e[1;4;5;31mFBI Warning： 尽量不要把mha装在主库上\e[0m"
#fi


#本地推密钥
push_public_key ${hosts[*]}
#全部推密钥
hhh ${hosts[*]}

echo "密钥配置完成"



# 获取用户输入的复制方式（GTID或传统）
read -p "请选择复制方式（1. GTID复制，2. 传统主从复制）: " replication_type

# 配置主库的复制方式
if [ $replication_type -eq 1 ]; then
    # GTID复制
    echo "GTID复制方式已选择。"

else
    # 传统主从复制
    echo "传统主从复制方式已选择。"
fi

# 配置从库的复制方式
#for slave_host in "${slave_hosts[@]}"; do
    if [ $replication_type -eq 1 ]; then
        # 配置GTID主从配置文件
        for host in "${hosts[@]}"; do
            ssh "${host}" "cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=$(expr ${host: -2} + 0)
log-bin=mysql-bin
gtid_mode=on
enforce_gtid_consistency
relay_log_purge=0
EOF"
        done
        chongqi
        echo "GTID配置文件已完成"
        # 配置主库的主从复制用户
        mysql -u${primary_user} -p${primary_password} -e "GRANT REPLICATION SLAVE ON *.* TO ${zhu_user}@'172.16.1.%' IDENTIFIED BY '${zhu_password}';"
        echo "主从复制用户已完成"
        # 配置从库的主从复制
        for slave_host in "${slave_hosts[@]}"; do
            mysql -u${slave_user} -p${slave_password} -h${slave_host} -e "CHANGE MASTER TO MASTER_HOST=\"${primary_host}\", MASTER_USER=\"${zhu_user}\", MASTER_PASSWORD=\"${zhu_password}\",master_auto_position='1',master_port=3306;"
        done
        echo "GTID主从复制已完成"

    else


#推从库的MHA配置文件
for host in "${hosts[@]}"; do
	ssh "${host}" "cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=$(expr ${host: -2} + 0)
log-bin=mysql-bin
relay_log_purge=0
EOF"
done

#启动数据库
chongqi
echo "GTID配置文件已经完成"

# 配置主库的主从复制用户
mysql -u${primary_user} -p${primary_password} -e "GRANT REPLICATION SLAVE ON *.* TO ${zhu_user}@'172.16.1.%' IDENTIFIED BY '${zhu_password}';"
echo "主从复制完成"

# 配置从库的主从复制(获取主库的位置信息)
master_log_file=$(mysql -u${primary_user} -p${primary_password} -e "SHOW MASTER STATUS" | awk 'NR==2{print $1}')
        master_log_pos=$(mysql -u${primary_user} -p${primary_password} -e "SHOW MASTER STATUS" | awk 'NR==2{print $2}')
for slave_host in "${slave_ip[@]}"; do
            mysql -u${slave_user} -p${slave_password} -h${slave_ip} -e "CHANGE MASTER TO MASTER_HOST=\"${slave_ip}\", MASTER_USER=\"${zhu_user}\", MASTER_PASSWORD=\"${zhu_password}\",MASTER_LOG_FILE=\"${master_log_file}\", MASTER_LOG_POS=${master_log_pos},master_port=3306;"
        done
echo "从库复制完成"
fi

# 启动从库的复制进程
for slave_host in "${slave_hosts[@]}"; do
    "mysql -u${slave_user} -p${slave_password} -h${slave_host} -e \"stop slave;\""
    "mysql -u${slave_user} -p${slave_password} -h${slave_host} -e \"start slave;\""
done
echo "启动从库"

# 安装MHA Node组件
for host in "${hosts[@]}"; do
qqq
done
echo "安装Node组件"

# 在从库中安装manager组件
www
echo "安装manager组件"

# 创建主从用户及高可用用户
mysql -u"$primary_user" -p"$primary_password" -e "GRANT REPLICATION SLAVE ON *.* TO "$zhu_user"@'172.16.1.%' IDENTIFIED BY '$zhu_password';"
mysql -u"$primary_user" -p"$primary_password" -e "GRANT REPLICATION ALL ON *.* TO "$mha_user"@'172.16.1.%' IDENTIFIED BY '$mha_password';"
echo "创建主从用户与高可用用户"

# 设置从库只读
for slave_host in "${slave_hosts[@]}"; do
    mysql -u${slave_user} -p${slave_password} -h${slave_host} -e 'set global read_only=1;'
done
echo "设置从库只读已完成"

# 命令软链接
for host in "${hosts[@]}"; do
    ssh "root@$host" "ln -s /app/mysql/bin/mysql /usr/bin/mysql"
    ssh "root@$host" "ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog"
done
echo "命令软链接"

# 创建mha配置文件目录并编写配置文件
ssh "root@$manager_ip" "mkdir /etc/mha"
ssh "root@$manager_ip" "cat >/etc/mha/app1.cnf<<EOF
[server default]
manager_log=/etc/mha/logs/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/app/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
ssh_port=22

[server1]                                   
hostname=172.16.1.51
port=3306                                  

[server2]
#candidate_master=1
#check_repl_delay=0
hostname=172.16.1.52
port=3306

[server3]
hostname=172.16.1.53
port=3306

[server4]
hostname=172.16.1.54
port=3306
EOF"

echo "创建mha配置文件目录并编写配置文件已完成"


# 创建日志目录
ssh "$slave1_user"@"$slave1_host" "mkdir /etc/mha/logs"
echo "创建日志目录已完成"
# 启动 MHA Manager
ssh "$slave1_user"@"$manager_ip" "nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/logs/manager.log 2>&1 &"
echo "启动 MHA Manager已完成"
EOF

MHA集群恢复脚本

[root@db04 ~]# cat recovery_mha.sh
#!/bin/bash
. /etc/init.d/functions

slave_ip=("172.16.1.51" "172.16.1.52" "172.16.1.53" "172.16.1.54")
for runt in "${slave_ip[@]}";do
	ssh "$runt" "mysql -uroot -p123 -e \"grant all on *.* to root@'%' identified by '123';\""
done

mha_log="/etc/mha/logs/manager.log"
pass="123"
down_master=$(sed -nr 's#^M.* (.*)\(.*\).*!$#\1#gp' ${mha_log})
# 2.找到change master语句
change=$(grep -i 'change master to' ${mha_log} |awk -F: '{print $4}'|sed "s#xxx#$pass#g")
mha_user=mha
mha_pass=mha

# 1.修复宕机主库
ssh $down_master "systemctl start mysqld"
while true;do
	mysql_count=`ps -ef|grep -c [m]ysqld`
	if [ $mysql_count -gt 0 ];then
		action "$down_master MySQL Server" /bin/true
		# 3.在宕机主库中执行，开启主从复制
mysql -u${mha_user} -p${mha_pass} -h$down_master -e "${change};start slave;" &>/dev/null
		break
	fi
done

# 4.修复 MHA 配置文件
cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=${mha_log}
manager_workdir=/etc/mha/app1
master_binlog_dir=/app/mysql/data
password=${mha_user}
ping_interval=2
repl_password=123
repl_user=rep
ssh_port=22
ssh_user=root
user=${mha_pass}

[server1]
hostname=172.16.1.51
port=3306
[server2]
hostname=172.16.1.52
port=3306
[server3]
hostname=172.16.1.53
port=3306
[server4]
hostname=172.16.1.54
port=3306
EOF