MySQL主从复制最佳实践-亲试ok

#!/bin/bash

export master_ip=192.168.7.199

export slave_ip=192.168.7.244

export root_password=123456

#postion地址，注意这里不能用$ip ""格式 也不能用awk指令，不知道为什么

postion_num=$(ssh -Tq $master_ip <<eof

    mysql -uroot -p${root_passwd} -h$master_ip  -e 'show master status\G' 2>/dev/null|grep Position |cut -f2 -d :

eof

)

#bin-log文件名

log_name=$(ssh -Tq $master_ip <<eof

    mysql -uroot -p${root_passwd} -h$master_ip  -e 'show master status\G' 2>/dev/null|grep File |cut -f2 -d :

eof

)

#从mysql设置密码和配置

ssh -Tq ${slave_ip} <<EOF

mysql  -h$slave_ip -uroot -p123456 <<eof

set password = password("${root_passwd}");

stop slave;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '${root_passwd}';

GRANT ALL PRIVILEGES ON *.* TO 'root'@localhost IDENTIFIED BY '${root_passwd}';

change master to master_host='${master_ip}',master_port=3306,master_user='${r_user}',master_password='${r_passwd}', master_log_file='${log_name}',master_log_pos=${postion_num};

start slave;

eof

EOF

#检查是否主从复制搭建成功

running_num=$(ssh -Tq $slave_ip <<eof

   mysql -h$slave_ip -uroot -p${root_passwd} -e "show slave status\G" 2>/dev/null |grep "Running:"|wc -l

eof

)

if [ $running_num -eq 2 ];then

     echo 2 "mysql主从复制搭建成功"     

  else

     echo 3  "mysql主从搭建失败"

fi