Creating a MySQL slave from a running master

 Creating a MySQL slave from a running master

First, start by reading the online documentation. The MySQL documentation is well written and accurate. It goes through all of the steps in a good level of detail except for one. Unfortunately, that one step is critical.
To start MySQL replication, you need your new slave server to have an exact snapshot of the master at a point in time. MySQL recommends shutting down the master to take a backup. That sounds nice, but I can't afford several hours of downtime. The naive approach is just to use mysqldump to export a copy of the master and load it on the slave server. This works if you only have one database. With multiple database, you'll end up with inconsistent data. Mysqldump will dump data from each database on the server in a different transaction. That means that your export will have data from a different point in time for each database.
Instead of dumping all of the data at once, I dump my databases one at a time. Once the first dump completes, I then dump the next database. All databases are dumped using

mysqldump -u root -e -q --single-transaction --master-data database_name

Once I have dumped all of the databases on the server, I load the slave by importing the first database . 

I configure replication to only process replication events for this database by setting



 

replicate-wild-do-table= database_name.%




Next, I find out what the master log position is for my next import.NIt's in the first 25 lines of the MySQL 

dmp. Once I have that, I start the slave and tell mysql to replicate until until this position. You can


do this by passing the until argument to start slave. For example:







START SLAVE UNTIL MASTER_LOG_FILE='bin.000029', MASTER_LOG_POS=651322976;

Now, the database will be in the same state as when I dumped the next database. I then load that export. Once the load is done, I shutdown mysql and add a record for the newly import database to the replication setup. It will now look like:

replicate-wild-do-table= database_name.%
replicate-wild-do-table= another_db.%

I then start mysql and immediately stop the slave. I can the repeat as necessary, running replication until the same time as the previous export, exporting the data then adding the new database to replication.

This process allows me to easily build a new slave server from an existing MySQL master with no downtime.




Reference

Creating a MySQL slave from a running master