The following steps are to be followed to setup replication of an existing MySQL Database :
- Login To the Existing mysql database and issue the following command :
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
- 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
Note : If you wish to ignore some database enter :
replicate-ignore-db = dbname (or) replicate-ignore-db = dbname1, dbname2, …, dbnameN