主从复制脚本-05151756-20201020

#!/bin/bash

export master_ip=192.168.1.123

export slave_ip=192.168.1.254

export root_passwd=123456

echo '  #1.取Master主机ip的后三位作为master的server_id'

export Master_server_id=`echo $master_ip |awk -F . '{print $4}'`

echo 'The Master db server_id is ' $Master_server_id

 

echo '  #2.取Slave主机ip的后三位作为slave的server_id'

export Slave_server_id=`echo $slave_ip |awk -F . '{print $4}'`

echo 'The Slave db server_id is ' $Slave_server_id

 

echo '  #3.取出Master主库的log_name'

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 'Master主库的log_name is ' $log_name

echo '  #4.取出Master主库的postion'

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

mster_server_idysql -uroot   -p123456 -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 'Master主库的postion is ' $postion_num

echo '  #5.事先修改master主库的server_id'

echo 'The Master db server_id is ' $Master_server_id

#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 '  #7.事先修改slave从库的server_id'

echo 'The Slave db server_id is ' $Slave_server_id

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

set global server_id=$Slave_server_id;

show variables like '%server_id%';

eof

echo '  #8.slave库设置密码和配置'

#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 '  #9.slave库做chang master 操作'

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 '  #10.取出master主库的postion'

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 '  #11.取出master主库的log_name'

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

--#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='${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;