migration via replication for large DB?

------=_NextPart_000_00EB_01CB2C44.1A341410
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hello.

I need to migrate a master and slave to new hardware. The DB is approx =
50G on disk and my time window for downtime is approximately 4 hours. =20

My question is, is it advisable to do a mysqldump from the old master =
and then load on the new master and slave, or is it faster to just set =
the new master up as a slave, and when it catches up to the old master I =
"flip the switch?" The catch is that the new hardware is in a different =
datacenter.

In other words, which is faster: dump and load or replication over the =
Internet?

Thanks for your time.






------=_NextPart_000_00EB_01CB2C44.1A341410--

Re: migration via replication for large DB?

On Sun, Jul 25, 2010 at 12:55 PM, Geoff Galitz wrote:
>
> Hello.
>
> I need to migrate a master and slave to new hardware. =A0The DB is approx=
50G on disk and my time window for downtime is approximately 4 hours.
>
> My question is, is it advisable to do a mysqldump from the old master and=
then load on the new master and slave, or is it faster to just set the new=
master up as a slave, and when it catches up to the old master I "flip the=
switch?" =A0The catch is that the new hardware is in a different datacente=
r.
>
> In other words, which is faster: dump and load or replication over the In=
ternet?
>
> Thanks for your time.
>


There may be significantly better options available to you.

What version are you coming from and what version are you to?

What engines do you use (Innodb, MyISAM,etc)?

Is the data directory currently mounted on a lvm volume?

--
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: migration via replication for large DB?

--e0cb4e887f0fe94a11048c451e91
Content-Type: text/plain; charset=ISO-8859-1

Hello Goeff,

Data Import might take some time (considering 50 GB) if the majority of the
tables are of type "INNODB". If yes, 4 hours should not be enough. If its
"MYISAM", you can go ahead (Provided you choose data import to replication).

Another suggestion would be :-

Take a FULL "tar" of the MYSQL Data Directory and push it to the NEW server
and "untar" and start mysql (take the master status of the probable Master
Server, for replication and bringing the new server to sync with its
Master). I think this should be one of the quickest way to do it.

Please let me know how it goes if you decide to do it this way.

Cheers,
Anirudh Sundar

On Mon, Jul 26, 2010 at 9:56 AM, Rob Wultsch wrote:

> On Sun, Jul 25, 2010 at 12:55 PM, Geoff Galitz wrote:
> >
> > Hello.
> >
> > I need to migrate a master and slave to new hardware. The DB is approx
> 50G on disk and my time window for downtime is approximately 4 hours.
> >
> > My question is, is it advisable to do a mysqldump from the old master and
> then load on the new master and slave, or is it faster to just set the new
> master up as a slave, and when it catches up to the old master I "flip the
> switch?" The catch is that the new hardware is in a different datacenter.
> >
> > In other words, which is faster: dump and load or replication over the
> Internet?
> >
> > Thanks for your time.
> >
>
>
> There may be significantly better options available to you.
>
> What version are you coming from and what version are you to?
>
> What engines do you use (Innodb, MyISAM,etc)?
>
> Is the data directory currently mounted on a lvm volume?
>
> --
> Rob Wultsch
> wultsch@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
>
>

--e0cb4e887f0fe94a11048c451e91--

Re: migration via replication for large DB?

--00c09f9729426f6ac8048c4725fc
Content-Type: text/plain; charset=ISO-8859-1

>
> Another suggestion would be :-
>
> Take a FULL "tar" of the MYSQL Data Directory and push it to the NEW server
> and "untar" and start mysql (take the master status of the probable Master
> Server, for replication and bringing the new server to sync with its
> Master). I think this should be one of the quickest way to do it.
>

You can optimize this by :
copy MYSQL Data Directory from old server to new server (*without stopping
MySQL Server on source, but make sure on target MySQL service is not running
* )

Then during the maintenance window , Rsync the MYSQL Data Directory (*Make
sure MySQL services is stopped in source and target server*). This will
check and copy only changed data from source to target.

Thanks,


On Mon, Jul 26, 2010 at 12:43 PM, Anirudh Sundar
wrote:

> Hello Goeff,
>
> Data Import might take some time (considering 50 GB) if the majority of the
> tables are of type "INNODB". If yes, 4 hours should not be enough. If its
> "MYISAM", you can go ahead (Provided you choose data import to
> replication).
>
> Another suggestion would be :-
>
> Take a FULL "tar" of the MYSQL Data Directory and push it to the NEW server
> and "untar" and start mysql (take the master status of the probable Master
> Server, for replication and bringing the new server to sync with its
> Master). I think this should be one of the quickest way to do it.
>
> Please let me know how it goes if you decide to do it this way.
>
> Cheers,
> Anirudh Sundar
>
> On Mon, Jul 26, 2010 at 9:56 AM, Rob Wultsch wrote:
>
> > On Sun, Jul 25, 2010 at 12:55 PM, Geoff Galitz wrote:
> > >
> > > Hello.
> > >
> > > I need to migrate a master and slave to new hardware. The DB is approx
> > 50G on disk and my time window for downtime is approximately 4 hours.
> > >
> > > My question is, is it advisable to do a mysqldump from the old master
> and
> > then load on the new master and slave, or is it faster to just set the
> new
> > master up as a slave, and when it catches up to the old master I "flip
> the
> > switch?" The catch is that the new hardware is in a different
> datacenter.
> > >
> > > In other words, which is faster: dump and load or replication over the
> > Internet?
> > >
> > > Thanks for your time.
> > >
> >
> >
> > There may be significantly better options available to you.
> >
> > What version are you coming from and what version are you to?
> >
> > What engines do you use (Innodb, MyISAM,etc)?
> >
> > Is the data directory currently mounted on a lvm volume?
> >
> > --
> > Rob Wultsch
> > wultsch@gmail.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
> >
> >
>



--
Best Regards,

Prabhat Kumar
MySQL DBA
Mobile : 91-9987681929

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--00c09f9729426f6ac8048c4725fc--