mysql group replication 一鍵安裝部署

#!/bin/bash  

#set mysql root password  

echo "============start==============="  

mysqlrootpwd="root"  

echo -e "Please input the root password of mysql:"  

read -p "input password of root:" mysqlrootpwd 

while [ -z $mysqlrootpwd ]    #  判斷空值

do

     read -p "input password of root:" mysqlrootpwd  

done  

echo "MySQL root's password is $mysqlrootpwd"  

read -p "need mysql group replication (y/n)?:" needmysqlga 

while [ $needmysqlga != "y" -a $needmysqlga != "n" ]    #  判斷y/n

do

     read -p "need mysql group replication (y/n)?:" needmysqlga  

done  

if [ $needmysqlga = "y" ]

then

echo "input the ip of the groups example:192.168.1.1:33061,192.168.1.2:33061,..."

    read -p "input the ip:port of the groups:" mysqlgr

while [ -z $mysqlgr  ]    #  判斷kong

do

   read -p "input the ip:port of the groups:" mysqlgr

done 

read -p "the day of the binlog will not delete(defalt if 7):" mysqlgrday

if [ -z $mysqlgrday  ]    #  判斷kong

then

  mysqlgrday=7

fi

#加入gr的使用者

echo "you should create the same user for mysqlGR defalt is (rpl_user,rpl_pass)"

    read -p "input the username of the groups :" grusername

read -p "input the password of the groups :" grpwd

if [ -z $grusername  ]    #  判斷kong

then

  grusername="rpl_user"

fi

if [ -z $grpwd  ]    #  判斷kong

then

  grpwd="rpl_pass"

fi

#判斷主cong

read -p "this mysqlGR server is the master (y/n)?:" first 

while [ $first != "y" -a $first != "n" ]    #  判斷y/n

do

    read -p "this mysqlGR server is the master (y/n)?:" first  

done 

fi

read -p "install mysql from local yum repo (y/n)?:" needinternet

while [ $needinternet !=  "y" -a $needinternet !=  "n" ]    #  判斷y/n

do

     read -p "need mysql group replication (y/n)?:" needinternet  

done  

#先安裝後修改配置

if [ $needinternet = "y" ]

then

sudo yum install -y mysql-community-server

else

sudo rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

yum update

sudo yum install -y mysql-community-server

fi

#設定密碼

echo "

skip-grant-tables

" >>/etc/my.cnf

sudo service mysqld start

mysql  -uroot -e"update mysql.user set authentication_string=password('$mysqlrootpwd') where user='root';"

mysql  -uroot -e"flush privileges;"

ip=`ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'`

id=`echo $ip|awk -F '.' '{print $4}'`

#寫配置

if [ $needmysqlga = "y" ]

then

echo "

[mysqld]

datadir=/var/lib/mysql

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

symbolic-links=0

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

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

validate-password=OFF

server_id=$id

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository    = table #複制中繼資料存入系統表

relay_log_info_repository = table #複制中繼資料存入系統表

binlog_checksum = none #禁用二進制日志事件校驗和

log_slave_updates=ON

log_bin=binlog

binlog_format=ROW

expire_logs_days = $mysqlgrday # 隻保留 7 天的日志檔案

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name=\"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa\"

loose-group_replication_start_on_boot=on        #插件在server啟動時自動啟動組複制

loose-group_replication_local_address= \"$ip:33061\"

loose-group_replication_group_seeds= \"$mysqlgr\"

loose-group_replication_bootstrap_group= off   #插件不自動引導組

loose-group_replication_single_primary_mode=off   ###本次搭建的是mutil_mode多主模式

sql_mode = ''

lower_case_table_names=0

innodb_force_recovery=0

" >/etc/my.cnf

 else

echo "

[mysqld]

datadir=/var/lib/mysql

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

symbolic-links=0

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

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

sql_mode = ''

lower_case_table_names=0

validate-password=OFF

innodb_force_recovery=0" >/etc/my.cnf

fi

sudo service mysqld restart

#重置密碼

mysql -uroot -p$mysqlrootpwd -e"SET PASSWORD = PASSWORD('$mysqlrootpwd');" --connect-expired-password 2>/dev/null

#開啟遠端通路

mysql -uroot -p$mysqlrootpwd -e"GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '$mysqlrootpwd' WITH GRANT OPTION ;

" --connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e"FLUSH PRIVILEGES;" --connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e"select @@hostname" --connect-expired-password 2>/dev/null

#加入開機啟動

systemctl enable mysqld

if [ $needmysqlga = "y" ]

then

mysql -uroot -p$mysqlrootpwd -e "CREATE USER $grusername@'%' IDENTIFIED BY '$grpwd';" --connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e "GRANT REPLICATION SLAVE ON *.* TO $grusername@'%';" --connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e "FLUSH PRIVILEGES;" --connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e "CHANGE MASTER TO MASTER_USER='$grusername', MASTER_PASSWORD='$grpwd'  FOR CHANNEL 'group_replication_recovery';

"--connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"--connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e "reset master;"--connect-expired-password 2>/dev/null

if [ $first = "y" ]

then

mysql -uroot -p$mysqlrootpwd -e "SET GLOBAL group_replication_bootstrap_group=ON;" --connect-expired-password 2>/dev/null

mysql -uroot -p$mysqlrootpwd -e "START GROUP_REPLICATION;" --connect-expired-password 2>/dev/null

sleep 1

mysql -uroot -p$mysqlrootpwd -e "SET GLOBAL group_replication_bootstrap_group=OFF;" --connect-expired-password 2>/dev/null

else

mysql -uroot -p$mysqlrootpwd -e "START GROUP_REPLICATION;" --connect-expired-password 2>/dev/null

fi 

mysql -uroot -p$mysqlrootpwd -e "SELECT * FROM performance_schema.replication_group_members;" --connect-expired-password 2>/dev/null

fi