On the master, enable binary logging and set a unique server ID. Each MySQL server being replicated to must have a unique ID also. Modify my.cnf with the following, and restart MySQL.

[mysqld]
log-bin=mysql-bin
server-id=1

On the Slave, edit my.cnf and add a unique server ID for the slave.

[mysqld]
server-id=2

On the master, add a user for replication that has the ‘replication slave’ privilege. This should be a separate user that can only be accessed from specific hosts, for security purposes. More info at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-repuser.html

Get master bin log coordinates and lock tables to prevent changes whilst synching…

Start a session on the master and run:

FLUSH TABLES WITH READ LOCK;

Using a different session, run SHOW MASTER STATUS to get the current bin log file name and position. Note these down as they are used in the ‘CHANGE MASTER TO’ slave statement later.

On the master, dump the database you’re wanting to replicate and scp it to the slave server.

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+

mysqldump': mysqldump -u root -p TheDb > TheDb.sql
scp TheDb.sql [email protected]:

On the slave, import the DB dump from the master:

mysql -u dbuser -p < TheDb.sql

After that, run a ‘CHANGE MASTER TO’ statement:

CHANGE MASTER TO
MASTER_HOST='1.2.3.4',
MASTER_USER='replication_user',
MASTER_PASSWORD='********'
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345,
MASTER_CONNECT_RETRY=10;


and then start replication with:

START SLAVE;


You should now exit the session on the master that was locking the tables (or use the unlock tables statement).

Replication should now be setup