MySQL8.0的自動部署指令碼

主要是MySQL8.0的自動部署指令碼，引數變化很大，不相容5.7，僅測試了Centos 7+mysql8.0.28版本。接收2個引數: 引數1是版本指定。引數2指定埠號。預設啟動root無密碼，自行設定。

mysql-shell未驗證，需要自行測試驗證。 mysqlx是mysql的 X 外掛，是用於類似mongo的JSON文件處理，是獨立的協議和操作方法。mysql客戶端登陸可檢視資料。

環境變數/etc/profile配置，是直接追加，未判斷替換，新增。過多測試可手動清理。

#bin/sh

#Author:lzj
#Date:2024-07-01
#Description:MySQL8.0 自動化部署指令碼

#yum源
#https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
#https://repo.percona.com/yum/percona-release-latest.noarch.rpm
#https://mirrors.tuna.tsinghua.edu.cn/
#

#sed -i 's/gpgcheck = 1/gpgcheck = 0/' percona-original-release.repo
#sed -i 's/https/http/' percona-original-release.repo
#sed -i 's#https://repo.percona.com#http://mirrors.tuna.tsinghua.edu.cn#' percona-original-release.repo

#防火牆
#systemctl stop firewalld
#systemctl disable firewalld

#檔案描述符號
#cat >> /etc/security/limits.conf << EOF
#mysql    soft    nproc    16384
#mysql    hard    nproc    16384
#mysql    soft    nofile    65536
#mysql    hard    nofile    65536
#EOF

VERSION=$1
PORT=$2
VERSION=${VERSION:='8.0.28'}
PORT=${PORT:=3306}
CORE_VERSION=`echo ${VERSION:0:3}`

URL="https://mirrors.aliyun.com/mysql/MySQL-${CORE_VERSION}/"
FILE_NAME="mysql-${VERSION}-linux-glibc2.12-x86_64.tar"
TMP="/tmp/soft"
MYSQL_BASE=/data/mysql$PORT
MYSQL_DATA=$MYSQL_BASE/data
#日誌輸出,output列印輸出，log輸出日誌檔案，all兩種都輸出
LOG_TYPE=all
MESSAGE_LOG=$TMP/message.log
#mysql-shell
MYSQLSH_URL='https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.38-linux-glibc2.12-x86-64bit.tar.gz'
MYSQLSH_NAME='mysql-shell-8.0.38-linux-glibc2.12-x86-64bit.tar'

function log_info()
{
	local msg=$1
	local dt=`date '+%Y%m%d %H:%M:%S'`
	case $LOG_TYPE in 
		output)
			echo "$dt [info] $msg." ;;
		log)
			echo "$dt [info] $msg." &>> $MESSAGE_LOG ;;
		all)
			echo "$dt [info] $msg."
			echo "$dt [info] $msg." &>> $MESSAGE_LOG ;;
		*)
	esac
}

function log_error()
{
	local msg=$1
	local dt=`date '+%Y%m%d %H:%M:%S'`
	case $LOG_TYPE in 
		output)
			echo "$dt [error] $msg." ;;
		log)
			echo "$dt [error] $msg." &>> $MESSAGE_LOG ;;
		all)
			echo "$dt [error] $msg."
			echo "$dt [error] $msg." &>> $MESSAGE_LOG ;;
		*)
	esac
}


function check_env()
{
    #目錄
    [ ! -d "$TMP" ] && mkdir -p $TMP

	#檢查系統
	grep CentOS /etc/redhat-release &>/dev/null
	[ "$?" -ne 0 ] && { log_error 'Only suppert Centos.';exit 1;}

    #檢查是否是root使用者
    [ $UID -ne 0 ] && { log_error "Please switch root user and try again."; exit 10;}

	#檢查使用者
	/usr/sbin/groupadd mysql &> /dev/null
	/usr/bin/id mysql &> /dev/null
	if [ "$?" -eq 0 ];then
		/usr/sbin/usermod -s /sbin/nologin mysql &> /dev/null && log_info "MySQL user already exists."
	else
		/usr/sbin/useradd -g mysql mysql -s /sbin/nologin &> /dev/null && log_info "MySQL user created successfully."
	fi

    #校驗MySQL例項
    mysql_status=`netstat -nltp | grep 'mysqld' | grep -w $PORT | wc -l`
    [ "$mysql_status" = 1 ] && { log_error "MySQL instance exists. please check it."; exit 18;}


    #檢查資料庫目錄
    if [ -d ${MYSQL_BASE} -a "`ls -A ${MYSQL_BASE}`" != "" ];then
    	read -r -p "${MYSQL_BASE} exists,do you want to delete them? [Y/N]:" input
    	case $input in
    		[yY][eE][sS]|[yY]) 
                mkdir -p /tmp/data &> /dev/null 
				mv ${MYSQL_BASE} /tmp${MYSQL_BASE}-`date '+%Y%m%d%H%M%S'` && log_info "delete ${MYSQL_BASE} succeed." 
                mkdir -p ${MYSQL_BASE} && mkdir -p ${MYSQL_BASE}/{log,tmp,innodb_tmp} && log_info "MySQL directory is created." ;;
			[nN][oO]|[nN]) 
				log_error "Please delete ${MYSQL_BASE} manually." && exit 11 ;;
			*) 
				log_error "Input error. " && exit 12 ;;
		esac
    else
    	mkdir -p ${MYSQL_BASE} && mkdir -p ${MYSQL_BASE}/{log,tmp,innodb_tmp} && log_info "MySQL directory is created."
    fi

    #檢查安裝包、mysql-shell包
    [ -e "${TMP}/${FILE_NAME}.xz" ] && log_info "MYSQL package exists.:`ls ${TMP}/${FILE_NAME}*`" || DOWNLOAD=1
    [ -e "${TMP}/${MYSQLSH_NAME}.gz" ] && log_info "MYSQLSH package exists.:`ls ${TMP}/${MYSQLSH_NAME}*`" || MYSQLSH_DOWNLOAD=1

    #解除安裝自帶mariadb和預設/etc/my.cnf檔案
    rpm -qa | grep mariadb | xargs yum remove -y > /dev/null
    mv /etc/my.cnf /tmp/my.cnf`date '+%Y%m%d%H%M%S'` &> /dev/null

    #selinux 和 防火牆
    sed -i -e "s:SELINUX=.*:SELINUX=permissive:g" /etc/selinux/config
	setenforce 0 &> /dev/null && log_info "SELinux is disabled"
    
	#安裝依賴包
	yum install -y libaio ncurses-compat-libs &> /dev/null
	if [ "$?" -eq 0 ];then
		log_info "The MySQL dependency package is successfully installed."
	else 
		log_error "The MySQL dependency package install failed."
		read -r -p "Whether to continue? [Y/N]:" input
    	case $input in
    		[yY][eE][sS]|[yY])  
				log_info "continue check env." ;;
			[nN][oO]|[nN]) 
				log_error "check exit." && exit 16 ;;
			*) 
				log_error "Input error. " && exit 17 ;;
		esac
	fi

	#8.0的mysql shell

}

function mysql_cnf()
{
    #SERVER_ID="rm-`cat /proc/sys/kernel/random/uuid | awk -F '-' '{print $4$5}'`"
    SERVER_ID=1`date +%N`
    TOTAL_MEM=$(free -m | awk '/Mem:/ {print $2}')

	tee > ${MYSQL_BASE}/my.cnf <<EOF
[mysqld]
server_id = $SERVER_ID
user = mysql
port = $PORT
bind_address = 0.0.0.0

basedir = $MYSQL_BASE
datadir = ${MYSQL_BASE}/data
pid_file = ${MYSQL_BASE}/mysqld.pid
socket = ${MYSQL_BASE}/mysqld.sock
#影響 LOAD DATA and SELECT ... INTO OUTFILE 和函式 LOAD_FILE()。配置為空，則變數無效。如果設定為目錄的名稱，伺服器將匯入和匯出操作限制為僅適用於該目錄中的檔案。如果設定為NULL，伺服器將禁用匯入和匯出操作。 預設路徑：/var/lib/mysql-files
secure_file_priv=${MYSQL_BASE}/tmp
log_error = ${MYSQL_BASE}/error.log
#skip-grant-tables

character-set-server = utf8mb4
external-locking = FALSE
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
#8.0,NO_AUTO_CREATE_USER廢棄。
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
#TIMESTAMP如果沒有顯示宣告NOT NULL，允許NULL值
explicit_defaults_for_timestamp=1
event_scheduler = 1
log_timestamps=system
#default-time-zone = '+08:00'
#default_authentication_plugin = mysql_native_password

open_files_limit = 65535
table_open_cache = 4000

##########connection settings##########
max_allowed_packet = 16m
back_log=500
skip_name_resolve = 1
max_connections = 2000
max_user_connections = 1000
max_connect_errors = 100000
interactive_timeout = 1800
wait_timeout = 1800

###################session###########################
read_buffer_size = 8m
read_rnd_buffer_size = 8m
sort_buffer_size = 8m
join_buffer_size = 16m
bulk_insert_buffer_size=16M
thread_cache_size = 16
key_buffer_size = 16M

###################slowlog settings###################
slow_query_log = 1
#long_query_time = 3
slow_launch_time = 3
#slow是否記錄未使用索引的查詢，多少行下不記錄。
#log_queries_not_using_indexes = 1
slow_query_log_file = ${MYSQL_BASE}/log/slow.log
min_examined_row_limit = 1000

###################replication settings###################
#master_info_repository = TABLE 			#8.0預設,將廢棄
#relay_log_info_repository = TABLE 		#8.0預設,將廢棄
log_bin = ${MYSQL_BASE}/log/mysql-bin.log
sync_binlog = 1000
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery=1
#binlog_rows_query_log_events = 1
binlog_cache_size=16M
max_binlog_cache_size=200M
max_binlog_size=1G
# 8.0 使用binlog_expire_logs_seconds 代替。
#expire_logs_days = 7
binlog_expire_logs_seconds=604800

binlog_format = row
#FULL一行完整資料，minimal只有變更的欄位
binlog_row_image = FULL
relay_log = ${MYSQL_BASE}/log/mysql-relay.log
relay_log_recovery = 1
relay-log-purge = 1
#8.0 使用log_replica_updates代替
#log_slave_updates=1
log_replica_updates=1
log-bin-trust-function-creators=1
#slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' 		#8.0預設,將廢棄
#基於組複製,8.0使用replica_parallel_type，replica_parallel_workers，replica_preserve_commit_order
#slave_parallel_type=LOGICAL_CLOCK
#slave-parallel-workers=4
#slave_preserve_commit_order=on
replica_parallel_type=LOGICAL_CLOCK
replica_parallel_workers=4
replica_preserve_commit_order=on
binlog_group_commit_sync_delay=1000 			#1毫秒
binlog_group_commit_sync_no_delay_count= 50

###################semi sync replication settings###################
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 300
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_wait_point=AFTER_SYNC
#rpl_semi_sync_master_wait_for_slave_count=1

###################password plugin settings###################
#validate_password_policy=STRONG
#validate-password=FORCE_PLUS_PERMANENT

#################innodb########################
innodb_page_size = 16384
innodb_buffer_pool_size=`echo $((TOTAL_MEM*75/100))`M
innodb_buffer_pool_instances = 16
innodb_data_file_path = ibdata1:1G:autoextend
innodb_temp_data_file_path=ibtmp1:100M:autoextend:max:40960M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 60

innodb_file_per_table=1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 300   #5分鐘，行鎖，表鎖，死鎖等待超時時間。
lock_wait_timeout = 360           #6分鐘，MDL鎖等待超時時間。

innodb_log_group_home_dir = ${MYSQL_BASE}/log
innodb_log_buffer_size=32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
# 8.0 將廢棄，預設建立2個undo表空間。需要則手動建立CREATE UNDO TABLESPACE
#innodb_undo_tablespaces = 3
innodb_undo_directory=${MYSQL_BASE}/log
innodb_undo_log_truncate = 1      #MySQL5.7 ，才支援線上收縮。
innodb_max_undo_log_size=500M

innodb_flush_log_at_trx_commit = 2
innodb_open_files=2048
innodb_rollback_on_timeout = on
innodb_print_all_deadlocks = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 6000
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 6
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_max_dirty_pages_pct = 75

innodb_flush_neighbors = 1
innodb_sort_buffer_size = 16m
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 3G
innodb_stats_persistent_sample_pages = 64

#innodb_status_output = 1
#innodb_status_output_locks = 1
#innodb_stats_on_metadata = 0
innodb_use_native_aio=1
innodb_adaptive_hash_index=1

group_concat_max_len=102400
#MySQL 8.0.16,internal_tmp_disk_storage_engine 不支援，磁碟上的內部臨時表使用InnoDB表。
#MySQL8.0.16版本及以上
internal_tmp_mem_storage_engine = TempTable 	#隱式臨時表在記憶體時用的儲存引擎,Memory或者Temptable引擎(推薦)
#8.0後才有的，主要是給Temptable引擎指定記憶體大小，超過這個後，要麼就轉換成磁碟臨時表,要麼就使用自帶的overflow機制
temptable_max_ram = 1G
temptable_use_mmap = 1
#是否使用Temptable的overflow機制，temptable引擎是否磁碟資料轉換成Innodb儲存，還是記憶體對映檔案。
default_tmp_storage_engine = innodb 		#預設的顯式臨時表的引擎，即使用者透過SQL語句建立的臨時表的引擎。
innodb_tmpdir = $mysql_install_dir/innodb_tmp 	#這個引數只要是被DDL中的排序臨時檔案使用的。其佔用的空間會很大,可以動態設定，也是一個會話變數。
tmpdir = ${MYSQL_BASE}/tmp 		#臨時表/檔案預設都會放在這,這個引數可以配置多個目錄，如果不同的目錄分別指向不同的磁碟，就可以達到分流的目的.
replica_load_tmpdir = $mysql_install_dir/innodb_tmp	#這個引數主要是給BinLog複製中Load Data時，配置備庫存放臨時檔案位置時使用。因為資料庫Crash後還需要依賴Load資料的檔案.
tmp_table_size = 1G		#min(tmp_table_size,max_heap_table_size)是隱式臨時表的記憶體大小，超過這個值會轉換成磁碟臨時表。
max_heap_table_size = 1G  #使用者建立的Memory記憶體表的記憶體限制大小。
#big_tables =   我們能提前知道執行某個SQL需要用到磁碟臨時表，即記憶體肯定不夠用，可以設定這個引數，這樣最佳化器就跳過使用記憶體臨時表，直接使用磁碟臨時表，減少開銷。

#8.0 使用--skip-symbolic-links 代替，預設關閉。引數廢棄。
#symbolic-links=0
#skip-symbolic-links
innodb_numa_interleave=off

[client]
port = $PORT
socket = ${MYSQL_BASE}/mysql.sock
default-character-set = utf8mb4

[mysql]
no-auto-rehash
default-character-set = utf8mb4

EOF

}

function mysql_install()
{
    #判斷下載安裝包
    if [ $DOWNLOAD ]; then
        log_info "Download MYSQL package."
        wget -P $TMP "${URL}${FILE_NAME}.xz" && log_info "Successfully downloaded MYSQL package ." || { log_error "Cannot connect to the target address.";exit 20;}
    fi

    #判斷是否存在解壓檔案
    log_info "Extract and copy the installation files."
    SRC_DIR=`echo -e "$FILE_NAME" | sed -nr 's/(^.*)\.tar*/\1/p'`
    if [ -e ${TMP}/${SRC_DIR} ];then 
        cp -r ${TMP}/${SRC_DIR}/* $MYSQL_BASE/ &> /dev/null && log_info "Copy installation files successfully." || { log_error "Copying installation files failed."; exit 23;}
    else
        tar -xf "${TMP}/${FILE_NAME}.xz" -C $TMP/ &> /dev/null || { log_error "MySQL installation package not found.";exit 21; }
        cp -r ${TMP}/${SRC_DIR}/* $MYSQL_BASE/ &> /dev/null && log_info "Copy installation files successfully." || { log_error "Copying installation files failed."; exit 23;}
    fi

    #data目錄(8.0初始化自動建立)，許可權
    #mkdir -p $MYSQL_DATA
    chown -R mysql.mysql $MYSQL_BASE

    #環境變數
    echo "PATH=$MYSQL_BASE/bin:$PATH" >> /etc/profile
    source /etc/profile
    export "PATH=$MYSQL_BASE/bin:$PATH"
    #--datadir=$MYSQL_DATA
    cd $MYSQL_BASE && log_info "MySQL initialization start..."
    ./bin/mysqld --defaults-file=$MYSQL_BASE/my.cnf --initialize --initialize-insecure --user=mysql --basedir=$MYSQL_BASE --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
    [ "$?" -eq 0 ] && log_info "MySQL initialization succeed." || { log_error "MySQL initialization failed."; exit 24;}

    #bin/mysql_ssl_rsa_setup，SSL安裝
    #.bin/mysql_ssl_rsa_setup

    #/etc/init.d/指令碼管理
    cp -a $MYSQL_BASE/support-files/mysql.server /etc/init.d/mysqld$PORT
    #限制替換basedir=開頭，僅替換第一次匹配到的內容。
    sed -i "0,/^basedir=/{s#^basedir=#basedir=/data/mysql${PORT}#}" /etc/init.d/mysqld$PORT
    sed -i "0,/^datadir=/{s#^datadir=#datadir=/data/mysql${PORT}/data#}" /etc/init.d/mysqld$PORT
    chmod +x /etc/init.d/mysqld$PORT
    #/etc/init.d/mysqld$PORT start 

    #新增systemed服務
    mkdir -p /usr/lib/systemd/system/mysqld.service.d
    tee > /usr/lib/systemd/system/mysqld.service.d/mysqld$PORT.service << EOF
[unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking
TimeoutSec=0

ExecStart=$MYSQL_BASE/bin/mysqld_safe --daemonize --basedir=$MYSQL_BASE --datadir=$MYSQL_DATA --defaults-file=${MYSQL_BASE}/my.cnf' \$MYSQLD_OPTS'
Environment="TZ=Asia/shanghai"
LimitNOFILE=100000
PIDFile=$MYSQL_BASE/mysql.pid

Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
EOF
    systemctl daemon-reload
    systemctl start mysqld$PORT

    #校驗MySQL例項是否啟動成功
    mysql_status=`netstat -nltp | grep 'mysqld' | grep -w $PORT | wc -l`
    if [ "$mysql_status" = 1 ];then
        log_info "MySQL start succeed."
        #初始化設定密碼
        #./bin/mysql -uroot -p -S $MYSQL_BASE/mysql.sock -e "alter user root@'localhost' identified by 'mysql';flush privileges;" &> /dev/null 
    else
        log_error "MySQL start failed."
    fi

}

function mysql_shell()
{
	#判斷下載mysql-shell工具
    if [ $MYSQLSH_DOWNLOAD ]; then
    	wget -P $TMP "$MYSQLSH_NAME.gz" && log_info "Successfully downloaded MYSQLSH package ." || log_error "Cannot connect to the target address."
    fi

    #判斷MYSQLSH是否存在解壓檔案
    MYSQLSH_DIR=`echo -e "$MYSQLSH_NAME" | sed -nr 's/(^.*)\.tar*/\1/p'`
    if [ -e ${TMP}/${MYSQLSH_DIR} ];then 
        cp -r ${TMP}/${MYSQLSH_DIR} $MYSQL_BASE/ &> /dev/null
    else
        tar -xf "${TMP}/${MYSQLSH_NAME}.gz" -C $TMP/ &> /dev/null || log_error "MySQLSH installation package not found."
        cp -r ${TMP}/${MYSQLSH_DIR} $MYSQL_BASE/ &> /dev/null
    fi

    #新增環境變數
    echo "PATH=$MYSQL_BASE/${MYSQLSH_DIR}/bin:$PATH" >> /etc/profile
    source /etc/profile
    export "PATH=$MYSQL_BASE/${MYSQLSH_DIR}/bin:$PATH"
    exit 0
}

function main(){
	check_env
	mysql_cnf
	mysql_install
	#mysql_shell
}

main

#systemctl set-environment MYSQL_OPTS='--general_log=1 --'
#systemctl unset-environment MYSQL_OPTS
#systemctl start mysqld