MySQL 重做从库脚本

#!/bin/bash

date;

/home/mysql/mysql-8.0.16/bin/mysqldump -h 10.10.10.1 -P3306 -u root -p123456 --all-databases --routines --events --triggers --master-data=2 --single-transaction > /data/3306/a.sql
date;

/home/mysql/mysql-8.0.16/bin/mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "
set global super_read_only =0;
stop slave; 
reset slave all; 
reset master;"
/home/mysql/mysql-8.0.16/bin/mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock < /data/3306/a.sql

date;

master_log_file=`egrep -m 1 'MASTER_LOG_FILE' /data/3306/a.sql | awk -F"'" '{print $2}'`
master_log_pos=`egrep -m 1 'MASTER_LOG_POS' /data/3306/a.sql | awk -F"=" '{print substr($3, 1, length($3) - 1)}'`

/home/mysql/mysql-8.0.16/bin/mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "
change master to
master_host='10.10.10.1',
master_port=3306,
master_user='root',
master_password='123456',
master_log_file='$master_log_file',
master_log_pos=$master_log_pos;

start slave;"

/home/mysql/mysql-8.0.16/bin/mysqladmin -uroot -p123456 -S /data/3306/mysqldata/mysql.sock shutdown
/home/mysql/mysql-8.0.16/bin/mysqld_safe --defaults-file=/home/mysql/my_3306.cnf &

counter=0

# 等待MySQL服务启动
until /home/mysql/mysql-8.0.16/bin/mysqladmin -uroot -p123456 -S /data/3306/mysqldata/mysql.sock ping >/dev/null 2>&1; do
  echo "MySQL is starting, waiting for it to be available..."
  sleep 1
  ((counter++))
  if [ $counter -eq 60 ]; then
      echo "mysql startup failed !!!"
      exit
  fi
done

/home/mysql/mysql-8.0.16/bin/mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "show slave status\G" | egrep 'Seconds_Behind_Master|Retrieved_Gtid_Set'

date;

nohup ~/reset_slave.sh > ~/reset_slave.log 2>&1 &