can incremental backups be done with a database?
Rahul
"mysqldump -u backup --all-databases" but this is a full dump.
In the past this was ok since my databases were small and had infrequent
activity. But now I am using mysql as backend for MediaWiki and I am afraid
that the full dumps will get huge.
For disk backups, for example, a schedule of full and incremental backups
seems the convention. Is there a similar possibility for databases as well?
Just curious.
--
Rahul
Jean-David Beyer
using tools provided by the dbms being used. There are all kinds of
synchronizations among the files that must be preserved and your average
backup program would not know them. Incremental backups seem even more
unlikely to be successful. All this especially true if the database is up
and running. You would never get the transactions correct.
For postgreSQL I use their tools to do the backups.
For everything else, I use BRU, but a combination of _find_ and _cpio_ would
do about as well. I do full backups every morning while I am asleep, but for
me a full backup (except for database stuff) fits on one tape. Otherwise I
would do a full backup on Sundays and a differential backup on the other 6
days. That way, when the disaster occurs, I would restore the last full
backup and the most recent differential backup. I use the definitions of
incremental and differential from here:
http://en.wikipedia.org/wiki/Incremental_backup
since some people attach the opposite meanings to those words.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 13:35:01 up 20 days, 24 min, 3 users, load average: 4.39, 4.41, 4.42
Rahul
news:p055m.1516$P5....@nwrddc02.gnilink.net:
> I do not know about mysql, but for other databases it is advisable to
> backup using tools provided by the dbms being used. There are all
> kinds of
>
I am using a dbase tool. "mysqldump". It works well. BUt does full backups.
It seems sort of a bit wasteful to do full backups each day. I was just
wondering what is the equivalent of incremental backups in the database
world.
--
Rahul
toby
> Jean-David Beyer <jeandav...@verizon.net> wrote innews:p055m.1516$P5....@nwrddc02.gnilink.net:
>
> > I do not know about mysql, but for other databases it is advisable to
> > backup using tools provided by the dbms being used. There are all
> > kinds of
>
> I am using a dbase tool. "mysqldump". It works well. BUt does full backups.
> It seems sort of a bit wasteful to do full backups each day. I was just
> wondering what is the equivalent of incremental backups in the database
> world.
You could look into the possibilities of MySQL's binary log, possibly
combined with mysqldumps.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
>
> --
> Rahul
Gordon Burditt
>"mysqldump -u backup --all-databases" but this is a full dump.
>
>In the past this was ok since my databases were small and had infrequent
>activity. But now I am using mysql as backend for MediaWiki and I am afraid
>that the full dumps will get huge.
The main problem here is that there are modification times on files,
and directories but there are not automatically modification times
on rows.
You can do a sort-of incremental backup *if* each table has a
TIMESTAMP column which is updated every time the row is updated.
You also need to not delete rows, just mark them deleted. In this
case, you can use mysqldump with a --where="stamp >=
'$last_full_backup_time'" argument to mysqldump.
For many databases, this requires a lot of changes and is probably
not worth the trouble, especially the part about marking rows deleted
rather than actually deleting them (which may cause unwanted
"duplicate key" errors).
Otherwise, you could do a table-by-table incremental dump by dumping
only those tables where information_schema.update_time is greater
than the time the base dump was taken. This will only stop dumping
tables which are very infrequently modified, and probably will save
very little dumping.
Gordon Burditt
>only those tables where information_schema.update_time is greater
Oops, that should be information_schema.tables.update_time, but
this is still a pretty poor scheme for incremental backups.
Rahul
news:LYidnekJ5asfhcjX...@posted.internetamerica:
> For many databases, this requires a lot of changes and is probably
> not worth the trouble, especially the part about marking rows deleted
> rather than actually deleting them (which may cause unwanted
> "duplicate key" errors).
>
I see. Thanks gordon. Mine is only a trivial database but how do all you
"big" serious db guys handle this? The whole world runs on dbs these days
(or almost!) and some of these databases must be huge and also critical.
So they must be taking backups somehow everyday. What do they do? Full
backups everyday must be way too large to do rght? Or is it reasonable to
do a full backup each day but overwrite all old backups? Usually the
ability to rollback to a particular point of time is the reason why older
backups are also saved.
--
Rahul
Jerry Stuckle
Unfortunately, MySQL doesn't have any tools to perform incremental
backups. To do that you'll have to go to a database such as DB2,
Oracle, etc.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Jerry Stuckle
>> Is there a way to do incremental backups of mysql? Currently I am using
>> "mysqldump -u backup --all-databases" but this is a full dump.
>>
>> In the past this was ok since my databases were small and had infrequent
>> activity. But now I am using mysql as backend for MediaWiki and I am afraid
>> that the full dumps will get huge.
>
> The main problem here is that there are modification times on files,
> and directories but there are not automatically modification times
> on rows.
>
Some databases (i.e. DB2) have tools which support incremental backups.
Unfortunately, MySQL isn't one of them. These support all operations,
including insert, delete and update, typically via log files.
The downside is, to restore, you need to restore from a full backup then
every incremental backup, in order of backup.
Richard
"Rahul" <nos...@nospam.invalid> wrote in message
news:Xns9C42B6E30EB2466...@85.214.113.135...
I compress the backups.
Most of mine shrink to 10% of the original.
R.
ThanksButNo
> Is there a way to do incremental backups of mysql? Currently I am using
> "mysqldump -u backup --all-databases" but this is a full dump.
This may be useful:
http://dev.mysql.com/doc/refman/5.1/en/backup.html
\:-\
Axel Schwenke
> Is there a way to do incremental backups of mysql? Currently I am using
> "mysqldump -u backup --all-databases" but this is a full dump.
You can do incremental backups with MySQL by activating the binlog.
Your full backups should then use the --flush-logs option to make
MySQL start a new binlog (there is a trap: read the docs!)
An incremental backup would use 'mysqladmin flush-logs' to switch
to a new binlog and archive the old binlog(s).
To recover you would use the last full backup and replay the
binlogs written after that. Replay is done like so:
shell> mysqlbinlog log1 log2 ... | mysql -h ... -u ... -p
Notes:
- replaying binlogs can take long; MySQL just executes the same
SQL statements as it did at runtime
- if you have a lot of updates or deletes, the binlog can become
bigger than a full backup!
- MySQL automatically starts a new binlog if the current binlog
reaches a size of 1GB (can be tuned).
- the restore procedure using binlogs allows point-in-time
recovery. You can even skip some "bad" statements - lets say
a malicious "DROP TABLE customers"
See also: http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
XL
The Natural Philosopher
The transaction logfile WAS the database incremental backup.
WE took full backups periodically, and then archived and truncated IIRC
the transaction logfile every night.
restoration was about going to last full backup and reinserting the
transactions from then to the last logfile we had..
Surely its teh same in Mysql..I haven;'t needed to know..
Peter H. Coffin
> So they must be taking backups somehow everyday. What do they do?
> Full backups everyday must be way too large to do rght? Or is it
> reasonable to do a full backup each day but overwrite all old
> backups? Usually the ability to rollback to a particular point of
> time is the reason why older backups are also saved.
Many of the Big Boys use entirely different mechanisms for managing
giant databases and backups, such as having specialized storage that
allows entire volumes to be copied in seconds, or setting up slave
servers to offload backups from the main, or having really nice backup
media that can store hundreds of GB on a tape and write the whole tape
in two hours, and they can afford to use fresh tapes every time.
Whole-system backups can actually be faster than incrementals when
you've got a real budget to spend on backup kit.
--
The pig is nothing but a giant dish which walks while waiting to be served.
--Grimod de La Reyni�re