shell mysql双主自动配置脚本

在两台机器的Mysql上配置好双主模式，因为经常配置，所以写个脚本，主从模式的脚本应该类似

脚本需要两个参数，为两台机器的ip

WORKDIR="/tmp/"        
CONN_STR="/usr/bin/mysql -utest -p123456"   
IP1=$1
IP2=$2

1、生成默认的配置sql文件：

function checkDefaultSql()
{
	if [ ! -f $WORKDIR/2master-defaul.sql ];then
                echo "file not exist: $WORKDIR/2master-defaul.sql "
                echo "creating file:$WORKDIR/2master-defaul.sql"
                cat >> $WORKDIR/2master-defaul.sql <<EOF
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*  TO backup@'masterip' IDENTIFIED BY 'backup'; 
CHANGE MASTER TO MASTER_HOST='masterip',MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='log_file',MASTER_LOG_POS=log_pos;
start slave;
flush privileges;
EOF
                echo "created file done"
        fi
}

2、修改其中一个ip上的数据库的server-id

function modifyServerId()
{
	#ssh $ip2 "sed -i 's/^server-id.*$/server-id = 2/g' /etc/my.cnf"
	echo "modify server-id on $IP2 and restart mysql:"
        #修改server-id为2并重启数据库
       ssh $IP2 "sed -i 's/^server-id.*$/server-id = 2/g' /etc/my.cnf;/etc/init.d/mysql restart"
	[[ $? -eq 0 ]] || exit 1
}

3、替换掉默认配置sql文件中的一些字段,生成真正的sql文件，并向数据库导入sql文件

function configMaster()
{
	localip=$1
	masterip=$2
        #获取master status的状态值
       status_a=`$CONN_STR -h $masterip -e "show master status" | sed '1d'`
	[[ ! -n "$status_a" ]] && exit 1
	log_file=`echo ${status_a} | awk '{print $1}'`              #获取log_file
	log_pos=`echo ${status_a} | awk '{print $2}'`               #获取log_pos
	echo "Generating master config file..."
	cp $WORKDIR/2master-defaul.sql $WORKDIR/2master-$localip.sql
        #替换的sql中masterip,log_file,log_pos为真实值
       sed -i -e "s/masterip/$masterip/g" -e "s/log_file/$log_file/g" -e "s/log_pos/$log_pos/g" $WORKDIR/2master-$localip.sql
	echo "configing master and privileges on $1..."
        #向mysql注入配置命令
       $CONN_STR -h $localip < $WORKDIR/2master-$localip.sql
	if [ $? -eq 0 ];then
		echo "config on $localip success" 
	else
		echo "config on $localip fail"
		exit 1
	fi
}

4、检查slave状态

function checkSlaveStatus()
{
	ip1_s=`$CONN_STR -h $IP1 -e "show slave status\G;" | grep "Slave_IO_Running"`
	ip2_s=`$CONN_STR -h $IP2 -e "show slave status\G;" | grep "Slave_IO_Running"`
	echo $ip1_s
	echo $ip2_s
	[[ "$ip1_s" =~ "Yes" ]] && [[ "$ip2_s" =~ "Yes" ]] && echo "2master between $IP1 and $IP2 is running now"
}

基本的函数就是这些了，再写一些东西调用这些函数就可以了