MySQLdump and restore on very large DB

Hi,

I have a very large database > 1.5GB running on 2 servers all MyISAM
tables. One is a staging server and one the live server.

On the staging server the data is manipulated, so rows are modified/
removed/added constantly. The live server has a copy of these tables
and other tracking tables that are not on the staging server.

Once a day I update the live data with the data from the staging
server. I use MySQL dump and then a basic import to overwrite the
existing tables with the new data.

This, however is taking extremely long and the DB table on the live
server is unavailable during the import which is not ideal.

Is there a better way to do this? Is replication the answer? I've
never used it and wouldn't know where to start. The only issue being
that the live server contains some tables that are not on the staging
server and I would like to keep it that way.

Any expert help would be greatly appreciated. Thanks

tucj7 <kevin.tucker@gmail.com> wrote:
>
> I have a very large database > 1.5GB running on 2 servers all MyISAM
> tables. One is a staging server and one the live server.

Hehe. 1.5GB is not considered large :-)

> On the staging server the data is manipulated, so rows are modified/
> removed/added constantly. The live server has a copy of these tables
> and other tracking tables that are not on the staging server.
>
> Once a day I update the live data with the data from the staging
> server. I use MySQL dump and then a basic import to overwrite the
> existing tables with the new data.
>
> This, however is taking extremely long and the DB table on the live
> server is unavailable during the import which is not ideal.
>
> Is there a better way to do this? Is replication the answer?

Yes. And Yes.

> I've never used it and wouldn't know where to start.

Then start reading the manual:

http://dev.mysql.com/doc/refman/5.0/...ion-intro.html
http://dev.mysql.com/doc/refman/5.1/...ion-howto.html


some notes from me:

- I intentionally gave the link to 5.1 manual because replication
chapters have been restructured to be better understandable.

- I suggest to use the mysqldump --master-data method to get a
clean copy of your staging server (master) to your live server
(slave)

- There is no need to have the slave process running all time.
If you want to stay with daily synchronization, just start the
slave once a day (START SLAVE) and when it has replicated all
changes (seen in Seconds_Behind_Master from SHOW SLAVE STATUS)
then just stop it (STOP SLAVE)

- You should set a sensible max. binlog size on your master and/or
rotate the binlog (e.g. with FLUSH LOGS). There are more options
re. replication and binary log:

http://dev.mysql.com/doc/refman/5.0/...n-options.html
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

- If you stay with daily synchronization, you should setup a routine
like this:
1. FLUSH LOGS on master to start a new binlog
2. START SLAVE, wait until the slave reached the new binlog
3. STOP SLAVE
4. PURGE MASTER LOGS TO '<insert current binlog>'

> The only issue being
> that the live server contains some tables that are not on the staging
> server and I would like to keep it that way.

This is no problem and will work out of the box.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

== Quote from Axel Schwenke (axel.schwenke@gmx.de)'s article
> tucj7 <kevin.tucker@gmail.com> wrote:
> >
> > I have a very large database > 1.5GB running on 2 servers all MyISAM
> > tables. One is a staging server and one the live server.
> Hehe. 1.5GB is not considered large :-)
> > On the staging server the data is manipulated, so rows are modified/
> > removed/added constantly. The live server has a copy of these tables
> > and other tracking tables that are not on the staging server.
> >
> > Once a day I update the live data with the data from the staging
> > server. I use MySQL dump and then a basic import to overwrite the
> > existing tables with the new data.
> >
> > This, however is taking extremely long and the DB table on the live
> > server is unavailable during the import which is not ideal.
> >
> > Is there a better way to do this? Is replication the answer?
> Yes. And Yes.
> > I've never used it and wouldn't know where to start.
> Then start reading the manual:
> http://dev.mysql.com/doc/refman/5.0/...ion-intro.html
> http://dev.mysql.com/doc/refman/5.1/...ion-howto.html
> some notes from me:
> - I intentionally gave the link to 5.1 manual because replication
> chapters have been restructured to be better understandable.
> - I suggest to use the mysqldump --master-data method to get a
> clean copy of your staging server (master) to your live server
> (slave)
> - There is no need to have the slave process running all time.
> If you want to stay with daily synchronization, just start the
> slave once a day (START SLAVE) and when it has replicated all
> changes (seen in Seconds_Behind_Master from SHOW SLAVE STATUS)
> then just stop it (STOP SLAVE)
> - You should set a sensible max. binlog size on your master and/or
> rotate the binlog (e.g. with FLUSH LOGS). There are more options
> re. replication and binary log:
> http://dev.mysql.com/doc/refman/5.0/...n-options.html
> http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
> - If you stay with daily synchronization, you should setup a routine
> like this:
> 1. FLUSH LOGS on master to start a new binlog
> 2. START SLAVE, wait until the slave reached the new binlog
> 3. STOP SLAVE
> 4. PURGE MASTER LOGS TO '<insert current binlog>'
> > The only issue being
> > that the live server contains some tables that are not on the staging
> > server and I would like to keep it that way.
> This is no problem and will work out of the box.
> XL

Axel,
Do you by any chance have a generic script that does the 4 step synch process?

--
POST BY: lark with PHP News Reader

lark <hamzee@sbcglobal.net> wrote:
> == Quote from Axel Schwenke (axel.schwenke@gmx.de)'s article

<cut>

> Axel,
> Do you by any chance have a generic script that does the 4 step synch process?

Nope.

<joking>
If you buy a platinum support contract I will happily write you one
</joking>


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> lark <hamzee@sbcglobal.net> wrote:
>> == Quote from Axel Schwenke (axel.schwenke@gmx.de)'s article
>
> <cut>
>
>> Axel,
>> Do you by any chance have a generic script that does the 4 step synch process?
>
> Nope.
>
> <joking>
> If you buy a platinum support contract I will happily write you one
> </joking>
>
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

Axel, Axel, Axel:

where is the goodness of your heart?

actually, i didn't expect you to provide something like that. i just
wanted a pointer! but if you don't know of any, don't worry!

thanks