mysql8主备安装脚本

## mysql安装包
https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.21-1.el7.x86_64.rpm-bundle.tar

## 使用方法
1. 将init_mysql.sh上传至主、从节点：/app/mysql/，然后执行：
```shell
chmod + x init_mysql.sh
./init_mysql.sh  1  ${slave_ip}
```

2. 在从节点上执行倒数第二行提示的命令
```shell
cd /app/mysql
./init_mysql.sh  ${server_id}  ${master_logfile}  ${master_log_pos}  ${master_ip}
```

3. 按照提示将主节点的table解锁

## 脚本
```shell
#!/bin/env bash
set -e
MEMORY=`cat /proc/meminfo  |grep -i memtotal |awk '{print $2}'`
INNODB_BUFFER_POOL_SIZE=`expr $MEMORY / 5 \* 4 / 1024 / 1024`
SERVER_ID=${1:-1}
MYROOT_PASSWD=${MYROOT_PASSWD:-app@123}
MYTRANS_PASSWD=${MYSLAVE_PASSWD:-app@123}

if [ ! -d /app/mysql/logs ] ; then
  mkdir -p /app/mysql/{logs,data}
fi

if [ ! -f /app/mysql/init_mysql.sh ] ; then
  copy $0 /app/mysql/init_mysql.sh
fi

if [ `find /app/mysql/data/ -type f | wc -l` -gt 1 ] ; then
  echo "Already installed? exit."
  exit 1
fi

if [ $SERVER_ID -gt 1 ] ; then
  echo "SERVER_ID is greater than 1, set slave mode"
  if [ -z $4 ] ; then
    read -p "set master ip> " MASTER_IP
  else
    MASTER_IP=$4
  fi
  READ_ONLY=read_only
  MASTER_LOG_FILE=$2
  MASTER_LOG_POS=$3
else
  if [ -z $2 ] ; then
    read -p "set slave ip or pass> " SLAVE_IP
  else
    echo "SLAVE_IP = $2"
    SLAVE_IP=$2
  fi
  unset READ_ONLY
fi

setfacl -Rd -m u:mysql:rwx /app/mysql

cat > /etc/security/limits.d/mysql <<EOF
*               -    nofile          65535
*               -    nproc           unlimited
*               -    memlock         unlimited
EOF

ulimit -n 65535

cat > /etc/my.cnf <<EOF
[client]
port = 3306
socket = /var/run/mysqld/mysqld_3306.sock

[mysqld]
# generic configuration options
port = 3306
socket = /var/run/mysqld/mysqld_3306.sock
basedir = /usr/
datadir = /app/mysql/data
tmpdir = /tmp
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
open_files_limit = 65535
default_authentication_plugin = mysql_native_password
sql_mode = 'NO_ENGINE_SUBSTITUTION'
interactive_timeout = 36000
wait_timeout = 36000
# raise number on slave server
server-id = ${SERVER_ID}
$READ_ONLY
skip-name-resolve
skip_external_locking

max_connections = 3000
back_log = 1024
max_connect_errors = 100
lower_case_table_names = 1
ft_min_word_len = 4
transaction_isolation = READ-COMMITTED #REPEATABLE-READ

#MASTER/SLAVE OPTION
gtid_mode = ON
enforce-gtid-consistency
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = 1
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking = WRITESET_SESSION
binlog_transaction_dependency_history_size = 5000
master_info_repository = table
relay_log_info_repository = table
skip-slave-start
#super_read_only = 1

#LOGS
log_timestamps = SYSTEM
log-error = /app/mysql/logs/error.log
general_log_file = /app/mysql/logs/mysql.log
general_log = 0
log-bin = /app/mysql/logs/mysql-bin.log
binlog_format = row
max_binlog_size = 524288000
binlog_cache_size = 32M
binlog_expire_logs_seconds = 432000
log_slave_updates
#binlog_rows_query_log_events = 1
relay-log-index = /app/mysql/data/mysqld-relay-bin.index
relay-log = /app/mysql/data/mysqld-relay-bin
relay-log-recovery
slow_query_log
slow_query_log_file = /app/mysql/logs/slow.log
long_query_time = 2

#BUFFER\CACHE\THREADS
key_buffer_size = 16M
table_open_cache = 2000
max_allowed_packet = 1024M
max_heap_table_size = 256M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 64
thread_stack = 512K
tmp_table_size = 256M
#query_cache_limit = 64M
#query_cache_size = 64M

# *** INNODB Specific options ***
default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size = ${INNODB_BUFFER_POOL_SIZE}G
innodb_data_file_path = ibdata1:2048M;ibdata2:2048M:autoextend
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2 #1
sync_binlog = 500
innodb_log_buffer_size = 32M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
#innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[mysqlhotcopy]
#interactive-timeout

[mysqld_safe]
open-files-limit = 65535
log-error = /app/mysql/logs/error.log
pid-file = /var/run/mysqld/mysqld_3306.pid

EOF

sed -i 's/LimitNOFILE = 10000/LimitNOFILE = 65535/' /usr/lib/systemd/system/mysqld.service

systemctl daemon-reload

systemctl start mysqld

if [ $? -eq 0 ] ; then
  MYSQL_TEMP_PASSWD=`cat /app/mysql/logs/error.log |grep 'temporary password' |awk '{print $(NF)}'`
  mysql --connect-expired-password -uroot -p$MYSQL_TEMP_PASSWD -e "set global validate_password.policy=0;alter user root@localhost identified by '$MYROOT_PASSWD';" 2> /dev/null
  if [ $? -eq 0 ] ; then
    echo "root password change to $MYROOT_PASSWD"
  else
    echo "Fail to change root password! Abort"
    exit 1
  fi

if [ ! -z $SLAVE_IP ] ; then
    mysql -uroot -p$MYROOT_PASSWD -e "create user datatrans@$SLAVE_IP identified by '$MYSLAVE_PASSWD'; grant replication slave on *.* to datatrans@$SLAVE_IP; flush privileges; flush tables with read lock;" 2> /dev/null

if [ $? -eq 0 ] ; then
      echo "Success to create slave user: datatrans/$MYSLAVE_PASSWD"
    else
      echo "Fail to create slave user! Abort"
      exit 1
    fi

if [ -f /app/mysql/master-data.sql ] ; then
      mv /app/mysql/master-data.sql /app/mysql/master-data.sql.`date +%Y%m%d%H%M%S`
    fi

mysqldump  -uroot -p$MYROOT_PASSWD --all-databases > /app/mysql/master-data.sql 2> /dev/null

if [ $? -eq 0 ] ; then
      echo -ne "Master data dump to /app/mysql/master-data.sql\n\n\n"
    else
      echo "Fail to dump Master data! Please dump manually~"
    fi

MASTER_LOG_FILE=`mysql -uroot -p$MYROOT_PASSWD -e 'show master status ' 2>/dev/null |grep bin| awk '{print $1}'`
    MASTER_LOG_POS=`mysql -uroot -p$MYROOT_PASSWD -e 'show master status ' 2>/dev/null |grep bin| awk '{print $2}'`
    echo -ne "Please copy /app/mysql/master-data.sql to slave server: /app/mysql/master-data.sql\n\n"
    echo -ne "Then login slave node run: $0 2 $MASTER_LOG_FILE $MASTER_LOG_POS \$MASTER_IP\n\n"
    echo -ne "After slave init success, run 'mysql -uroot -p$MYROOT_PASSWD -e \"unlock tables;\"'\n\n"
  fi

if [ $SERVER_ID -gt 1 ] ; then
    if [ ! -f /app/mysql/master-data.sql ] ; then
      echo "Missing file: /app/mysql/master-data.sql"
      echo "Copy from master, then run:"
      cat <<EOF
mysql -uroot -p$MYROOT_PASSWD -e "unlock tables;"

mysql -uroot -p$MYROOT_PASSWD < /app/mysql/master-data.sql

mysql -uroot -p$MYROOT_PASSWD -e "change master to master_host='$MASTER_IP', master_user='datatrans', master_password='$MYSLAVE_PASSWD', master_log_file='$MASTER_LOG_FILE', master_log_pos=$MASTER_LOG_POS;"

EOF
      exit 1
    else
      mysql -uroot -p$MYROOT_PASSWD -e "unlock tables;" 2> /dev/null
      if [ $? -eq 0 ] ; then
        echo "Success to unlock table".
      else
        echo "Fail to unlock table!"
      fi

mysql -uroot -p$MYROOT_PASSWD < /app/mysql/master-data.sql 2> /dev/null
      if [ $? -eq 0 ] ; then
        echo "Success to import master data".
      else
        echo "Fail to import master data! Abort"
        exit 1
      fi

mysql -uroot -p$MYROOT_PASSWD -e "change master to master_host='$MASTER_IP', master_user='datatrans', master_password='$MYSLAVE_PASSWD', master_log_file='$MASTER_LOG_FILE', master_log_pos=$MASTER_LOG_POS; start slave;" 2> /dev/null
      if [ $? -eq 0 ] ; then
        echo "Success to connect to master".
      else
        echo "Fail to connect to master! Abort"
        exit 1
      fi

mysql -uroot -p$MYROOT_PASSWD -e "flush tables with read lock " 2> /dev/null
      echo -ne "Init slave success! login master node then run: 'mysql -uroot -p$MYROOT_PASSWD -e \"unlock tables;\"'\n\n"
    fi
  fi

else
  echo "Install Failed. "
fi

```