MySQL replication

MySQL replication sets up master and slave databases.  The slave database keeps itself synchronized with the master database in near real time.

Setting Up MySQL replication

In the master server [mysqld] section of /etc/my.cnf you should have something like:

[mysqld]
server-id=1
log-bin
log-warnings
log-slave-updates
skip-bdb

On the master server run:

GRANT REPLICATION SLAVE ON *.* TO 
mysql_slave@slaveserverip identified by 'somepassword'

In the slave MySQL server's /etc/my.cnf file you should have something like:

server-id       = 2
log-bin
# so user changes do not replicate to the slaves
replicate-ignore-db=mysql

Note that the server IDs need to be unique for all the servers in your mysql replication cluster.

On the master server run: SHOW MASTER STATUS.

It should output something similar to:

+-------------------+-----------+--------------+------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| hostxx-bin.000376 | 105373861 |              |                  | 
+-------------------+-----------+--------------+------------------+

On the slave server's MySQL client use some of the values from that command (specifically 'File' and 'Position') to run the following:

CHANGE MASTER TO MASTER_HOST='masterserverip'
, MASTER_USER='mysql_slave',  MASTER_PASSWORD='somepassword'
, MASTER_LOG_FILE='<File>'
, MASTER_LOG_POS=<Position>;

SLAVE START;

Further documentation can be found on the official MySQL site here: http://dev.mysql.com/doc/refman/5.0/en/replication.html