Master/Slave - trucate master table

Good day everyone...

I am working with a master/slave set up, and of course, we started it once
the database tables were a combined size of 60gb+

We've got it running and in sync now, (stopped the master, deleted the bin
files, rsync'd the database tables and restarted the master) and all has
been fine for the last week... until today.

We stopped the mysql process on the slave, and did a backup (due to size, we
just rsync and backup the rsync) of the table files. Took about 40 minutes
to do (still not sure why it was that long, but anyway), and then we
restarted the slave mysql. We're getting an error now. Apparently one of
the tables is out of sync now? What appears to have happened, is that when
the slave was down, i truncated a table on the master.

I've read that this sometimes causes errors, is this a bug? Or a is there
something I should do differently?


Steven Staples



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

Re: Master/Slave - trucate master table

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

Does START SLAVE fail?

If so, what is the output of SHOW SLAVE STATUS?

JW

On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples wrote:

> Good day everyone...
>
> I am working with a master/slave set up, and of course, we started it once
> the database tables were a combined size of 60gb+
>
> We've got it running and in sync now, (stopped the master, deleted the bin
> files, rsync'd the database tables and restarted the master) and all has
> been fine for the last week... until today.
>
> We stopped the mysql process on the slave, and did a backup (due to size,
> we
> just rsync and backup the rsync) of the table files. Took about 40 minutes
> to do (still not sure why it was that long, but anyway), and then we
> restarted the slave mysql. We're getting an error now. Apparently one of
> the tables is out of sync now? What appears to have happened, is that
> when
> the slave was down, i truncated a table on the master.
>
> I've read that this sometimes causes errors, is this a bug? Or a is there
> something I should do differently?
>
>
> Steven Staples
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6d6481529fd2f048111a3fb--

RE: Master/Slave - trucate master table

I don't have the error anymore, but the slave will start, but when you show
slave status, it shows that there is a failure, and the failure was, a
duplicate primary key.

What i did to fix/bandaid it, was i truncated the table on the slave, and
restarted the slave again... then i did a delete on the master, where ID <
XXXX.




Steven Staples


-----Original Message-----
From: Johnny Withers [mailto:johnny@pixelated.net]
Sent: March 5, 2010 1:11 PM
To: Steven Staples
Cc: mysql@lists.mysql.com
Subject: Re: Master/Slave - trucate master table

Does START SLAVE fail?

If so, what is the output of SHOW SLAVE STATUS?

JW

On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples wrote:

> Good day everyone...
>
> I am working with a master/slave set up, and of course, we started it once
> the database tables were a combined size of 60gb+
>
> We've got it running and in sync now, (stopped the master, deleted the bin
> files, rsync'd the database tables and restarted the master) and all has
> been fine for the last week... until today.
>
> We stopped the mysql process on the slave, and did a backup (due to size,
> we
> just rsync and backup the rsync) of the table files. Took about 40
minutes
> to do (still not sure why it was that long, but anyway), and then we
> restarted the slave mysql. We're getting an error now. Apparently one of
> the tables is out of sync now? What appears to have happened, is that
> when
> the slave was down, i truncated a table on the master.
>
> I've read that this sometimes causes errors, is this a bug? Or a is there
> something I should do differently?
>
>
> Steven Staples
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10
02:34:00


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

Re: Master/Slave - trucate master table

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

did u stop the slave process before stopping mysql on slave.
Did u do the below before stopping the mysql on slave?
slave stop;
show slave status\G;

regards
anandkl

On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples wrote:

> I don't have the error anymore, but the slave will start, but when you show
> slave status, it shows that there is a failure, and the failure was, a
> duplicate primary key.
>
> What i did to fix/bandaid it, was i truncated the table on the slave, and
> restarted the slave again... then i did a delete on the master, where ID <
> XXXX.
>
>
>
>
> Steven Staples
>
>
> -----Original Message-----
> From: Johnny Withers [mailto:johnny@pixelated.net]
> Sent: March 5, 2010 1:11 PM
> To: Steven Staples
> Cc: mysql@lists.mysql.com
> Subject: Re: Master/Slave - trucate master table
>
> Does START SLAVE fail?
>
> If so, what is the output of SHOW SLAVE STATUS?
>
> JW
>
> On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples wrote:
>
> > Good day everyone...
> >
> > I am working with a master/slave set up, and of course, we started it
> once
> > the database tables were a combined size of 60gb+
> >
> > We've got it running and in sync now, (stopped the master, deleted the
> bin
> > files, rsync'd the database tables and restarted the master) and all has
> > been fine for the last week... until today.
> >
> > We stopped the mysql process on the slave, and did a backup (due to size,
> > we
> > just rsync and backup the rsync) of the table files. Took about 40
> minutes
> > to do (still not sure why it was that long, but anyway), and then we
> > restarted the slave mysql. We're getting an error now. Apparently one
> of
> > the tables is out of sync now? What appears to have happened, is that
> > when
> > the slave was down, i truncated a table on the master.
> >
> > I've read that this sometimes causes errors, is this a bug? Or a is there
> > something I should do differently?
> >
> >
> > Steven Staples
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10
> 02:34:00
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--000e0cd72d66d163c504814cc89f--

RE: Master/Slave - trucate master table

Technically, shouldn't the slave be able to "shutdown" and then catch back
up?
What if the slave lost power and shut down... and when the power came back,
shouldn't the slave restart, get the bin logs, and "catchup"?

From now on, I wont truncate any tables, i will just delete from the table
where the ID < XXXX and then reset the ID to 0 or 1... and then delete
everything from XXXX and beyond

I was just wondering if this was a bug, or if there was something wrong with
what I did?


Steven Staples


-----Original Message-----
From: Ananda Kumar [mailto:anandkl@gmail.com]
Sent: March 8, 2010 11:45 AM
To: Steven Staples
Cc: Johnny Withers; mysql@lists.mysql.com
Subject: Re: Master/Slave - trucate master table

did u stop the slave process before stopping mysql on slave.
Did u do the below before stopping the mysql on slave?
slave stop;
show slave status\G;

regards
anandkl

On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples wrote:

> I don't have the error anymore, but the slave will start, but when you
show
> slave status, it shows that there is a failure, and the failure was, a
> duplicate primary key.
>
> What i did to fix/bandaid it, was i truncated the table on the slave, and
> restarted the slave again... then i did a delete on the master, where ID <
> XXXX.
>
>
>
>
> Steven Staples
>
>
> -----Original Message-----
> From: Johnny Withers [mailto:johnny@pixelated.net]
> Sent: March 5, 2010 1:11 PM
> To: Steven Staples
> Cc: mysql@lists.mysql.com
> Subject: Re: Master/Slave - trucate master table
>
> Does START SLAVE fail?
>
> If so, what is the output of SHOW SLAVE STATUS?
>
> JW
>
> On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples wrote:
>
> > Good day everyone...
> >
> > I am working with a master/slave set up, and of course, we started it
> once
> > the database tables were a combined size of 60gb+
> >
> > We've got it running and in sync now, (stopped the master, deleted the
> bin
> > files, rsync'd the database tables and restarted the master) and all has
> > been fine for the last week... until today.
> >
> > We stopped the mysql process on the slave, and did a backup (due to
size,
> > we
> > just rsync and backup the rsync) of the table files. Took about 40
> minutes
> > to do (still not sure why it was that long, but anyway), and then we
> > restarted the slave mysql. We're getting an error now. Apparently one
> of
> > the tables is out of sync now? What appears to have happened, is that
> > when
> > the slave was down, i truncated a table on the master.
> >
> > I've read that this sometimes causes errors, is this a bug? Or a is
there
> > something I should do differently?
> >
> >
> > Steven Staples
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10
> 02:34:00
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/08/10
02:34:00


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