MySQL一键式安装脚本

对于Linux系统不是太熟悉的人去到上面安装一个MySQL可能比较吃力，这里整理了一个傻瓜式安装脚本，直接一键安装。

数据库安装

这个脚本是基于5.7.30编写的自动安装部署脚本。

上传安装包

使用xftp工具上传数据库安装包，我这里是 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz，上传到了 /mydata/tools

脚本

在/mydata下新建脚本mysql_autosetup.sh，脚本中的内容根据实际情况所需配置。

#!/bin/bash
#####MySQL5.7.30数据库自动安装脚本
# mysql 安装包的绝对路径，去掉.tar.gz
tarGzPath=/mydata/tools/
tarGzFile=mysql-5.7.30-linux-glibc2.12-x86_64
# mysql 安装路径
installPath=/home/mysql/

# my.cnf配置文件
mysqlcnf=/home/mysql/my.cnf

# mysql serverid需要设置唯一的id,比如 ip+3位数字
mysqlServerid=1010101

# mysql 密码(不可擅自修改)
defaultPwd=root

# mysql 端口
mysqlPort=3306

# mysql数据目录
data_default=${installPath}${mysqlPort}
data_datadir=${data_default}/data
data_binlog=${data_default}/binlog
data_dbdata=${data_default}/dbdata
data_logs=${data_default}/logs
data_tmp=${data_default}/tmp
data_undo=${data_default}/undo
# 校验是否为ROOT用户
CheckRoot()
{
if [ $(id -u) != "0" ]; then
    echo "Error: You must be root to run this script, please use root to install"
    exit 1
fi
clear
}
 
# 优化文件最大打开数
DependFile()
{

if [ $( cat /etc/security/limits.conf  | grep "mysql" | wc -l )  -lt 1 ] ;then
cat >>/etc/security/limits.conf << EOF
* soft nproc 65536
* hard nproc 65536
* soft nofile 65536
* hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF

fi

if [ -e /etc/security/limits.d/20-nproc.conf ];then
if [ $( cat /etc/security/limits.d/20-nproc.conf  | grep "mysql" | wc -l )  -lt 1 ] ;then
cat >>/etc/security/limits.d/20-nproc.conf<<EOF
mysql       soft    nproc     unlimited
EOF

fi
fi

if [ -e /etc/security/limits.d/90-nproc.conf ];then
if [ $( cat /etc/security/limits.d/90-nproc.conf  | grep "mysql" | wc -l )  -lt 1 ] ;then
cat >>/etc/security/limits.d/90-nproc.conf<<EOF
mysql       soft    nproc     unlimited
EOF

fi
fi

if [ -e /etc/sysctl.conf ];then
fs_file=$( cat /proc/sys/fs/file-max)
if [ ${fs_file} -lt 65535 ] ;then
sed -i "s/${fs_file}/65535/g" /etc/sysctl.conf
/usr/sbin/sysctl -p 

fi
fi

echo -e "\e[31m #1.配置基础资源 \e[0m"
# 提前安装可能需要使用的库，防止报错
yum install -y libaio
}

# 拷贝tar.gz包
DecompressionTarGz()
{
if [ ! -e ${tarGzPath}${tarGzFile}.tar.gz  ];then
    echo -e "\e[31m ${tarGzPath}${tarGzFile}.tar.gz  不存在！请检查后重新执行脚本 \e[0m"
    exit 1
fi
# 解压并重命名到安装目录
if  [ ! -d ${installPath}${tarGzFile} ] ;then
    mkdir -p ${installPath}
    tar -xvf ${tarGzPath}${tarGzFile}.tar.gz -C ${installPath} &> /dev/null
fi

echo -e "\e[31m #2.软件已解压 \e[0m"

}
# 添加组合角色
AddMysqlUser()
{
if [ ! $(id -u "mysql") ]; then
   echo "mysql user is not exists for to created"
   /usr/sbin/groupadd mysql
   /usr/sbin/useradd -g mysql -r -s /sbin/nologin -M mysql
fi

echo -e "\e[31m #3.mysql启动用户已准备完成 \e[0m"

}

# 创建mysql数据目录
createMysqlFolder()
{
if  [ -d ${data_default} ] ;then
    if [ $(du -s  ${data_default}  |  awk 'NR==1{print $1}') -gt 0 ] ;then
        mv  ${data_default}  ${data_default}"`date +%Y%m%d%H%M`"
    fi
fi

mkdir -p ${data_datadir}
mkdir -p ${data_binlog}
mkdir -p ${data_dbdata}
mkdir -p ${data_logs}
mkdir -p ${data_tmp}
mkdir -p ${data_undo}

# 赋予权限
chown -R mysql:mysql ${data_default}
chmod 700 ${data_tmp}

echo -e "\e[31m #4.mysql 数据目录 权限 已准备完成 \e[0m"
}

# 创建my.cnf
MakeMyCnf()
{

if  [ -e ${mysqlcnf} ] ;then
    #mv  ${mysqlcnf}  ${mysqlcnf}"`date +%Y%m%d%H%M`"
    rm ${mysqlcnf}
fi

cat >${mysqlcnf}<<EOF
[mysqld_safe]
user = mysql
nice = 0

[client]                           
socket                             = ${data_datadir}/mysql.sock
port                               = ${mysqlPort}

[mysqld]
############# GENERAL #############
skip_ssl
skip-name-resolve
autocommit                         = ON
character_set_server               = utf8mb4
collation_server                   = utf8mb4_unicode_ci
explicit_defaults_for_timestamp    = ON  
lower_case_table_names             = 1
port                               = ${mysqlPort}
read_only                          = OFF
transaction_isolation              = READ-COMMITTED
open_files_limit                   = 65535
max_connections        = 2000
expire_logs_days                   = 10
default-time_zone                  = '+8:00'
secure_file_priv 				   =   # 没有值时，表示不限制mysqld在任意目录的导入导出。
####### CACHES AND LIMITS #########
interactive_timeout                = 600 
lock_wait_timeout                  = 300
max_connect_errors                 = 1000000

table_definition_cache             = 2000
table_open_cache                   = 2000 
table_open_cache_instances         = 8

thread_cache_size                  = 32
thread_stack                       = 256K

tmp_table_size                     = 32M
max_heap_table_size                = 64M

query_cache_size                   = 0
query_cache_type                   = 0

sort_buffer_size                   = 1M
join_buffer_size        = 1M
sort_buffer_size        = 1M
read_rnd_buffer_size        = 2M

innodb_io_capacity            = 1000 
innodb_io_capacity_max       = 2000

max_allowed_packet                 = 1024M
slave_max_allowed_packet           = 1024M
slave_pending_jobs_size_max        = 1024M


############# SAFETY ##############
local_infile                       = OFF
skip_name_resolve                  = ON
sql_mode                           = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

############# LOGGING #############
general_log                        = 0
log_queries_not_using_indexes      = ON
log_slow_admin_statements          = ON
log_warnings                       = 2
long_query_time                    = 1  # 1秒慢日志
slow_query_log                     = ON

############# REPLICATION #############

server_id                          = ${mysqlServerid}  # ip+3位数字
binlog_checksum                    = CRC32
binlog_format                      = ROW
binlog_rows_query_log_events       = ON

enforce_gtid_consistency           = ON
gtid_mode                          = ON
log_slave_updates                  = ON

master_info_repository             = TABLE
master_verify_checksum             = ON

max_binlog_size                    = 512M
max_binlog_cache_size              = 1024M   # 已修改，原值1024
binlog_cache_size        = 8M

relay_log_info_repository          = TABLE
skip_slave_start                   = ON
slave_net_timeout                  = 10
slave_sql_verify_checksum          = ON

sync_binlog                        = 1
sync_master_info                   = 1
sync_relay_log                     = 1
sync_relay_log_info                = 1

############### PATH ##############
basedir                            = ${installPath}${tarGzFile}

datadir                            = ${data_datadir}
tmpdir                             = ${data_tmp}
socket                             = ${data_datadir}/mysql.sock
pid_file                           = ${data_datadir}/mysql.pid
innodb_data_home_dir               = ${data_dbdata}

log_error                          = ${data_logs}/error.log
general_log_file                   = ${data_logs}/general.log
slow_query_log_file                = ${data_logs}/slow.log

log_bin                            = ${data_binlog}/mysql-bin
log_bin_index                      = ${data_binlog}/mysql-bin.index
relay_log                          = ${data_binlog}/relay-log
relay_log_index                    = ${data_binlog}/relay-log.index

# undo settings
innodb_undo_directory        = ${data_undo}
innodb_undo_log_truncate           = 1 
innodb_max_undo_log_size      = 16M
innodb_undo_tablespaces            = 4


############# INNODB #############
innodb_file_format                 = barracuda
innodb_flush_method                = O_DIRECT

innodb_buffer_pool_size            = 1024M
innodb_buffer_pool_instances       = 4 
innodb_thread_concurrency          = 0

innodb_log_file_size               = 1024M
innodb_log_files_in_group          = 2
innodb_flush_log_at_trx_commit     = 1
innodb_support_xa                  = ON
innodb_strict_mode                 = ON

innodb_data_file_path              = ibdata1:32M;ibdata2:16M:autoextend
innodb_temp_data_file_path         = ibtmp1:1G:autoextend:max:30G
innodb_checksum_algorithm          = strict_crc32
innodb_lock_wait_timeout           = 600

innodb_log_buffer_size             = 8M
innodb_open_files                  = 65535

innodb_page_cleaners               = 1
innodb_lru_scan_depth              = 256
innodb_purge_threads               = 4
innodb_read_io_threads             = 4
innodb_write_io_threads            = 4 

innodb_print_all_deadlocks         = 1

[mysql]
############# CLIENT #############                            
max_allowed_packet                 = 16M
socket                             = ${data_datadir}/mysql.sock
no-auto-rehash

[mysqldump]                        
max_allowed_packet                 = 16M

EOF

echo -e "\e[31m #5.mysql cnf配置完成，【需要按照实际情况更改】 \e[0m"
}

# 初始化数据库
InitDataBase()
{
# cd ${installPath}${tarGzFile}
${installPath}${tarGzFile}/bin/mysqld --defaults-file=${mysqlcnf} --basedir=${installPath}${tarGzFile} --datadir=${data_datadir} --user=mysql --initialize

${installPath}${tarGzFile}/bin/mysqld_safe --defaults-file=${mysqlcnf}  --user=mysql   &

echo -e "\e[31m #6. 初始化数据库完成并启动服务. \e[0m"

}

# 重置密码为脚本中设置的密码
ResetPwd()
{
sleep 10s
# 从日志中获取mysql初始密码
pwd=`grep "A temporary password is generated for root@localhost: " ${data_logs}/error.log`
pwd=${pwd##*root@localhost:}
# 防止因为初始密码中有特殊字符出错 拼接单引号
pwd=${pwd// /}
echo ${pwd}
${installPath}${tarGzFile}/bin/mysql -uroot -p${pwd} -S ${data_datadir}/mysql.sock --connect-expired-password  -e "alter user 'root'@'localhost' identified by   '${defaultPwd}';"

echo -e "\e[31m #7. 已重置数据库密码。登录方式如下: \e[0m"
echo -e "\e[31m ${installPath}${tarGzFile}/bin/mysql -uroot -p  -S ${data_datadir}/mysql.sock \e[0m"

}

main()  
{  
###1.校验是否为ROOT用户
CheckRoot  

###2.优化文件最大打开数
DependFile

###3.拷贝tar.gz包
DecompressionTarGz

###4.添加组合角色
AddMysqlUser

###5.创建mysql 数据目录
createMysqlFolder

###6.创建my.cnf
MakeMyCnf

###7.初始化数据库
InitDataBase

###8.重置密码
ResetPwd
}  
main

授予执行权限

chmod +x /mydata/mysql_autosetup.sh

执行脚本

# 这里是在 /mydata 下直接执行的
./mysql_autosetup.sh

等待脚本执行完成后，最后会出现类似以下字样

#7. 已重置数据库密码。登录方式如下: 
 /home/mysql/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysql -uroot -p  -S /home/mysql/3306/data/mysql.sock

使用上述命令，然后输入你设置的密码应该就能进去数据库了，如果安装过程中出现了报错，需要你自己排查，我这里是一步成功的。

给数据库授予远程登录

进入数据库，执行以下命令

grant all on *.* to root@'%' identified by 'root';

flush privileges; # 刷新权限

exit # 退出

如果需要修改密码

进入数据库执行以下命令

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';

flush privileges; # 刷新权限

exit # 退出

这样就安装好了，然后你可以用可视化工具去远程连接操作了。

将相关命令软连接：

ln -s /home/mysql/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqladmin /usr/bin
ln -s /home/mysql/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysql /usr/bin
ln -s /home/mysql/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqld /usr/bin
ln -s /home/mysql/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysql_safe /usr/bin
ln -s /home/mysql/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqldump /usr/bin

重启mysql：

mysqld --defaults-file=/home/mysql/my.cnf -uroot

如果显示找不到某个目录，则创建，然后授权777。

JDK一键安装

基本步骤

1、上传 JDK 压缩包到虚拟机的 /mydata/tools 目录下，比如我的是：jdk-8u161-linux-x64.tar.gz

这里的 /mydata 可以换成你自己创建的目录，这里只是我的习惯而已。

2、编写脚本

vi jdk_autosetup.sh

#!/bin/bash
cd /mydata/tools
gz=`find -name 'jdk*.gz' | awk -F '/' '{print $2}'`
tar -zxvf $gz
echo 'export JAVA_HOME=/mydata/tools/jdk1.8.0_161' >> /etc/profile
echo 'export CLASSPATH=.:$JAVA_HOME/lib/rt.jar:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar' >> /etc/profile
echo 'export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH' >> /etc/profile
source /etc/profile
java -version

3、添加可执行权限

chmod +x jdk_autosetup.sh

4、启动 Shell 脚本即可一键安装

./jdk_autosetup.sh