mysql 5.7一键部署脚本，支持服务器多实例

背景

最近一个项目上，一台linux需要部署mysql 5.7 多实例，为了加快部署的速度，写了一个脚本。

解决方案

#!/bin/bash
#download mysql package
package="mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz"
echo -n "please input start_port:"
read start_port
echo -n "please input end_port:"
read end_port
pts=$(seq ${start_port} ${end_port})
echo -n "please input innodb_pool_size,eg 512M,2G:"
read pool_size
innodb_pool_size=${pool_size} 
echo -n "please input data_base dir:"
read base_dir
data_root=${base_dir}
#test network connecting
ping -c 3 www.baidu.com
if test $? -eq 0;then
   mkdir software && cd software
#download package
   wget https://downloads.mysql.com/archives/get/p/23/file/$package
fi
#create mysql user and group
if test `cat /etc/passwd |grep mysql`;then
   echo "mysql user already exist"
else
useradd -M -s /sbin/nologin mysql
fi
  
#unzip mysql package
tar -xzvf  $package;

package_dir=$(basename ${package} .tar.gz)

for pt in $pts;
do
 mkdir -p ${data_root}/$pt
 cp -rpf ${package_dir}/*  ${data_root}/$pt && mkdir -p ${data_root}/$pt/data
 chown -R mysql:mysql ${data_root}/$pt
#config mysql configfile
 cat  >/etc/my$pt.cnf<<EOF
[mysqld]
####: for global
user                                =mysql                          #    mysql
basedir                             =${data_root}/$pt/              #    /usr/local/mysql/
datadir                             =${data_root}/$pt/data/     #    /usr/local/mysql/data
server_id                           =$pt                           #    0
port                                =$pt                           #    3306
character_set_server                =utf8mb4                           #    latin1
log_bin_trust_function_creators     =ON                             #   0
max_prepared_stmt_count             =1048576                        #   
log_timestamps                      =system                         #    utc
socket                              =${data_root}/$pt/data/mysql$pt.sock                 #    /tmp/mysql.sock
read_only                           =OFF                            #    off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #    1
auto_increment_offset               =1                              #    1
lower_case_table_names              =1                              #    0
secure_file_priv                    =                               #    null
open_files_limit                    =65536                          #   1024
max_connections                     =512                            #   151
thread_cache_size                   =64                             #   9
table_open_cache                    =4000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16

####: for binlog
binlog_format                       =ROW                            #    row
log_bin                             =mysql-bin                      #    off
binlog_rows_query_log_events        =ON                             #    off
log_slave_updates                   =ON                             #    off
expire_logs_days                    =7                              #    0
binlog_cache_size                   =96k                            #    65536(64k)
binlog_checksum                     =none                           #    CRC32
sync_binlog                         =1                              #    1
slave-preserve-commit-order         =ON                             #    

####: for error-log
log_error                           =err.log                        #    /usr/local/mysql/data/localhost.localdomain.err

####: for general-log
general_log                         =OFF                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =ON                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
log_queries_not_using_indexes       =OFF                            #    off
long_query_time                     =2.0                            #    10.000000

####: for gtid
gtid_executed_compression_period    =1000                           #    1000
gtid_mode                           =ON                             #    off
enforce_gtid_consistency            =ON                             #    off


####: for replication
skip_slave_start                    =0                              #    
master_info_repository              =table                          #    file
relay_log_info_repository           =table                          #    file
slave_parallel_type                 =logical_clock                  #    database | LOGICAL_CLOCK
slave_parallel_workers              =4                              #    0
rpl_semi_sync_master_enabled        =1                              #    0
rpl_semi_sync_slave_enabled         =1                              #    0
rpl_semi_sync_master_timeout        =1000                           #    1000(1 second)
plugin_load_add                     =semisync_master.so             #
plugin_load_add                     =semisync_slave.so              #
binlog_group_commit_sync_delay      =4000                            #    0      4000(4ms) 
binlog_group_commit_sync_no_delay_count = 10                        #    0
binlog_transaction_dependency_tracking  = WRITESET                  #    COMMIT_ORDER | WRITESET       
transaction_write_set_extraction        = XXHASH64


####: for innodb
default_storage_engine                          =innodb                     #    innodb
default_tmp_storage_engine                      =innodb                     #    innodb
innodb_data_file_path                           =ibdata1:256M;ibdata2:256M:autoextend    #    ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:64M:autoextend      #    ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #    ib_buffer_pool
innodb_log_group_home_dir                       =./                         #    ./
innodb_log_files_in_group                       =8                          #    2
innodb_log_file_size                            =128M                       #    50331648(48M)
innodb_file_per_table                           =ON                         #    on
innodb_online_alter_log_max_size                =128M                       #   134217728(128M)
innodb_open_files                               =64000                      #   2000
innodb_page_size                                =16k                        #    16384(16k)
innodb_thread_concurrency                       =0                          #    0
innodb_read_io_threads                          =4                          #    4
innodb_write_io_threads                         =4                          #    4
innodb_purge_threads                            =4                          #    4(garbage collection)
innodb_page_cleaners                            =4                          #   4(flush lru list)
innodb_print_all_deadlocks                      =ON                         #    off
innodb_deadlock_detect                          =ON                         #    on
innodb_lock_wait_timeout                        =50                         #    50
innodb_spin_wait_delay                          =6                          #    6
innodb_autoinc_lock_mode                        =2                          #    1
innodb_flush_sync                               =OFF                        #   on
innodb_io_capacity                              =4000                       #   200
innodb_io_capacity_max                          =20000                      #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =ON                         #   on
innodb_stats_persistent                         =ON                         #    on
innodb_stats_persistent_sample_pages            =20                         #    20
innodb_buffer_pool_instances                    =10                          #   1
innodb_adaptive_hash_index                      =ON                         #    on
innodb_change_buffering                         =all                        #    all
innodb_change_buffer_max_size                   =25                         #    25
innodb_flush_neighbors                          =0                          #    1
innodb_flush_method                             =O_DIRECT                   #    
innodb_doublewrite                              =ON                         #    on
innodb_log_buffer_size                          =256M                       #    16777216(16M)
innodb_flush_log_at_timeout                     =1                          #    1
innodb_flush_log_at_trx_commit                  =1                          #    1
innodb_buffer_pool_size                         =${innodb_pool_size}                         #   128M
autocommit                                      =ON                          #    1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =off                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =50                         #    25 
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON





####  for performance_schema
performance_schema                                                      =on    #    on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'


EOF

##config mysql startup script
cat<<EOF > /etc/init.d/mysql$pt  
port=$pt
mysql_user="mysql"
cmdpath="${data_root}/${pt}/bin"
mysql_sock="${data_root}/${pt}/data/mysql$pt.sock"
#pidname="$(hostname)"
mysqld_pid_file_path="${data_root}/${pt}/data/$(hostname).pid"

start(){
    if [ ! -e "\$mysql_sock" ];then
        printf "Starting MySQL...\n"
        \${cmdpath}/mysqld --defaults-file=/etc/my${pt}.cnf --user=\${mysql_user} 2>/dev/null &
        sleep 3
    else
    printf "MySQL is running...\n"
    exit 1
    fi
}
stop(){
    if [ ! -e "\$mysql_sock" ];then
    printf "MySQL is stopped...\n"
    exit 1
    else
    printf "Stoping MySQL...\n"
    mysqld_pid=\`cat "\$mysqld_pid_file_path"\`
       if (kill -0 \$mysqld_pid 2>/dev/null)
         then
         kill \$mysqld_pid
         sleep 2
       else
         rm \$mysqld_pid_file_path
     
        fi
    fi
}
restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}
case "\$1" in
start)
    start
;;
stop)
    stop
;;
restart)
    restart
;;
*)
    printf "Usage: /etc/init.d/mysql${pt} {start|stop|restart}\n"
esac

EOF
#initial mysqlserver
${data_root}/$pt/bin/mysqld --defaults-file=/etc/my$pt.cnf --initialize --basedir=${data_root}/$pt

#startup mysqlserver
 chmod 755 /etc/init.d/mysql${pt}
/etc/init.d/mysql${pt} start
if [ $? -eq 0  ];then
   sleep 20
   #modify mysqlserver password
   export MYSQL_PWD=$(cat ${data_root}/$pt/data/err.log  |grep password  |awk -F 'root@localhost:' '{print $NF}' |sed 's/^[][ ]*//g')
   ${data_root}/$pt/bin/mysql --connect-expired-password -hlocalhost -uroot  -P${pt} -S ${data_root}/${pt}/data/mysql$pt.sock -e "alter user root@localhost identified by 'Rootasdf2023';"
    if [ $? -eq 0 ];then
        echo "MySQL INSTANCE $pt is install sucessed!"
    else
        echo "MySQL INSTANCE $pt is install maybe failed!please you check"
    fi  
else
    break
fi
done

解决方案说明

1、生成的mysql的实例默认的root的密码为：Rootasdf2023

2、生成的mysql实例配置文件的位置：/etc/my{port}.cnf

3、生成的mysql实例配置启动脚本的位置：/etc/init.d/mysql{port}