[mysqld] ################################################################################### # Created: 2018-08-09 # Updated: 2018-08-17 ################################################################################### ####################################### # MySQL Basic Parameter ####################################### user=mysql basedir=/usr/local/mysql datadir=/data/mysql socket=/tmp/mysql.sock port=3306 skip-name-resolve=1 character_set_server=utf8mb4 collation_server=utf8mb4_general_ci default_storage_engine=InnoDB transaction-isolation=READ-COMMITTED table_open_cache=8000 table_open_cache_instances=16 table_definition_cache=4400 max_connect_errors=10000 max_connections=5000 max_allowed_packet=16M thread_cache_size=3000 open-files-limit=65535 lower_case_table_names=1 explicit_defaults_for_timestamp=true ######################################## # InnoDB Related Parameter ######################################## innodb_buffer_pool_size=80G innodb_buffer_pool_instances=64 innodb_file_per_table innodb_support_xa=1 innodb_autoextend_increment=64 innodb_log_files_in_group=3 innodb_log_file_size=1024M innodb_log_buffer_size=64M ### I/O Related (Flash Storage) innodb_write_io_threads=20 innodb_read_io_threads=20 innodb_io_capacity=20000 innodb_io_capacity_max=40000 innodb_flush_neighbors=0 innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT ### InnoDB Thread Related innodb_thread_concurrency=0 innodb_commit_concurrency=0 innodb_print_all_deadlocks=ON innodb_large_prefix=ON innodb_purge_threads=4 innodb_adaptive_hash_index=0 innodb_strict_mode=ON ####################################### # Connection Thread Related Memory ####################################### key_buffer_size=16M read_buffer_size=256K read_rnd_buffer_size=512K join_buffer_size=512K sort_buffer_size=1048576 tmp_table_size = 16M max_heap_table_size = 16M ####################################### # Log Related Configuration ####################################### # log_warnings=1 (Depricated) # 1:Error messages 2:Error and warning messages 3:Error, warning, and information messages log_error_verbosity=3 log_output=FILE slow_query_log slow_query_log_file=/log/mysql/slow-query.log long_query_time=10 log_timestamps=SYSTEM log_bin=mysql-bin log_slave_updates=ON sync_binlog=1 binlog_format=ROW binlog_row_image=minimal binlog_rows_query_log_events=ON max_binlog_size=256M binlog_cache_size=32M expire_logs_days=7 report-port=3306 report-host=db-14 server_id=101 gtid_mode=ON enforce_gtid_consistency=ON #enforce_gtid_consistency=WARN slave_net_timeout=60 slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8 slave_preserve_commit_order=ON slave_pending_jobs_size_max=67108864 ##################### Chage After Upgrade to MySQL8.0 ###################### #innodb_undo_directory=/usr/local/mysql/undo #innodb_undo_tablespaces=3 # crash-safe slave & for Group Replication master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON ####################################### # Security Related ####################################### symbolic-links=0 secure_file_priv='' ### MySQL5.7 Trun Off Until Upgrade Connector skip_ssl ####################################### # SQL MODE # REMOVE ONLY_FULL_GROUP_BY AND ADD PIPES_AS_CONCAT ####################################### #sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT sql_mode=PIPES_AS_CONCAT,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ######################################## # Temporary ######################################## tmpdir=/mnt/data/tmp tmp_table_size=16M max_heap_table_size=16M open_files_limit=65535 group_concat_max_len=2048 ############## Mecab ################### loose-mecab-rc-file=/usr/local/mysql/lib/mecab/etc/mecabrc innodb_ft_min_token_size=2 innodb_optimize_fulltext_only=ON ############## Group Replication ################### transaction_write_set_extraction = XXHASH64 binlog_checksum=NONE group_replication_allow_local_disjoint_gtids_join = OFF group_replication_allow_local_lower_version_join = OFF group_replication_auto_increment_increment = 1 group_replication_bootstrap_group = OFF group_replication_components_stop_timeout = 31536000 group_replication_compression_threshold = 1000000 group_replication_enforce_update_everywhere_checks = OFF group_replication_flow_control_applier_threshold = 25000 group_replication_flow_control_certifier_threshold = 25000 group_replication_flow_control_mode = QUOTA group_replication_force_members group_replication_group_name = AAAAAAA-BBBB-CCCC-DDDD-02011477005C group_replication_group_seeds = 192.168.100.30:33061,192.168.100.31:33061 group_replication_gtid_assignment_block_size = 1000000 group_replication_ip_whitelist = AUTOMATIC group_replication_local_address = 192.168.100.32:33061 group_replication_member_weight = 50 group_replication_poll_spin_loops = 0 group_replication_recovery_complete_at = TRANSACTIONS_APPLIED group_replication_recovery_reconnect_interval = 60 group_replication_recovery_retry_count = 10 group_replication_recovery_ssl_ca group_replication_recovery_ssl_capath group_replication_recovery_ssl_cert group_replication_recovery_ssl_cipher group_replication_recovery_ssl_crl group_replication_recovery_ssl_crlpath group_replication_recovery_ssl_key group_replication_recovery_ssl_verify_server_cert = OFF group_replication_recovery_use_ssl = OFF group_replication_single_primary_mode = ON group_replication_ssl_mode = DISABLED group_replication_start_on_boot = ON group_replication_transaction_size_limit = 0 group_replication_unreachable_majority_timeout = 0 auto_increment_increment = 1 auto_increment_offset = 1 [mysqld_safe] log-error=/data/log/mysql/mysqld.log pid-file= /data/tmp/mysqld.pid [mysql] #no_auto_rehash max_allowed_packet = 8M prompt = '\u@\h [\d]> ' default_character_set = utf8mb4 [client] socket=/tmp/mysql.sock