Shell Scripts for MySQL Database Backup and Master‑Master Replication

This guide presents two Bash scripts that automate MySQL maintenance tasks on a Linux server: one for daily backup of databases and another for configuring master‑master replication.

Backup script creates a backup directory, logs the start time, removes archives older than seven days, dumps each specified database, compresses the dump, and records the end time.

#!/bin/bash
#########################################
DATE=date +%Y-%m-%d
if [ -d /usr/local/bin/dbbackup ] ;then
    echo "exists"
else
    mkdir -p /usr/local/bin/dbbackup
fi
cd /usr/local/bin/dbbackup
#开始备份时间
DAT=date +%H:%M:%S
echo "================$DATE================" >> backup.log
echo "start bak    date :  $DAT" >> backup.log
#删除7天之前的备份文件
find /usr/local/bin/dbbackup -mtime +7 -name "*.tar.gz"|xargs rm -f
#循环备份每个数据库里面的表
for i in  ctrip
do
    /usr/local/mysql/bin/mysqldump -uroot -p123456 $i --single-transaction --set-gtid-purged=OFF > $i.sql
    tar -czf  $DATE.$i.tar.gz $i.sql
    rm -rf $i.sql
done
DAT=date +%H:%M:%S
echo "end time :  $DAT" >> backup.log
echo "               " >> backup.log

Master‑master configuration script validates input parameters, retrieves the current binary log file and position from the master, and issues MySQL commands to set up replication on the slave.

#!/bin/bash
set -e
source /etc/profile
cmd=$(cd dirname $0;pwd)
master=$1
slave=$2
if [ "$1" == "" ];then
    echo -e "请在脚本后输入主服务器ip地址"
    exit 1
fi
if [ "$2" == "" ];then
    echo -e "请在脚本后输入从服务器ip地址"
    exit 1
fi
bin_log=/usr/local/mysql/bin/mysql -uroot -p123456 -h $master -e "show master status;" | awk '/mysql-bin/{print $1}'
pos_ID=/usr/local/mysql/bin/mysql -uroot -p123456 -h $master -e "show master status;" | awk '/mysql-bin/{print $2}'
#同步bin_log#
/usr/local/mysql/bin/mysql -uroot -p123456 --connect-expired-password <
Both scripts are intended to be run on a Linux host with MySQL installed, and they illustrate practical automation techniques useful for database administrators and DevOps engineers.