mysql主库与从库配置（并行复制配置）

mysql主库与从库配置（并行复制配置）

主库：

[mysqld]

server-id = 2233
port = 13306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
pid-file=/usr/local/mysql/data/mysql.pid
skip-external-locking
skip_name_resolve = 0
skip-slave-start
transaction_isolation = REPEATABLE-READ
character-set-server = utf8
collation-server = utf8_general_ci
init_connect='SET NAMES utf8'
lower_case_table_names = 1
max_connections = 2000
max_connect_errors = 1000
max_allowed_packet = 24M
open_files_limit=65536
max_prepared_stmt_count=1048576
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_size = 128M
query_cache_type = 1
query_cache_limit=4M
sort_buffer_size = 2M　　　　#线程缓存
binlog_cache_size = 5242880
back_log = 130
log_timestamps=SYSTEM

log-error=/usr/local/mysql/data/mysqld.log
log-bin=mysql-bin
binlog_rows_query_log_events=1
log_bin_trust_function_creators=1

table_open_cache=2000
innodb_open_files=1024
binlog_format = row

gtid_mode=ON
enforce-gtid-consistency

innodb_flush_log_at_trx_commit = 1
sync_binlog = 0
innodb_buffer_pool_size = 6G
innodb_file_per_table=1
innodb_data_file_path = ibdata1:12M;ibdata2:1G:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size=512M
innodb_log_files_in_group=3
innodb_log_buffer_size = 128M
innodb_io_capacity=800
innodb_adaptive_flushing=1
innodb_support_xa = ON　　　　　　　
innodb_read_io_threads=12
innodb_write_io_threads=12
#auto_increment_offset=1
#auto_increment_increment=2

innodb_lock_wait_timeout = 100
innodb_large_prefix = 1
innodb_purge_threads = 4
#innodb_print_all_deadlocks = 1
innodb_buffer_pool_dump_at_shutdown=on
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_now=ON
innodb_buffer_pool_load_now=ON
innodb_thread_concurrency=0
innodb_read_ahead_threshold=42
innodb_change_buffering=all
innodb_change_buffer_max_size=30

secure-file-priv=''

[mysqldump]
quick
max_allowed_packet = 24M

[mysql]
auto-rehash #使用TAB键自动补全

[mysqld_safe]
open-files-limit = 65536

从库：

[mysqld]

server-id = 2233
port = 13306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
pid-file=/usr/local/mysql/data/mysql.pid
skip-external-locking
skip_name_resolve = 0
skip-slave-start
transaction_isolation = REPEATABLE-READ
character-set-server = utf8
collation-server = utf8_general_ci
init_connect='SET NAMES utf8'
lower_case_table_names = 1
max_connections = 2000
max_connect_errors = 1000
max_allowed_packet = 24M
open_files_limit=65536
max_prepared_stmt_count=1048576
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_size = 128M
query_cache_type = 1

query_cache_limit=4M
sort_buffer_size = 2M　　　　#线程缓存
binlog_cache_size = 5242880
back_log = 130

log_timestamps=SYSTEM

log-error=/usr/local/mysql/data/mysql-err.log
log-bin=mysql-bin

binlog_rows_query_log_events=1

log_bin_trust_function_creators=1

table_open_cache=2000
innodb_open_files=1024
binlog_format = row

relay_log_recovery = 1
relay-log=mysql-relay
slave_skip_errors = all
slave_net_timeout=60
#replicate-do-db =
replicate-ignore-db = mysql
#并行复制参数
slave-parallel-type=LOGICAL_CLOCK

#slave_preserve_commit_order开启时slave-parallel-typ必须为LOGICAL_CLOCK，保证顺序执行语句。

slave_preserve_commit_order=1
slave-parallel-workers=8
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode=ON

#将mster的记录到slave的二进制日志文件中
log-slave-updates=ON
enforce-gtid-consistency

innodb_flush_log_at_trx_commit = 1
sync_binlog = 0
innodb_buffer_pool_size = 6G
innodb_file_per_table=1
innodb_data_file_path = ibdata1:12M;ibdata2:1G:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size=512M
innodb_log_files_in_group=3
innodb_log_buffer_size = 128M
innodb_io_capacity=800
innodb_adaptive_flushing=1
innodb_support_xa = ON　　　　　　　
innodb_read_io_threads=12
innodb_write_io_threads=12
#auto_increment_offset=1
#auto_increment_increment=2

innodb_lock_wait_timeout = 100
innodb_large_prefix = 1
innodb_purge_threads = 4
#innodb_print_all_deadlocks = 1
innodb_buffer_pool_dump_at_shutdown=on
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_now=ON
innodb_buffer_pool_load_now=ON
innodb_thread_concurrency=0
innodb_read_ahead_threshold=42
innodb_change_buffering=all
innodb_change_buffer_max_size=30

secure-file-priv=''

[mysqldump]
quick
max_allowed_packet = 24M

[mysql]
auto-rehash #使用TAB键自动补全

[mysqld_safe]
open-files-limit = 65536

posted on 2018-04-12 10:59  叶落千尘 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/magmell/p/8806259.html