MySQL: Master/Slave config


just note


Master server config with bind-address, server-id, log_bin, binlog-do-db
Slave server config with bind-address, server-id, relay-log, log_bin, binlog-do-db


Samples

Master 1
bind-address = 202.xxx.xxx.11
server-id = 11
log_bin = /var/log/mysql/mysql-bin.log

Slave 1
bind-address = 202.xxx.xxx.21
server-id = 21
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = thaitv_web

Slave 2
bind-address = 202.xxx.xxx.22
server-id = 22
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = thaitv_web

Slave 3
bind-address = 202.xxx.xxx.23
server-id = 23
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = thaitv_web


Command 



Master servers 






STOP MASTER; 


RESET MASTER;


START MASTER;


SHOW MASTER STATUS;


SHOW PROCESSLIST; 


UNLOCK TABLES;


PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';


SET sql_log_bin = {0|1}


SHOW BINLOG EVENTS;


 




Slave servers 


STOP SLAVE;
RESET SLAVE;
START SLAVE;
SHOW SLAVE STATUS;
SHOW BINLOG EVENTS;


 


CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;


 


CHANGE MASTER TO
  RELAY_LOG_FILE='slave-relay-bin.006',
  RELAY_LOG_POS=4025;


 


CHANGE MASTER TO
  RELAY_LOG_FILE='myhost-bin.153',
  RELAY_LOG_POS=410,
  MASTER_HOST='some_dummy_string';
START SLAVE SQL_THREAD; 


 






  



Reference 
















Email Post





















Comments








Post a Comment