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;
#-----
# 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/