mariadb主从复制
#!/bin/bash
# by author:Ryan
# 一键部署mariadb主从复制
MYSQL_SOFT="mariadb-server"
NUM=`rpm -qa | grep -i $MYSQL_SOFT | wc -l`
MASTER_MYSQL_IP=192.168.30.165
BACKUP_MYSQL_IP=192.168.30.195
#配置数据库yum源
config_mariadb_yum() {
cat>/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name=MariaDB
enabled=1
gpgcheck=1
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
EOF
if [ $? -eq 0 ] ;then
echo -e "********MariaDB yum源配置完毕!********\n"
fi
}
#下载并启动数据库函数
yum_install_mariadb(){
if [ -f "etc/yum.repos.d/mariadb.repo" -o $NUM -ne 0 ];then
echo -e "*****yum源文件已存在!开始下载mariadb*****\n"
yum install $MYSQL_SOFT -y
else
echo -e "********yum源文件不存在!********\n"
echo "正在配置MariaDB yum源......"
config_mariadb_yum
echo "开始下载MariaDB......"
yum install $MYSQL_SOFT -y
if [ $? -eq 0 ];then
systemctl restart mariadb
if [ $? -eq 0 ];then
PORT=`ss -tnl | grep 3306 | wc -l`
if [ $PORT -ne 0 ];then
echo -e "********数据库启动成功********\n"
fi
fi
systemctl enable mariadb
fi
fi
}
#配置主库所需配置文件
master_config(){
cat>/etc/my.cnf.d/server.cnf<<EOF
[server]
server-id=1
log-bin=mysql-bin
[mysqld]
[galera]
[embedded]
[mariadb]
[mariadb-10.3]
EOF
}
#置从库所需配置文件
backup_config(){
ssh root@$BACKUP_MYSQL_IP > /dev/null 2>&1 << eeooff
cat>/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name=MariaDB
enabled=1
gpgcheck=1
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
EOF
yum install $MYSQL_SOFT -y
mv /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.bak
cat>/etc/my.cnf.d/server.cnf<<EOF
[server]
server-id=2
[mysqld]
[galera]
[embedded]
[mariadb]
[mariadb-10.3]
EOF
systemctl start mariadb
systemctl enable mariadb
mysql -uroot -e "change master to master_host='$MASTER_MYSQL_IP',master_user='slave',master_password='slave',master_log_file='$MYSQL_BINFILE',master_log_pos=$MYSQL_POSITION;"
mysql -uroot -e "start slave;"
exit
eeooff
}
#配置免密登录函数
free_login(){
echo -e "********开始配置免密登录********\n\n********开始安装expect工具********\n"
yum install expect -y #安装expect
echo "按enter键3次即可"
ssh-keygen -t rsa #生成秘钥(按enter键3次即可生成)
SERVERS=$BACKUP_MYSQL_IP #需要配置的主机名,多个用空格间隔
PASSWORD=root #需要配置的主机登录密码
#将本机生成的公钥复制到其他机子上
#如果(yes/no)则自动选择yes继续下一步
#如果password:怎自动将PASSWORD写在后面继续下一步
auto_ssh_copy_id(){
expect -c "set timeout -1;
spawn ssh-copy-id $1;
expect {
*(yes/no)* {send -- yes\r;exp_continue;}
*password:* {send -- $2\r;exp_continue;}
eof {exit 0;}
}";
}
ssh_copy_id_to_all(){
for SERVER in $SERVERS #遍历要发送到各个主机的ip
do
auto_ssh_copy_id $SERVER $PASSWORD
done
}
ssh_copy_id_to_all
}
#配置主从复制
echo -e "********请选择配置主库或从库********\n1.主库\n2.从库"
read -p "please choose number:" MYSQL_NUM
#配置主库
if [ $MYSQL_NUM -eq 1 ];then
echo -e "********开始配置主库********\n"
yum_install_mariadb
mv /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.bak
master_config
if [ $? -eq 0 ] ;then
systemctl restart mariadb
systemctl enable mariadb
echo -e "********主库配置server文件并重启数据库完成!********\n"
#创建主从连接账号与授权
mysql -uroot -e "CREATE USER 'slave'@'%' IDENTIFIED BY 'slave';"
mysql -uroot -e "GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';"
mysql -uroot -e "flush privileges;"
echo "********主从连接账号与授权创建完成********"
fi
#配置从库
elif [ $MYSQL_NUM -eq 2 ];then
MYSQL_BINFILE=`mysql -uroot -e "show master status;" | grep bin | awk '{print $1}'`
MYSQL_POSITION=`mysql -uroot -e "show master status;" | grep bin | awk '{print $2}'`
echo -e "********开始配置免密登录********\n"
free_login
echo -e "********免密登录配置完毕********\n"
echo -e "********开始配置从库********\n"
backup_config
echo -e "********从库配置完毕********\n"
ssh -l root $BACKUP_MYSQL_IP "mysql -uroot -e \"show slave status\G;\""
fi