在centos7系统上用脚本实现自动搭建mysql8——教程及搭建演示

vim mysql.sh

#!/bin/bash
#FileName=mysql.sh
#Date=2022-1-9
#label=jf
#exit 5:no wget
#exit 6:Download failed
#exit 7:initialization failed
#exit 8:Service startup failed
#exit 9:Login and password modification failed
#exit 10:MySQL process exists

hd="/dev/null"

ps -ef | grep "mysql" | egrep -v "grep|mysql.sh"

if [ $? -eq 0 ];then
echo "已有MySQL服务启动中"
exit 10
fi

which wget &> "$hd" || yum -y install wget &> "$hd"

if [ `rpm -qa | egrep "net-tools|libaio" | wc -l` -ne 2 ];then
yum -y install net-tools libaio &> "$hd"
fi

cd /data/

#wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz &> "$hd" || exit 5

rpm -qa | grep mariadb &> "$hd"
if [ $? -eq 0 ];then
yum erase -y mariadb* &> "$hd"
fi

[ -d /data/mysql ]
if [ $? -eq 0 ];then
read -p "确定清除/data/mysql目录吗?[y/n]" a
if [ $a == y ];then
rm -rf /data/mysql
else
echo "已停止执行"
exit 9
fi
fi
cd /usr/local/

tar xf /data/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

mv mysql-8.0.11-linux-glibc2.12-x86_64 mysql

useradd -M -s /sbin/nologin mysql &> "$hd"

chown -R mysql.mysql /usr/local/mysql

ln -sf /usr/local/mysql/bin/* /usr/local/bin/

mkdir -p /data/mysql/{data,log,binlog,conf,tmp}

chown -R mysql.mysql /data/mysql

cat > /data/mysql/conf/my.cnf << kof
[mysqld]
lower_case_table_names = 1
user = mysql
server_id = 1
port = 3306

default-time-zone = '+08:00'
enforce_gtid_consistency = ON
gtid_mode = ON
binlog_checksum = none
default_authentication_plugin = mysql_native_password
datadir = /data/mysql/data
pid-file = /data/mysql/tmp/mysqld.pid
socket = /data/mysql/tmp/mysqld.sock
tmpdir = /data/mysql/tmp/
skip-name-resolve = ON
open_files_limit = 65535
table_open_cache = 2000

#################innodb########################
innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size = 2000M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout

innodb_undo_directory = /data/mysql/data
innodb_log_group_home_dir = /data/mysql/data

###################session###########################
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M

############log set###################
log-error = /data/mysql/log/mysqld.err
log-bin = /data/mysql/binlog/binlog
log_bin_index = /data/mysql/binlog/binlog.index
max_binlog_size = 500M
slow_query_log_file = /data/mysql/log/slow.log
slow_query_log = 1
long_query_time = 10
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = ON
log_output = FILE,TABLE
master_info_file = /data/mysql/binlog/master.info
kof

mysqld --defaults-file=/data/mysql/conf/my.cnf --initialize-insecure --user=mysql &> /data/mysql/log/error.log
if [ $? -eq 0 ]; then
mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &> "$hd" &
sleep 5
ps -ef | grep mysql | egrep -v "grep|mysql.sh" &> /dev/null
if [ $? -ne 0 ];then
echo "MySQL启动失败，请检查"
exit 7
else
sleep 3
ln -sf /data/mysql/tmp/mysqld.sock /tmp/mysql.sock
sleep 3
/usr/local/bin/mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';"

if [ $? -ne 0 ];then
echo "MySQL修改密码失败"
exit 9
else
echo "MySQL搭建成功，请登录"
fi
fi
else
echo "初始化失败，请检查"
exit 6
fi

-------------------------分割线

vim start.sh

#!/bin/bash
#FileName=start.sh
#Date=2021-1-9
#label=jf
#使用方法:sh start.sh
hd="/dev/null"

mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &> "$hd" &

sleep 3

ps -ef | grep mysql | grep -v grep &> "$hd"

if [ $? -eq 0 ];then
echo "MySQL启动成功"
else
echo "MySQL启动失败"
fi

 ----------------分割线

vim stop.sh

#!/bin/bash
#FileName=stop.sh
#Date=2021-1-9
#label=jf
#使用方法：sh stop.sh 用户 密码

j="$1"  #用户名
f="$2"  #密码
hd="/dev/null"

mysqladmin -u"$j" -p"$f" shutdown &> "$hd"

if [ $? -eq 0 ];then
echo "退出成功"
else
echo "退出失败"
fi

---------------------分割线

参数性能解释

本脚本适用于Centos7系列

本脚本使用MySQL8.0.11版本，如下载源过期请联系作者

MySQL安装位置在/data/

MySQL所有相关文件都在/data/mysql/

Mysql解压包位置在/usr/local/mysql/

如果脚本执行结束并未出现“MySQL搭建成功，请登录字样”则未搭建成功

如果脚本执行结束并未出现明确报错信息请echo "$?"，查看返回值，并进入脚本中，对照最上方返回值查看错误原因

初始账号：root 密码：123456

MySQL初次安装完成登录可能会报错“bash: /usr/bin/mysql: 没有那个文件或目录”原因是系统env未找到，需要bash一下或者绝对路径

启动MySQL：mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &> /dev/null &

登录MySQL：mysql -uroot -p123456

停止MySQL：mysqladmin -uroot -p123456 shutdown

命令已链接，可直接使用MySQL相关命令

start.sh为启动MySQL脚本：使用方法：sh start.sh

stop.sh为退出MySQL脚本：使用方法：sh stop.sh 用户 密码

-------------------------------------------------
性能优化解析
[client] # 客户端设置
port = 3306 # 默认连接端口
socket = /data/mysql/tmp/mysqld.sock # 用于本地连接的socket套接字
default-character-set = utf8mb4 # 字符集编码
[mysqld] # 服务端基本设置
lower_case_table_names = 1 # 表名存储在磁盘是小写的，但是比较的时候不区分大小写
user = mysql # 默认用户是
server_id = 1 # 全局唯一id
port = 3306 # mysql监听端口
default-time-zone = '+08:00' # 修改MySQL时区到北京时间
enforce_gtid_consistency = ON # 启动强制GTID的一致性
gtid_mode = ON # 开启GTID复制功能
binlog_checksum = none # Checksum在获取event内容的时候不进行校验
default_authentication_plugin = mysql_native_password # 修改MySQL密码认证插件
datadir = /data/mysql/data # 数据文件存放目录
pid-file = /data/mysql/tmp/mysqld.pid # pid文件所在位置
socket = /data/mysql/tmp/mysqld.sock # 为MySQL客户端与服务端之间的本地通讯指定一个套接字文件
tmpdir = /data/mysql/tmp/ # Mysql存放临时文件的目录
skip-name-resolve = ON # 跳过客户端DNS域名解析
open_files_limit = 65535 # mysql打开最大文件数
table_open_cache = 2000 # 表描述符缓存大小，可减少文件打开/关闭次数
max_connect_errors = 10000 # 当达到一定错误指标时，MySQL强制禁止该IP再次连接
max_connections = 1000 # MySQL允许的最大连接数
slave-skip-errors = all # 跳过所有的错误，继续执行复制操作(从使用)
#################innodb########################
innodb_data_home_dir = /data/mysql/data # innodb表空间文件位置，默认在MySQL的datadir目录
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend # 指定innodb共享表空间文件
innodb_buffer_pool_size = 8000M # 在内存中开辟一个缓冲池保存索引和数据,设置过大可能由于内存不足启动不起来
innodb_flush_log_at_trx_commit = 2 # 日志写入日志文件在每次提交后，但是日志文件大约每几秒flush到磁盘
innodb_io_capacity = 600 # innodb后台任务每秒可用的I/O操作数，定义了innodb刷新页面的速度
innodb_lock_wait_timeout = 120 # 设置数据库锁等待时间
innodb_log_buffer_size = 8M # 用来缓冲日志数据的缓冲区大小，当该值快满时innoDB将必须刷新数据到磁盘上
innodb_log_file_size = 200M # 日志组中的每个日志文件的大小,可以减少刷新缓冲池的次数
innodb_log_files_in_group = 3 # 指定有几个日志组
innodb_max_dirty_pages_pct = 85 # 在innodb缓冲池中最大允许的脏页面的比例
innodb_read_io_threads = 6 # 读线程个数
innodb_write_io_threads = 6 # 写线程个数
innodb_thread_concurrency = 32 # 在innodb核心内的允许线程数量
innodb_file_per_table = 1 # 使每一个innodb的表有自己的独立的表空间
innodb_rollback_on_timeout # 事务超时后的操作
innodb_undo_directory = /data/mysql/data # 指定undo文件存放的目录
innodb_log_group_home_dir = /data/mysql/data # innodb的日志文件所在位置
###################session###########################
join_buffer_size = 8M # 优化联合查询，减少参与join的被驱动表的读取次数
key_buffer_size = 256M # 索引块的缓冲区大小，决定索引处理速度,尤其是读
bulk_insert_buffer_size = 8M # 当堆myisam表load大文件时候，调大会极大提升速度
max_heap_table_size = 96M # 独立的内存表所允许的最大容量
tmp_table_size = 96M # 内部内存临时表的最大值
read_buffer_size = 8M # MySQL读入缓冲区的大小
sort_buffer_size = 2M # MySQL执行排序使用的缓冲大小
max_allowed_packet = 64M # 一个查询语句包的最大尺寸
read_rnd_buffer_size = 32M # MySQL的随机读缓冲区
############log set###################
log-error = /data/mysql/log/mysqld.err # 错误日志文件
log-bin = /data/mysql/binlog/binlog # 二进制日志文件
log_bin_index = /data/mysql/binlog/binlog.index # 索引文件位置
max_binlog_size = 300M # binlog日志最大大小
slow_query_log_file = /data/mysql/log/slow.log # 慢查询日志文件
slow_query_log = 1 # 开启慢查询
long_query_time = 10 # 慢查询日志的时间定义(秒)
log_queries_not_using_indexes = ON # 将所有没有带索引的查询语句全部写到慢查询日志中
log_throttle_queries_not_using_indexes = 10 # 限制每分钟写入慢日志中的不走索引的SQL语句的个数
log_slow_admin_statements = ON # 是否记录管理类命令到慢查询日志
log_output = FILE,TABLE # 将日志同时存入文件并写入数据库
master_info_file = /data/mysql/binlog/master.info # master_info文件位置
log_timestamps = SYSTEM # 用于控制error log、general log、slow log日期时区的
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" # mysql支持的基本语法及校验规则