Mysql主从复制的配置

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

则说明复制机制已经正常启动运行。