Mysql主從複製的配置

標籤：style   os   io   strong   ar   for   檔案   art   cti   

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

則說明複製機制已經正常啟動運行。

Mysql主從複製的配置