mysql 10分钟半自动搭建一套mgr

精选原创

xuebaoling 2020-11-13 15:47:48 ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者xuebaoling的原创作品，请联系作者获取转载授权，否则将追究法律责任

linux：CentOS Linux release 7.8.2003 (Core) mysql：Ver 8.0.18 for el7 on x86_64 (MySQL Community Server - GPL)

一、搭建过程 1、服务器规划 192.168.17.170 中控机 192.168.17.175 mysql01 192.168.17.176 mysql02 192.168.17.177 mysql03 2、服务器初始化脚本hostinit.sh #/bin/bash #sh hostinit.sh mysql01 192.168.17.175 hostnamectl set-hostname $1 echo "IPADDR=$2" >>/etc/sysconfig/network-scripts/ifcfg-ens33 echo "NETMASK=255.255.255.0" >>/etc/sysconfig/network-scripts/ifcfg-ens33 echo "GATEWAY=192.168.17.2" >>/etc/sysconfig/network-scripts/ifcfg-ens33 echo "DNS1=219.141.140.10" >>/etc/sysconfig/network-scripts/ifcfg-ens33

sed -i 's/BOOTPROTO=dhcp/BOOTPROTO=static/' /etc/sysconfig/network-scripts/ifcfg-ens33

echo "192.168.17.170 linuxsinglemysql" >> /etc/hosts echo "$2 $1" >> /etc/hosts systemctl restart network mkdir -p /soft

yum install lrzsz -y 1>/dev/null 2>/dev/null 3、三台装mysql服务器mgr01,mgr02,mgr03 都与中控机配置互信 4、在中控机/shell/installmanyhost放如下脚本和配置文件 hostnameip.txt installmanymysql.sh my.cnf mysqld mysqlinstall.sh mysql.local 5、执行sh installmanymysql.sh

6、到mysql01 执行 sh mgrmastercreate.sh

7、在mysql02，mysql03执行sh mgrslavecreate.sh

8、msyql.local

自此，1主2从mgr搭建完毕

二、配置文件及脚本 1、hostnameip.txt 192.168.17.175 mysql01 192.168.17.176 mysql02 192.168.17.177 mysql03 2、mysql.local /usr/local/mysql/bin/mysql -uroot -pXbl@mysql817 #/bin/bash #sh hostinit.sh mysql01 192.168.17.175 hostnamectl set-hostname $1 echo "IPADDR=$2" >>/etc/sysconfig/network-scripts/ifcfg-ens33 echo "NETMASK=255.255.255.0" >>/etc/sysconfig/network-scripts/ifcfg-ens33 echo "GATEWAY=192.168.17.2" >>/etc/sysconfig/network-scripts/ifcfg-ens33 echo "DNS1=219.141.140.10" >>/etc/sysconfig/network-scripts/ifcfg-ens33

sed -i 's/BOOTPROTO=dhcp/BOOTPROTO=static/' /etc/sysconfig/network-scripts/ifcfg-ens33

echo "192.168.17.170 linuxsinglemysql" >> /etc/hosts echo "$2 $1" >> /etc/hosts systemctl restart network mkdir -p /soft

yum install lrzsz -y 1>/dev/null 2>/dev/null 3、installmanymysql.sh #/bin/bash echo ' ' echo ' ' echo 开始执行**

set -e cat /shell/installmanyhost/hostnameip.txt|awk 'NR>0 {print $2}'|while read hostname

do echo $hostname** yes|scp /soft/mysql-8.0.18-el7-x86_64.tar.gz $hostname:/soft 1>/dev/null 2>/dev/null

if [ $? != 0 ]; then echo $hostname拷贝mysql失败！ exit else echo $hostname拷贝mysql成功！ fi

yes|scp /shell/installmanyhost/my.cnf $hostname:/soft 1>/dev/null 2>/dev/null

if [ $? != 0 ]; then echo $hostname拷贝my.cnf失败！ exit else echo $hostname拷贝my.cnf成功！ fi

yes|scp /shell/installmanyhost/mysqld $hostname:/soft 1>/dev/null 2>/dev/null

if [ $? != 0 ]; then echo $hostname拷贝mysqld失败！ exit else echo $hostname拷贝mysqld成功！ fi

yes|scp /shell/installmanyhost/mysql.local $hostname:/usr/bin 1>/dev/null 2>/dev/null

if [ $? != 0 ]; then echo $hostname拷贝mysql.local失败！ exit else echo $hostname拷贝mysql.local成功！ fi

ssh -tt $hostname < /shell/installmanyhost/mysqlinstall.sh 1>/dev/null 2>/dev/null if [ $? != 0 ]; then echo $hostname安装mysql失败！ exit else echo $hostname安装mysql成功！ fi

done

echo 执行结束***

echo "bye" echo ' ' echo ' ' 4、mysqlinstall.sh #/bin/bash cd /soft tar -zxvf mysql-8.0.18-el7-x86_64.tar.gz rpm -e --nodeps mariadb-libs-5.5.65-1.el7.x86_64 service mysqld stop sleep 10

rm -rf /usr/local/mysql 1>/dev/null 2>/dev/null rm -rf /data 1>/dev/null 2>/dev/null

mkdir /usr/local/mysql -p mv mysql-8.0.18-el7-x86_64/* /usr/local/mysql mkdir -p /data/mysql/mysql mkdir -p /data/mysql/logs

mysql_user=cat /etc/passwd |grep mysql|wc -l if [ 1 -eq $mysql_user ];then echo "Mysql用户存在" else echo -e "Mysql用户不存在，开始添加mysql用户" useradd mysql echo -e "添加mysql用户成功！！！" fi

chown -R mysql:mysql /data/mysql chown -R mysql:mysql /usr/local/mysql/

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql/ yes|cp /soft/mysqld /etc/init.d/mysqld yes|cp /soft/my.cnf /etc/my.cnf

addrip=$(ip addr | awk '/^[0-9]+: / {}; /inet.global/ {print gensub(/(.)/(.*)/, "\1", "g", $2)}' ) echo $addrip localaddress='"'$addrip':24900"' echo $localaddress sed -i 's/"192.168.17.175:24900"/'"$localaddress"'/' /etc/my.cnf sed -i 's/whitelist="192.168.17.175/whitelist="'"$addrip"'/' /ect/my.cnf

chmod 775 /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on

echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile source /etc/profile touch /data/mysql/logs/mysql-error.log chown mysql:mysql /data/mysql/logs/mysql-error.log

#newserverid=ip a | grep ens33| awk '/inet/{ print $2}'|awk -F '/' '{print $1}'|awk -F '.' '{print $NF}' newserverid=$(ip addr | awk '/^[0-9]+: / {}; /inet.global/ {print gensub(/(.)/(.*)/, "\1", "g", $2)}' |awk -F '.' '{print $4}') sed -i 's/server_id=313306/server_id='"$newserverid"'3306/' /etc/my.cnf

service mysqld start

mysql_pass=cat /data/mysql/logs/mysql-error.log|grep password | awk '/root@localhost:/{print $13}'

#####修改mysql密码以及建立replication通道########

mysql -uroot -p$mysql_pass --connect-expired-password <<EOF ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xbl@mysql817' PASSWORD EXPIRE NEVER; flush privileges; use mysql; update user set host='%' where user='root'; flush privileges; set sql_log_bin=0; create user repl@'192.168.17.%' identified by 'Xbl@mysql!@#1'; grant replication slave on . to repl@'192.168.17.%'; flush privileges; set sql_log_bin=1; change master to master_user='repl', master_password=' Xbl@mysql!@#1' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; reset master;

EOF

chmod 755 /usr/bin/mysql.local

exit 5、mysqld 没啥好说的，修改 basedir=/usr/local/mysql datadir=/data/mysql/mysql/ 6、主节点mgrmastercreate.sh #!/bin/bash set -e ip=$(ip addr | awk '/^[0-9]+: / {}; /inet.global/ {print gensub(/(.)/(.*)/, "\1", "g", $2)}' ) /usr/local/mysql/bin/mysql -uroot -pXbl@mysql817 --connect-expired-password <<EOF SET GLOBAL group_replication_ip_whitelist=''"$ip"'/24,127.0.0.1/8'; set @@global.group_replication_bootstrap_group=on; start group_replication; set @@global.group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members EOF

echo -e "master启动成功！！！" 7、两个从节点 mgrslavecreate.sh #!/bin/bash set -e

ip=$(ip addr | awk '/^[0-9]+: / {}; /inet.global/ {print gensub(/(.)/(.*)/, "\1", "g", $2)}' ) /usr/local/mysql/bin/mysql -uroot -pXbl@mysql817 --connect-expired-password <<EOF SET GLOBAL group_replication_ip_whitelist='$ip/24,127.0.0.1/8'; set GLOBAL group_replication_recovery_get_public_key=ON; start group_replication; select * from performance_schema.replication_group_members;

EOF

echo -e "slave启动成功！！！"