shell script自动搭建简单的mysql主从(replication)

xlid

#!/bin/bash
#
#Shell name:replication_of_mysql.sh
#
#Program:
#    build the msyql of high availability with replication
#    in the lamp env
##
#Author:perofu
#
#Mail:  perofu.com@gmail.com
#
#History:
#    2013/2/12
#Usage:
#    1.every pc-server can be ssh without password
#    2.running in master server
#    3.user and password of mysql should been same
#    4.run:/full/path/replication_of_mysql.sh

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:.
export PATH

##########################variable##########################
##########ip##############
master_ip=192.168.10.3
slave_ip=192.168.10.4

##########user of mysql###
user_root=root
user_passwd=123456

########replication user##
rep_user=repl
rep_passwd=123456

########backup database#ignore mysql...
back_db=perofu

##########configure file of mysql######
conf_file=/etc/my.cnf

##########cmd location#################
mysql_cmd=/usr/local/mysql/bin/mysql
mysql_cmd_start=/usr/local/mysql/bin/mysqld_safe
mysql_dump=/usr/local/mysql/bin/mysqldump

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

fun_check ()
{
    if [ ${UID} -ne "0" ]
    then
        echo "Please run as root!!!"
        exit 77
    fi

    [ -z ${master_ip} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${slave_ip} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${user_root} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${user_passwd} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${rep_user} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${rep_passwd} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${back_db} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${conf_file} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${mysql_cmd} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${mysql_cmd_start} ] && echo "Please input some strings in part of variable" &&exit 77
    [ -z ${mysql_dump} ] && echo "Please input some strings in part of variable" &&exit 77
   
    #check backup database
    echo "show databases;" |${mysql_cmd} -u${user_root} -p${user_passwd} |grep ${back_db} &> /dev/null
   
    if [ $? -ne "0" ]
    then
        echo "${back_db} is not exist!!!"
        exit 77
    fi
}

####################main#####################

fun_check

#########master##########
#change my.cnf

grep "^log-bin=mysql-bin" ${conf_file} &> /dev/null

if [ $? -ne "0" ]
then
    ed -s ${conf_file} <<EOF
    /#log-bin=mysql-bin/s/#log-bin=mysql-bin/log-bin=mysql-bin/
    w
    q
EOF
fi

ed -s ${conf_file} <<EOF
/^server
a
binlog-do-db=${back_db}
binlog-ignore-db=mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
skip-name-resolve
.
w
q
EOF

#start mysql
#pkill mysqld;pkill mysqld;pkill mysqld
#nohup ${mysql_cmd_start} --user=mysql --defaults-file=${conf_file} &
/etc/init.d/mysqld restart

#add slave user in master server for some slave servers

for i in ${slave_ip}
do
    ${mysql_cmd} -u${user_root} -p${user_passwd} <<EOF
    grant replication slave,replication client on *.* to "${rep_user}"@"${i}" identified by "${rep_passwd}";
    flush privileges;
EOF
done

#lock tables in master server

${mysql_cmd} -u${user_root} -p${user_passwd} <<EOF
    flush tables with read lock;
EOF

#backup

${mysql_dump} --master-data -u${user_root} -p${user_passwd} ${back_db} > /root/${back_db}.sql

#backup to slave server and create database

for ip in ${slave_ip}
do
    scp -p ${back_db}.sql ${ip}:/root/
   
    #create database
    ssh ${ip} ${mysql_cmd} -u${user_root} -p${user_passwd} <<EOF
    create database ${back_db};
    use ${back_db}
    source /root/${back_db}.sql;
    grant replication slave,replication client on *.* to "${rep_user}"@"${master_ip}" identified by "${rep_passwd}";
    flush privileges;
EOF
done

#ulock tables in master server

${mysql_cmd} -u${user_root} -p${user_passwd} <<EOF
    unlock tables;
EOF

#change my.cnf in slave server

for ips in ${slave_ip}
do
    n=2
    ssh ${ips} ed -s ${conf_file} <<EOF
/^server-id/d
/#server-id
a
server-id=${n}
log-slave-updates
.
w
q
EOF
    n=$((n+1))
done

#start mysql in slave server

for ipss in ${slave_ip}
do
    #ssh ${ipss} 'pkill mysqld;pkill mysqld;pkill mysqld;'
    #ssh ${ipss} nohup ${mysql_cmd_start} --user=mysql --defaults-file=${conf_file} &
    ssh ${ipss} /etc/init.d/mysqld restart
done

#variable of master log

log=$(echo "show master status;" | ${mysql_cmd} -u${user_root} -p${user_passwd} |tail -n1 |awk '{print $1}')
pos=$(echo "show master status;" | ${mysql_cmd} -u${user_root} -p${user_passwd} |tail -n1 |awk '{print $2}')

for ipsss in ${slave_ip}
do
    ssh ${ipsss} ${mysql_cmd} -u${user_root} -p${user_passwd} <<EOF
    slave stop;
    change master to master_host="${master_ip}",master_user="${rep_user}",master_password="${rep_passwd}",master_log_file="${log}",master_log_pos=${pos};
    slave start;
EOF
done

#check replication status is ok in slave master

for ipssss in ${slave_ip}
do
    io=$(echo 'show slave status\G'|${mysql_cmd} -u${rep_user} -p${rep_passwd} -h ${ipssss} |grep Running|awk '{print $2}'|head -n1)
    sql=$(echo 'show slave status\G'|${mysql_cmd} -u${rep_user} -p${rep_passwd} -h ${ipssss} |grep Running|awk '{print $2}'|tail -n1)

    if [[ ${io} = Yes ]] && [[ ${sql} = Yes ]]
    then
        echo "slave of ${ipssss} is running"
    else
        echo "slave of ${ipssss} is stop"
    fi
done

0

.其实我是一个天才，可惜天妒英才！

帅帅男孩

如果跟导师讲不清楚，那么就把他搞胡涂吧!

yzwj2004

穿别人的鞋，走自己的路，让他们找去吧。

jxdiscuz

爱她，就请为她做无痛人流手术!

fjptec-xm

恐龙说：“遇到色狼，不慌不忙；遇到禽兽，慢慢享受……”

爱在莫斯科

生活***好玩，因为生活老***玩我!