The following steps are to be followed to setup replication of an existing MySQL Database :

1. Login To the Existing mysql database and issue the following command :

reset master;

FLUSH TABLES WITH READ LOCK;

Master Show Status;

Note the Binary Log Position and the Binary Log name from the result of above command.

mysqldump –all-databases –lock-all-tables  > dbdump.db

UNLOCK TABLES;

2. Now you can copy the dump to the slave machine, import it and then issue the following command :

CHANGE MASTER TO MASTER_HOST=’masterservername or ip’, MASTER_LOG_FILE=’binarylogname’, MASTER_LOG_POS=’binarylogposition from step  above’;

Note : If the server is already a slave, stop slave, import the database , execute the command above and  then start the slave.

You can also compress the dump by issuing the following command :

mysqldump –user=abc –password=xyz | gzip > dump.sql.gz

In order to import the compressed backup issue :
zcat dump.sql.gz | mysql –user=abc –password=xyz

The mysqldump process lockes up “FLUSH TABLES WITH READ LOCK;” as described above  this would only happen for databases that contained an InnoDB table.

If you are having this issue you can fix it my adding the –single-transaction argument to your mysqldump call.

Additional Tips and Tricks :

If you are starting a previously master replication server after recovery. You might want to start the mysql server wiwth the argument : –skip-slave-start (type skip master start option, not tested, need to explore  )

If you need to start slave with a fresh copy of the master database don’t forget to issue : STOP SLAVE and RESET SLAVE before issuing CHANGE MASTER before starting the SLAVE.

Changing a slave to become a master :

mysql> CHANGE MASTER TO
->     MASTER_HOST=’master_host_name’,
->     MASTER_USER=’replication_user_name’,
->     MASTER_PASSWORD=’replication_password’,
->     MASTER_LOG_FILE=’recorded_log_file_name’,
->     MASTER_LOG_POS=recorded_log_position;

Note : If you wish to ignore some database enter :

replicate-ignore-db = dbname (or) replicate-ignore-db = dbname1, dbname2, …, dbnameN