#!/bin/bash

SRC_DIR=/usr/local
MYSQL_FILE=mysql-5.7.23-linux-glibc2.12-x86_64
TAR=.tar.gz
NEIBU_URL=http://58.220.50.162:7777/download/
DOWNLOAD_URL=https://dev.mysql.com/get/Downloads/MySQL-5.7/
MYSQL_BASE_DIR=/home/mysql
CPUS=`lscpu | awk '/^CPU\(s\)/{print $2}'`
PASSWORD=cqt!010@Mysql
PORT=3300

color () {
    RES_COL=60
    MOVE_TO_COL="echo -en \\033[${RES_COL}G"
    SETCOLOR_SUCCESS="echo -en \\033[1;32m"
    SETCOLOR_FAILURE="echo -en \\033[1;31m"
    SETCOLOR_WARNING="echo -en \\033[1;33m"
    SETCOLOR_NORMAL="echo -en \E[0m"
    echo -n "$2" && $MOVE_TO_COL
    echo -n "["
    if [ $1 = "success" -o $1 = "0" ] ;then ${SETCOLOR_SUCCESS}
        echo -n $"  OK  "
    elif [ $1 = "failure" -o $1 = "1" ] ;then ${SETCOLOR_FAILURE}
        echo -n $"FAILED"
    else
        ${SETCOLOR_WARNING}
        echo -n $"WARNING"
    fi
    ${SETCOLOR_NORMAL}
    echo -n "]"
    echo
}

check () {
	[ -e $SRC_DIR/mysql ]  && { color 1 "mysql 已安装,退出" ; exit; }
	if [ $UID -ne 0 ]; then
		color 1 "当前用户不是root,请切换至root用户下执行!"
		exit;
	fi
	yum -y install autoconf libaio numactl-libs ansible-core
	cd $SRC_DIR
	if [ -e $MYSQL_FILE$TAR ];then
		color 0 "mysql5.7源码包已准备好，继续安装"
	else 
		color 1 "mysql5.7源码包不存在，现在开始下载" 
		wget $NEIBU_URL$MYSQL_FILE$TAR
		[ $? -ne 0 ]  && { color 1 "下载 ${MYSQL_FILE}${TAR}文件失败" ; exit ; }
	fi

}

get_local_ip() {
    local main_interface ip_addr
    main_interface=$(ip route show default 2>/dev/null | awk '/default/ {print $5}' | head -n1)
    if [ -z "$main_interface" ]; then return 1; fi
    ip_addr=$(ip addr show "$main_interface" 2>/dev/null | grep -oP 'inet \K[\d.]+' | head -n1)
    if [ -z "$ip_addr" ]; then return 1; fi
    SERVER_ID=$(echo "$ip_addr" | awk -F"." '{print $NF}')
    if [ -z "$SERVER_ID" ]; then return 1; fi
    color 0 "根据本机IPserver_id自动识别为 $SERVER_ID"
    return 0
}

install_mysql () {
	color 0 "开始安装mysql5.7......"
	cd $SRC_DIR
	tar xf $MYSQL_FILE$TAR 
	ln -s $MYSQL_FILE mysql
	id mysql &> /dev/null || { color 1 "mysql 用户不存在" ; useradd -s /sbin/nologin mysql ; }
	id mysql &> /dev/null && color 0 "mysql 用户创建成功" || { color 1 "mysql 创建失败 退出!" ; exit ; }
	chown -R  mysql.mysql $SRC_DIR/mysql

	echo "PATH=$SRC_DIR/mysql/bin/:\$PATH" > /etc/profile.d/mysql.sh
	source /etc/profile.d/mysql.sh
	ln -s $SRC_DIR/mysql/bin/* /usr/bin/
	cd $MYSQL_BASE_DIR
	mkdir -p data log tmp share lib
	chown -R mysql.mysql $MYSQL_BASE_DIR
	cp $SRC_DIR/mysql/share/english/errmsg.sys $MYSQL_BASE_DIR/share/errmsg.sys
	cp -r $SRC_DIR/mysql/lib/* $MYSQL_BASE_DIR/lib
	#mysqld --initialize-insecure  --user=mysql --basedir=$MYSQL_BASE_DIR --datadir=$MYSQL_BASE_DIR/data && color 0 "mysql 初始化完成！" || { color 1 "mysql 初始化失败 退出!" ; exit ; }
}

start_mysql () {
        # 定义颜色变量，方便统一修改
        local YELLOW='\e[1;33m'
        local GREEN='\e[1;32m'
        local RESET='\e[0m'

	color 0 "启动mysqld"	
cat > /etc/my.cnf << EOF
[client]                                                       
port            = $PORT                                         
socket          = $MYSQL_BASE_DIR/mysql.sock                              
  
[mysql]                                                        
no-auto-rehash                                                 
max_allowed_packet = 128M                                      
prompt = '(product)\u@\h [\d]> '
default-character-set=utf8
local-infile=1
                                           
[mysqld]               
local-infile=1
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 
port            = 3300                                        
user            = mysql
basedir         = $MYSQL_BASE_DIR                             
datadir         = $MYSQL_BASE_DIR/data               
tmpdir          = $MYSQL_BASE_DIR/tmp                
socket          = $MYSQL_BASE_DIR/mysql.sock   

symbolic-links = 0                                             
max_allowed_packet = 32M  
skip_name_resolve = 1                                        
auto-increment-offset=1
auto-increment-increment=2
                                                       
interactive_timeout = 600                                      
wait_timeout = 600                                             
connect_timeout = 20                                           
slave-net-timeout=30                                             
server-id       = $SERVER_ID
character-set-server = utf8                                    
lower_case_table_names=1                                                                                     
wait_timeout=100                                               
interactive_timeout=100                                         
open_files_limit = 65535                                       
back_log = 1024                                                 
max_connections=1100                                           
max_user_connections=1000                                      
max_connect_errors = 1000000                                   
external-locking = FALSE                                          
skip-name-resolve                                              
event_scheduler=1                                              
log_timestamps = system                                                                                     
log-output="FILE"                                              
slow_query_log = 1                                             
slow-query-log-file = $MYSQL_BASE_DIR/log/slow.log                                 
log-error   = $MYSQL_BASE_DIR/log/error.log                                        
log_error_verbosity = 2                                        
pid-file = mysql.pid                                           
long_query_time=1  
slow_query_log = on                                            
log-slow-slave-statements = 1                 
explicit_defaults_for_timestamp = 1                            
default-time-zone = '+8:00'
log-slow-admin-statements = 1   
binlog_format = row
log-bin     = $MYSQL_BASE_DIR/log/mysql-bin         
binlog_cache_size = 1G                                         
max_binlog_cache_size = 2G                                     
max_binlog_size = 1G                                           
expire-logs-days = 7                                           
log_bin_trust_function_creators = 1
gtid_mode = on
enforce_gtid_consistency = 1                                 
slave-skip-errors=1062,1050,1051,1146,1145,1061,1032,1304,1396,1537,1539
max_relay_log_size = 1G                                        
relay_log_purge = 1                                            
relay_log_recovery = 1
master_verify_checksum = 1                                     
master_info_repository = TABLE
relay_log_info_repository = TABLE                          
slave_sql_verify_checksum = 1                                  
slave_allow_batching = 1                                       
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
table_open_cache = 2048                                        
table_definition_cache = 2048 
table_open_cache_instances = 64                                 
sort_buffer_size = 128M
join_buffer_size = 128M                                         
thread_cache_size = 768                                        
query_cache_size = 0                                           
query_cache_type = 0                                           
query_cache_limit = 256K                                       
query_cache_min_res_unit = 512                                 
thread_stack = 512K                                            
key_buffer_size = 32M                                          
read_buffer_size = 8M                                          
read_rnd_buffer_size = 4M                                      
bulk_insert_buffer_size = 64M                                  
tmp_table_size = 96M                                           
max_heap_table_size = 96M                                                                 
myisam_sort_buffer_size = 128M                                 
myisam_max_sort_file_size = 10G                                
myisam_repair_threads = 1  
lock_wait_timeout = 3600                                    
   
                                                        
innodb_data_home_dir = $MYSQL_BASE_DIR/data          
innodb_log_group_home_dir = $MYSQL_BASE_DIR/log
innodb_data_file_path = ibdata1:10M:autoextend                  
innodb_buffer_pool_size = 8G                                   
                            
innodb_spin_wait_delay = 30                                    
sync_binlog=1000
innodb_flush_log_at_trx_commit =2
innodb_log_buffer_size = 128M                                   
innodb_log_file_size = 256M                                    
innodb_log_files_in_group = 3                                  
innodb_max_dirty_pages_pct = 50                                
innodb_lock_wait_timeout = 10                                 
innodb_file_per_table = 1                                      
innodb_rollback_on_timeout= 1  
innodb_print_all_deadlocks = 1                                
innodb_status_file = 1                                         
innodb_read_io_threads = 4                                    
innodb_write_io_threads = 4                                   
innodb_io_capacity = 300
  
innodb_flush_neighbors = 1                              
innodb_adaptive_flushing = 1                                   
innodb_flush_method = O_DIRECT       
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32                          
transaction_isolation = READ-COMMITTED 
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1                        
  
innodb_sync_spin_loops = 100                                    
                                       
innodb_thread_concurrency = 0                                  
innodb_thread_sleep_delay = 500                                
                                
innodb_concurrency_tickets = 1000                              
                               
innodb_purge_threads=4
innodb_page_cleaners = 4
innodb_open_files = 65535                                         
innodb_purge_batch_size = 32                                   
innodb_old_blocks_pct=75                                       
innodb_change_buffering=all   
innodb_online_alter_log_max_size = 4G                                 
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0                   
  
innodb_max_undo_log_size=4G    


innodb_status_output = 0
innodb_status_output_locks = 0                        
  

performance_schema = 1
performance_schema_instrument = '%=on'


innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
      
plugin_load = "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
rpl_semi_sync_master_wait_for_slave_count=1      


binlog_group_commit_sync_delay=10
binlog_group_commit_sync_no_delay_count=20 
                   
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
  
                                     
  
[mysqldump]                                                    
quick                                                          
max_allowed_packet = 128M                                      
                                 
[mysqld_safe]                                                  
                          
[mysqld_multi]                                                 
mysqld = $SRC_DIR/mysql/bin/mysqld_safe                      
mysqladmin = $SRC_DIR/mysql/bin/mysqladmin                  
log = $MYSQL_BASE_DIR/data/mysqld_multi.log
EOF


cat > /lib/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=$SRC_DIR/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
	#mysqld --initialize  --user=mysql --basedir=$MYSQL_BASE_DIR --datadir=$MYSQL_BASE_DIR/data && color 0 "mysql 初始化完成！" || { color 1 "mysql 初始化失败 退出!" ; exit ; }
	mysqld --initialize-insecure  --user=mysql --basedir=$MYSQL_BASE_DIR --datadir=$MYSQL_BASE_DIR/data && color 0 "mysql 初始化完成！" || { color 1 "mysql 初始化失败 退出!" ; exit ; }
	systemctl daemon-reload
	systemctl enable --now mysqld
	systemctl is-active mysqld && color 0 "mysql 启动成功! 继续创建密码,创建集群!" || { color 1 "mysql 启动失败" ; exit; }
	sudo ln -sf /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5
	sudo ln -sf /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5
	#color 0 "修改mysql密码为$PASSWORD"
	#MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' $MYSQL_BASE_DIR/log/error.log`
	#mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $PASSWORD
    #mysql -uroot -pMYSQL_OLDPASSWORD -e "grant all privileges on *.* to 'root'@'%' identified by 'Cqt@1234';flush privileges"
	#mysql -uroot -p$PASSWORD -e "SELECT VERSION();"
	echo -e "${RED}注意：${RESET}${YELLOW}需要手动修改/etc/my.cnf 中修改 auto-increment-offset参数。${RESET}"
	echo -e "${RED}注意：${RESET}${YELLOW}主节点设置为1,从节点设置为2${RESET}"
}

check
get_local_ip
install_mysql
start_mysql