my.cnf 설정파일

 

 

 

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
#!includedir /etc/my.cnf.d

[mysqld]

##### replication
# Slave server ID (different from master server)
server-id = 2221

max_allowed_packet = 33554432


# GTID settings (if needed)
gtid_strict_mode = 1

# Replication settings
relay_log = /mnt/xvdb1/mariadblog/RELAYLOG/mysql-relay-bin
relay_log_index = /mnt/xvdb1/mariadblog/RELAYLOG/mysql-relay-bin.index
log_slave_updates = 1
slave_parallel_mode = optimistic
slave_parallel_threads = 3  # Adjusted for 4 CPU cores
slave_domain_parallel_threads = 3  # Adjusted for 4 CPU cores
slave_parallel_max_queued = 10485760
relay_log_recovery = 1
relay_log_purge = 1
skip-slave-start = 1
slave-skip-errors=1062,1032,1452
# slave-skip-erros = ALL

# Databases and tables to replicate

replicate-ignore-db = sys
replicate-ignore-db = test
replicate-ignore-db = vervotech_allmytourdb
replicate-ignore-db = sanha_allmytourdb
replicate-ignore-db = cache_allmytourdb
replicate-ignore-db = quartz_allmytourdb


replicate-wild-ignore-table = allmytourdb.%product_union_minprice%
replicate-wild-ignore-table = allmytourdb.%address_keyword_search%
replicate-wild-ignore-table = allmytourdb.%product_keyword_search%
replicate-wild-ignore-table = allmytourdb.%main_recommend_product%
replicate-wild-ignore-table = allmytourdb.%external_offer_api_log%

replicate-ignore-table = allmytourdb.product_1_batch
replicate-ignore-table = allmytourdb.product_mapper_1_batch



## MYSQL_SETTING
user                                = mysql
port                                = 3306
basedir                             = /usr/
datadir                             = /mnt/xvdb1/DATA
tmpdir                              = /mnt/xvdb1/TMP
plugin_dir                          = /usr/local/mariadb/lib/plugin
open_files_limit                    = 65536
character_set_server                = utf8mb4
collation_server                    = utf8mb4_unicode_ci
lower_case_table_names              = 1
log_bin_trust_function_creators     = 1
default_storage_engine              = InnoDB
transaction-isolation               = READ-COMMITTED
table_open_cache                    = 4000
max_connections                     = 400
thread_cache_size                   = 12   # 8+(max_connections/100)
wait_timeout                        = 180
interactive_timeout                 = 180
bind-address                        = 0.0.0.0
event-scheduler                     = 1
performance_schema                  = on
thread_handling                     = pool-of-threads
thread_pool_max_threads             = 800
thread_pool_idle_timeout            = 120
skip-character-set-client-handshake
skip-name-resolve                                   
skip-host-cache      
large-pages
session_track_system_variables      = last_gtid # Maxscale Use causal_reads

## Buffer size
key_buffer_size                     = 64M
sort_buffer_size                    = 2M
read_buffer_size                    = 2M
read_rnd_buffer_size                = 2M
join_buffer_size                    = 512K


## Innodb add setting
innodb_data_home_dir                = /mnt/xvdb1/DATA
innodb_log_group_home_dir           = /mnt/xvdb1/IBLOG
innodb_data_file_path               = ibdata1:500M:autoextend
innodb_temp_data_file_path          = ibtmp1:50M:autoextend:max:10G
innodb_file_per_table               = ON
innodb_buffer_pool_size             = 10G
innodb_flush_neighbors              = 0              # SSD : 0, DISK : 1
innodb_log_file_size                = 2560M          # buffer_pool_size 대비 25%
innodb_log_buffer_size              = 64M
innodb_flush_log_at_trx_commit      = 2              # set 1 : File Flush, set 2 : OS Buffer save
innodb_flush_method                 = O_DIRECT_NO_FSYNC
innodb_fast_shutdown                = 0
innodb_write_io_threads             = 4             # nvme max = 64, sas ssd = 32, vm = 16
innodb_read_io_threads              = 4             # nvme max = 64, sas ssd = 32, vm = 16
innodb_io_capacity                  = 3000           # set IOPS
innodb_io_capacity_max              = 6000
innodb_flush_sync                   = OFF            # If set on, innodb_io_capacity setting ignored
innodb_lock_wait_timeout            = 10
innodb_print_all_deadlocks          = ON
innodb_fill_factor                  = 80
innodb_autoinc_lock_mode            = 2              # If 2 set Must binlog_format ROW or MIXED
innodb_undo_tablespaces             = 30
innodb_undo_directory               = /mnt/xvdb1/UNDO
innodb_adaptive_hash_index          = OFF

## Recovery
binlog_cache_size                   = 16M
binlog_format                       = ROW
expire_logs_days                    = 3


## Performance schema parameter
performance_schema_digests_size                        = 20000
performance_schema_max_digest_length                   = 2048
performance_schema_max_sql_text_length                 = 2048
performance_schema_events_statements_history_long_size = 50000


## Fulltext min word
ft_min_word_len                     = 2
innodb_ft_min_token_size            = 2




[mariadb]


## Error log and slow log
log_error                           = /mnt/xvdb1/ADMIN/error.log
log_warnings                        = 3
slow_query_log                      = off
slow_query_log_file                 = /mnt/xvdb1/ADMIN/slow_query.log
log_queries_not_using_indexes       = off
long_query_time                     = 1
back_log                            = 256



[mysqld_safe]
open-files-limit=65536
malloc-lib                          = /usr/lib64/libtcmalloc_minimal.so.4

#relay_log                    = /mnt/xvdb1/mariadblog/RELAYLOG/mysql-relay-bin

[mysqladmin]
user=root
password=""

my.cnf

0.01MB