MySql 5.1 Setting my.cnf 잘된샘플 KTH

[client] port = 3306 socket = /tmp/mysql.sock

[mysqld]

generic configuration options

port = 3306 socket = /tmp/mysql.sock

back_log = 100 max_connections = 500 max_connect_errors = 10 table_open_cache = 2048 max_allowed_packet = 16M join_buffer_size = 8M thread_cache_size = 128 thread_concurrency = 16 query_cache_type = 0 default_storage_engine = innodb thread_stack = 192K lower_case_table_names = 1 max_heap_table_size = 128M tmp_table_size = 128M local_infile = 0 max_prepared_stmt_count = 256K event_scheduler = ON log_bin_trust_function_creators = 1 secure_auth = 1 skip_external_locking skip_symbolic_links #skip_name_resolve

config server and data path

basedir = /home/server/mysql datadir = /data/mysql-data tmpdir = /data/mysql-tmp log_bin = /data/mysql-binlog/mysql-bin relay_log = /data/mysql-binlog/mysql-relay innodb_data_home_dir = /data/mysql-data innodb_log_group_home_dir = /data/mysql-iblog

config character set

##utf8 character_set_client_handshake = FALSE character_set_server = utf8 collation_server = utf8_general_ci init_connect = “SET collation_connection = utf8_general_ci” init_connect = “SET NAMES utf8”

##euckr #character_set_client_handshake = FALSE #character_set_server = euckr #collation_server = euckr_korean_ci #init_connect = “SET collation_connection = euckr_korean_ci” #init_connect = “SET NAMES euckr”

bin log

binlog_format = mixed binlog_cache_size = 4M

Replication related settings

server_id = 2 expire_logs_days = 7 slave_net_timeout = 60 log_slave_updates #read_only

MyISAM Specific options

key_buffer_size = 32M read_buffer_size = 128K read_rnd_buffer_size = 512K sort_buffer_size = 512K bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 16M myisam_repair_threads = 1 myisam_recover = FORCE,BACKUP

*** INNODB Specific options ***

innodb_additional_mem_pool_size = 16M

innodb buffer pool

for 24G

#innodb_buffer_pool_size = 16G

for 16G

innodb_buffer_pool_size = 8G

for 8G

#innodb_buffer_pool_size = 4G

for 4G

#innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G innodb_file_per_table = 1 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 120 innodb_support_xa = 0 innodb_file_io_threads = 8

[mysqldump] quick max_allowed_packet = 16M

[mysql] no_auto_rehash

[myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M

[mysqlhotcopy] interactive_timeout

[mysqld_safe] open_files_limit = 8192