MySQL主从复制自动化脚本

master

#!/bin/bash
sed 's/server-id\t=.*/server-id\t= 10/g' -i /etc/my.cnf
sed 's/log-bin=mysql-bin/log-bin=master-bin/g' -i /etc/my.cnf
sed '/log-bin=master-bin/a\log-slave-updates=true' -i /etc/my.cnf
service mysqld restart
mysql -u root -p123.com -e "grant replication client,replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123.com';"
mysql -u root -p123.com -e "flush privileges;"

slave

#!/bin/bash
sed 's/server-id\t=.*/server-id\t= 20/g' -i /etc/my.cnf
sed '/server-id/a\relay-log=relay-log-bin' -i /etc/my.cnf
sed '/server-id/a\relay-log-index=slave-relay-bin.index' -i /etc/my.cnf
service mysqld restart

master=`mysql -h 192.168.1.1 -u myslave -p123.com -e "show master status;" | grep master | awk '{print $1}'`
pos=`mysql -h 192.168.1.1 -u myslave -p123.com -e "show master status;" | grep master | awk '{print $2}'`
mysql -u root -p123.com -e "change master to master_host='192.168.1.1',master_user='myslave',master_password='123.com',master_log_file='$master',master_log_pos=$pos;"
mysql -u root -p123.com -e "start slave;"
mysql -u root -p123.com -e "show slave status \G"

tip:

1.主从数据库的服务器ID号不能相同
2.主从数据的IP地址要根据实际情况来配置。