前言：
今天咱们对“encryptmysql”大致比较看重，看官们都想要分析一些“encryptmysql”的相关资讯。那么小编在网络上汇集了一些关于“encryptmysql””的相关资讯，希望咱们能喜欢，大家快快来学习一下吧！背景
最近一个项目上，客户需要把阿里云的rds for mysql数据库同步至线下，用作数据的灾备，需要在线下的服务器上部署mysql 8.0多实例，为了加快部署的速度，写了一个脚本。
解决方案
#!/bin/bash#download mysql packagepackage="mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz"echo -n "please input start_port:"read start_portecho -n "please input end_port:"read end_portpts=$(seq ${start_port} ${end_port})echo -n "please input innodb_pool_size,eg 512M,2G:"read pool_sizeinnodb_pool_size=${pool_size} echo -n "please input data_base dir:"read base_dirdata_root=${base_dir}#test network connectingping -c 3 test $? -eq 0;then mkdir software && cd software#download package wget mysql user and groupif test `cat /etc/passwd |grep mysql`;then echo "mysql user already exist"elseuseradd -M -s /sbin/nologin mysqlfi #unzip mysql packagexz -dk $package;tar -xvf $(basename ${package} .xz)package_dir=$(basename ${package} .tar.xz)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 [mysql]auto-rehashsocket =${data_root}/$pt/data/mysql$pt.sock # /tmp/mysql.sock[mysqld]####: for globaluser =mysqlserver_id =$pt # 1 port =$ptmysqlx =0basedir =${data_root}/$pt/ # /usr/local/mysql/datadir =${data_root}/$pt/data # /usr/local/mysql/data/max_prepared_stmt_count =1048576 # 16382open_files_limit =65536 # 65536socket =${data_root}/$pt/data/mysql$pt.sock # /tmp/mysql.sockskip_name_resolve =1 # 0super_read_only =OFF # OFFsql_require_primary_key =ON # OFFcte_max_recursion_depth =1000 # 1000log_timestamps =system # UTClower_case_table_names =1 # 0auto_increment_increment =1 # 1auto_increment_offset =1 # 1lock_wait_timeout =31536000 # ! for metadata lock waitevent_scheduler =OFF # ONauto_generate_certs =ON # ONbig_tables =OFF # OFF ! for TempTable storage engine join_buffer_size =256k # 0.25Mactivate_all_roles_on_login =ON # OFFend_markers_in_json =OFF # OFFtmpdir =/tmp/max_connections =512 # 151autocommit =ON # ONsort_buffer_size =262144 # 262144(256k) ! fix too many Sort_merge_passes per second # # in SHOW GLOBAL STATUS output; speed up ORDER BY or GROUP BY operations####: for table cachetable_open_cache =4000 # 2000table_definition_cache =2000 # 1400table_open_cache_instances =32 # 16####: for netmax_allowed_packet =64M # 64Mbind_address =* # *connect_timeout =10 # 10interactive_timeout =28800 # 28800net_read_timeout =30 # 30net_retry_count =10 # 10net_write_timeout =60 # 60net_buffer_length =32k # 16384(16k)####: for logslog_output =FILE # FILE## -- general loggeneral_log =OFF # OFFgeneral_log_file =general.log # hotname.log## -- error loglog_error =err.log # stderrlog_statements_unsafe_for_binlog =ON # ! for error 1592## -- slow loglong_query_time =2.0 # 10.000000log_queries_not_using_indexes =OFF # OFFlog_slow_admin_statements =OFF # OFFlog_slow_slave_statements =OFF # OFFslow_query_log =ON # OFFslow_query_log_file =slow.log # slow.log ####: for binloglog_bin =mysql-binbinlog_checksum =none # CRC32log_bin_trust_function_creators =ON # OFFbinlog_direct_non_transactional_updates =OFF # OFFbinlog_expire_logs_seconds =604800 # 2592000(30days) | 604800(7days)binlog_error_action =ABORT_SERVER # ABORT_SERVER | IGNORE_ERRORbinlog_format =ROW # ROW | STATEMENT | MIXEDmax_binlog_stmt_cache_size =1G # 18446744073709547520max_binlog_cache_size =1G # 18446744073709547520(1G)max_binlog_size =1Gbinlog_order_commits =ON # ONbinlog_row_image =FULL # FULL | MINIMAL | NOBLOBbinlog_row_metadata =MINIMAL # MINIMAL | FULLbinlog_rows_query_log_events =ON # OFFsync_binlog =1 # 1 | 0 | Nbinlog_stmt_cache_size =32k # 32768(32k)log_slave_updates =ON # ONbinlog_group_commit_sync_delay =4000 # 0binlog_group_commit_sync_no_delay_count =10 # 0binlog_cache_size =96k # 32768(32k)binlog_transaction_dependency_history_size =25000 # 25000binlog_transaction_dependency_tracking =WRITESET # COMMIT_ORDER | WRITESET | WRITESET_SESSION####: for storage enginedefault_storage_engine =innodb # InnoDBdefault_tmp_storage_engine =innodb # InnoDBinternal_tmp_mem_storage_engine =TempTable # TempTable####: for innodb## disk I/O and file space managementinnodb_data_home_dir =./ # ./innodb_data_file_path =ibdata1:256M;ibdata2:256M:autoextend # ibdata1:12M:autoextendinnodb_page_size =16k # 16384(16k)innodb_default_row_format =dynamic # dynamic | compact | redundantinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =8 # 2innodb_log_file_size =128M # 50331648(48M)innodb_log_buffer_size =256M # 16777216(16M)innodb_redo_log_encrypt =OFF # OFFinnodb_online_alter_log_max_size =128M # 134217728(128M)innodb_undo_directory =./ # ./innodb_undo_log_encrypt =OFF # OFFinnodb_undo_log_truncate =ON # ONinnodb_max_undo_log_size =1G # 1073741824(1G)innodb_rollback_on_timeout =OFF # OFFinnodb_rollback_segments =128 # 128 [1~128]innodb_log_checksums =ON # ONinnodb_checksum_algorithm =crc32 # crc32innodb_log_compressed_pages =ON # ONinnodb_doublewrite =ON # ON ! do not disable it please.innodb_commit_concurrency =0 # 0## configuring innodb readonlyinnodb_read_only =OFF # OFF ## configuring innodb dedicated serverinnodb_dedicated_server =OFF # OFF ! related to mysql auto config please donot chanage## configuring innodb buffer pool size and instancesinnodb_buffer_pool_chunk_size =128M # 134217728(128M)innodb_buffer_pool_size =${innodb_pool_size} # 134217728(128M)innodb_buffer_pool_instances =10 # 1## making the buffer pool scan resistantinnodb_old_blocks_pct =37 # 37innodb_old_blocks_time =1000 # 1000## configuring innodb buffer pool prefetching(read ahead)innodb_random_read_ahead =off # OFFinnodb_read_ahead_threshold =56 # 56## configuring innodb buffer pool flushinginnodb_max_dirty_pages_pct_lwm =20 # 10innodb_max_dirty_pages_pct =90 # 90## fine-tuning innodb buffer pool flushinginnodb_flush_neighbors =0 # off | on (off for ssd ,on for hdd)innodb_lru_scan_depth =1024 # 1024## tuning for sharp checkpointinnodb_adaptive_flushing =ON # ONinnodb_adaptive_flushing_lwm =10 # 10innodb_flushing_avg_loops =30 # 30(a heih value means adaptive flushing is slow)## saving and restoring the buffer pool stateinnodb_buffer_pool_dump_pct =50 # 50innodb_buffer_pool_dump_at_shutdown =ON # ONinnodb_buffer_pool_load_at_startup =ON # ONinnodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_poolinnodb_stats_persistent =ON # ONinnodb_stats_on_metadata =ON # OFFinnodb_stats_method =nulls_equal # nulls_equalinnodb_stats_auto_recalc =ON # ONinnodb_stats_include_delete_marked =ON # ONinnodb_stats_persistent_sample_pages=20 # 20innodb_stats_transient_sample_pages =8 # 8innodb_status_output =OFF # OFFinnodb_status_output_locks =OFF # OFFinnodb_buffer_pool_dump_now =OFF # OFFinnodb_buffer_pool_load_abort =OFF # OFFinnodb_buffer_pool_load_now =OFF # OFF## configuring thread concurrency for innodbinnodb_thread_concurrency =0 # 0#? if innodb_thread_concurrency is 0, the value of innodb_thread_sleep_delay is ignored; #? so default the next 3 are ignored.innodb_concurrency_tickets =5000 # 5000innodb_thread_sleep_delay =15000 # 4000 ( 4ms)innodb_adaptive_max_sleep_delay =150000 # 150000 (15/100 s)## configuring the number of background innoDB i/o threads#? if you see more than 64 × innodb_read_io_threads pending read requests in #? SHOW ENGINE INNODB STATUS output, you might improve performance #? by increasing the value of innodb_read_io_threads.innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4## using asynchronous i/o on linux#? 1):perform read-ahead and write requests for data file pages.#? 2):Too many I/O write requests dispatched to the operating system for parallel processing could, #? in some cases, result in I/O read starvationinnodb_use_native_aio =ON # ON## configuring the innodb master thread i/o rateinnodb_flush_sync =OFF # ON#? To adhere to the limit on InnoDB background I/O activity defined by the innodb_io_capacity setting, #? disable innodb_flush_sync.innodb_io_capacity =4000 # 200innodb_io_capacity_max =20000 # 2000## configuring spin lock pollinginnodb_spin_wait_delay =6 # 6## configuring innoDB purge schedulinginnodb_purge_threads =4 # 4innodb_purge_batch_size =300 # 300(300 undo log page)innodb_purge_rseg_truncate_frequency=128 # 128## -- Lock & Waitinnodb_deadlock_detect =ON # ONinnodb_autoinc_lock_mode =2 # 0 | 1 | 2innodb_print_all_deadlocks =ON # OFFinnodb_lock_wait_timeout =50 # 50innodb_table_locks =ON # ONinnodb_sync_array_size =1 # 1innodb_sync_spin_loops =30 # 30 ## innodb othersinnodb_print_ddl_logs =OFF # OFFinnodb_replication_delay =0 # 0innodb_cmp_per_index_enabled =OFF # ! do not enable it please.innodb_disable_sort_file_cache =OFF # OFFinnodb_numa_interleave =OFF # OFFinnodb_strict_mode =ON # ONinnodb_sort_buffer_size =1M # 1M(global and only for full-text search)innodb_fast_shutdown =1 # 0 | 1 | 2innodb_force_load_corrupted =OFF # OFFinnodb_force_recovery =0 # 0 | 1 | 2 | 3 | 4 | 5 | 6innodb_temp_tablespaces_dir =./#innodb_temp/ # ./#innodb_temp/innodb_tmpdir =./ # ! the sort file temp dir of alter table oprationinnodb_temp_data_file_path =ibtmp1:64M:autoextend # ibtmp1:12M:autoextend ! stores rollback segments for changes made to user-created temporary tables.innodb_page_cleaners =4 # 1## adaptive hash index innodb_adaptive_hash_index =ON # ONinnodb_adaptive_hash_index_parts =8 # 8## -- Flush & Ioinnodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1 | 0 | 2innodb_flush_method =O_DIRECT # fsync | o_directinnodb_fsync_threshold =0 # 0 ~ 2**64-1innodb_change_buffer_max_size =25 # 25innodb_change_buffering =all # all | none | inserts | deletes | changes | purges####: othersdiv_precision_increment =4 # 4eq_range_index_dive_limit =200 # 200explicit_defaults_for_timestamp =ON # ONgroup_concat_max_len =1024 # 1024flush =OFF # OFFflush_time =0 # 0automatic_sp_privileges =ON # ONinnodb_fill_factor =90 # 100innodb_file_per_table =ON # ONinnodb_autoextend_increment =64 # 64innodb_open_files =64000 # 4000####: for authenticationcaching_sha2_password_auto_generate_rsa_keys =ON # ONcaching_sha2_password_private_key_path =private_key.pem # private_key.pemcaching_sha2_password_public_key_path =public_key.pem # public_key.pemdefault_authentication_plugin =caching_sha2_password # caching_sha2_passworddefault_password_lifetime =0 # 0disconnect_on_expired_password =ON # ON####: for charactercharacter_set_server =utf8mb4 # utf8mb4collation_server =utf8mb4_0900_ai_ci # utf8mb4_0900_ai_ci####: for optimizeroptimizer_prune_level =1optimizer_search_depth =62optimizer_switch =index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=onoptimizer_trace =enabled=off,one_line=offoptimizer_trace_features =greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=onoptimizer_trace_limit =1optimizer_trace_max_mem_size =1048576 optimizer_trace_offset =-1 #### for performance_schemaperformance_schema =on # onperformance_schema_consumer_global_instrumentation =on # onperformance_schema_consumer_thread_instrumentation =on # onperformance_schema_consumer_events_stages_current =on # offperformance_schema_consumer_events_stages_history =on # offperformance_schema_consumer_events_stages_history_long =off # offperformance_schema_consumer_statements_digest =on # onperformance_schema_consumer_events_statements_current =on # onperformance_schema_consumer_events_statements_history =on # onperformance_schema_consumer_events_statements_history_long =off # offperformance_schema_consumer_events_waits_current =on # offperformance_schema_consumer_events_waits_history =on # offperformance_schema_consumer_events_waits_history_long =off # offperformance-schema-instrument ='memory/%=COUNTED'EOF##config mysql startup scriptcat<<EOF > /etc/init.d/mysql$pt port=$ptmysql_user="root"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" instart) start;;stop) stop;;restart) restart;;*) printf "Usage: /etc/init.d/mysql${pt} {start|stop|restart}\n"esacEOF#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} startif [ $? -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 fidone方案说明1、配置文件参数是mysql 8.0
2、安装成功后root的初始密码是：Rootasdf2023
3、运行脚本时，只需要输入开始端口号，结束端口号、innodb_buffer_size的大小、安装目录即可。
4、Mysql各实例配置文件路径为：/etc/my{port}.cnf
5、Mysql各实例配置文件路径为：/etc/init.d/mysql{port}
版权声明：
本站文章均来自互联网搜集，如有侵犯您的权益，请联系我们删除，谢谢。
标签： #encryptmysql
