mysql master-master

gistfile1.txt

REFERENCE:
http://hzcsky.blog.51cto.com/1560073/479476

# $Id: mysql-replication 335 2005-10-13 19:46:20Z sbalukoff $
# To set up bi-directional mysql replication:

# For the purposes of this document, we have two servers which will in the
# end be filling a co-master type relationship.  However, since when you set up
# this replication there will probably be one machine with all the data on it
# that needs to be brought into sync with the other machine, the machine
# with all the data will be considered the 'master', and the one being brought
# into the relationship will be considered the 'slave'.  Again, don't let
# the names fool you:  We're only using 'master' and 'slave' here to
# differentiate the machines.  In the end they will be in a true co-master
# relationship.

# Before you begin:
#
# Make sure that /etc/my.cnf is set to have binary and relay logging turned on
# on both the slave and the master.  (Verify the log files actually exist if
# they weren't set up with this, you will need to restart the server process once
# the config file has been changed.)  Pertinent config lines should look like:
#
# # This stuff is needed for replication purposes
#   server-id=2
#   log-bin=/var/lib/mysql/master-bin.log
#   log-bin-index = /var/lib/mysql/master-log-bin.index
#   log-error = /var/lib/mysql/master-error.log
#
#   relay-log = /var/lib/mysql/slave-relay.log
#   relay-log-info-file = /var/lib/mysql/slave-relay-log.info
#   relay-log-index = /var/lib/mysql/slave-relay-log.index
#
# In the above, the master and slave must have different server-id's.

# Make sure these machines' firewalls are set up to allow them talk to each
# other on tcp port 3306, or wherever your mysql daemon is listening.

# Set up the replication user on the master:
#
sudo mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%.cea.wsu.edu' IDENTIFIED BY 'replication_password';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'replicator'@'%.cea.wsu.edu';
mysql> GRANT SUPER ON *.* TO 'replicator'@'%.cea.wsu.edu';
mysql> GRANT RELOAD ON *.* TO 'replicator'@'%.cea.wsu.edu';
mysql> GRANT SELECT ON *.* TO 'replicator'@'%.cea.wsu.edu';

# Get a clean snapshot of the master's data:
mysql> FLUSH TABLES WITH READ LOCK;
# On the shell:
sudo tar cvf /tmp/mysql.bak /var/lib/mysql
# on my mysql prompt:
mysql> SHOW MASTER STATUS;
# Take note of what is said!  You'll need to enter the log file name and position later on.
mysql> UNLOCK TABLES;

# Copy the snapshot to the slave, and slurp it into the database:
scp /tmp/mysql.bak user@slave-server:/tmp
# on the slave
sudo /etc/init.d/mysqld stop
cd /
sudo tar xvf /tmp/mysql.bak
sudo /etc/init.d/mysqld start

# Start replication from master to slave:
# on the slave's mysql command line as root:
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name',
->     MASTER_USER='replicator',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

mysql> START SLAVE;

# Test the replication by creating a dummy table on the master...  it should be
# created on the slave

# On the master:
mysql> USE test;
mysql> CREATE TABLE foo (id INT);

# On the slave;
mysql> USE test;
mysql> SHOW TABLES;
# you should see the 'foo' table, eh.

# Start replication from slave to master:
# First, ensure that no updates are being made to the slave, by ensuring
# that no clients are connected to it, eh.  Do this however you see fit.
# Shutting down any local clients and adding firewall rules to prevent anyone but
# the master from connecting should do it.  Updates to the master can continue while
# you do this.

# Get the 'master' status from the slave:
# on the slave's mysql console:
mysql> SHOW MASTER STATUS;
# Make note of what you see!  Again, gonna need that logfile and position in the next step...

# Add the replication configuration to the master.
# On the master's mysql command line as root:
mysql> CHANGE MASTER TO MASTER_HOST='slave_host_name',
->     MASTER_USER='replicator',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

# Start replication on the master:
# on the master:
mysql> START SLAVE;

# Test bi-directional data flow by creating something on one server, and
# deleting it on the other, eh.  (Or just delete that 'foo' table we made
# earlier.)

# Slave:
mysql> use test;
mysql> drop table foo;

# Master:
mysql> use test;
mysql> SHOW TABLES;

# Test restarting each server individually, and then together
# to ensure there are no errors that show up in the logs when you do this.

# Be sure the check out the output of the following, on both master and slave:
mysql> SHOW SLAVE STATUS \G
# If you don't see the following in the list on both systems, you've got problems:
#   Slave_IO_Running: Yes
#   Slave_SQL_Running: Yes

# Enjoy the glow!