MySql - create replication with minimal downtime

Question

I have a ~80GB MySql DB.

I want to create a replication on that DB while having the current DB as master and setting up a slave for it.

My main question is how can i move the data (all 80GB) of it from the master to the new slave with as minimal downtime as possible, preferably none.

my initial thought was to stop the DB (after taking the log position), and then copy the files from the mysqldata lib, and then re start the server but just copying the files would take ~2 hours.

any thoughts?

Answer 1

On July 8, 2011 I addressed a similar question. I wrote scripts that would zap binary logs and starting performing an rsync.

On June 16, 2011, I wrote a post contrasting doing an rsync versus using XtraBackup.

On May 23, 2011, I discussed what considerations to make when doing this kind of backup.

Rather than reinvent the wheel and rewrite in the information I already wrote in those posts, I simply provided the links to my own posts that address this question.

Please read them carefully.

Give it a Try !!!

CAVEAT

The only downtime in my rsync algorithm is when after you have performed multiple rsyncs as specified, you shutdown mysql, perform one more rsync, and then start up mysql.

I would like to clarify the reason for the shutdown:

When you shutdown mysql:

• All open MyISAM tables are closed, There is a header that marks how many file handles are open to the MyISAM table. That must be at zero(0) for the table to be OK. Otherwise, a closed MyISAM tables with a nonzero value in this header field marks the table as crashed and in need of a table repair. Shutting down mysql cleans all of that up.

• All InnoDB tables that have either data pages or index pages in the Buffer Pool that are marked dirty needs to be flushed to disk. Performing a shutdown triggers a full flush of the Buffer Pool. Naturally, the bigger the pool and the higher the number of dirty pages, the longer the Buffer Pool flush time will be. To shorten this phase of the mysqld's shutdown, run SET GLOBAL innodb_max_dirty_pages_pct = 0; before performing any of the rsyncs. All transactions are completed (either commited or rolled back).