mysql数据库读写分离_MySQL

grant replication slave on *.* to 'rep'@'172.17.1.%' identified by '12QAZXCvfr34';

change master to master_host='172.17.1.150',master_user='rep',master_password='12QAZXCvfr34',master_log_file='mysql-bin.000001',master_log_pos=107;

master配置：

[mysql]
port = 3306
socket = /mysql/mysqld.sock
prompt="\u@\h \d>"

[mysqld]
#Instance
server-id = 44170
port = 3306
socket = /mysql/mysqld.sock
pid-file = /mysql/mysqld.pid
datadir = /mysql/data/
tmpdir = /tmp/
log-error = /mysql/mysqld.err

#Bin-log parameter
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/binlog/mysql-bin.index
binlog_cache_size = 12m
binlog_format = mixed
sync_binlog = 0
max_binlog_cache_size = 1024m
max_binlog_size = 512m
expire_logs_days = 7

#Server parameter
skip_name_resolve
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'
max_allowed_packet = 64m
max_connections = 8192
table_open_cache = 1024
open_files_limit = 8192
thread_cache_size = 512
query_cache_size = 0
tmp_table_size = 512m
max_heap_table_size = 2048m

#MyISAM parameter
skip-external-locking
join_buffer_size = 16m
sort_buffer_size = 16m
read_buffer_size = 64m
read_rnd_buffer_size = 64m
key_buffer_size = 16384m

#Innodb parameter
innodb_data_file_path = ibdata1:1024m:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 64m
innodb_log_files_in_group = 2
innodb_log_buffer_size = 4m
innodb_flush_log_at_trx_commit = 2

innodb_buffer_pool_size = 256m
innodb_additional_mem_pool_size = 16m
innodb_io_capacity = 200
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 30

long_query_time = 3
slow_query_log = ON

#Slave GTID
gtid-mode = on
log-slave-updates = true
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1

[mysqldump]
quick
max_allowed_packet = 64m
socket=/mysql/mysqld.sock

[myisamchk]
key_buffer_size = 2048m
sort_buffer_size = 256m
read_buffer = 256m
write_buffer = 64m

[mysqlhotcopy]
socket = /mysql/mysqld.sock

[mysqladmin]
port = 3306
socket = /mysql/mysqld.sock

slave配置：

[mysql]
port = 3306
socket = /mysql/mysqld.sock
prompt="\u@\h \d>"

[mysqld]
#Instance
server-id = 44170
port = 3306
socket = /mysql/mysqld.sock
pid-file = /mysql/mysqld.pid
datadir = /mysql/data/
tmpdir = /tmp/
log-error = /mysql/mysqld.err

#Bin-log parameter
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/binlog/mysql-bin.index
relay-log = /mysql/binlog/relay-log
relay-log-index = /mysql/binlog/relay-log.index
binlog_cache_size = 12m
binlog_format = mixed
sync_binlog = 0
max_binlog_cache_size = 1024m
max_binlog_size = 512m
expire_logs_days = 7

#Server parameter
skip_name_resolve
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'
max_allowed_packet = 64m
max_connections = 8192
table_open_cache = 1024
open_files_limit = 8192
thread_cache_size = 512
query_cache_size = 0
tmp_table_size = 512m
max_heap_table_size = 2048m

#MyISAM parameter
skip-external-locking
join_buffer_size = 16m
sort_buffer_size = 16m
read_buffer_size = 64m
read_rnd_buffer_size = 64m
key_buffer_size = 16384m

#Innodb parameter
innodb_data_file_path = ibdata1:1024m:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 64m
innodb_log_files_in_group = 2
innodb_log_buffer_size = 4m
innodb_flush_log_at_trx_commit = 2

innodb_buffer_pool_size = 256m
innodb_additional_mem_pool_size = 16m
innodb_io_capacity = 200
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 30

long_query_time = 3
slow_query_log = ON

#Slave GTID
gtid-mode = on
log-slave-updates = true
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1

[mysqldump]
quick
max_allowed_packet = 64m
socket=/mysql/mysqld.sock

[myisamchk]
key_buffer_size = 2048m
sort_buffer_size = 256m
read_buffer = 256m
write_buffer = 64m

[mysqlhotcopy]
socket = /mysql/mysqld.sock

[mysqladmin]
port = 3306
socket = /mysql/mysqld.sock