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

 

转载于:https://www.cnblogs.com/wendu/p/6703280.html