主从复制脚本最佳实践-亲试ok(附带颜色标识输出值)-202005272114-20201020
#########################################################################
# File Name: 主从同步 #
# Author: hui #
# mail: unknown #
# Created Time: 2020年 05月 27日 星期三 21:14:42 CST #
#########################################################################
#!/bin/bash
export master_ip=192.168.1.123
export slave_ip=192.168.1.254
export root_passwd=123456
echo -e "\033[颜色1;颜色2m#1.取Master主机ip的后三位作为master的server_id \033[0m"
export Master_server_id=`echo $master_ip |awk -F . '{print $4}'`
echo -e "\033[颜色1;颜色2m The Master db server_id is $Master_server_id \033[0m"
echo -e "\033[颜色1;颜色2m#2.取Slave主机ip的后三位作为slave的server_id \033[0m"
export Slave_server_id=`echo $slave_ip |awk -F . '{print $4}'`
echo -e "\033[颜色1;颜色2m The Slave db server_id is $Slave_server_id \033[0m"
echo -e "\033[颜色1;颜色2m#3.取出Master主库的log_name \033[0m"
log_name=$(ssh -Tq $master_ip <<eof
mysql -uroot -p123456 -e 'show master status\G' 2>/dev/null|grep File |cut -f2 -d :
eof
)
echo -e "\033[颜色1;颜色2m Master主库的log_name is $log_name \033[0m"
echo -e "\033[颜色1;颜色2m#4.取出Master主库的postion \033[0m"
postion_num=$(ssh -Tq $master_ip <<eof
--#mysql -uroot -e 'show master status\G' 2>/dev/null|grep Position |cut -f2 -d :
mysql -uroot -p123456 -h$master_ip -e 'show master status\G' 2>/dev/null|grep Position |cut -f2 -d :
eof
)
echo -e "\033[颜色1;颜色2m Master主库的postion is $postion_num \033[0m"
echo -e "\033[颜色1;颜色2m#5.事先修改master主库的server_id \033[0m"
echo -e "\033[颜色1;颜色2mThe Master db server_id is $Master_server_id\033[0m"
#ssh ${master_ip} <<EOF
mysql -uroot -p123456 -h$master_ip <<eof
set global server_id=$Master_server_id;
show variables like '%server_id%';
eof
service mysql restart
echo -e "\033[颜色1;颜色2m#7.事先修改slave从库的server_id \033[0m"
echo -e "\033[颜色1;颜色2mThe Slave db server_id is $Slave_server_id\033[0m"
mysql -uroot -p123456 -h$slave_ip <<eof
set global server_id=$Slave_server_id;
show variables like '%server_id%';
eof
echo -e "\033[颜色1;颜色2m#8.slave库设置密码和配置 \033[0m"
#ssh -Tq `${slave_ip}` <<EOF
mysql -uroot -p123456 -h192.168.1.254 <<eof
use mysql;
stop slave;
select sysdate();
flush privileges;
#set password = password("${root_passwd}");
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='192.168.7.206',master_port=3309,master_user='root',master_password='123456', master_log_file='bin.000017',master_log_pos=8888;
--#change master to master_host='192.168.7.206',master_port=3309,master_user='root',master_password='123456', master_log_file='${log_name}',master_log_pos=${postion_num};
flush privileges;
eof
echo -e "\033[颜色1;颜色2m#9.slave库做chang master 操作 \033[0m"
ssh -Tq $master_ip <<EOF
service mysql restart
EOF
ssh -Tq $slave_ip <<EOF
service mysql restart
EOF
export master_ip=192.168.1.123
export slave_ip=192.168.1.254
echo -e "\033[颜色1;颜色2m#10.取出master主库的postion \033[0m"
postion_num=$(ssh -Tq $master_ip <<eof
--#mysql -uroot -e 'show master status\G' 2>/dev/null|grep Position |cut -f2 -d :
mysql -uroot -p123456 -h$master_ip -e 'show master status\G' 2>/dev/null|grep Position |cut -f2 -d :
eof
)
echo -e "\033[颜色1;颜色2m#11.取出master主库的log_name \033[0m"
log_name=$(ssh -Tq $master_ip <<eof
mysql -uroot -p123456 -e 'show master status\G' 2>/dev/null|grep File |cut -f2 -d :
eof
)
echo $postion_num
echo $log_name
mysql -uroot -p123456 -h192.168.1.254 <<eof
stop slave;
change master to master_host='${master_ip}',master_port=3306,master_user='root',master_password='123456',master_log_file='${log_name}',master_log_pos=${postion_num};;
start slave;
select sysdate();
show slave status\G;
eof
exit;
