随笔- 380 文章- 21 评论- 152 阅读- 149万

MySQL5.7安装脚本

目录结构：

install_mysql.sh：安装脚本
my.cnf: MySQL配置文件
mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz：MySQL二进制包

以下为目录中的文件：

#
## my.cnf for MySQL 5.7/8.0
## 注意：个别建议可能需要根据实际情况作调整，请自行判断或联系我，本人不对这些建议结果负相应责任
## 本配置文件主要适用于MySQL 5.7/8.0版本
#
[client]
port    = 3306
socket    = /var/run/mysql.sock

[mysql]
prompt="\u@mysqldb \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
basedir    = /usr/local/mysql
datadir    = /usr/local/mysql/data
socket    = /var/run/mysql.sock
pid-file = mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1

#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"

open_files_limit    = 65535
back_log = 1024
max_connections = 256
max_connect_errors = 1000000
table_open_cache = 60
table_definition_cache = 60
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 384
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /usr/local/mysql/data/slow.log
log-error = /usr/local/mysql/data/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /usr/local/mysql/data/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项
expire_logs_days = 7

master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 5734M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = /usr/local/mysql/data/undolog
innodb_undo_tablespaces = 95

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

#注意：MySQL 8.0.16开始删除该选项
internal_tmp_disk_storage_engine = InnoDB

# some var for MySQL 5.7
innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_size = 0
query_cache_type = 0
innodb_undo_logs = 128

innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

[mysqldump]
quick
max_allowed_packet = 32M

my.cnf

install_mysql.sh

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

#!/bin/bash
 
#Date：2019-08-27
#Description：deploy MySQL(5.7+) service
#MySQL下载地址：http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.7/
#my.cnf生成地址：http://imysql.cn/my_cnf_generator
. /etc/init.d/functions
 
function Install_require(){
echo -e "\033[44;30m开始安装依赖包 \033[0m"
yum -y install lsof libaio libaio-devel 2&>1 /dev/null
if [[ $? -ne 0 ]];then
    action  "yum依赖安装失败，请检查后重新安装！！" /bin/false
    exit 1
fi
action  "yum依赖安装完毕" /bin/true
}
 
function Check_Mysql_Service(){
#该函数功能为检查当前机器是否已经启动MySQL程序
echo -e "\033[44;30m开始检查本机是否存在MySQL程序 \033[0m"
mysql_pid=$(pidof mysqld) 
mysql_port=$(lsof -i:3306)
if ([[ ${mysql_pid} != '' ]] ||  [[ ${mysql_port} != '' ]]);then
    action "当前本机存在MySQL服务或3306端口被占用，请确认" /bin/false
    exit 1
else
    action "当前本机不存在MySQL服务，检查通过" /bin/true
fi
echo -e "\n"
} 
 
 
function Mysql_Num_Version_Check(){
#该函数功能为检查当前部署的版本是否小于 5.7 主要因为my.cnf文件5.7以下未验证
echo -e "\033[44;30m开始检查部署MySQL版本 \033[0m"
mysql_num=$(ls -l .|grep mysql-.*.tar.gz|wc -l)
if [[ ${mysql_num} != 1 ]];then 
    action  "当前目录下没有或存在多个MySQL安装文件，请确保只有一个！！" /bin/false
    exit 1
fi 
mysql_version=$(ls  .|grep mysql-.*.tar.gz|awk -F- '{print $2}')
mysql_version_num=$(ls  .|grep mysql-.*.tar.gz|awk -F- '{print $2}'|awk -F. '{print  $2}')
if [[ ${mysql_version_num} -lt 7 ]];then
   action  "MySQL部署版本不能低于5.7" /bin/false
   exit 1
fi
while true;
do
    read  -p "当前部署的，MySQL版本为：${mysql_version}，是否部署(y/n):" deploy_choose
    if ([[ ${deploy_choose} == "y" ]] || [[ ${deploy_choose} == "Y" ]]);then
       echo "开始部署"
       echo -e "\n"
       break
    elif ([[ ${deploy_choose} == "n" ]] || [[ ${deploy_choose} == "N" ]]);then
       echo "退出部署"
       exit 0
    else
       echo -e "\033[41;30m错误的选项，请确认后重新输入！\033[0m"
       continue
    fi
done
}
 
function Decode_Mysql(){
#该函数功能为获取用户指定的MySQL basedir目录以及MySQL datadir目录
export script_path=$(pwd $0)
tar_name=$(ls .|grep mysql-.*.tar.gz)
tar_dir=${tar_name%.tar.gz*}
read -p "开始解压二进制文件，请确认解压目录(默认路径 /usr/local/):" decode_dir
decode_dir=${decode_dir:-/usr/local}
if [[ -d ${decode_dir}/${tar_dir} ]];then
    while true;
    do
        read -p "${decode_dir}/${tar_dir}目录已存在，是否覆盖(y/n)？" overwrite  #检查解压目录下是否已经存在解压的文件
        if ([[ ${overwrite} == "y" ]] || [[ ${overwrite} == "Y" ]]);then
           echo -e "\n"
           rm -rf ${decode_dir}/${tar_dir} && rm -f ${decode_dir}/mysql
           break
        elif ([[ ${overwrite} == "n" ]] || [[ ${overwrite} == "N" ]]);then
           echo "退出部署"
           exit 1
        else
           echo -e "\033[41;30m错误的选项，请确认后重新输入！\033[0m"
           continue
        fi
    done
fi
 
while true;
do
    if [[  -d ${decode_dir} ]];then
       export decode_dir=${decode_dir}
       break
    elif [[ ${decode_dir} == '' ]];then
       export decode_dir=/usr/local
       break
    else
        read -t 10 -p "${decode_dir}目录不存在，请重新输入(默认路径 /usr/local/):" decode_dir 
        [[ $? -ne 0 ]] && echo -e "\033[41;30m选择超时，使用默认路径 /usr/local/！\033[0m"
        continue
    fi
done
echo "正在解压至${decode_dir}目录,请稍后..."
tar xf ${tar_name} -C ${decode_dir} && cd ${decode_dir} && ln -s  ${tar_dir} mysql
if [[ $? -eq 0 ]];then
    action "解压成功" /bin/true
    echo -e "\n"
else
    action "解压失败" /bin/false
fi
}
 
 
function Mysql_User(){
echo -e "\033[44;30m检查MySQL用户\033[0m"
id mysql  &> /dev/bull
if [[ $? -eq 0 ]];
then
   action "MySQL 用户已存在，无需创建" /bin/true
else
   echo "MySQL 用户不存在，新建MySQL用户"
   groupadd -g 8888 mysql && useradd -r -g 8888 -u 8888 -s /bin/false  mysql && chown -R mysql.mysql ${decode_dir}/mysql
   [[ $? -eq 0 ]] && action "新建MySQL用户：mysql" /bin/true
fi
id mysql
echo -e "\n"
}
 
function Create_AND_Modify_Config_File(){
[[ -f /etc/my.cnf ]] && mv /etc/my.cnf /etc/my.cnf-$(date +%F-%H:%H:%S)
cd ${script_path} && cp my.cnf /etc/
read -p "开始生成my.cnf文件，请选择MySQL数据目录(默认 ${decode_dir}/mysql/data 目录):" data_dir
while true;
do
    if ([[ ${data_dir} != '' ]] &&  [[ $(ls -A ${data_dir}) != '' ]]);then           #判断指定的数据目录是否存在数据
        read -p "${data_dir}不为空，请重新选择(默认 ${decode_dir}/mysql/data 目录):" data_dir
        continue
    else
        break
    fi
done
 
while true;
do
     if [[  -d ${data_dir} ]];then
       export data_dir=${data_dir}
       break
    elif [[ ${data_dir} == '' ]];then
       export data_dir=${decode_dir}/mysql/data
       break
    else
        read -p "${data_dir}目录不存在，请重新输入(默认路径 ${decode_dir}/mysql/data):" data_dir
        [[ $? -ne 0 ]] && echo "选择超时，使用默认路径"
        continue
    fi
done
echo -e "\033[44;30m 数据目录为：${data_dir}\033[0m"
system_mem_size=$(grep "^MemTotal" /proc/meminfo |awk '{print $2}') #获取当前系统内存大小，设置innodb 缓存大小
innodb_buffer_pool_size="$(echo ${system_mem_size}/1024/10*6|bc)M"
sed -i "s#^socket.*#socket = ${data_dir}/mysql.sock#g" /etc/my.cnf
sed -i "s#^slow_query_log_file.*#slow_query_log_file = ${data_dir}/slow.log#g" /etc/my.cnf
sed -i "s#^log-error.*#log-error = ${data_dir}/error.log#g" /etc/my.cnf
sed -i "s#^log-bin.*#log-bin = ${data_dir}/mybinlog#g" /etc/my.cnf
sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = ${innodb_buffer_pool_size}M#g" /etc/my.cnf
sed -i "s#^innodb_undo_directory.*#innodb_undo_directory = ${data_dir}/undolog#g" /etc/my.cnf
sed -i "s#^basedir.*#basedir = ${decode_dir}/mysql#g" /etc/my.cnf
sed -i "s#^datadir.*#datadir = ${data_dir}#g" /etc/my.cnf
echo -e "\n"
}
 
function Init_Mysql(){
echo -e "\033[44;30m开始初始化数据库\033[0m"
cd ${decode_dir}/mysql && bin/mysqld --initialize --user=mysql --basedir=${decode_dir}/mysql --datadir=${data_dir}
if [[ $? -eq 0 ]];then 
    action "MySQL初始化完毕" /bin/true
    mysql_init_pwd=$(grep "temporary password" ${data_dir}/error.log  |awk -F "root@localhost: " '{print $NF}') 
    echo "MySQL初始化密码为：${mysql_init_pwd}"
    chown -R mysql:mysql ${decode_dir}/mysql 
    echo -e "\n"
else
    action "MySQL初始化失败请查看${data_dir}/error.log日志"  /bin/false
    exit
fi
}
 
function Set_System_Server_And_Init(){
echo -e "\033[44;30m开始设置MySQL开机自启 firewalld 全局环境变量 启动脚本 \033[0m"
decode_dir=${decode_dir:-/usr/local}
cd ${decode_dir}/mysql/support-files && cp mysql.server /etc/init.d/mysqld
[[ $? -eq 0 ]] && action "服务脚本拷贝成功" /bin/true || action "服务脚本拷贝失败" /bin/false
if ([[ ${decode_dir} != "/usr/local" ]] || [[ ${decode_dir} != "/usr/local/" ]]);then
    sed -i "s#^basedir=.*#basedir=${decode_dir}/mysql#g" /etc/init.d/mysqld
fi
if ([[ ${data_dir} != "/usr/local/mysql/data" ]] || [[ ${data_dir} != "/usr/local/mysql/data/" ]]);then
    sed -i "s#^datadir=.*#datadir=${data_dir}#g"  /etc/init.d/mysqld
fi
echo "export PATH=${decode_dir}/mysql/bin:$PATH"  >> ~/.bash_profile && source ~/.bash_profile
chkconfig --level 35 mysqld on
[[ $? -eq 0 ]] && action "设置开机自启成功" /bin/true || action "设置开机自启失败" /bin/false
firewall-cmd --zone=public --add-port=3306/tcp --permanent && systemctl restart firewalld.service
[[ $? -eq 0 ]] && action "防火墙规则设置成功" /bin/true || action "防火墙规则设置失败，请检查firewalld服务！" /bin/false
echo -e "\n"
}
 
function Start_Mysql(){
echo -e "\033[44;30m开始启动MySQL服务\033[0m"
/etc/init.d/mysqld start &> /dev/null
[[ $? -eq 0 ]] && action "启动成功..." /bin/true || action "启动失败,请查看${data_dir}/error.log日志" /bin/false
echo -e "\n"
echo -e "\033[41;30m使用 source ~/.bash_profile;mysql -u root --password="\'${mysql_init_pwd}\'" 命令登录 \033[0m"
echo -e "\033[41;30m使用 ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOURPASSWD'; 命令设置MySQL root用户本地登录密码\033[0m"
echo -e "\033[41;30m使用 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YOUR PASSWD' WITH GRANT OPTION; 命令设置MySQL root用户远程登录密码\033[0m"
echo -e "\033[41;30m密码强度要求包含 大小写字母、特殊字符、数字\033[0m"
}
 
function Main(){
Install_require;
Check_Mysql_Service;
Mysql_Num_Version_Check;
Decode_Mysql;
Mysql_User;
Create_AND_Modify_Config_File;
Init_Mysql;
Set_System_Server_And_Init;
Start_Mysql;
}
Main;