恒富网

#!/bin/bash
 
 
### WARING: 
#1.执行此脚本请确保【/data/mysqldata】目录无其他文件，初始化会清空此目录 
#2.运行此脚本需内存8G以上，小于8G需调整my.cnf配置，否则无法启动mysql服务
#3.部署后mysql初始root密码为123456，部署完毕后可登录mysql自行修改
###
 
 
#####
#MySQL5.7.39数据库自动安装脚本
# Version:      1.0
# Author:       zzs
# Date:         2022-10-09
#####
 
#sed -i 's/\r//' install_mysql.sh
 
#安装包 脚本存放路径
# mkdir -p /data/package
installPackage=/data/package
 
#mysql 安装路径
installPath=/data/mysqldata
 
 
#my.cnf配置文件
mysqlcnf=/etc/my.cnf
 
 
#mysql serverid需要设置唯一的id,比如 ip+3位数字
mysqlServerid=100001
 
  
#mysql 端口
mysqlPort=3306
 
 
version=5.7.39
 
 
 
 
# 校验是否为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
 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
 
echo -e "\e[31m #1.配置基础资源 \e[0m"
 
 
}
 
 
#解压mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar包,初始化环境
DecompressionTar()
{
 
 
#防止环境缺少libaio导致mysql安装失败
libaio=`rpm -qa | grep libaio`
if [ -z "${libaio}" ]; then
    rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
fi
 
#卸载mariadb,防止与安装冲突
mariadb=`rpm -qa | grep mariadb`
if [ $? -eq 0 ];then
    rpm -e $mariadb --nodeps
fi
 
 
#卸载mysql,防止与安装冲突
ori_mysql=`rpm -qa | grep mysql-community-`
rpm -e $ori_mysql --nodeps
rm -rf $installPath
 
 
cd $installPackage
tar -xvf mysql-$version-1.el7.x86_64.rpm-bundle.tar
 
echo -e "\e[31m #2.软件已解压，环境已初始化 \e[0m"
 
 
}
 
 
 
#创建mysql 数据目录
createMysqlFolder()
{
 
 
mkdir -p $installPath/data
mkdir -p $installPath/logs/bin_log
mkdir -p $installPath/logs/relay
mkdir -p $installPath/logs/undo_log
mkdir -p $installPath/tmp
 
 
 
 
 
echo -e "\e[31m #3.mysql 数据目录已准备完成 \e[0m"
 
 
}
 
#安装mysql
install_mysql()
{
cd $installPackage
 
# 安装mysql 建议按顺序安装：common --> libs --> clients --> server
 
rpm -ivh mysql-community-common-$version-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-$version-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-$version-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-$version-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-$version-1.el7.x86_64.rpm
 
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
 
 
# For advice on how to change settings please see
# # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 
[client]
port =  $mysqlPort
socket = /var/lib/mysql/mysql.sock
default_character_set = utf8mb4
 
[mysqld]
#skip-grant-tables
skip-ssl
port = $mysqlPort
socket = /var/lib/mysql/mysql.sock
 
 
datadir=$installPath/data
log-error=$installPath/logs/mysqld.log
slow_query_log_file = $installPath/logs/slow.log
innodb_undo_directory = $installPath/logs/undo_log
log_bin = $installPath/logs/bin_log/bin.log
relay_log = $installPath/logs/relay/relay.log
secure_file_priv=$installPath/tmp
pid-file=/var/run/mysqld/mysqld.pid
tmpdir  = $installPath/tmp
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
 
# 伪随机生成id
server-id = $mysqlServerid
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
autocommit = 1
character_set_server=utf8mb4
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
max_allowed_packet = 268435456
event_scheduler = 1
 
# connection #
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 1800
skip-name-resolve
max_connections = 6000
max_connect_errors = 1000000
 
# table cache performance settings
table_open_cache = 4096
table_definition_cache = 4096
 
# session memory settings #
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size = 64M
max_heap_table_size=64M
join_buffer_size = 16M
thread_cache_size = 64
 
 
slow_query_log = 1
general_log    = 0
log_output     = file
 
log_queries_not_using_indexes = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 7
long_query_time = 2
min_examined_row_limit = 100
binlog-rows-query-log-events = 1
log-bin-trust-function-creators = 1
log-slave-updates = 1
 
lower_case_table_names=1
#collation_server=utf8_bin
 
 
# innodb settings #
innodb_page_size = 16384
#innodb_buffer_pool_size = 18589934592
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 5
innodb_io_capacity = 200
innodb_io_capacity_max = 400
innodb_flush_method = O_DIRECT
 
 
innodb_flush_neighbors = 1
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 4M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 0
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_write_io_threads = 2
innodb_read_io_threads = 2
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size=512M
innodb_open_files=4096
 
# replication settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
 
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
binlog_format=ROW
 
 
 
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
 
# semi sync replication settings #
plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 3000
# rpl_semi_sync_slave_enabled = 1
# password plugin #
validate_password_policy=LOW
validate_password_length=6
validate_password_number_count=0
# validate-password=FORCE_PLUS_PERMANENT
 
 
[mysql]
no-auto-rehash
max_allowed_packet = 1G
prompt  = '(product-m)\u@\h [\d]> '
default_character_set          = utf8mb4
 
 
EOF
 
 
echo -e "\e[31m #5.mysql cnf配置完成，【需要按照实际情况更改】 \e[0m"
}
 
 
#初始化数据库
InitDataBase()
{
	
#赋予权限
chown mysql:mysql -R  $installPath
chmod 700 $installPath/tmp
 
mysqld --initialize --user=mysql
 
systemctl start mysqld.service
 
#设置开机自启
systemctl enable mysqld
 
echo -e "\e[31m #6. 初始化数据库完成并启动服务. \e[0m"
 
 
}
 
#设置mysql备份
backup_mysql()
{
cd $installPackage
 
rpm -ivh libev-4.04-2.el6.x86_64.rpm
rpm -ivh perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm
rpm -ivh perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm
rpm -ivh perl-IO-Compress-2.061-2.el7.noarch.rpm
rpm -ivh perl-Net-Daemon-0.48-5.el7.noarch.rpm
rpm -ivh perl-PlRPC-0.2020-14.el7.noarch.rpm
rpm -ivh perl-DBI-1.627-4.el7.x86_64.rpm
rpm -ivh perl-DBD-MySQL-4.023-6.el7.x86_64.rpm
rpm -ivh perl-Digest-1.17-245.el7.noarch.rpm 
rpm -ivh perl-Digest-MD5-2.52-3.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
 
echo -e "\e[31m   xtrabackup安装完成 \e[0m"
 
mkdir -p /data/mysql_backup
 
 
#全备脚本
if  [ -e /data/mysql_backup/mysql_xtrabackup.sh ] ;then
    rm -rf /data/mysql_backup/mysql_xtrabackup.sh
fi
cat >/data/mysql_backup/mysql_xtrabackup.sh<<EOF
 
#!/bin/bash
 
##Filename : mysql_xtrabackup.sh
##Date     : 2022-10-10
##Author   : zzs
##Desc     : Phisical mysql_backup mysql database
 
 
##备份策略:
##周日(7)： 全备
##周一 ~ 周六(1-6)： 增量备份
 
 
##
##========== global var ============
##
 
 
BAK_DIR_ROOT="/data/mysql_backup/"
#默认周日进行全备 (1 - 7), 1 是周一，7是周日
 
FULL_BAK_DAY_OF_WEEK=7 
 
#备份文件保留周期，默认保留35天 (4-5周)
HOLD_DAYS=14
 
 
MYSQL_USERNAME=root
MYSQL_PASSWORD=123456
 
MYSQL_CNF="/etc/my.cnf"
 
 
 
CURRENT_WEEK_OF_YEAR=\$(date +%U)
CURRENT_DAY_OF_WEEK=\$(date +%u)
CURRENT_DATE=\$(date +%F)
CURRENT_TIME=\$(date +%H-%M-%S)
CURRENT_DATETIME="\${CURRENT_DATE}_\${CURRENT_TIME}"
 
BAK_WEEK_DIR="\${BAK_DIR_ROOT}/WEEK_\${CURRENT_WEEK_OF_YEAR}"
 
BAK_FULL_DIR="\${BAK_WEEK_DIR}/FULL"
 
BAK_LOG="\${BAK_WEEK_DIR}/backup.log"
 
 
 
##
##========== function =============
##
 
function clean_backup() {
    find \${BAK_DIR_ROOT}  -mtime +\${HOLD_DAYS}  -prune -exec rm -rf {} \;
}
 
 
function write_start_log() {
 
    if [[ ! -d \${BAK_WEEK_DIR} ]];then
        mkdir -p \${BAK_WEEK_DIR}
    fi
 
    echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> \${CURRENT_DATETIME} Begin Backup <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<" >> \${BAK_LOG}
}
 
function full_backup() {
 
    if [[ ! -d \${BAK_FULL_DIR} ]]; then
        mkdir -p \${BAK_FULL_DIR}
    fi
 
    echo "*** FULL BACKUP Date : \${CURRENT_DATETIME}" >> \${BAK_FULL_DIR}/full_backup.date
    /usr/bin/innobackupex --defaults-file=\${MYSQL_CNF} --no-timestamp --user=\${MYSQL_USERNAME} --password=\${MYSQL_PASSWORD} --no-lock   \${BAK_FULL_DIR}    2>&1 >> \${BAK_LOG}
 
}
 
 
#每周周一到周六进行增量备份
function incr_backup() {
 
    CURRENT_INCR_DIR="\${BAK_WEEK_DIR}/INCR_\${CURRENT_DAY_OF_WEEK}"
    PREV_DAY_OF_WEEK=\$((\${CURRENT_DAY_OF_WEEK} - 1))
    BASE_DIR="\${BAK_WEEK_DIR}/INCR_\${PREV_DAY_OF_WEEK}"
 
    #如果不存在之前的增量，则使用全量路径作为增量的BASE
    #比如周一的时候
    if [[ ! -d \${BASE_DIR} ]];then
        BASE_DIR=\${BAK_FULL_DIR}
    fi
 
    #如果在此函数中，还没有BASE，则认为可能是在项目第一周执行
    #进行一次全量备份
    if [[  ! -d \${BASE_DIR} ]];then
        echo "*** \${BASE_DIR} as BASE_DIR is not exists!" >> \${BAK_LOG}
        echo "***  So Backup Processor into FULL BACKUP " >> \${BAK_LOG}
        full_backup
        exit \$?
    fi
 
    #如果存放增量数据的目录已经存在，这里进行添加时间戳处理（一天备份多次）
    if [[ -d \${CURRENT_INCR_DIR} ]];then
        CURRENT_INCR_DIR="\${CURRENT_INCR_DIR}_\${CURRENT_DATETIME}"
    fi
 
    #如果BASE_DIR 存在，则进行增量备份
    if [[ ! -d \${CURRENT_INCR_DIR} ]];then
        mkdir -p \${CURRENT_INCR_DIR}
    fi
 
    echo "*** INCR BACKUP Date : \${CURRENT_DATETIME}" >> \${CURRENT_INCR_DIR}/incr_backup.date
    /usr/bin/innobackupex --defaults-file=\${MYSQL_CNF}  --no-timestamp --user=\${MYSQL_USERNAME} --password=\${MYSQL_PASSWORD} --no-lock --incremental --incremental-basedir=\${BASE_DIR}   \${CURRENT_INCR_DIR}   2>&1 >> \${BAK_LOG}
}
#################main #################
 
write_start_log
 
clean_backup
 
#如果指定的全备时间 == 当前的时间，则执行全备
if [[ \${FULL_BAK_DAY_OF_WEEK} -eq \${CURRENT_DAY_OF_WEEK} ]];then
    full_backup
    exit \$?
fi
 
if [[ \${FULL_BAK_DAY_OF_WEEK} -ne \${CURRENT_DAY_OF_WEEK} ]];then
   incr_backup
   exit \$?
fi
 
 
 
EOF
 
[ ! -f /data/mysql_backup/backup.log ] && touch /data/mysql_backup/backup.log
 
echo "35 0 * * 6 /data/mysql_backup/mysql_xtrabackup.sh>>/data/mysql_backup/backup.log 2>&1 &" >> /var/spool/cron/root
 
echo -e "\e[31m #7. 备份设置完成，每周日全备，每日增备 \e[0m"
}
 
 
#接入zabbix监控
install_zabbix()
{
 
zabbix_agent=`rpm -qa | grep zabbix-agent`
zabbix_conf_dir=`find / -name zabbix_agentd.conf`
if [ -z "${zabbix_agent}" ]; then
    cd  $installPackage
    rpm -ivh zabbix-agent-3.4.15-1.el7.x86_64.rpm
fi
 
if [ -n "${zabbix_conf_dir}" ]; then
zabbix_key=`grep "UserParameter=mysql.statsvp"  ${zabbix_conf_dir}`
fi
 
 
if [ -n "${zabbix_conf_dir}" ]&&[ -z "${zabbix_key}" ] ; then
   sed -i '$a UserParameter=mysql.statsvp[*],/etc/zabbix/scripts/chk_mysql.sh $1' ${zabbix_conf_dir}
 
fi
 
mkdir -p /etc/zabbix/scripts
cat >/etc/zabbix/scripts/chk_mysql.sh<<EOF
 
#!/bin/bash
# -------------------------------------------------------------------------------
# FileName: check_mysql.sh
# Revision: 1.0
# -------------------------------------------------------------------------------
# Copyright:
# License: GPL
 
# 用户名
MYSQL_USER='root'
 
# 密码
MYSQL_PWD='123456'
 
# 主机地址/IP
MYSQL_HOST='localhost'
 
# 端口
MYSQL_PORT='3306'
 
# 数据连接
MYSQL_CONN="/usr/bin/mysqladmin -u\${MYSQL_USER} -p\${MYSQL_PWD} -h\${MYSQL_HOST} -P\${MYSQL_PORT}"
 
# 参数是否正确
if [ \$# -ne "1" ];then
echo "arg error!"
fi
 
# 获取数据
case \$1 in
Uptime)
result=\`\${MYSQL_CONN} status 2>/dev/null |cut -f2 -d":"|cut -f1 -d"T"\`
echo \$result
;;
Com_update)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null  |grep -w "Com_update"|cut -d"|" -f3\`
echo \$result
;;
Slow_queries)
result=\`\${MYSQL_CONN} status 2>/dev/null |cut -f5 -d":"|cut -f1 -d"O"\`
echo \$result
;;
Com_select)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_select"|cut -d"|" -f3\`
echo \$result
;;
Com_rollback)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_rollback"|cut -d"|" -f3\`
echo \$result
;;
Questions)
result=\`\${MYSQL_CONN} status 2>/dev/null |cut -f4 -d":"|cut -f1 -d"S"\`
echo \$result
;;
Com_insert)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_insert"|cut -d"|" -f3\`
echo \$result
;;
Com_delete)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_delete"|cut -d"|" -f3\`
echo \$result
;;
Com_commit)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_commit"|cut -d"|" -f3\`
echo \$result
;;
Bytes_sent)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Bytes_sent" |cut -d"|" -f3\`
echo \$result
;;
Bytes_received)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null  |grep -w "Bytes_received" |cut -d"|" -f3\`
echo \$result
;;
Com_begin)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null  |grep -w "Com_begin"|cut -d"|" -f3\`
echo \$result
;;
Threads_connected)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null  |grep -w "Threads_connected"|cut -d"|" -f3\`
echo \$result
;;
Threads_running)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null  |grep -w "Threads_running"|cut -d"|" -f3\`
echo \$result
;;
Innodb_row_lock_current_waits)
result=\`\${MYSQL_CONN} extended-status 2>/dev/null  |grep -w "Innodb_row_lock_current_waits"|cut -d"|" -f3\`
echo \$result
;;
Exec_long_time)
result=\`mysql -u\${MYSQL_USER} -p\${MYSQL_PWD} -h\${MYSQL_HOST} -P\${MYSQL_PORT} -e "SELECT count(*) FROM information_schema.processlist WHERE COMMAND<>'Sleep' AND TIME >60 AND info IS NOT NULL AND user NOT IN ('root','event_scheduler','system user ','master','repl','repl1') " 2> /dev/null |sed 1d \`
echo \$result
;;
 
 
*)
echo "Usage:\$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin|Threads_connected|Threads_running|Innodb_row_lock_current_waits|Aborted_connects|Exec_long_time)"
;;
esac
   
EOF
   
chmod 777 /etc/zabbix/scripts/chk_mysql.sh
systemctl restart zabbix-agent.service
echo -e "\e[31m #8. MySQL已接入zabbix脚本已创建 \e[0m"	
	
}
 
install_mysqld_exporter()
{
if [ -e /usr/local/mysqld_exporter/.my.cnf ]; 
then echo "mysqld_exporter已存在"
else  
cd $installPackage
tar -zxvf mysqld_exporter-0.14.0.linux-amd64.tar.gz -C /usr/local/
ln -s /usr/local/mysqld_exporter-0.14.0.linux-amd64 /usr/local/mysqld_exporter
cat >/usr/local/mysqld_exporter/.my.cnf<<EOF
[client]
user=root
password=123456
EOF
nohup /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf &
 
fi
 
echo -e "\e[31m #9.prometheus mysqld_exporter已安装完成 \e[0m"	
 
}
 
#重置密码
ResetPwd()
{
sleep 5s
 
#获取root用户密码
 
OLD_PASSWORD=`grep 'temporary password' $installPath/logs/mysqld.log | awk '{print $NF}'`
 
if [ -n "${OLD_PASSWORD}" ]; then
     echo -e "get password succefull"
     #修改新密码
echo "reset password"
mysql -uroot -p$OLD_PASSWORD --connect-expired-password <<EOF
set password=password("123456");
FLUSH PRIVILEGES;
EOF
 
 
echo -e "\e[31m #10. 已重置数据库密码。登录方式如下: \e[0m"
echo -e "\e[31m mysql -uroot -p123456 \e[0m"
else     
     echo -e "get password error"
fi
 
 
 
 
}
 
 
 
 
main()  
{  
###0.校验是否为ROOT用户
CheckRoot  
 
 
###1.优化文件最大打开数
DependFile
 
 
###2.解压tar包,初始化环境
DecompressionTar
 
 
 
###3.创建mysql 数据目录
createMysqlFolder
 
 
###4.安装mysql
install_mysql
 
###5.创建my.cnf
MakeMyCnf
 
 
###6.初始化数据库
InitDataBase
 
###7.设置mysql备份
backup_mysql
 
 
###8.配置zabbix监控
install_zabbix
 
 
###9.配置prometheus-mysqld_exporter
install_mysqld_exporter
 
 
###10.重置密码
ResetPwd
 
 
}  
 
 
main