暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 系统重启后两节点mgr自动恢复的脚本

系统重启后两节点mgr自动恢复的脚本

原创 jieguo 2024-07-23
56

节点1：


[root@db1 ~]# ps -ef|grep mysql
root       2710      1  0 20:54 ?        00:00:00 /bin/sh /db/mysql/mysql-8.0.36/bin/mysqld_safe --datadir=/db/mysql/mysql-8.0.36/data --pid-file=/db/mysql/mysql-8.0.36/data/db1.pid
mysql      3809   2710 84 20:54 ?        00:01:44 /db/mysql/mysql-8.0.36/bin/mysqld --basedir=/db/mysql/mysql-8.0.36 --datadir=/db/mysql/mysql-8.0.36/data --plugin-dir=/db/mysql/mysql-8.0.36/lib/plugin --user=mysql --log-error=db1.err --pid-file=/db/mysql/mysql-8.0.36/data/db1.pid --socket=/db/mysql/mysql-8.0.36/mysql.sock --port=3307
root       4567   4382  0 20:56 pts/0    00:00:00 grep --color=auto mysql
[root@db1 ~]# date
Fri Mar 29 20:56:53 CST 2024
[root@db1 ~]# ip a|grep 192.168
    inet 192.168.1.52/24 brd 192.168.1.255 scope global noprefixroute bond0
[root@db1 ~]# crontab -l
* * * * * /etc/vip_check.sh > /dev/null 2>&1
[root@db1 ~]# cat /etc/vip_check.sh 
step=3
for ((i = 0; i < 60; i = (i + step))); do
    $(/etc/vip.sh)
    sleep $step
done
exit 0
[root@db1 ~]# cat /etc/vip.sh
#!/bin/bash
dbstats=`/db/mysql/mysql-8.0.36/bin/mysql -u root -pxxxxxx -P 3307 -e "select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"|grep "192.168.1.52"|awk '{print $2}'|grep "PRIMARY"|wc -l`
ip=`/usr/sbin/ip a|grep bond0:1|wc -l`
 
if [[ "${dbstats}" -eq 1 ]] ; then
    if [[ "${ip}" -eq 0 ]]; then
    /usr/sbin/ifconfig bond0:1 192.168.1.50 netmask 255.255.255.0 up
    /usr/sbin/arping -I bond0 -b -s 192.168.1.50 192.168.1.3 -c 3
    fi
else
    if [[ "${ip}" -gt 0 ]]; then
    /usr/sbin/ifconfig bond0:1 down
    fi
fi

[root@db1 ~]# cat /etc/rc.local 
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.

touch /var/lock/subsys/local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
/etc/automgr.sh

[root@db1 ~]# cat /etc/automgr.sh 
#!/bin/bash

##延迟等待6秒mysql自启动service mysql status
service mysql start
sleep 3

# MySQL 用户名和密码
MYSQL_USER="root"
MYSQL_PASSWORD="xxxxxx"
local_host=192.168.1.52
local_port=3307
remote_host=192.168.1.51
remote_port=3307

# 获取主服务器和从服务器的 GTID 值
LOCAL_GTID=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $local_host -P $local_port -e "SELECT @@gtid_executed\G" | grep "gtid_executed"|awk '{print $2}'`
REMOTE_GTID=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $remote_host -P $remote_port -e "SELECT @@gtid_executed\G" | grep "gtid_executed"|awk '{print $2}'`

#检查集群状态：
local_st=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $local_host -P $local_port -e "select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"|grep $local_host|grep "PRIMARY"|awk '{print $2}'|wc -l`
remote_st=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $remote_host -P $remote_port -e "select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"|grep $remote_host|grep "PRIMARY"|awk '{print $2}'|wc -l`

#1.两台同时reboot
if [[ $local_st -eq 0 && $remote_st -eq 0 ]]; then
        # 比较 GTID 值
        if [ "$LOCAL_GTID" \> "$REMOTE_GTID" ]||[ "$LOCAL_GTID" = "$REMOTE_GTID" ]; then
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;"
        sleep 3
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$remote_host" -P "$remote_port" -e "START GROUP_REPLICATION;"
echo "1"
  else
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$remote_host" -P "$remote_host" -e "SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;"
        sleep 3
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "START GROUP_REPLICATION;"  
echo "2"
        fi
fi
#2.本地reboot加入集群，远程正常主库状态
if [[ $local_st -eq 0 && $remote_st -gt 0 ]]; then
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "START GROUP_REPLICATION;"
fi
#3.本地主库，远程reboot加入集群备机
if [[ $local_st -gt 0 && $remote_st -eq 0 ]]; then
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "START GROUP_REPLICATION;"
echo "4"
fi
[root@db1 ~]# chkconfig --list|grep mysql

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysql           0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@db1 ~]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:systemd-sysv-generator(8)
[root@db1 ~]# 

复制

节点2：


[root@db2 ~]# mysql -uroot -pxxxxxx -Dprebill
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 102
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 773123a4-daad-11ee-930b-3009f923fbf1 | 192.168.1.51   |        3307 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
| group_replication_applier | b2b77622-daad-11ee-a732-3009f925119f | 192.168.1.52   |        3307 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@db2 ~]# ip a|grep 192.168
    inet 192.168.1.51/24 brd 192.168.1.255 scope global noprefixroute bond0
    inet 192.168.1.50/24 brd 192.168.1.255 scope global secondary bond0:1
[root@db2 ~]# crontab -l
* * * * * /etc/vip_check.sh > /dev/null 2>&1
[root@db2 ~]# cat /etc/vip_check.sh 
step=3
for ((i = 0; i < 60; i = (i + step))); do
    $(/etc/vip.sh)
    sleep $step
done
exit 0
[root@db2 ~]# cat /etc/vip.sh
#!/bin/bash
dbstats=`/db/mysql/mysql-8.0.36/bin/mysql -u root -pxxxxxx -P 3307 -e "select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"|grep "192.168.1.51"|awk '{print $2}'|grep "PRIMARY"|wc -l`
ip=`/usr/sbin/ip a|grep bond0:1|wc -l`
 
if [[ "${dbstats}" -eq 1 ]] ; then
    if [[ "${ip}" -eq 0 ]]; then
    /usr/sbin/ifconfig bond0:1 192.168.1.50 netmask 255.255.255.0 up
    /usr/sbin/arping -b -s 192.168.1.50 192.168.1.3 -c 3
    fi
else
    if [[ "${ip}" -gt 0 ]]; then
    /usr/sbin/ifconfig bond0:1 down
    fi
fi
[root@db2 ~]# cat /etc/rc.local 
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.

touch /var/lock/subsys/local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
/etc/automgr.sh
[root@db2 ~]# cat /etc/automgr.sh 
#!/bin/bash

##延迟等待6秒mysql自启动service mysql status
service mysql start
sleep 3

# MySQL 用户名和密码
MYSQL_USER="root"
MYSQL_PASSWORD="xxxxxx"
local_host=192.168.1.51
local_port=3307
remote_host=192.168.1.52
remote_port=3307

# 获取主服务器和从服务器的 GTID 值
LOCAL_GTID=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $local_host -P $local_port -e "SELECT @@gtid_executed\G" | grep "gtid_executed"|awk '{print $2}'`
REMOTE_GTID=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $remote_host -P $remote_port -e "SELECT @@gtid_executed\G" | grep "gtid_executed"|awk '{print $2}'`

#检查集群状态：
local_st=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $local_host -P $local_port -e "select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"|grep $local_host|grep "PRIMARY"|awk '{print $2}'|wc -l`
remote_st=`/db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h $remote_host -P $remote_port -e "select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"|grep $remote_host|grep "PRIMARY"|awk '{print $2}'|wc -l`

#1.两台同时reboot
if [[ $local_st -eq 0 && $remote_st -eq 0 ]]; then
        # 比较 GTID 值
        if [ "$LOCAL_GTID" \> "$REMOTE_GTID" ]||[ "$LOCAL_GTID" = "$REMOTE_GTID" ]; then
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;"
        sleep 3
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$remote_host" -P "$remote_port" -e "START GROUP_REPLICATION;"
  else
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$remote_host" -P "$remote_host" -e "SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;"
        sleep 3
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "START GROUP_REPLICATION;"  
        fi
fi
#2.本地reboot加入集群，远程正常主库状态
if [[ $local_st -eq 0 && $remote_st -gt 0 ]]; then
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "START GROUP_REPLICATION;"
fi
#3.本地主库，远程reboot加入集群备机
if [[ $local_st -gt 0 && $remote_st -eq 0 ]]; then
        /db/mysql/mysql-8.0.36/bin/mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$local_host" -P "$local_port" -e "START GROUP_REPLICATION;"
fi

复制
mgr
最后修改时间：2024-07-23 09:31:30
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

目录
  • 节点1：
  • 节点2：