mgr脚本搭建

一、搭建过程
1、服务器规划
192.168.17.170 中控机
192.168.17.175 mysql01
192.168.17.176 mysql02
192.168.17.177 mysql03
2、服务器初始化脚本hostinit.sh
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
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 '/^[1]+: / {}; /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 '/^[2]+: / {}; /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 '/^[3]+: / {}; /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 '/^[4]+: / {}; /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启动成功！！！"

---

1. 0-9 ↩︎

2. 0-9 ↩︎

3. 0-9 ↩︎

4. 0-9 ↩︎