mysql conf配置

[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

转载于:https://blog.51cto.com/9025736/2061903