345 views
Linux-shell脚本

Mysql主从复制shell脚本

mysql_master:192.168.77.61 mysql_slave=192.168.77.62

一、思路:

1、MYSQL主库的操作:

主库上安装MYSQL,设置server-id、bin-log;

授权复制同步的用户,对客户端授权;

确认bin-log文件名、position位置点。

2、MYSQL丛库的操作:

从库上安装MYSQL,设置server-id;

change master 指定主库和bin-log名和position;

start slave; 启动丛库IO线程;

show slave status\G查看主从的状态。

二、手动配置免密登陆

首先需要将两台服务器配置免密码登陆

#Master操作

[code]

ssh-keygen -t rsa -P ”

ssh-copy-id root@192.168.77.62

[/code]

配置好后,就可以直接ssh 192.168.77.62 登陆了

三、具体实施

[code]

#!/bin/bash

#Auto install Mysql AB Repliation

#By ying

#2017年8月16日09:23:19

#设置变量

MYSQL_SOFT=”mysql mysql-server mysql-devel”

NUM=`rpm -qa |grep -i mysql |wc -l`

INIT=”/etc/init.d/mysqld”

CODE=$?

#判断mysql是否存在,存在则删除,不存在则安装

if [ $NUM -ne 0 -a -f $INIT ];then

    echo -e “\033[32mThis Server Mysql already Install.\033[0m”

    read -p “Please ensure yum remove Mysql Server,YES or NO”: INPUT

    if [ $INPUT == “y” -o $INPUT == “yes” ];then

         yum remove $MYSQL_SOFT -y ; rm -rf /var/lib/mysql /etc/my.cnf

         yum install $MYSQL_SOFT -y

    else

        continue        

    fi

else

    yum remove $MYSQL_SOFT -y ; rm -rf /var/lib/mysql /etc/my.cnf

    yum install $MYSQL_SOFT -y

    if [ $CODE -eq 0 ]; then

        echo -e “\033[32mThe Mysql Install Successfully.\033[0m”

    else

        echo -e “\033[32mThe Mysql Install Failed.\033[0m”

        exit 1

    fi

fi

cat >/etc/my.cnf<<EOF

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

log-bin=mysql-bin

server-id = 1

auto_increment_offset=1

auto_increment_increment=2

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

EOF

/etc/init.d/mysqld restart

ps -ef |grep mysql

#Master Config Mysql

mysql -e “grant replication slave on *.* to ‘tongbu’@’%’ identified by ‘123456’; ”

mysql -e “create database ying;”

mysqldump ying > /tmp/ying.sql

MASTER_FILE=`mysql -e “show master status; “|tail -1|awk ‘{print $1}’`

MASTER_POS=`mysql -e “show master status; “|tail -1|awk ‘{print $2}’`

MASTER_IPADDR=`ifconfig eth0|grep “Bcast”|awk ‘{print $2}’|cut -d: -f2`

read -p “Please Input Slave IPaddr: ” SLAVE_IPADDR

#Slave Config Mysql

ssh $SLAVE_IPADDR “yum remove $MYSQL_SOFT -y ; rm -rf /var/lib/mysql /etc/my.cnf ; yum install $MYSQL_SOFT -y”

ssh $SLAVE_IPADDR “rm -rf /etc/my.cnf”

scp -r /etc/my.cnf root@$SLAVE_IPADDR:/etc/

ssh $SLAVE_IPADDR “sed -i ‘s#server-id = 1#server-id = 2#g’ /etc/my.cnf”

ssh $SLAVE_IPADDR “sed -i ‘/log-bin=mysql-bin/d’ /etc/my.cnf”

ssh $SLAVE_IPADDR “/etc/init.d/mysqld restart”

ssh $SLAVE_IPADDR “mysql -e \”create database ying;\””

ssh $SLAVE_IPADDR “mysql ying < $MASTER_IPADDR:/tmp/ying.sql”

ssh $SLAVE_IPADDR “mysql -e \”change master to master_host=’$MASTER_IPADDR’,master_user=’tongbu’,master_password=’123456′,master_log_file=’$MASTER_FILE’,master_log_pos=$MASTER_POS; \””

ssh $SLAVE_IPADDR “mysql -e \”slave start; \””

ssh $SLAVE_IPADDR “mysql -e \”show slave status\G; \””

[/code]

Leave a Reply

影子专属博客 赣ICP备17013143号