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