Mysql自动安装优化脚本

基础材料：

CentOS7.6  minimal 关闭selinux  mysql 5.7.24

建议先按照https://blog.youkuaiyun.com/finalkof1983/article/details/80006606执行系统初始化配置脚本

---

#!/bin/bash

#collect information for install
echo -n "Input absolute path where mysql install package was put:"
read PackagePath
echo -n "Input absolute path where mysql be installed:"
read InstallPath
echo -n "Input absolute path where mysql binlog be storaged:"
read BinlogPath
TotalMem=`cat /proc/meminfo|grep MemTotal|awk -F ' ' '{print $2}'`
let "MaxBufferpoolMem=$TotalMem/1024/100*80"
let "ShmMem=$TotalMem/1024/2"
CpuNum=`cat /proc/cpuinfo|grep processor|wc -l`
let "MaxConcurrenceThread=$CpuNum*2"


#install RPM package
if [ $ShmMem -gt 600 ]
then
package=`find $PackagePath -name mysql*x86_64.rpm-bundle.tar`
tar -xf $package -C /dev/shm
rm -f /dev/shm/mysql-community-server-minimal*.rpm
rm -f $PackagePath/mysql*x86_64.rpm-bundle.tar
else
echo "the /dev/shm memory space is not enough"
exit
fi
rm -f /dev/shm/mysql-community-server-minimal*.rpm
if [ `rpm -qa|grep mariadb-libs|wc -l` -eq 1 ]
then
rpm -e mariadb-libs-* --nodeps  
fi
rpm -ivh /dev/shm/mysql-community-common-*.rpm
rpm -ivh /dev/shm/mysql-community-libs-*.rpm
rpm -ivh /dev/shm/mysql-community-client-*.rpm
rpm -ivh /dev/shm/mysql-community-server-*.rpm         
rm -f /dev/shm/mysql-community-*.rpm


#mysql init
if [ ! -d $InstallPath ]
then
mkdir -p $InstallPath
fi
chown mysql. $InstallPath
mysqld --initialize --user=mysql  --datadir=$InstallPath


#my.cnf config
sed -i 's/datadir\=\/var\/lib\/mysql/# datadir\=\/var\/lib\/mysql/g' /etc/my.cnf
sed -i 's/socket\=\/var\/lib\/mysql\/mysql.sock/# socket\=\/var\/lib\/mysql\/mysql.sock/g' /etc/my.cnf
sed -i '/^symbolic/,$d' /etc/my.cnf
echo "">>/etc/my.cnf
echo "#ErrorLog">>/etc/my.cnf
echo "log-error = /var/log/mysqld.log">>/etc/my.cnf
echo "log_timestamps = SYSTEM">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#per_thread_query_buffer">>/etc/my.cnf
echo "binlog_cache_size = 2MB">>/etc/my.cnf
echo "join_buffer_size = 2MB">>/etc/my.cnf
echo "read_rnd_buffer_size = 16MB">>/etc/my.cnf
echo "sort_buffer_size = 8MB">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#Slow_query_log">>/etc/my.cnf
echo "slow_query_log = ON">>/etc/my.cnf
echo "long_query_time = 5">>/etc/my.cnf
echo "log_output = TABLE">>/etc/my.cnf
echo "log_queries_not_using_indexes = ON">>/etc/my.cnf
echo "log_throttle_queries_not_using_indexes = 100">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#redolog">>/etc/my.cnf
echo "innodb_log_file_size = 1G">>/etc/my.cnf
echo "innodb_log_files_in_group = 4">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#NormalInfo">>/etc/my.cnf
echo "datadir = $InstallPath">>/etc/my.cnf
echo "socket = $InstallPath/mysql.sock">>/etc/my.cnf
echo "character-set-server = utf8mb4">>/etc/my.cnf
echo "pid-file = /var/run/mysqld/mysqld.pid">>/etc/my.cnf
echo "symbolic-links = 0">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#per_thread_query_buffer">>/etc/my.cnf
echo "binlog_cache_size = 2MB">>/etc/my.cnf
echo "join_buffer_size = 2MB">>/etc/my.cnf
echo "read_rnd_buffer_size = 16MB">>/etc/my.cnf
echo "sort_buffer_size = 8MB">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#BufferPool">>/etc/my.cnf
echo "innodb_numa_interleave = ON">>/etc/my.cnf
if [ $MaxBufferpoolMem -gt 4096 ]
then
echo "innodb_buffer_pool_size = $MaxBufferpoolMem""M">>/etc/my.cnf
echo "innodb_buffer_pool_instances = 4">>/etc/my.cnf
let "innodb_buffer_pool_chunk_size = $MaxBufferpoolMem/4"
echo "innodb_buffer_pool_chunk_size = $innodb_buffer_pool_chunk_size""M">>/etc/my.cnf
else
echo "innodb_buffer_pool_size = $MaxBufferpoolMem""M">>/etc/my.cnf
fi
echo "">>/etc/my.cnf
echo "#thread">>/etc/my.cnf
echo "innodb_thread_concurrency = $MaxConcurrenceThread">>/etc/my.cnf
echo "max_connections = 1000">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#binlog">>/etc/my.cnf
if [ ! -d "$BinlogPath" ]
then
mkdir -p $BinlogPath
fi
chown  mysql. $BinlogPath
echo "server-id = 99">>/etc/my.cnf
echo "log-bin = $BinlogPath/mysql-binlog">>/etc/my.cnf
echo "binlog_format = row">>/etc/my.cnf
echo "expire_logs_days = 7">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#GTID">>/etc/my.cnf
echo "gtid_mode = on">>/etc/my.cnf
echo "enforce_gtid_consistency = 1">>/etc/my.cnf
echo "">>/etc/my.cnf
echo "#others">>/etc/my.cnf
echo "performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'">>/etc/my.cnf
echo "performance_schema_events_statements_history_size = 50">>/etc/my.cnf
echo "">>/etc/my.cnf
echo  "[client]">>/etc/my.cnf
echo  "default-character-set = utf8mb4">>/etc/my.cnf
echo  "socket = $InstallPath/mysql.sock">>/etc/my.cnf


#startup & change init password
tmp_passwd=`cat /var/log/mysqld.log |grep "A temporary password is generated for root"|awk -F ' ' '{print $NF}'|sed 's/[[:space:]]//g'`
systemctl start mysqld
if [ `systemctl status mysqld|grep running|wc -l` -eq 1 ]
then
mysql -u root -p"$tmp_passwd" --connect-expired-password -e "SET PASSWORD = PASSWORD('root');"
echo "####################################################################"
echo "mysql install finished & service already startup & user root password is 'root',you need to change it as soon as possible"
else
echo "####################################################################"
echo "mysql install finished & but service startup failed & checkup /var/log/mysqld.log"
fi

 

---

使用说明：

1、将下载好的mysql*x86_64.rpm-bundle.tar安装包，上传到服务器

2、root用户执行脚本，保证内存至少1.5G用于解压安装包，由于要对redolog进行优化，需要保证mysql安装绝对路径硬盘空间至少5G。

3、分别输入mysql安装包存放的绝对路径、mysql安装绝对路径、binlog存放的绝对路径，后两者不需要提前创建

4、脚本完成后mysql的root用户密码被重置为root，需要及时修改