4核8g mysql 配置_mysql配置文件集锦

1，4核8G内存 主读

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8 #设置客户端的字符编码

[mysqld]

#basic settings#

default-time_zone='+8:00'

character-set-server=utf8

collation-server=utf8_general_ci

basedir = /usr/local/mysql

datadir=/usr/local/mysql/data/data

lower_case_table_names=1 #不区分大小写

socket=/tmp/mysql.sock

#pid-file=/var/run/mysqld/mysqld.pid

port = 3306

server-id = 2

symbolic-links=0

max_allowed_packet = 100MB

slave_pending_jobs_size_max = 200MB

transaction-isolation = READ-COMMITTED

#主从配置

#master-host=192.168.108.167

#master-user=flowtrade

#master-password=123456

#master-port=3306

#master-connect-retry=60

#replicate-do-db =flow_trade

#replicate-ignore-db=mysql

#connection#

interactive_timeout = 28800

wait_timeout = 28800

lock_wait_timeout = 1200

skip_name_resolve = 1

max_connections = 2000

max_connect_errors = 1000000

#log setting#

log-bin=mysql-bin-log

#log-error=/usr/local/mysql/data/mysql-error.log

long_query_time = 5

slow_query_log = 1

slow_query_log_file = /usr/local/mysql/data/slow.log

expire_logs_days = 90

log_slave_updates = 1

relay_log = mysql-relay-bin

relay_log_recovery=1

log_timestamps = SYSTEM

master_info_repository=TABLE

relay_log_info_repository=TABLE

#innodb settings#

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

innodb_file_per_table = 1

innodb_buffer_pool_size = 4G

innodb_doublewrite = ON

innodb_thread_concurrency = 64

innodb_purge_threads = 6

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_buffer_pool_instances = 8

innodb_log_file_size = 1GB

innodb_log_files_in_group = 3

innodb_log_buffer_size = 50M

innodb_io_capacity = 200

innodb_io_capacity_max = 400

innodb_flush_method=O_DIRECT

innodb_flush_neighbors = 0

innodb_print_all_deadlocks = 1

#session memory setting#

join_buffer_size = 6M

query_cache_type = 1

thread_cache_size = 64

query_cache_size = 128M

table_open_cache = 2000

sort_buffer_size = 6M

read_buffer_size = 6M

read_rnd_buffer_size = 6M

#replication setting#

slave-parallel-type = LOGICAL_CLOCK

slave-parallel-workers = 4

slave_skip_errors = ddl_exist_errors

2，4核16G 主写

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8 #设置客户端的字符编码

[mysqld]

#basic settings#

default-time_zone = '+8:00'

character-set-server = utf8

collation-server = utf8_general_ci

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data/data

lower_case_table_names=1 #不区分大小写socket = /tmp/mysql.sock #pid-file = /var/run/mysqld/mysqld.pid bind_address = 0.0.0.0 port = 3306 server-id = 1 max_allowed_packet = 100MB transaction-isolation = READ-COMMITTED #主从复制数据库设置 binlog-do-db=flow_trade binlog-ignore-db=mysql #connection# interactive_timeout = 28800 wait_timeout = 28800 lock_wait_timeout = 1200 skip_name_resolve = 1 max_connections = 3000 ##最大连接数，需根据业务实际用户链接设置 max_connect_errors = 1000000 #log setting# #log-error = /usr/local/mysql/data/datamysql-error.log long_query_time = 5 slow_query_log = 1 slow_query_log_file = /usr/local/mysql/data/data/slow.log expire_logs_days = 90 log_slave_updates = 1 log-bin=/usr/local/mysql/data/data/mysql-bin-log log_timestamps = SYSTEM #innodb settings# innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 10G innodb_doublewrite = ON innodb_thread_concurrency = 64 innodb_purge_threads = 6 innodb_read_io_threads = 6 innodb_write_io_threads = 6 innodb_buffer_pool_instances = 6 innodb_log_file_size = 1GB innodb_log_files_in_group = 3 innodb_log_buffer_size = 50M innodb_io_capacity = 200 ##根据磁盘的实际IO能力设置 innodb_io_capacity_max = 400 innodb_flush_method=O_DIRECT innodb_flush_neighbors = 0 innodb_print_all_deadlocks = 1 #session memory setting# join_buffer_size = 6M thread_cache_size = 256 query_cache_type = 1 query_cache_size = 128M table_open_cache = 2000 sort_buffer_size = 6M read_buffer_size = 6M read_rnd_buffer_size = 6M