版本信息:
OS: CentOS7.* /Redhal 7*
DB: MySQL 8.* / MySQL 5.7.*
简介:shell脚本执行,通过传入对应参数,执行mysql 数据库安装,包括系统依赖包,系统参数,防火墙,用户创建,数据库部分参数自动调整(根据cpu,内存),初始化localhost 数据库root 权限,新建root@% 用户,授权。 以及启动脚本自动修改等。
脚本可以反复执行。
脚本分为两部分:
1,pre_01.sh 数据的传参。
2,body_01.sh 真正执行主体脚本
脚本一,参数传入,当然这参数可以自行调整新增。
#!/bin/bash
sh list1-hopped-m8c7-install.sh \
--dbdir=/data/mysql \
--dbport=3308 \
--basedir=/usr/local/mysql \
--dbsuperpwd='xxxxxxx' \
备注: dbdir 为 存储数据目录
dbport 为端口号指定
basedir 为mysql 软件解压目录
dbsuperpwd 为mysql数据库root 账号密码
脚本二,执行主体。
#! /bin/bash
# editor Dominic.Lee
# editime 20230612
# golden
#set -x
# 接受传输数据提示
# Get the key value of input argumenttime format like '--args=value'.
function get_key_value()
{
echo "$1" | sed 's/^--[a-zA-Z_-]*=//'
}
# Usage will be helpful when you need to input the valid argumenttime.
function usage()
{
cat <<EOF
Usage: $0 [configure-options]
-?, --help Show this help message.
--dbdir=<> Set 数据目录:dbdir/{datafile,logfile}
--basedir=<> Set 软件目录:/usr/local/mysql/version*
--dbport=<> Set 数据库port:默认3306
--dbsuperpwd=<> Set 数据库super密码
EOF
}
# Parse the input argumenttime and get the value of the input argument.
if [ $# -eq 0 ];then
usage
# print_default
exit 0;
fi
function parse_options()
{
while test $# -gt 0
do
case "$1" in
--dbdir=*)
db_directory=`get_key_value "$1"`;;
--basedir=*)
base_dir=`get_key_value "$1"`;;
--dbport=*)
db_port=`get_key_value "$1"`;;
--dbsuperpwd=*)
dbroot_pwd=`get_key_value "$1"`;;
-? | --help )
usage
# print_default
exit 0;;
*)
echo "Unknown option '$1'"
exit 1;;
esac
shift
done
}
##
Tdate=`date +%Y%m%d`
Ttime=`date '+%F %T'`
current_dir=`pwd`
parse_options "$@"
## 打印出对应传入参数 ##
echo " "
echo -e "...............${Ttime}. Begin................. "
echo " "
echo -e "数据目录->${db_directory}...... "
echo -e "软件目录->${base_dir}....... "
echo -e "DB端口号->${db_port}...... "
echo -e "super密码->${dbroot_pwd}...... "
data_dir=${db_directory}/${db_port}/datafile
log_dir=${db_directory}/${db_port}/logfile
echo -e "数据目录->$data_dir......"
echo -e "数据日志目录->$log_dir......"
## 一,防止重复执行,端口占用异常 ##
echo ".1-清理环境......"
# 检查端口是否被占用并且是MySQL运行的
# 指定要检查的端口号
port=${db_port}
# 检查端口是否被占用
is_port_in_use() {
netstat -ln | grep ":$1 " >/dev/null
return $?
}
# 检查端口是否是MySQL运行的
is_mysql_running_on_port() {
pid=$(lsof -i :$1 | awk 'NR==2{print $2}')
process_name=$(ps -p $pid -o comm= 2>/dev/null)
if [ "$process_name" = "mysqld" ]; then
return 0
else
return 1
fi
}
# 杀死MySQL进程
kill_mysql_process() {
pid=$(lsof -i :$1 | awk 'NR==2{print $2}')
kill $pid
}
# 检查端口是否被占用且是MySQL运行的
if is_port_in_use $port && is_mysql_running_on_port $port; then
echo "Port $port is in use by MySQL."
echo "Killing the MySQL process..."
kill_mysql_process $port
echo "MySQL process killed."
else
echo "Port $port is not in use by MySQL."
fi
sleep 30
## 二,定义登录提醒/etc/motd ##
echo -e ".2-/etc/motd提示......"
cat >> /etc/motd <<EOF
###################################################
# --------------- #
# datadir -> ${data_dir} 数据文件目录 #
# logdir -> ${log_dir} 数据日志目录 #
# baseidir -> ${base_dir}数据目录 #
# MySQL Port ${db_port} utf8mb4 #
# /etc/init.d/mysqld start && stop #
# -------------- #
###################################################
EOF
## 三,调整limits.conf系统参数 ##
echo -e ".3-limit.conf参数设置......"
cat >> /etc/security/limits.conf << EOF
mysql soft nproc 16384
mysql hard nproc 16384
mysql soft nofile 65536
mysql hard nofile 65536
mysql soft stack 1024000
mysql hard stack 1024000
EOF
## 四,调整sysctl.conf 文件参数 ##
echo -e ".4-sysctl.conf参数设置......"
cat >> /etc/sysctl.conf << EOF
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
EOF
sysctl -p & > /etc/null
## 五,关闭防火墙及selinux ##
echo -e ".5-关闭防火墙......"
systemctl stop firewalld
systemctl disable firewalld
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
## 六,安装依赖包及处理 yum install ##
# 函数:安装依赖包
echo -e ".6-依赖包检测...... "
function install_dependencies()
{
local dependencies=(
"wget"
"numactl"
"libaio"
"libstdc++"
"libgcc"
"numactl-libs"
"gcc-"
)
for dependency in "${dependencies[@]}"; do
if ! rpm -qa | grep -qw "$dependency"; then
echo "安装依赖包: $dependency"
sudo yum remove libnuma.so.1
sudo yum install -y "$dependency"
if [ $? -ne 0 ]; then
echo "安装依赖包 $dependency 失败"
exit 1
fi
else
echo "依赖包 $dependency 已安装"
fi
done
}
## 七,创建mysql用户及用户组 ##
echo -e ".7-用户组检测......"
user=mysql
group=mysql
#create group if not exists
egrep "^$group" /etc/group >& /dev/null
if [ $? -ne 0 ]
then
groupadd $group
fi
#create user if not exists
egrep "^$user" /etc/passwd >& /dev/null
if [ $? -ne 0 ]
then
useradd -r -g $group -s /sbin/nologin $user
fi
## 八,创建数据文件目录 ##
echo -e ".8-创建对应文件目录及清理......"
if [ ! -d ${base_dir} ];then
mkdir -p ${base_dir}
else
rm ${base_dir}/* -rf
fi
if [ ! -d "${data_dir}" ];then
mkdir -p ${data_dir}
mkdir -p ${log_dir}
else
rm ${data_dir}/* -rf
rm ${log_dir}/* -rf
fi
## 九,mysql8数据库初始化 ##
echo -e ".9-数据库初始化I......"
tar_name=$(ls .|grep mysql-8.*.tar.*)
tar_dir=${tar_name%.tar.xz*}
tar -xvf "${current_dir}"/"${tar_name}"
mv "${current_dir}"/"${tar_dir}" ${base_dir}/
# 判断文件目录是否为空
if [ "$(ls -A ${data_dir})" ];then
echo -e "初始化时,$data_dir目录必须为空"
else
rm ${data_dir}/* -rf
fi
# 初始化数据库
echo -e ".9-初始化数据库II......"
touch ${log_dir}/error.log
chown -R mysql:mysql ${base_dir}/
chown -R mysql:mysql ${log_dir}/
chown -R mysql:mysql ${data_dir}/
chown -R mysql:mysql /etc/my.cnf
chmod -R 755 ${data_dir}/
chmod -R 755 ${log_dir}
cd ${base_dir}/${tar_dir} && bin/mysqld --initialize-insecure --user=mysql --basedir=${base_dir}/${tar_dir} --datadir=${data_dir}
if [[ $? -eq 0 ]];then
echo -e "MySQL初始化完毕"
echo -e "\n"
else
echo -e "MySQL初始化失败请查看error.log日志"
exit
fi
## 十,配置基本参数my.cnf ##
# 这里的my.cnf 直接放在系统/etc/init.d/下.
echo -e ".10-数据库参数设置......"
echo " "
cat > ${base_dir}/my.cnf << EOF
[client]
port = ${db_port}
socket = /tmp/mysql.sock
default_character_set = utf8mb4
[mysql]
prompt="\u@mysqldb \R:\m:\s [\d]> "
no-auto-rehash
default_character_set = utf8mb4
[mysqld]
bind-address = 0.0.0.0
port = ${db_port}
datadir = ${data_dir}
basedir = ${base_dir}/${tar_dir}
socket = /tmp/mysql.sock
log-error = ${log_dir}/error.log
pid-file = ${data_dir}/mysqld.pid
default_authentication_plugin = mysql_native_password
default_time_zone = "+8:00"
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 0
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = off
open_files_limit = 65535
max_connections = 600
max_connect_errors = 999999999
back_log = 170
thread_cache_size = 30
max_allowed_packet = 512M
sort_buffer_size = 8M
join_buffer_size = 8M
interactive_timeout = 1800
wait_timeout = 1800
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = ${log_dir}/slow.log
long_query_time = 2
log_slow_admin_statements = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000
log_throttle_queries_not_using_indexes = 60
relay_log_info_repository = TABLE
master_info_repository = TABLE
server-id = ${db_port}01
log-bin = ${log_dir}/mybinlog
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 864000
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1
binlog_cache_size = 2M
max_binlog_cache_size = 2G
max_binlog_size = 2G
gtid_mode = on
enforce_gtid_consistency = 1
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 4G
innodb_io_capacity = 5000
innodb_io_capacity_max = 15000
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 = 20
innodb_print_all_deadlocks = 1
innodb_rollback_on_timeout = 1
innodb_sort_buffer_size = 64M
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 1
[mysqldump]
quick
max_allowed_packet = 512M
EOF
# 十一, 修改参数 #
echo -e ".11-基于OS内存修改数据库参数......"
if [ -f /etc/my.cnf ];then
mv /etc/my.cnf /etc/my.cnf-$(date +%F-%H:%H:%S)
cp ${base_dir}/my.cnf /etc/
system_mem_size=$(grep "^MemTotal" /proc/meminfo |awk '{print $2}')
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 = ${log_dir}/slow.log#g" /etc/my.cnf
sed -i "s#^log-error.*#log-error = ${log_dir}/error.log#g" /etc/my.cnf
sed -i "s#^log-bin.*#log-bin = ${log_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 = ${base_dir}/mysql#g" /etc/my.cnf
sed -i "s#^datadir.*#datadir = ${data_dir}#g" /etc/my.cnf
echo -e "\n"
fi
# 十二, mysqld 程序 设置 #
echo " "
echo ".12-设置启动脚本......"
cd ${base_dir}/${tar_dir}/support-files && cp mysql.server /etc/init.d/mysqld
sed -i "s#^basedir=.*#basedir=${base_dir}/${tar_dir}/#g" /etc/init.d/mysqld
sed -i "s#^datadir=.*#datadir=${data_dir}#g" /etc/init.d/mysqld
#touch ${log_dir}/error.log
chown -R mysql:mysql ${base_dir}/
chown -R mysql:mysql ${log_dir}/
chown -R mysql:mysql ${data_dir}/
chown -R mysql:mysql /etc/my.cnf
chmod -R 755 ${data_dir}/
chmod -R 755 ${log_dir}
chown -R mysql:mysql /etc/init.d/mysqld
/etc/init.d/mysqld start
sleep 20
## 十三,登录mysql 修改默认root@localhost密码,及创建一个裁剪权限的root@'%'用户
echo -e ".13-设置super账号权限...... "
echo " "
echo " "
login_mysql="${base_dir}/${tar_dir}/bin/mysql -uroot -P${db_port} -S ${data_dir}/mysql.sock "
${login_mysql} << EOF
flush privileges;
alter user root@'localhost' identified by '${dbroot_pwd}';
create user root@'%' identified by '${dbroot_pwd}';
flush privileges;
grant select,insert,update,delete,create on *.* to root@'%' with grant option;
grant drop,reload,file,references, index, alter on *.* to root@'%' with grant option;
grant show databases, create temporary tables on *.* to root@'%' with grant option;
grant lock tables, execute, create view on *.* to root@'%' with grant option;
grant show view, create routine, alter routine on *.* to root@'%' with grant option;
grant create user,create tablespace, create role on *.* to root@'%' with grant option;
grant drop role,event,trigger on *.* to root@'%' with grant option;
flush privileges;
EOF
echo -e " ............... ${Ttime} The End ................. "
echo " "
echo -e " ..... 数据库MySQL8.0.*安装完成 ....."
exit;