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

Comments

Popular posts from this blog

Wowza 4.0.1 (Wowza Streaming Engine) Released!

Charles, Java Web Debugging Proxy