Mysql主从复制的配置
一、 my.ini配置文件的修改：
1、 在主服务器上修改：
[mysqld]
#datadir=F:/Application/Mysql5.6/data
port=5616
event_scheduler=ON
max_allowed_packet = 16M
skip-name-resolve
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_file_per_table=1
innodb_file_format = Barracuda
innodb_open_files=1000
innodb_buffer_pool_size=2188M
innodb_log_file_size=50M
max_connections=500
max_connect_errors=5000
max_prepared_stmt_count=50000
innodb_print_all_deadlocks = 1
innodb_lock_wait_timeout = 60
#table_cache=2048
transaction_isolation=REPEATABLE-READ
loose-skip-external-locking
innodb_flush_log_at_trx_commit=2
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
sort_buffer_size=2M
tmp_table_size=64M
max_heap_table_size=64M
slow_query_log=off
slow_query_log_file = slow-queries.log
#log-queries-not-using-indexes
log-bin=matster-bin
log-bin-index=matster-bin.index
server-id=1
expire-logs-days=3
max_binlog_size = 512M
log_bin_trust_function_creators=1
query_cache_type=1
query_cache_size=128M
query_cache_limit=128M
innodb_read_io_threads=8
innodb_write_io_threads=8
[client]
port=5616
#default-character-set=gbk
[mysqldump]
quick
max_allowed_packet = 16M
2、 在从服务器上修改：
[mysqld]
#basedir=D:/Application/mysql-5.6.16-slave
#datadir=D:/Application/mysql-5.6.16-slave/data
port=5600
event_scheduler=ON
max_allowed_packet = 16M
skip-name-resolve
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_file_per_table=1
innodb_file_format = Barracuda
innodb_open_files=1000
innodb_buffer_pool_size=1023M
innodb_log_file_size=50M
max_connections=500
max_connect_errors=5000
max_prepared_stmt_count=50000
innodb_print_all_deadlocks = 1
innodb_lock_wait_timeout = 60
#table_cache=2048
transaction_isolation=REPEATABLE-READ
loose-skip-external-locking
innodb_flush_log_at_trx_commit=2
#binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
sort_buffer_size=2M
tmp_table_size=64M
max_heap_table_size=64M
slow_query_log=off
slow_query_log_file = slow-queries.log
#log-queries-not-using-indexes
#log-bin=matster-bin
#log-bin-index=matster-bin.index
server-id=160
#expire-logs-days=3
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#max_binlog_size = 512M
#log_bin_trust_function_creators=1
query_cache_type=1
query_cache_size=128M
query_cache_limit=128M
innodb_read_io_threads=8
innodb_write_io_threads=8
[client]
port=5600
#default-character-set=gbk
[mysqldump]
quick
max_allowed_packet = 16M
二、在主服务器上创建复制用户
DROP USER repl_user;
CREATE USER repl_user ;
GRANT replication slave on *.* to repl_user IDENTIFIED by ‘fz‘;
flush privileges;
show master status;
三、在从服务器上连接到主服务器
change master to master_host=‘10.53.2.18‘,master_port=5616,master_user=‘repl_user‘,master_password=‘fz‘;
START SLAVE;
SHOW SLAVE STATUS;
四、如果执行SHOW SLAVE STATUS后显示：
Slave_io_Running:Yes
Salve_SQL_Running:Yes
则说明复制机制已经正常启动运行。