# Config file to be used with InnoDB clusters 8.0.29
# Created by Leipeng on 2022-07-15

[mysql]
reconnect
show-warnings

[mysqld]
server_id=1621
port=3010
user = mysql
basedir=/usr/local/mysql8029
datadir=/app/mysqldata8029
tmpdir=/app/mysqldata8029
secure_file_priv=/app/mysqldata8029
max_allowed_packet = 128M
lower_case_table_names=1
explicit_defaults_for_timestamp=1
sql_mode='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
default-time-zone='+8:00'
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

skip_replica_start
loose_clone_max_network_bandwidth=200

#plugin
mysqlx=0
default_authentication_plugin = mysql_native_password
plugin-load-add=mysql_clone.so
plugin-load-add=group_replication.so

#table and file
table_definition_cache=4000
table_open_cache = 4000
open_files_limit = 655360

#connection
socket=/app/mysqldata8029/mysql.sock
skip_name_resolve=ON
bind-address=0.0.0.0
connect_timeout=15
interactive_timeout=28800
wait_timeout=28800
max_connections= 10000
max_connect_errors= 1000000
back_log=2000

#log
log_timestamps=SYSTEM
log-error=/app/mysqldata8029/error.log
log_error_verbosity=3
#log_error_suppression_list='MY-013360'

#character set, collation 
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

#innodb
innodb_data_home_dir = /app/mysqldata8029/
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_print_all_deadlocks =1
transaction_isolation = READ-COMMITTED
innodb_rollback_on_timeout=on
innodb_log_files_in_group=3
innodb_log_file_size = 1G
innodb_log_buffer_size = 32m
innodb_temp_data_file_path = ibtmp1:1024M:autoextend
innodb_online_alter_log_max_size = 1G
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 2G

#doublewrite
innodb_doublewrite_dir='/app/mysqldata8029'

#undo
innodb_undo_directory= /app/mysqldata8029/
innodb_undo_log_truncate=off
#The innodb_undo_tablespaces variable is deprecated and is no longer configurable as of MySQL 8.0.14. 
#innodb_undo_tablespaces = 8

#binlog
log_bin=/app/mysqldata8029/binlog
log-bin-index=/app/mysqldata8029/mysql-bin.index
log_replica_updates=ON
binlog_format=ROW
binlog_checksum=CRC32
sync_binlog=1000
binlog_expire_logs_seconds = 691200
binlog_transaction_dependency_tracking=WRITESET
transaction_write_set_extraction=XXHASH64

#relaylog
relay-log=/app/mysqldata8029/relaylog
relay_log_recovery=0

###slow log###
long_query_time = 1
slow-query-log = on
slow-query-log-file = /app/mysqldata8029/slow-query.log
log_slow_admin_statements = on

#gtid
gtid_mode=ON
enforce-gtid-consistency=ON
replica_parallel_workers=16
replica_parallel_type=LOGICAL_CLOCK
replica_preserve_commit_order=ON

#Group Replication
loose_group_replication_components_stop_timeout=300
loose_group_replication_enforce_update_everywhere_checks = OFF
loose_group_replication_flow_control_mode = DISABLED
loose_group_replication_ip_allowlist='0.0.0.0/0'
loose_group_replication_transaction_size_limit= 1073741824
loose_group_replication_group_name=900aaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
loose_group_replication_group_seeds="172.16.106.20:4010,172.16.106.21:4010"
loose_group_replication_local_address="172.16.106.21:4010"
loose_group_replication_paxos_single_leader=on

#IO
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0
innodb_page_cleaners = 8
innodb_io_capacity = 1000
innodb_lru_scan_depth = 512

#session
tmp_table_size = 32M
max_heap_table_size = 32M

#innodb_monitor
innodb_monitor_enable='log_lsn%'

#Performance Schema options
### setup_instruments 
performance-schema-instrument='stage/%=ON'
performance-schema-instrument='stage/%=ON'
performance_schema_instrument='memory/%=counted'

###wait
performance-schema-consumer-events_waits_current = ON
performance-schema-consumer-events_waits_history = ON
performance-schema-consumer-events_waits_history_long = ON

###stage
performance-schema-consumer-events_stages_current = ON
performance-schema-consumer-events_stages_history = ON
performance-schema-consumer-events_stages_history_long = ON

###statement
performance-schema-consumer-events_statements_current = ON
performance-schema-consumer-events_statements_history = ON
performance-schema-consumer-events_statements_history_long = ON

### row events_statements_history_long
performance_schema_events_statements_history_size=500
performance_schema_events_statements_history_long_size=100000