一个自动配置mysql主从的shell脚本

#!/bin/bash
export mysqlbinpath="/usr/bin" #mysql路径
    
#variables for master
export master_mysql_root_passwd="root"  #mysql主数据库的root密码
export replication_user="copydb"  #用于复制的mysql用户
export replication_passwd="123456" #mysql用户copydb的密码
export replication_db="centos" #需要同步的数据库名
export master_ip="8.8.8.8"    #mysql主服务器IP
    
#variables for slave
export slave_mysql_root_passwd="123456" #mysql从数据库的root密码
export slave_ip="8.8.4.4"               #从服务器IP地址
export slave_ssh_root_passwd="123456"  #mysql从数据库的ssh的root密码
    
#create replication user
{
    ${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} <<EOF
    CREATE USER '$replication_user'@'$slave_ip' IDENTIFIED BY '$replication_passwd';
    GRANT REPLICATION SLAVE ON *.* TO '$replication_user'@'$slave_ip' IDENTIFIED BY '$replication_passwd';
    FLUSH TABLES WITH READ LOCK;
    select sleep(10);
    EOF
    } &
    
    #export the database sql data.
    ${mysqlbinpath}/mysqldump -uroot -p${master_mysql_root_passwd} ${replication_db} > ${replication_db}.sql
    
    #get the master status info.
    export status=`${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} -e"show master statusG"`
    export binlogname=`echo "$status" | grep "File" | linuxjishu/13830.html target=_blank class=infotextkey>awk '{print $2}'`
    export position=`echo "$status" | grep "Position" | awk '{print $2}'`
    
    #create database on slave server.
    export createdb="${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} -e'drop database if exists ${replication_db};create database ${replication_db};'"
    
    #import database sql data on slave server.
    export importsql="${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} ${replication_db} < /root/${replication_db}.sql"
    
    #deploy the slave mysql server.
    export change_master="${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} -e'stop slave;CHANGE MASTER TO MASTER_HOST="${master_ip}",MASTER_USER="${replication_user}",MASTER_PASSWORD="${replication_passwd}",MASTER_PORT=3306,MASTER_LOG_FILE="${binlogname}",MASTER_LOG_POS=${position},MASTER_CONNECT_RETRY=10;start slave;select sleep(3);show slave statusG'"
    
    yum -y install expect
    ./slave.exp

#!/usr/bin/expect -f
spawn scp $env(replication_db).sql root@$env(slave_ip):/root
expect {
        "*assword" {set timeout 300; send "$env(slave_ssh_root_passwd)r";}
        "yes/no" {send "yesr"; exp_continue;}
}
expect eof
    
spawn ssh root@$env(slave_ip) "$env(createdb);$env(importsql);$env(change_master)"
expect {
    "*assword" {set timeout 300; send "$env(slave_ssh_root_passwd)r";}
   "yes/no" {send "yesr"; exp_continue;}
}
expect eof