MySQL安装搭建脚本

#!/bin/bash

##########################################################################################################################

# add by yangmm 20220830 增加内存动态获取，原来内存参数指定innodb_buffer_pool_size=4G，修改为内存占用系统总内存40% #

# #

##########################################################################################################################

## 获取安装配置

echo "获取安装配置"

PORT=63306

ROOTPWD="PrdRootpwd&123"

## 运维账号，备份，日志分割

BKPUSER=bkpuser

BKPPWD=ifs,#RFV2

## 安装文件

INSTALLFILE=/data/software/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

## 安装目录

HOMEPATH=/data/mysqldb

## 20220830 系统总内存

TOTALMEM=$(free -m | sed -n '2p' | awk '{print $2}')

MYSQLMEM=`awk -v x=${TOTALMEM} -v y=0.4 'BEGIN{printf "%.0f\n",x*y}'`M

## 安装前校验是否存在 mariadb，如果存在需要卸载

deinstallmariadb=`rpm -qa| grep mariadb-`

if [ "$deinstallmariadb" != "" ]; then

rpm -e --nodeps $deinstallmariadb

fi

echo "yum -y install libaio-devel libaio numactl"

yum -y install libaio-devel libaio numactl

## 校验安装目录

echo "校验安装目录${HOMEPATH}是否存在"

if [ -d ${HOMEPATH} ];then

echo dir ${HOMEPATH} exist!

else

echo dir ${HOMEPATH} not exist!

exit 1

fi

echo "校验安装文件${INSTALLFILE}是否存在"

## 校验安装文件

if [ -f ${INSTALLFILE} ];then

echo file ${INSTALLFILE} exist!

else

echo file ${INSTALLFILE} not exist!

exit 1

fi

## 创建目录

echo "创建安装，数据，日志，配置等目录"

# 软件目录

BASEDIR=$(echo ${HOMEPATH})/$(echo $(echo $(basename ${INSTALLFILE})|cut -b 1-12))

# 数据目录

DATADIR=${HOMEPATH}/mysqldata

# 日志目录

LOGDIR=${HOMEPATH}/log

# binlog目录

BINDIR=${HOMEPATH}/binlog

# undolog目录

UNDODIR=${HOMEPATH}/undolog

# relaylog目录

RELAYDIR=${HOMEPATH}/relaylog

# 临时表空间

TMPDIR=${HOMEPATH}/tmpdir

# dump备份目录

DUMPBAKDIR=${HOMEPATH}/backups/dumpbak

# xtrbackup 目录

XTRBAKDIR=${HOMEPATH}/backups/xtrbackup

# 运维脚本目录

SCRIPTDIR=${HOMEPATH}/script

## 创建目录

mkdir -p ${DATADIR} ${BASEDIR} ${LOGDIR} ${BINDIR} ${UNDODIR} ${RELAYDIR} ${TMPDIR} ${DUMPBAKDIR} ${XTRBAKDIR} ${SCRIPTDIR}

## 添加用户

echo "创建mysql用户"

USERNAME=mysql

if id -u ${USERNAME} >/dev/null 2>&1 ; then

echo "User ${USERNAME} exists."

else

groupadd mysql

useradd -r -g mysql mysql

fi

## 设置MySQL配置文件

echo "生成 my.cnf 配置文件"

if [ -f /etc/my.cnf ]; then

mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%M%S`.bak

fi

grep ^[^#] >/etc/my.cnf<<EOF

[client]

port=${PORT}

socket=${BASEDIR}/mysql${PORT}.sock

default-character-set=utf8

[mysqld]

server-id=101

user=mysql

# 端口

port=${PORT}

# 临时文路径修改

tmpdir=${TMPDIR}

# 软件目录

basedir=${BASEDIR}

# 数据目录

datadir=${DATADIR}

# socket目录

socket=${BASEDIR}/mysql${PORT}.sock

# 开启bin日志

log-bin=${BINDIR}/mysql-bin

binlog_format=row

max_binlog_size=1G

# binlog_cache_size=4M

# max_binlog_cache_size=2G

innodb_max_undo_log_size=2G

innodb_undo_directory=${UNDODIR}

# 8.0 bin日志保留周期(秒)

binlog_expire_logs_seconds=604800

# expire_logs_days=30 # 5.7 bin日志保留周期(天)

#是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数(默认是0)

log_bin_trust_function_creators=1

# 默认引擎 innodb

default-storage-engine=innodb

# 默认事务隔离级别 READ-COMMITTED

transaction_isolation=READ-COMMITTED

# 服务器字符集和排序规则

character_set_server=utf8

collation-server=utf8_bin