Mailing List Archive: DBMail: users

[Slightly OffTopic] Backing up dbmail ( error 2013 )

Index | Next | Previous | View Threaded

dan at entropy

Jan 11, 2009, 1:34 PM

Post #1 of 12 (1495 views)
Permalink

[Slightly OffTopic] Backing up dbmail ( error 2013 )

Hi all.

I've been alerted to an issue that my previous employer is having
backing up dbmail with the backup script I set up. If I do:

mysqldump --opt dbmail > dbmail.sql -p

... which used to work for years, I now get 'error 2013 - Lost
connection to MySQL server during query'. This occurs about a sixth of
the way through the backup ( 5GB of a 30GB database ). I've done
extensive 'check table' commands looking for corruption. There is
none. The database works fine other than not backing up. I've read
some suggestions about playing with max_packet_size on the server and
in the mysqldump command. My limited playing with this is not fixing
anything. Has anyone encountered this before / know of a solution?

Sorry about slightly off-topic email. If no-one knows what's up I can
post to the MySQL list, but I'm not subscribed any more .
Dan

jake at vapourforge

Jan 11, 2009, 3:30 PM

Post #2 of 12 (1428 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Dan wrote:
>
> Hi all.
>
> I've been alerted to an issue that my previous employer is having
> backing up dbmail with the backup script I set up. If I do:
>
> mysqldump --opt dbmail > dbmail.sql -p
>
> ... which used to work for years, I now get 'error 2013 - Lost
> connection to MySQL server during query'. This occurs about a sixth of
> the way through the backup ( 5GB of a 30GB database ). I've done
> extensive 'check table' commands looking for corruption. There is
> none. The database works fine other than not backing up. I've read
> some suggestions about playing with max_packet_size on the server and
> in the mysqldump command. My limited playing with this is not fixing
> anything. Has anyone encountered this before / know of a solution?
>
> Sorry about slightly off-topic email. If no-one knows what's up I can
> post to the MySQL list, but I'm not subscribed any more .
>
>
> Dan
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
I'm seeing it with a corrupt table.
check the syslog and mysq logs to see if its giving you any hints

fxp at corp

Jan 11, 2009, 11:20 PM

Post #3 of 12 (1420 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Just face the same with 20Gb InnoDB dbmail database.
My server with only 1Gb RAM all my tries with command line and my.cnf
doesn`t work.
The only solution i see - migrate from dbmail.

Dan :
>
> Hi all.
>
> I've been alerted to an issue that my previous employer is having
> backing up dbmail with the backup script I set up. If I do:
>
> mysqldump --opt dbmail > dbmail.sql -p
>
> ... which used to work for years, I now get 'error 2013 - Lost
> connection to MySQL server during query'. This occurs about a sixth of
> the way through the backup ( 5GB of a 30GB database ). I've done
> extensive 'check table' commands looking for corruption. There is
> none. The database works fine other than not backing up. I've read
> some suggestions about playing with max_packet_size on the server and
> in the mysqldump command. My limited playing with this is not fixing
> anything. Has anyone encountered this before / know of a solution?
>
> Sorry about slightly off-topic email. If no-one knows what's up I can
> post to the MySQL list, but I'm not subscribed any more .
>
>
> Dan
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

7crewz at gmail

Jan 11, 2009, 11:29 PM

Post #4 of 12 (1422 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

What do mean 20gb data and 1gb ram not enough ?
Sent from 7crewz BlackBerry® wireless device via Vodafone-Celcom Mobile.

-----Original Message-----
From: Андрей Юртайкин <fxp [at] corp>

Date: Mon, 12 Jan 2009 10:20:48
To: DBMail mailinglist<dbmail [at] dbmail>
Subject: Re: [Dbmail] [Slightly OffTopic] Backing up dbmail ( error 2013 )

Just face the same with 20Gb InnoDB dbmail database.
My server with only 1Gb RAM all my tries with command line and my.cnf
doesn`t work.
The only solution i see - migrate from dbmail.

Dan :
>
> Hi all.
>
> I've been alerted to an issue that my previous employer is having
> backing up dbmail with the backup script I set up. If I do:
>
> mysqldump --opt dbmail > dbmail.sql -p
>
> ... which used to work for years, I now get 'error 2013 - Lost
> connection to MySQL server during query'. This occurs about a sixth of
> the way through the backup ( 5GB of a 30GB database ). I've done
> extensive 'check table' commands looking for corruption. There is
> none. The database works fine other than not backing up. I've read
> some suggestions about playing with max_packet_size on the server and
> in the mysqldump command. My limited playing with this is not fixing
> anything. Has anyone encountered this before / know of a solution?
>
> Sorry about slightly off-topic email. If no-one knows what's up I can
> post to the MySQL list, but I'm not subscribed any more .
>
>
> Dan
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

dan at entropy

Jan 13, 2009, 2:06 PM

Post #5 of 12 (1413 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

On Wed, 2009-01-14 at 08:31 +1000, Josh Marshall wrote:

> Hi,

Hi Josh. Thanks for the suggestions & script. Very nice. I'll try it out on
my home server and see how the backup / restore procedure works.

> I use a different method of backing up MySQL. I use a separate server
> that's a slave to the master and stop slave ; backup ; start slave. That
> gives no performance hit to the main db (I can backup during peak hours)
> and always gives a consistent snapshot.

Unfortunately we're dealing with people who can't even get their shit
together sufficiently to have 1 decent server. An additional server is out
of the question :(

> * The backup and restore times are faster (Good for a 60Gb db)

Faster sounds good. I did a successful ( not yet tested ) backup with
--skip-opt and some other options that gave me a single-line insert
statement per record. The backup took 10 hours :( It used to take 90 mins.

Anyway, thanks again. Will check it out when I get home.

Dan

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

josh at worldhosting

Jan 13, 2009, 2:31 PM

Post #6 of 12 (1415 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Hi,

I use a different method of backing up MySQL. I use a separate server
that's a slave to the master and stop slave ; backup ; start slave. That
gives no performance hit to the main db (I can backup during peak hours)
and always gives a consistent snapshot.

Note my database is well over 60Gb and my backup machine has 640Mb RAM,
so the method I'm using should work with 20Gb data and 1Gb RAM.

I've attached the script I use, I don't use mysqldump to dump the data,
I use "select * into outfile" and "LOAD DATA INFILE" - this is better
because:

* The backup and restore times are faster (Good for a 60Gb db)
* The backup files are smaller
* The backup files rsync faster
* The backup files can be stored more efficiently with xdelta

This script should work with all mysql databases, not just dbmail. One
gotcha I haven't yet worked around is that if an autoincrement field has
a value of 0 then you need to edit the schema and move the SET lines
around:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

to be at the end of the script.

Enjoy!
Josh.

Attachments:

backupmysql (1.88 KB)

niblettda at gru

Jan 14, 2009, 5:34 AM

Post #7 of 12 (1411 views)
Permalink

RE: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Dan,

I know you are using mysql, but if ever you were to move to
Postgres, I'm using the Point in Time Recovery (PITR) setup.
This has been great for making backups when your database
is large (mine is around 52G).

Restore could take you longer, but my opinion is, if I'm to
the point that I have to restore it's better to have the
data and take longer to restore, than to not or cause
performance hits every day to backup the data.

Basically the PITR saves the transaction logs of the database.
So I just copy the database every Sunday early morning, then
just archive out the transaction logs to another drive as they
fill up.

If I should have to recover, I just copy back in the last Sunday
copy of the DB and tell Postgres to playback the transaction log
files. The really nice thing is if I think it was a DB corruption
problem, I can say restore everything to 5m before the crash.

Just another thought for you.

--
David A. Niblett | email: niblettda [at] gru
Network Administrator | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/

-----Original Message-----
From: dbmail-bounces [at] dbmail [mailto:dbmail-bounces [at] dbmail] On
Behalf Of Dan
Sent: Tuesday, January 13, 2009 5:07 PM
To: josh [at] worldhosting; DBMail mailinglist
Subject: Re: [Dbmail] [Slightly OffTopic] Backing up dbmail ( error 2013
)

On Wed, 2009-01-14 at 08:31 +1000, Josh Marshall wrote:

> Hi,

Hi Josh. Thanks for the suggestions & script. Very nice. I'll try it out
on my home server and see how the backup / restore procedure works.

> I use a different method of backing up MySQL. I use a separate server
> that's a slave to the master and stop slave ; backup ; start slave.
> That gives no performance hit to the main db (I can backup during peak

> hours) and always gives a consistent snapshot.

Unfortunately we're dealing with people who can't even get their shit
together sufficiently to have 1 decent server. An additional server is
out of the question :(

> * The backup and restore times are faster (Good for a 60Gb db)

Faster sounds good. I did a successful ( not yet tested ) backup with
--skip-opt and some other options that gave me a single-line insert
statement per record. The backup took 10 hours :( It used to take 90
mins.

Anyway, thanks again. Will check it out when I get home.

Dan

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

rabbit+list at rabbit

Jan 14, 2009, 6:28 AM

Post #8 of 12 (1413 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Niblett, David A wrote:
> Dan,
>
> I know you are using mysql, but if ever you were to move to
> Postgres, I'm using the Point in Time Recovery (PITR) setup.
> This has been great for making backups when your database
> is large (mine is around 52G).
>
> Restore could take you longer, but my opinion is, if I'm to
> the point that I have to restore it's better to have the
> data and take longer to restore, than to not or cause
> performance hits every day to backup the data.
>
> Basically the PITR saves the transaction logs of the database.
> So I just copy the database every Sunday early morning, then
> just archive out the transaction logs to another drive as they
> fill up.
>
> If I should have to recover, I just copy back in the last Sunday
> copy of the DB and tell Postgres to playback the transaction log
> files. The really nice thing is if I think it was a DB corruption
> problem, I can say restore everything to 5m before the crash.
>
> Just another thought for you.
>

I do absolutely the same with my 60GB mysql database:

in mysql.cnf:
=================
log_bin = /space/system/mysql/binlog/mysql-bin *
log_bin_index = /space/system/mysql/binlog/mysql-bin.index *
server_id = 1
expire_logs_days = 20
max_binlog_size = 500M

* You can use the defaults, I am just copying my complete example here

in my backup script:
=======================

/usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf \
--execute "FLUSH TABLES WITH READ LOCK, LOGS; SYSTEM
/sbin/lvcreate --snapshot --size $S_LV_SIZE --chunksize 512K --name
$S_LV $VG/$LV &>/dev/null ; UNLOCK TABLES;"

where the variables are just fill-ins as per the lvcreate manpage. WHat
this does is:

1) flush all pending writes and lock the database as read-only
2) force a binlog rotation so you know exactly where the snapshot was taken
3) make a LV snapshot (takes care of all sync()s at fs level)
4) unlock everything (the lock persists while the snapshto is created,
which lasts about half a second)

Then I go ahead and slowly backup/compress/whatever I want the database
files from the snapshot volume

When shit hits the fan:
===========================

1) Stop the database
2) Unpack the mysql data directory saved from the latest snapshot (since
the snapshot was made within a read-lock, the database is guaranteed to
be consistent)
3) Start the database, but make sure no connections are accepted (as the
data is healthy but old)
4) gather all binlog files since the snapshot was taken (correlate the
backup script run time with the snapshot timestamos)
5) replay them using the wonderful mysqlbinlog utility
6) go back to sleep

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

marc at electronics-design

Jan 14, 2009, 8:57 AM

Post #9 of 12 (1411 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

This is not the same.

> 1) flush all pending writes and lock the database as read-only

Postgres does not flush pending writes, and also does not set the
database read-only.

> 2) force a binlog rotation so you know exactly where the snapshot was taken
> 3) make a LV snapshot (takes care of all sync()s at fs level)
> 4) unlock everything (the lock persists while the snapshto is created,
> which lasts about half a second)
>
> Then I go ahead and slowly backup/compress/whatever I want the database
> files from the snapshot volume
>
>
> When shit hits the fan:
> ===========================
>
> 1) Stop the database
> 2) Unpack the mysql data directory saved from the latest snapshot (since
> the snapshot was made within a read-lock, the database is guaranteed to
> be consistent)

With PITR you can recover to *any* point in time. Not just your snapshot
time. It is completely different. PITR is a query log which starts from
your snapshot onward. So sunday snapshot day (without locking or
read-only) then continueing with query logs up to crash day.

However, I would not even think about using it with dbmail. Because it
slows down your database. For every INSERT and UPDATE query the data has
to be saved twice. Once in the database and once in the log.

Marc
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

rabbit+list at rabbit

Jan 14, 2009, 9:23 AM

Post #10 of 12 (1416 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Marc Dirix wrote:
> This is not the same.
>
>> 1) flush all pending writes and lock the database as read-only
>
> Postgres does not flush pending writes, and also does not set the
> database read-only.
>

Is this considered a good thing...? I mean I am about to save away the
actual database binaries.

>> 2) force a binlog rotation so you know exactly where the snapshot was taken
>> 3) make a LV snapshot (takes care of all sync()s at fs level)
>> 4) unlock everything (the lock persists while the snapshto is created,
>> which lasts about half a second)
>>
>> Then I go ahead and slowly backup/compress/whatever I want the database
>> files from the snapshot volume
>>
>>
>> When shit hits the fan:
>> ===========================
>>
>> 1) Stop the database
>> 2) Unpack the mysql data directory saved from the latest snapshot (since
>> the snapshot was made within a read-lock, the database is guaranteed to
>> be consistent)
>
> With PITR you can recover to *any* point in time. Not just your snapshot
> time. It is completely different. PITR is a query log which starts from
> your snapshot onward. So sunday snapshot day (without locking or
> read-only) then continueing with query logs up to crash day.

Again - how do you take a consistent snapshot on Sunday without ensuring
a lock?

> However, I would not even think about using it with dbmail. Because it
> slows down your database. For every INSERT and UPDATE query the data has
> to be saved twice. Once in the database and once in the log.
>

Not sure about PG - the penalty in MySQL is in the low single digits.
It's not like fsync() is called on every logwrite.
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

aleksander at krediidiinfo

Jan 14, 2009, 9:54 AM

Post #11 of 12 (1410 views)
Permalink

Re: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

Marc Dirix wrote:
> However, I would not even think about using it with dbmail. Because it
> slows down your database. For every INSERT and UPDATE query the data has
> to be saved twice. Once in the database and once in the log.

I consider NOT running a logical/binary/whatever_you_call_it log on any
database which has even slightly significant data writes a no go. You
always run the log unless you're performing a restore or some other
administrative task where you have a full backup available.

Daily/weekly/monthly snapshots/backups are another issue altogether.

Why is it so slow for you, did you try locating it on a separate disk
array/controller from the main db? That recommendation is not only due
to performance reasons. And the small performance hit you get anyway has
to be swallowed, it just the way things are.

I doubt postresql has major problems with it. I've never used postgre
though, but still. It's one the main features of a relational db, isn't it?

Regards,

--

Aleksander Kamenik
System Administrator
Krediidiinfo AS
an Experian Company
Phone: +372 665 9649
Email: aleksander [at] krediidiinfo

http://www.krediidiinfo.ee/
http://www.experiangroup.com/
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

niblettda at gru

Jan 14, 2009, 12:10 PM

Post #12 of 12 (1410 views)
Permalink

RE: [Slightly OffTopic] Backing up dbmail ( error 2013 ) [In reply to]

I can only speak for myself, and I have not had any performance
hits, or at least anything that I would consider negative.

Let's face it, there is no free lunch, no matter what you do.
I just like the fact that I don't have to do a giant pg_dump
every night, and even then I have no "backup" until the next
dump. With PITR the worst case is the current transaction
log file has a problem. I seem to be rotating files about
every 10-15m. So worst case is I can recover to 10-15m ago.

In reference to how to get a consistent backup, you do issue
the command "SELECT pg_start_backup('label');". This is the
trigger so that the transaction logs know where to start
replaying from.

For those that want to read how it's implemented:
http://www.postgresql.org/docs/8.1/static/backup-online.html

--
David A. Niblett | email: niblettda [at] gru
Network Administrator | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/

-----Original Message-----
From: dbmail-bounces [at] dbmail [mailto:dbmail-bounces [at] dbmail] On
Behalf Of Aleksander Kamenik
Sent: Wednesday, January 14, 2009 12:54 PM
To: marc [at] electronics-design; DBMail mailinglist
Subject: Re: [Dbmail] [Slightly OffTopic] Backing up dbmail ( error 2013
)

Marc Dirix wrote:
> However, I would not even think about using it with dbmail. Because it

> slows down your database. For every INSERT and UPDATE query the data
> has to be saved twice. Once in the database and once in the log.

I consider NOT running a logical/binary/whatever_you_call_it log on any
database which has even slightly significant data writes a no go. You
always run the log unless you're performing a restore or some other
administrative task where you have a full backup available.

Daily/weekly/monthly snapshots/backups are another issue altogether.

Why is it so slow for you, did you try locating it on a separate disk
array/controller from the main db? That recommendation is not only due
to performance reasons. And the small performance hit you get anyway has
to be swallowed, it just the way things are.

I doubt postresql has major problems with it. I've never used postgre
though, but still. It's one the main features of a relational db, isn't
it?

Regards,

--

Aleksander Kamenik
System Administrator
Krediidiinfo AS
an Experian Company
Phone: +372 665 9649
Email: aleksander [at] krediidiinfo

http://www.krediidiinfo.ee/
http://www.experiangroup.com/
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads