mysql conf配置

原创

hanye韩也 2018-01-17 10:50:43 博主文章分类：mysql数据库 ©著作权

文章标签 mysql conf 配置 文章分类 运维

©著作权归作者所有：来自51CTO博客作者hanye韩也的原创作品，请联系作者获取转载授权，否则将追究法律责任

[mysqld_multi] mysqld = /data/mysql/bin/mysqld_safe mysqladmin = /data/mysql/bin/mysqladmin #user = root #password = 1

[mysqld1] port = 3306 socket = /tmp/mysql3306.sock pid-file = /data/mysql_db/db_inst1/3306.pid datadir = /data/mysql_db/db_inst1 user = mysql skip-name-resolve skip-external-locking lower_case_table_names = 1 open_files_limit=1536 back_log=400 max_connections=2000 max_connect_errors=100000 external-locking = FALSE max_allowed_packet = 128M table_open_cache = 512 sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 200 tmp_table_size = 128M max_heap_table_size = 128M key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M autocommit = 1 log_error = /data/mysql_log/db_inst1/error.log slow_query_log=on long_query_time=1 slow_query_log_file=/data/mysql_log/db_inst1/slow.log query_cache_type=0 query_cache_limit=2M query_cache_size=0 #master - slave log-bin=mysql-bin binlog_format=mixed expire_logs_days = 7 server-id = 1 binlog-ignore-db=mysql replicate-do-db=qqnyw replicate-ignore-db=mysql auto_increment_offset=1 auto_increment_increment=2 log-slave-updates #end innodb_data_home_dir = /data/mysql_db/db_inst1 innodb_data_file_path = ibdata1:16M:autoextend innodb_log_group_home_dir = /data/mysql_db/db_inst1 innodb_file_per_table = 1 innodb_buffer_pool_size = 5G innodb_additional_mem_pool_size = 16M innodb_log_file_size = 256M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 50

#function log_bin_trust_function_creators = 1 [mysqld2] port = 3307 socket = /tmp/mysql3307.sock pid-file = /data/mysql_db/db_inst2/3307.pid datadir = /data/mysql_db/db_inst2 user = mysql skip-name-resolve skip-external-locking lower_case_table_names = 1 open_files_limit=1536 back_log=400 max_connections=2000 max_connect_errors=100000 external-locking = FALSE max_allowed_packet = 128M table_open_cache = 512 sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 200 query_cache_size = 32M tmp_table_size = 128M max_heap_table_size = 128M key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M autocommit = 0 log_error = /data/mysql_log/db_inst2/error.log slow_query_log=on long_query_time=1 slow_query_log_file=/data/mysql_log/db_inst2/slow.log query_cache_type=1 query_cache_limit=2M query_cache_size=256M

master - slave

log-bin=mysql-bin binlog_format=mixed expire_logs_days = 7 server-id = 2 binlog-ignore-db=mysql replicate-do-db=crm replicate-do-db=nyh replicate-do-db=bbs replicate-do-db=zshYHSC replicate-do-db=crmcrm replicate-do-db=nycnzs replicate-do-db=bugOS replicate-ignore-db=mysql auto_increment_offset=1 auto_increment_increment=2 log-slave-updates #end

innodb_data_home_dir = /data/mysql_db/db_inst2 innodb_data_file_path = ibdata1:16M:autoextend innodb_log_group_home_dir = /data/mysql_db/db_inst2 innodb_file_per_table = 1 innodb_buffer_pool_size = 2G innodb_additional_mem_pool_size = 16M innodb_log_file_size = 256M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 50

#function log_bin_trust_function_creators = 1 [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash [mysqlhotcopy] interactive-timeou

[mysqld_multi] mysqld = /data/mysql/bin/mysqld_safe mysqladmin = /data/mysql/bin/mysqladmin user = admin password = password

[client] default-character-set = utf8mb4

[mysqld1] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' port = 3306 socket = /tmp/mysql3306.sock pid-file = /data/mysql_db/db_inst1/3306.pid datadir = /data/mysql_db/db_inst1 user = mysql skip-name-resolve skip-external-locking lower_case_table_names = 1 open_files_limit=1536 back_log=400 max_connections=2000 max_connect_errors=100000 external-locking = FALSE max_allowed_packet = 128M table_open_cache = 512 sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 200 tmp_table_size = 1024M max_heap_table_size = 1024M key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M autocommit = 1 log_error = /data/mysql_log/db_inst1/error.log slow_query_log=on long_query_time=1 slow_query_log_file=/data/mysql_log/db_inst1/slow.log query_cache_type=1 query_cache_limit=2M query_cache_size=256M #master - slave log-bin=mysql-bin binlog_format=mixed expire_logs_days = 7 server-id = 2 #binlog-ignore-db=mysql #replicate-do-db=qqnyw #replicate-ignore-db=mysql auto_increment_offset=1 auto_increment_increment=2 log-slave-updates #end innodb_data_home_dir = /data/mysql_db/db_inst1 innodb_data_file_path = ibdata1:16M:autoextend innodb_log_group_home_dir = /data/mysql_db/db_inst1 innodb_file_per_table = 1 innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 16M innodb_log_file_size = 256M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 50

#function log_bin_trust_function_creators = 1 #[mysqld2] #port = 3307 #socket = /tmp/mysql3307.sock #pid-file = /data/mysql_db/db_inst2/3307.pid #datadir = /data/mysql_db/db_inst2 #user = mysql #skip-name-resolve #skip-external-locking #lower_case_table_names = 1 #open_files_limit=1536 #back_log=400 #max_connections=2000 #max_connect_errors=100000 #external-locking = FALSE #max_allowed_packet = 128M #table_open_cache = 512 #sort_buffer_size = 2M #join_buffer_size = 2M #thread_cache_size = 200 #query_cache_size = 32M #tmp_table_size = 128M #max_heap_table_size = 128M #key_buffer_size = 32M #read_buffer_size = 1M #read_rnd_buffer_size = 16M #bulk_insert_buffer_size = 64M #autocommit = 0 #log_error = /data/mysql_log/db_inst2/error.log #slow_query_log=on #long_query_time=1 #slow_query_log_file=/data/mysql_log/db_inst2/slow.log #query_cache_type=1 #query_cache_limit=2M #query_cache_size=256M

master - slave

#log-bin=mysql-bin #binlog_format=mixed #expire_logs_days = 7 #server-id = 2 #binlog-ignore-db=mysql #replicate-do-db=crm #replicate-do-db=nyh #replicate-do-db=bbs #replicate-do-db=zshYHSC #replicate-do-db=crmcrm #replicate-do-db=nycnzs #replicate-do-db=bugOS #replicate-ignore-db=mysql #auto_increment_offset=1 #auto_increment_increment=2 #log-slave-updates ##end

#innodb_data_home_dir = /data/mysql_db/db_inst2 #innodb_data_file_path = ibdata1:16M:autoextend #innodb_log_group_home_dir = /data/mysql_db/db_inst2 #innodb_file_per_table = 1 #innodb_buffer_pool_size = 2G #innodb_additional_mem_pool_size = 16M #innodb_log_file_size = 256M #innodb_log_buffer_size = 16M #innodb_flush_log_at_trx_commit = 2 #innodb_flush_method = O_DIRECT #innodb_lock_wait_timeout = 50

##function #log_bin_trust_function_creators = 1 [mysqldump] quick max_allowed_packet = 128M

[mysql] default-character-set = utf8mb4 no-auto-rehash

[mysqlhotcopy] interactive-timeout