MySQL8.0的自动部署脚本

主要是MySQL8.0的自动部署脚本，参数变化很大，不兼容5.7，仅测试了Centos 7+mysql8.0.28版本。接收2个参数: 参数1是端口号，默认3306。参数2指定mysql版本。默认启动root无密码，自行设置。
环境变量/etc/profile配置，更改进行简单的判断。修改了一些代码在一些环境下的bug， 下载的安装文件在/tmp/soft/目录下，删除的数据库目录在/tmp/data下。默认安装路径/data/下MySQL+端口号的base目录。
 
mysql-shell未验证，需要自行测试验证。 mysqlx是mysql的 X 插件，是用于类似mongo的JSON文档处理，是独立的协议和操作方法。mysql客户端登陆可查看数据。
 

编译完成，配置systemd 管理服务。方便管理。使用systemctl daemon-reload 加载配置。
  启动：systemctl start mysqld$PORT

  停止：systemctl stop mysqld$PORT

  查看状态，检查错误信息：systemctl status mysqld$PORT      mysql自己的错误日志位于/data/mysql$PORT目录下/error.log

 
#!bin/sh

#Author:lzj
#Date:2024-07-01
#Description:MySQL8.0 自动化部署脚本，注意linux命令可能需要完整路径。

#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

. /etc/profile


PORT=$1
VERSION=$2
PORT=${PORT:=3306}
VERSION=${VERSION:='8.0.28'}
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/mysql_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=`/usr/bin/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=`/usr/bin/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" ] && /usr/bin/mkdir -p $TMP

	#检查系统
	/usr/bin/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实例
    env_mysql_status=`/usr/bin/netstat -nltp | /usr/bin/grep 'mysqld' | /usr/bin/grep -w $PORT | /usr/bin/wc -l`
    [ "$env_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]) 
                /usr/bin/mkdir -p /tmp/data &> /dev/null 
				/usr/bin/mv ${MYSQL_BASE} /tmp${MYSQL_BASE}-`/usr/bin/date '+%Y%m%d%H%M%S'` && log_info "delete ${MYSQL_BASE} succeed." 
                /usr/bin/mkdir -p ${MYSQL_BASE} && /usr/bin/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
    	/usr/bin/mkdir -p ${MYSQL_BASE} && /usr/bin/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文件
    /usr/bin/rpm -qa | /usr/bin/grep mariadb | xargs yum remove -y &> /dev/null
    /usr/bin/mv /etc/my.cnf /tmp/my.cnf`/usr/bin/date '+%Y%m%d%H%M%S'` &> /dev/null

    #selinux 和 防火墙
    /usr/bin/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

}

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

	/usr/bin/cat <<EOF > ${MYSQL_BASE}/my.cnf
[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."
        /usr/bin/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

    #环境变量,先过滤掉空行和注释行，再判断
    is_env=`/usr/bin/grep -vE '^#|^$' /etc/profile | /usr/bin/grep "${MYSQL_BASE}/bin:" | /usr/bin/wc -l` &> /dev/null
    if [ $is_env -gt 0 ];then
        log_info "env is exists."
    else
        echo "export PATH=$MYSQL_BASE/bin:\$PATH" >> /etc/profile

        source /etc/profile
        export "PATH=$MYSQL_BASE/bin:\$PATH"
        log_info "env add succeed."
    fi

    #--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服务
    /usr/bin/cat <<EOF > /usr/lib/systemd/system/mysqld$PORT.service 
[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=simple
TimeoutSec=0

ExecStart=$MYSQL_BASE/bin/mysqld_safe --defaults-file=${MYSQL_BASE}/my.cnf --basedir=$MYSQL_BASE --datadir=$MYSQL_DATA '\$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
    /usr/bin/systemctl daemon-reload
    /usr/bin/systemctl start mysqld$PORT

    #校验MySQL实例是否启动成功,启动可能比较慢，可增加延迟校验时间。
    /usr/bin/sleep 10s
    mysql_status=`/usr/bin/netstat -nltp | /usr/bin/grep 'mysqld' | /usr/bin/grep -w $PORT | /usr/bin/wc -l`
    if [ "$mysql_status" -eq 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
    	/usr/bin/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

 

posted @   cdrcsy  阅读(137)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL空间优化（空间清理）
· MySQL sys性能监控
· mysql 8.0多实例批量部署script
· 在centos7系统上用脚本实现自动搭建mysql8——教程及搭建演示
· mysql 8 二进制版本安装
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！
点击右上角即可分享
微信分享提示