mysql自动化同步校验_Shell: 分享MySQL数据同步+主从复制自动化脚本_20190313_七侠镇莫尛貝...

做mysql主从复制，要锁master表，导出数据，解锁master表，同步数据，查master的log偏移，配置slave的log偏移,开启slave复制。如果是做双向同步，还得再来一遍。麻烦死！

使用这个脚本，可以省不少时间。

作用：运行于mysql slave服务器上，添加到crontab计划任务里，可定期同步数据，并重置mysql slave配置。

使用方法：

my.cnf配置过程略。

配置好shell里的主服务器ip，用户名，密码。

手动创建rep用户，密码password.

添加到计划任务里，OK。

写的乱，有时间再梳毛。

#!/bin/bash

cd /opt/db_sync

timestamp=$(date +%Y%m%d_%H%M%S)

HOSTNAME_EX="192.168.100.100" #数据库Server信息

HOSTNAME="127.0.0.1" #数据库Server信息

#HOSTNAME="mysql" #数据库Server信息

PORT="3306"

USERNAME="user"

#PASSWORD="password"

export MYSQL_PWD="password"

DBNAME=$1 #要创建的数据库的库名称

#DBNAME="test_db_name" #要创建的数据库的库名称

TABLENAME="test_table_name" #要创建的数据库的表的名称

export PATH=.:$PATH

#MYSQL_CMD="mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}"

MYSQL_CMD="mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} "

MYSQL_CMD_EX="mysql -h${HOSTNAME_EX} -P${PORT} -u${USERNAME}"

#echo ${MYSQL_CMD}

#sql="show slave status \G"

#echo ${sql} | ${MYSQL_CMD} |grep Slave_IO_Running:

#echo ${sql} | ${MYSQL_CMD} |grep Slave_SQL_Running:

#if [ $? -ne 0 ] #判断是否创建成功

#then

# echo "connecting databases ${DBNAME} failed ..."

# exit 1

#fi

################################################################################

echo "master: lock master tables..."

sql="flush tables with read lock;"

echo ${sql} | ${MYSQL_CMD_EX}

sql="show master status \G"

#echo ${sql} | ${MYSQL_CMD_EX} |grep File

#echo ${sql} | ${MYSQL_CMD_EX} |grep Position

FILE=$(echo ${sql} | ${MYSQL_CMD_EX} |grep File|awk '{ print $2}')

POSITION=$(echo ${sql} | ${MYSQL_CMD_EX} |grep Position|awk '{ print $2}')

echo $FILE

echo $POSITION

#################################################

sql="stop slave ;"

echo ${sql} | ${MYSQL_CMD} |grep Slave_IO_Running:

echo "get dbname_master.sql..."

mysqldump -h192.168.100.100 -uuser dbname > dbname_master..sql

sql="drop database IF EXISTS dbname ;"

echo ${sql} | ${MYSQL_CMD}

sql="create database IF NOT EXISTS dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"

echo ${sql} | ${MYSQL_CMD}

echo "import dbname_master.sql to db_slave ..."

mysql -uuser dbname<.>

#####################################################

echo "master: unlock master tables..."

sql="unlock tables ;"

echo ${sql} | ${MYSQL_CMD_EX}

#####################################################

sql="reset slave ;"

echo ${sql} | ${MYSQL_CMD}

#rep用户手动创建，分配权限

sql="change master to master_host='192.168.100.100',master_user='rep',master_password='password',master_log_file='$FILE',master_log_pos=$POSITION;"

echo $sql

echo ${sql} | ${MYSQL_CMD}

sql="start slave ;"

echo ${sql} | ${MYSQL_CMD}

sleep 3

sql="show slave status \G"

#echo ${sql} | ${MYSQL_CMD}

echo "=================================================="

echo ${sql} | ${MYSQL_CMD} |grep Slave_IO_Running:

echo ${sql} | ${MYSQL_CMD} |grep Slave_SQL_Running:

echo "$timestamp db_sync OK!">> db_sync.log