背景
最近一个项目上，一台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}