主要是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