tools_shell
/
mysql_bin_ctl.sh

#!/usr/bin/env bash
set -e
# ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
# Function :CentOS7.X 二进制安装mysql
# Platform :RedHatEL7.x Based Platform
# Version  :0.01
# Date     :2024-03-22
# Author   :mugoLH
# Contact  :hxsaj@126.com
# Company  :
# explain  :命令模式
# 			  -c ：集群副本数
# 			  -d ：安装路径
# 			  -f ：二进制文件
# 			  -l ：环境查看
# 			  -m ：指定创建用户的密码
# 			  -p ：指定端口
# 			  -r ：URL地址
# 			  -t ：自动压测
# 			  -u ：创建用户
# 			  -v ：安装的版本


# ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

#  脚本引用 Import the script
# ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

#  变量列表 List of common variables
# ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

#  函数列表 Function list
# ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
info_p(){  printf "\e[1;32;40m%-12s %-1s\n\e[0m" "${1}" "${2}"  >&1 ;}
tips_p(){  printf "\e[1;36;40m%-5s %-1s\n\e[0m" "${1}" "${2}"  >&1 ;}
error_p(){ printf "\e[1;35;40m%-12s %-1s\n\e[0m" "${1}" "${2}"  >&1 ;}


# Mysql操控台
function choose_opera(){
	echo -e "\n操作内容："
	tips_p 1） Mysql_环境查看
	tips_p 2） Mysql_环境安装
	tips_p 3） Mysql_账号管理
	tips_p 4） Msyql_备份恢复
	tips_p 5） Mysql_自动压测
	tips_p q） 退出
	read -p "选择（输入序号）：" ch_op
	case C${ch_op} in
		C1 ) printf_app;  choose_opera  ;;
		C2 ) create_mysql;choose_opera  ;;
		C3 ) delete_app;  choose_opera  ;;
		C4 ) create_db;   choose_opera  ;;
		C5 ) create_user; choose_opera  ;;
		C* ) choose_opera  		        ;;
		Cq ) exit		   	            ;;
	esac
}


# mysql 二进制安装
function mysql_install(){
    install_dir=${1}
    server_port=${2}
    BintarFile=${3}

    if [[ $# -eq 3 ]];then
        # 创建安装目录
        if [[ ! -d ${install_dir} ]];then
            info_p "[ 创建目录 ]:" "${install_dir}" && mkdir -p ${install_dir}
        elif [[ $(ls -A ${install_dir}) ]]; then
        	info_p "[ ERROR    ]:" "安装目录不为空！" && exit 1001
        fi

        # 解压二进制到指定目录
        info_p "[ 解压文件 ]:" "${Bin_tar_file}"

        # 判断文件压缩类型并解压缩
        suffix=$(ls ${Bin_tar_file} | awk -F '.' '/tar/{print $NF}')
        if [ ${suffix} == "xz" ];then
            tar -Jxf ${Bin_tar_file} -C ${install_dir}/
            Bin_Basename=$(tar -Jtf ${Bin_tar_file} | head -5 | awk -F "/" '{print$1}' |sort -nr |uniq)
        elif [ ${suffix} == "gz" ]; then
            tar -zxf ${Bin_tar_file} -C ${install_dir}/
            Bin_Basename=$(tar -ztf ${Bin_tar_file} | head -5 | awk -F "/" '{print$1}' |sort -nr |uniq)
        fi

        # 修改目录名字，便于使用
        Base_Mysql=$(echo ${Bin_Basename} |awk -F "-" '{print$1"-"$2}')
        mv ${install_dir}/${Bin_Basename} ${install_dir}/${Base_Mysql}_${server_port}

        # 创建配置目录和数据目录
        mkdir -p ${install_dir}/${Base_Mysql}_${server_port}/{data,conf}

        # 生成配置文件
        mysql_create_conf ${install_dir} {server_port} ${Base_Mysql} ${Mysql_Ver}

        # 创建运行账号
        if ! id mysql >/dev/null 2>&1;then
            useradd mysql
        fi

        # 授权相关目录权限
        chown -R mysql.mysql ${install_dir}

        # 写入环境变量
        echo -e "[ 环境变量 ]：/etc/bashrc"
        if [ $(grep -w "${install_dir}/${Base_Mysql}_${server_port}/bin" /etc/bashrc | wc -l) -eq 0 ];then
            echo -e "export PATH=${install_dir}/${Base_Mysql}_${server_port}/bin:\$PATH" >> /etc/bashrc
        fi

        # 环境变量生效
        source /etc/bashrc

        # 软连接sock文件
        [ -L /tmp/mysql_${server_port}.sock ] && rm -f /tmp/mysql_${server_port}.sock
        ln -s ${install_dir}/${Base_Mysql}_${server_port}/mysql.sock /tmp/mysql_${server_port}.sock

        # 初始化 mysql
        echo -e "[ 初始服务 ]: ${install_dir}/${Base_Mysql}_${server_port}/bin/mysqld --defaults-file=${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf --user=mysql --initialize-insecure > /dev/null 2>&1 &"

        ${install_dir}/${Base_Mysql}_${server_port}/bin/mysqld --defaults-file=${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf --user=mysql --initialize-insecure > /dev/null 2>&1 &

        # 启动mysql
        sleep 10
        echo -e "[ 启动mysql]: ${install_dir}/${Base_Mysql}_${server_port}/bin/mysqld_safe --defaults-file=${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf --user=mysql &"

        ${install_dir}/${Base_Mysql}_${server_port}/bin/mysqld_safe --defaults-file=${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf --user=mysql > /dev/null 2>&1 &

        # 免密登录
        sleep 5
        echo -e "[ 部署完成 ]: 请执行: ${install_dir}/${Base_Mysql}_${server_port}/bin/mysql -uroot -p --socket=${install_dir}/${Base_Mysql}_${server_port}/mysql.sock  登录数据库修改密码(初次登录无密码)！"
        echo -e "[ 使用建议 ]：
        1. 修改root密码   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '594hxs';FLUSH PRIVILEGES;
        2. 远程root登录   CREATE USER 'root'@'%' IDENTIFIED BY '594hxs';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';FLUSH PRIVILEGES;
        3. 创建数据库     CREATE DATABASE db0 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;FLUSH PRIVILEGES;
        4. 创建用户       CREATE USER 'user0'@'%' IDENTIFIED BY '594hxs';FLUSH PRIVILEGES;
        5. 用户授权       GRANT ALL PRIVILEGES ON db0.* TO 'user0'@'%';FLUSH PRIVILEGES;"

    fi
}

if [[ $# -eq 3 ]];then
    install_dir=${1}
    server_port=${2}
    BintarFile_or_DownloadURL=${3}
    mysql_install ${install_dir} ${server_port} "${BintarFile_or_DownloadURL}"
else
    echo -e "[ 使用方法 ]：$0 \"[安装位置{字符串}]\" \"端口\" \"[下载二进制的URL地址 | 二进制压缩包绝对路径]\""
fi


# mysql配置文件
function mysql_create_conf(){
	install_dir=${1}
	server_port=${2}
	Base_Mysql=${3}
	Mysql_Ver=${4}

	echo -e "[ 生成配置 ]：${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf "
	if [ $(echo ${Mysql_Ver} | awk -F "[-|.]" '{print$2}') -eq 5 ];then
		cat > ${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf << EOF
client]
default-character-set  = utf8mb4

[mysql]
port                   = ${server_port}
socket                 = ${install_dir}/${Base_Mysql}_${server_port}/mysql.sock
default-character-set  = utf8mb4

[mysqld]
port                   = ${server_port}
default_storage_engine = InnoDB
basedir                = ${install_dir}/${Base_Mysql}_${server_port}
datadir                = ${install_dir}/${Base_Mysql}_${server_port}/data
socket                 = ${install_dir}/${Base_Mysql}_${server_port}/mysql.sock
character-set-client-handshake = FALSE
character-set-server   = utf8mb4
collation-server       = utf8mb4_unicode_ci
init_connect           = 'SET NAMES utf8mb4'
max_connections        = 2000
max_allowed_packet     = 128M
innodb_file_per_table  = 1
tmp_table_size         = 134217728
max_heap_table_size    = 134217728
lower_case_table_names = 1

log-bin                = mysql-bin
max_binlog_size        = 1024M
expire_logs_days       = 1
log_slave_updates      = 1
server-id              = 1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

EOF
	elif [ $(echo ${Mysql_Ver} | awk -F "[-|.]" '{print$2}') -eq 8 ];then
cat > ${install_dir}/${Base_Mysql}_${server_port}/conf/my.cnf << EOF
[mysql]
# 默认字符集
default-character-set=utf8mb4
[client]
#修改默认端口号
port       = ${server_port}
socket     = ${install_dir}/${Base_Mysql}_${server_port}/mysql.sock

[mysqld]
#修改默认端口号
port       = ${server_port}
server-id  = ${server_port}
user       = mysql
socket     = ${install_dir}/${Base_Mysql}_${server_port}/mysql.sock
# 安装目录
basedir    = ${install_dir}/${Base_Mysql}_${server_port}
# 数据存放目录
datadir    = ${install_dir}/${Base_Mysql}_${server_port}/data
log-bin    = ${install_dir}/${Base_Mysql}_${server_port}/data/mysql-bin
innodb_data_home_dir      =${install_dir}/${Base_Mysql}_${server_port}/data
innodb_log_group_home_dir =${install_dir}/${Base_Mysql}_${server_port}/data
#日志及进程数据的存放目录
log-error =${install_dir}/${Base_Mysql}_${server_port}/mysql.log
pid-file  =${install_dir}/${Base_Mysql}_${server_port}/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
#不区分大小写
lower_case_table_names=1
autocommit =1

 ##################以上要修改的########################
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128

#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

binlog_format=mixed

binlog_expire_logs_seconds =864000

# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
EOF

}