# # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mariadb] ###Replication TLS encryption ### #ssl-ca=/opt/mariadb/cacert.crt #ssl-cert=/opt/mariadb/server-cert.crt #ssl-key=/opt/mariadb/server-key.key ### Thread #### thread_handling=pool-of-threads thread_pool_size=10 ##########For Load Test############ ###back_log=50+(max_connections/5)### back_log=450 max_connections=2000 ##########buffer pool(cache)########## innodb_buffer_pool_size=90G innodb_log_file_size=1G sort_buffer_size=4M slow_query_log=1 slow_query_log_file=/opt/mariadb/slow_query.log long_query_time=10 query_cache_size=0 max_allowed_packet=1073741824 max_heap_table_size = 512M tmp_table_size = 512M query_response_time_stats=ON innodb_io_capacity=20000 innodb_io_capacity_max=40000 table_definition_cache=1500 table_open_cache=3000 ##########Data Directory############### datadir=/appdata socket=/opt/mariadb/mysql.sock log-error=/appdata/mariadb.err pid-file=/opt/mariadb/mariadb.pid #general_log=1 #general_log_file=/opt/mariadb/mariadb.log tmpdir=/opt/mariadb/tmpdir ##########Character Set############### character_set_server = utf8mb4 ##########Binary Log############### wsrep_gtid_mode=ON wsrep_gtid_domain_id=100 gtid_domain_id=10 server_id=1 #log-basename=master #binlog-format=ROW log-bin=/opt/mariadb/binlog/master-bin log-bin-index=/opt/mariadb/binlog/master-bin.index relay-log=/opt/mariadb/binlog/relay-bin relay-log-index=/opt/mariadb/binlog/relay-bin.index expire_logs_days=1 log_slave_updates=ON #########TRANSACTION ISOLATION######## transaction-isolation=READ-COMMITTED ##########Audit############### plugin_load_add=server_audit server_audit_logging=on server_audit_file_rotate_now=off # server_audit_file_rotate_size=1000000 server_audit_file_rotations=0 server_audit_file_path=/appdata/server_audit.log # For OS authentication # plugin_load_add=auth_socket # Audit DB team colleagues: server_audit_incl_users=root,mariadb,cntchan,swchan,whman,kkclam,dytwong,shmwu,gtschan,vycyu server_audit_events=connect,table,query_ddl,query_dcl ##########Performance schema########## performance_schema ##########Encryption########## #plugin_load_add = file_key_management #file_key_management_filename = /opt/mariadb/encryption/[hostname]_keyfile.enc #file_key_management_filekey = FILE:/opt/mariadb/encryption/[hostname]_keyfile.key #file_key_management_encryption_algorithm = aes_cbc #encrypt_binlog = 0 #innodb_encrypt_tables = ON #innodb_encrypt_temporary_tables = ON #innodb_encrypt_log = OFF #innodb_encryption_threads = 4 #Do not rotate key #innodb_encryption_rotate_key_age = 0 innodb_print_all_deadlocks = ON innodb_lock_wait_timeout=600 ##########FederatedX storage engine to access remote sever########## #plugin_load_add = ha_federatedx ##########Galera########## # (This must be substituted by wsrep_format) binlog_format=ROW # Currently only InnoDB storage engine is supported default-storage-engine=innodb # to avoid issues with 'bulk mode inserts' using autoinc # Statement base use 0 or 1, Row base use 2 for best performance innodb_autoinc_lock_mode=2 #For Galera, set to 0 or 2 innodb_flush_log_at_trx_commit=1 # Override bind-address # In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST # it will have (most likely) disastrous consequences on donor node bind-address=0.0.0.0 ########## WSREP options ########## ####### Rememeber to change ### option ####### # Enable wsrep wsrep_on=ON # Full path to wsrep provider library or 'none' #wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so # Provider specific configuration options # https://galeracluster.com/library/documentation/galera-parameters.html#gcs-max-throttle #wsrep_provider_options="gcs.max_throttle=0.25;gcs.recv_q_hard_limit=LLONG_MAX;gcs.recv_q_soft_limit=0.25" wsrep_provider_options="gcache.size=512M; gcache.page_size=100M; pc.weight=1" # Logical cluster name. Should be the same for all nodes. # amend : change cluster name for new cluster: ### wsrep_cluster_name="ELS_cluster" wsrep_cluster_name="STREAMS2_cluster" # Group communication system handle # amend : change cluster node address for new cluster: ### wsrep_cluster_address="gcomm://10.49.121.175,10.49.121.176" wsrep_cluster_address="gcomm://10.134.194.10,10.134.194.11,10.134.194.12" # Human-readable node name (non-unique). Hostname by default. #wsrep_node_name= wsrep_node_name=lx388phq # Base replication [:port] of the node. # The values supplied will be used as defaults for state transfer receiving, # listening ports and so on. Default: address of the first network interface. # amend : change cluster node address for new cluster: ### wsrep_node_address=10.49.52.116 wsrep_node_address=10.134.194.11 # Address for incoming client connections. Autodetect by default. #wsrep_node_incoming_address= # How many threads will process writesets from other nodes #wsrep_slave_threads=1 # DBUG options for wsrep provider #wsrep_dbug_option # Generate fake primary keys for non-PK tables (required for multi-master # and parallel applying operation) #wsrep_certify_nonPK=1 # Maximum number of rows in write set #wsrep_max_ws_rows=0 # Maximum size of write set #wsrep_max_ws_size=2147483647 # to enable debug level logging, set this to 1 #wsrep_debug=0 # convert locking sessions into transactions #wsrep_convert_LOCK_to_trx=0 # how many times to retry deadlocked autocommits #wsrep_retry_autocommit=1 # change auto_increment_increment and auto_increment_offset automatically #wsrep_auto_increment_control=1 wsrep_auto_increment_control=1 # retry autoinc insert, which failed for duplicate key error #wsrep_drupal_282555_workaround=0 # enable "strictly synchronous" semantics for read operations #wsrep_causal_reads=0 # Command to call when node status or cluster membership changes. # Will be passed all or some of the following options: # --status - new status of this node # --uuid - UUID of the cluster # --primary - whether the component is primary or not ("yes"/"no") # --members - comma-separated list of members # --index - index of this node in the list #wsrep_notify_cmd= ## ## WSREP State Transfer options ## # # # State Snapshot Transfer method wsrep_sst_method=rsync wsrep_slave_thread=8 # Address which donor should send State Snapshot to. # Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!! # (SST method dependent. Defaults to the first IP of the first interface) #wsrep_sst_receive_address= # SST authentication string. This will be used to send SST to joining nodes. # Depends on SST method. For mysqldump method it is root: #wsrep_sst_auth=root: # Desired SST donor name. #wsrep_sst_donor= # Reject client queries when donating SST (false) #wsrep_sst_donor_rejects_queries=0 # Protocol version to use # wsrep_protocol_version= #For create Trigger log_bin_trust_function_creators=1 wsrep_auto_increment_control=ON slave_parallel_threads=4 [mysqldump] socket=/opt/mariadb/mysql.sock [mysqladmin] socket=/opt/mariadb/mysql.sock