windows服务器MySQL主从读写分离

windows服务器MySQL主从读写分离
1、主master.ini

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir =E:\mysql\mysql-5.7.13-winx64
datadir =E:\mysql\mysql-5.7.13-winx64\data
port = 3312
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

# file
pid-file= E:/mysql/my/log/mysql.pid                          
log_error=E:/mysql/my/log/mysql.err                          
socket  = E:/mysql/my/log/mysql.sock                         
character_set_server = utf8                         
skip-name-resolve  
#skip-grant-tables    
#bind-address=27.148.248.2                            
slow_query_log = 0                                   
slow_query_log_file = E:/mysql/my/log/slowq.log              
long_query_time = 1                                 

# session                                            
back_log = 8192                                                                     
max_allowed_packet = 3000M                            
max_connections = 1024                             
max_connect_errors = 10000                          
wait_timeout = 30  
interactive_timeout = 120                              
binlog_cache_size = 1M                               

table_open_cache = 1024                              
#thread_concurrency = 4                               
thread_cache_size = 128                              
#thread_handling = pool-of-threads                    
#thread_pool_high_prio_mode = none                    
#thread_pool_idle_timeout = 50                     
#thread_pool_oversubscribe = 20                       
#max_statement_time = 5000                           
max_execution_time = 50000

query_cache_type = 1                                
query_cache_size = 1024M                                 
query_cache_limit = 1024
key_buffer_size = 128M                               
myisam_sort_buffer_size = 16M                       
read_buffer_size = 8M                               
read_rnd_buffer_size = 4M                           
sort_buffer_size = 16M                              
join_buffer_size = 16M                               

# innodb
default_storage_engine = InnoDB                      
innodb_data_home_dir = "E:/mysql/my/log"                       
#innodb_data_file_path = ibdata1:256M:autoextend      
innodb_log_group_home_dir = "E:/mysql/my/log/"       
innodb_log_files_in_group = 2                        
innodb_log_file_size = 512M                         
innodb_log_buffer_size = 8M                         
innodb_flush_log_at_trx_commit = 1                   
#innodb_flush_method = O_DIRECT                      
innodb_lock_wait_timeout = 20                       
innodb_thread_concurrency = 8                     
innodb_buffer_pool_size = 4096M                        
#innodb_additional_mem_pool_size = 20M                
innodb_io_capacity = 1500                            
innodb_use_native_aio = 1                            
innodb_file_per_table = 1                            
innodb_open_files = 3000                            
innodb_print_all_deadlocks = 1                       

# others
memlock = 1        
transaction-isolation=READ-COMMITTED                                                               
                                   
ft_min_word_len = 1
low_priority_updates = 1
concurrent_insert = 1
lock_wait_timeout= 50


# replication mj-master
server-id = 1
#log-bin = mysql-bin
log-bin = "E:/mysql/my/mysql-bin"
binlog_format = row
expire_logs_days = 60
log_slave_updates = 1
relay_log = "E:/mysql/my/relay-bin"
#read_only = 1
sync_binlog = 1
slave-skip-errors=1062,1053,1146,1690

binlog-do-db=QQ1193379199_db 
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=QQ1193379199_db_20160703

replicate-do-db=QQ1193379199_db
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema

rpl_semi_sync_master_enabled=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqldump]
default-character-set = utf8                         #数据库字符集

2、从slave.ini

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir =F:\mysql_back\mysql-5.7.13-winx64
datadir =F:\mysql_back\mysql-5.7.13-winx64\data
port = 3506
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

# file
pid-file= F:/mysql_back/my/log/mysql.pid                          
log_error=F:/mysql_back/my/log/mysql.err                          
socket  = F:/mysql_back/my/log/mysql.sock                         
character_set_server = utf8                         
skip-name-resolve
#skip-grant-tables    
#bind-address=                            
slow_query_log = 0                                   
slow_query_log_file = F:/mysql_back/my/log/slowq.log              
long_query_time = 1                                 

# session                                            
back_log = 8192                                                                     
max_allowed_packet = 3200M                            
max_connections = 16000                             
max_connect_errors = 10000                          
wait_timeout = 50  
interactive_timeout = 80                              
binlog_cache_size = 1M                               

table_open_cache = 1024                              
#thread_concurrency = 16                               
thread_cache_size = 128                              
#thread_handling = pool-of-threads                    
#thread_pool_high_prio_mode = none                    
#thread_pool_idle_timeout = 50                     
#thread_pool_oversubscribe = 20                       
#max_statement_time = 5000                           
max_execution_time = 50000

query_cache_type = 1                                
query_cache_size = 1024M                                 
query_cache_limit = 1024
key_buffer_size = 128M                               
myisam_sort_buffer_size = 16M                       
read_buffer_size = 8M                               
read_rnd_buffer_size = 4M                           
sort_buffer_size = 16M                              
join_buffer_size = 16M                               

# innodb
default_storage_engine = InnoDB                      
innodb_data_home_dir = "F:/mysql_back/my/log"                       
#innodb_data_file_path = ibdata1:256M:autoextend      
innodb_log_group_home_dir = "F:/mysql_back/my/log/"       
innodb_log_files_in_group = 2                        
innodb_log_file_size = 512M                         
innodb_log_buffer_size = 8M                         
innodb_flush_log_at_trx_commit = 2                   
#innodb_flush_method = O_DIRECT                      
innodb_lock_wait_timeout = 20                       
innodb_thread_concurrency = 8                     
innodb_buffer_pool_size = 4096M                        
#innodb_additional_mem_pool_size = 20M                
innodb_io_capacity = 1500                            
innodb_use_native_aio = 1                            
innodb_file_per_table = 1                            
innodb_open_files = 3000                            
innodb_print_all_deadlocks = 1                       

# others
memlock = 1        
transaction-isolation=READ-COMMITTED                                                               
                                   
ft_min_word_len = 1
#low_priority_updates = 1
concurrent_insert = 1
lock_wait_timeout= 50

# replication mj-slave
server-id=2

#log-bin=mysql-bin
log-bin = "F:/mysql_back/my/mysql-bin"
binlog_format=row
expire_logs_days=60
log_slave_updates = 1
relay_log = "F:/mysql_back/my/relay-bin" 
#slave-skip-errors=1032,1062,1053,1146,1690
slave-skip-errors=1032,1048,1062,1114,1146,1396

replicate-do-db=QQ1193379199_db
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema

binlog-do-db=QQ1193379199_db
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on

rpl_semi_sync_slave_enabled=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqldump]
default-character-set = utf8                         #数据库字符集