2-Way MySQL Replication

Some notes and commands I want to keep handy the next time I setup MySQL replication.

#-----
# SERVER_1: CHANGES TO my.cnf
#-----

server-id = 1
auto_increment_offset = 1

#-----
# SERVER_2: CHANGES TO my.cnf
#-----

server-id = 2
auto_increment_offset = 2

#-----
# SERVER_1 & SERVER_2: CHANGES TO my.cnf
#-----

auto_increment_increment = 10
replicate-do-db = mydb1
replicate-do-db = mydb2
log-bin = /var/lib/mysql/master-bin
log-bin-index = /var/lib/mysql/master-bin-index
relay-log = /var/lib/mysql/relay
relay-log-index = /var/lib/mysql/relay-log-index

#-----
# SERVER_1 & SERVER_2: GRANT PERMISSIONS
#-----

GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' IDENTIFIED BY 'mypassword';
GRANT REPLICATION CLIENT ON *.* TO 'replicate'@'%';
GRANT SUPER ON *.* TO 'replicate'@'%';
GRANT RELOAD ON *.* TO 'replicate'@'%';
GRANT SELECT ON *.* TO 'replicate'@'%';
GRANT DROP ON *.* TO 'replicate'@'%';
GRANT ALTER ON *.* TO 'replicate'@'%';
FLUSH PRIVILEGES;

#-----
# USEFUL COMMANDS
#-----

STOP SLAVE;
START SLAVE;
SHOW MASTER STATUS;
SHOW SLAVE STATUS \G;
SHOW BINLOG EVENTS;

#-----
# skip last replication error
#-----
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

#-----
# create fresh binlog file
#-----
FLUSH LOGS;

#-----
# CREATE SLAVE ON SERVER_2
#-----

CHANGE MASTER TO
  MASTER_HOST='SERVER_1_IP',
  MASTER_USER='replicate',
  MASTER_PASSWORD='mypassword',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=10;

#-----
# CREATE SLAVE ON SERVER_1
#-----

CHANGE MASTER TO
  MASTER_HOST='SERVER_2_IP',
  MASTER_USER='replicate',
  MASTER_PASSWORD='mypassword',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=10;

#-----
# CHANGE THE LOG FILE AND POSITION
#-----

CHANGE MASTER TO MASTER_LOG_FILE = 'master-bin.000038', MASTER_LOG_POS = 0;

also see this site for more mysql replication help tools:
http://www.maatkit.org/

Post new comment

  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <css>, <diff>, <drupal5>, <html>, <javascript>, <php>. Beside the tag style "<foo>" it is also possible to use "[foo]". PHP source code can also be enclosed in <?php ... ?> or <% ... %>.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
  • You may use [img:xx] tags to display uploaded files or images inline.
  • Allowed HTML tags: <b> <br> <p> <a> <strong> <cite> <em> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options