I am relatively new to MySQL. My database uses Innodb and
I have binary logging enabled.

Here is my proposed backup strategy:

# Raw backup
cd /var/lib/mysql
service mysql stop
tar -cvf /root/mysqlbinbackup/20110519.tar .
service mysql start
gzip /root/mysqlbinbackup/20110519.tar
chmod 600 /root/mysqlbinbackup/20110519.tar.gz

# mysqldump
mysqldump -u root -p --single-transaction --flush-logs --master-data=2
--delete-master-logs --opt Zebra > /root/mysqldumpbackup/20110519.sql
gzip /root/mysqldumpbackup/20110519.sql
chmod 600 /root/mysqldumpbackup/20110519.sql.gz

Of course, I already have seperate backups of my.cnf
as well as my database schema.

Does this make sense? Any classic newbie errors or
ommissions that are likely to result in tears?

Thanks for any comments.


--

malch (AT) malch (DOT) com (Malcolm Hoar) wrote:
OK

Why?

Supposing that /var/lib/mysql is your datadir and that InnoDB is
configured to put it's table spaces and redo logs into it ...
then this will give you a complete physical backup of your MySQL
instance.

And this will *additionally* to the above create an SQL dump of the
`Zebra` schema. And it will also remove old binlogs and record the
current binlog position.

Not sure what you mean by "database schema". But the logical schema
of all your databases is already in the above. The `Zebra` schema
twice already.

I have no idea what your goal is. But if you can live with shutting
down MySQL for the backup, then the first part is enough already.
OK, you might want to PURGE old binlogs too.

Mysqldump has the advantage that the MySQL server can stay up during
backup. And with using --single-transaction, it would not even lock
any tables. Physical backup OTOH is a) faster to backup and b) much
faster to restore.

Enabling the binlog will allow you to do point-in-time recovery.
Are you planning to use this? Have you RTFM?

http://dev.mysql.com/doc/refman/5.1/...-recovery.html


XL

In article <4p7ga8-aje.ln1 (AT) xl (DOT) homelinux.org>, axel.schwenke (AT) gmx (DOT) de wrote:

That was the goal.

Yes, of course.

Well, at this point, I want to cover all of my bases. You
can never have too many backups and all that... ;-)

Yes, I think shutting down for the backups is acceptable
at this point. However, it could become a problem down
the road as the data volumes (times) increase and so do
user demands.

I'm currently thinking of running a raw/physical backup
daily. But I might push that to weekly if the downtime
causes problems for my users.

And thanks for the PURGE tip -- I will look at that.

Yes, and I think the Mysqldump backup would also be highly
portable to another server if the primary met with some
catastrophe like a fire, natural disaster etc., right?

Yes, again I want to keep all of my options open at this
point. Frankly I'm hoping to avoid any point-in-time
recoveries but just in case. I have considered replicating
this database too. I think that's overkill at this point
but it may become a factor and a binary log would be the
first pre-requistite for replication, right?

Yeah. And some other books and articles. But not enough I'm
sure!

Thanks, I will reread that chapter again later today!

This app is still a few weeks from production but I plan
on loading up some test data next week and trying a full
backup *and recovery* just to be sure I have this covered
properly. I plan to test both the raw *and* mysqldump
scenarios.

Thanks for your comments.

--

malch (AT) malch (DOT) com (Malcolm Hoar) wrote:
I wouldn't second that. Of course you need one complete backup.
But it doesn't make much sense to have multiple backups of the
same thing from the same time.

What you need much more, is to test your restore process: does
it work at all? Is it fast enough? Is the restored data ok?

It's quite unusual to have a daily maintenance window that allows
to shutdown the database. But if you are that lucky, then doing a
physical backup that way is probably easiest and fastest.

There is one pitfall here: if you have physical data corruption,
then this might go unnoticed for a long time (longer than you keep
old backups and binlogs). This could be overcome by either testing
backups regularly (restore to a spare machine and run CHECK TABLE
on all tables). Or do a SQL dump occasionally.

The physical backup is just as portable - if we talk about restoring
data into MySQL. The SQL dump is more portable in the sense that you
can use it to load data into a different DBMS. Or that you can use
any kind of software to extract data from it, split it, modify it in
a certain way etc.

The physical backup is less flexible you cannot even restore a single
(InnoDB) table from it. It's all-or-nothing.

Sure. But if you want to be ready for PITR (aka incremental backup/
recovery), then there are some additional requirements for the full
backups:

- you must backup all databases
- the backup must be a snapshot (all objects in the backup must be
from the same point in time)
- your backup must include the information of the binlog number and
file position at that point in time

The physical backup as you do it, conforms to this (the binlog position
is implicit because MySLQ starts a new binlog when it is restarted).
The partial SQL dump however does not.


XL

On 5/20/2011 3:28 AM, Axel Schwenke wrote:
He's right, Axel - in the real world (which you have never experienced),
you can't have too many backups. But you can always have too few.
Things happen.

True, but then most companies don't do physical backups. They use the
database tools (as they should).

Best is to not do physical backups.

And if you're restoring to the exact same version of MySQL on the exact
same operating system and the exact same options... But all bets are off
if any of that changes.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

FTR: I answer mostly to correct the stucklish nonense

Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:
You see, some people prefer to do things right. And then it's enough
to do it only once.

OTOH I'm not astonished to see that you are among those who don't
trust their own craft and rather do things twice.

Wrong. You can restore to any platform. And to any MySQL version
from the same or the next major release.


XL

Axel Schwenke wrote:
And what craft would that be? I have yet to see any evidence of any..


See what I mean.

However as with all things Stuckle headed, there is a grain of truth.

Not that Stuckle can actually comprehend what it is.

You must tailor backup policy as a straightforward engineering solution
to counter any of many likely failure scenarios, and what you end up
with depends on what scenarios you wish to cover.

Destruction of the earth by asteroid impact might involve sending paper
or etched metal records into space...

Jerry likes lots of *similar* backups because his fat fingers have been
known to delete rather than restore the backup copies. Ergo, in true
random monkey principle, enough so that statistically there is always
one left when he randomly hits the right key, is the way to go.

Personally I like one on site and one off site. If its a professional setup.

But for home/SOHO use one on site is enough, really.

Its a balance of cost and benefit - as are all real world decisions.



> XL

In article <rqhha8-tg4.ln1 (AT) xl (DOT) homelinux.org>, axel.schwenke (AT) gmx (DOT) de wrote:
Sure it does. One on-site and one off-site, for starters.
Mandatory as far as I'm concerned.

On this we agree. The situation defines the issues. Sometimes
operatiosn demand a partial restore. Well, a logical backup
can be a lot more appropriate than a raw image. If I loose
the entire DB, restoring an image will be much faster.

On my own workstation, I image the disks *and* sync to
external mirrors. Loose a disk or O.S. and I restore from
an image. Have an "oh crap" moment with a file deletion
and I can pull a copy of the file off a mirror.

Good point. I plan to build CHECK TABLE into my procedures.

Not across operating systems.

I'm not a huge fan of incremental backups. Sometimes they're
a necessary evil but I plan to skip that unless/until the
data volumes force the issue.

--

On 5/20/2011 8:21 AM, Axel Schwenke wrote:
I NEVER trust a program with important data. I always have multiple
backups, and recommend my customers do the same.

Right now, yes. But there is no guarantee that will be true with the
next release.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

malch (AT) malch (DOT) com (Malcolm Hoar) wrote:
What is an "on site backup"? A second copy on the same disk as the
original data? This is no backup in the first place.

Traditionally a backup is written to some separate media (tape, MO,
etc) and then stored in a safe. Sometimes it's good enough to have
the tape changer in a separate room (separate fire protection zone)
than the original data.

And after all: now you speak about storing a *second copy* of the
*same backup* in another place. Yet again this is different from
doing two backups of the same data as originally intended.

Sure. It's designed that way. The only constraint is that origin
and target architecture must use the same floating point format,
because FLOAT/DOUBLE is stored in machine notation. All other data
is stored architecture-independent.

And it's also guaranteed that each MySQL release will be able to
correctly read (but not write to!) data from the previous major
version. This is needed for the binary upgrade procedure.

PITR can be really helpful, i.e. to undo a DROP TABLE from an
attacker. Just roll forward the binlog up to immediately before
the bad statement. You might even be able to just skip it and
apply the rest of the binlog.

BTW, since you are on InnoDB, you might want to have a look at
Percona XtraBackup. Or go for the real thing: MySQL Enterprise
Backup (XtraBackup is merely an open source re-implementation
of the same)

http://www.percona.com/software/percona-xtrabackup/


XL