MySQL Performance: MySQL => InnoDB Backup

Members

Help

Home

Home » Performance » MySQL » InnoDB Backup

Show: Today's Messages :: Show Polls :: Message Navigator

InnoDB Backup [message #1427]

Fri, 15 June 2007 09:56

adamb
Messages: 5
Registered: June 2007

Junior Member

All - I have a very large database, containing hundreds of millions of records across multiple tables in multiple databases. The DB is setup for a master/slave replication, but I would like to start doing a full weekly dump to a DVDRom or tape backup...whatever. With MyIasm tables I can just copy the binary files, but this is not the case with InnoDB, which is what I am using. the mysqldump takes forever and a day to complete, and produces an extremely large text file, I was looking for some other way, that is a. quicker, b. smaller dump files. Is this possible? Can someone recommend a method?

Thanks,

Adam.

Report message to a moderator

Re: InnoDB Backup [message #1428 is a reply to message #1427 ]

Fri, 15 June 2007 17:45

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

This is what you are after:
http://www.innodb.com/hot-backup/features

Report message to a moderator

Re: InnoDB Backup [message #1444 is a reply to message #1428 ]

Mon, 18 June 2007 13:57

adamb
Messages: 5
Registered: June 2007

Junior Member

I was hoping for something that is OSS or free....anyone other suggestions ?

Report message to a moderator

Re: InnoDB Backup [message #1474 is a reply to message #1427 ]

Mon, 25 June 2007 06:16

razdaman
Messages: 26
Registered: May 2007

Junior Member

I'm not an "backup expert" in any way, but I just wanted to add that you actually can backup the binary files of innodb tables. It is a bit more complex than with myisam and it is not the ideal solution, but it is doable.

However, you have to shutdown your mysql-server while doing it.

[Updated on: Mon, 25 June 2007 06:16]

Report message to a moderator

Re: InnoDB Backup [message #1475 is a reply to message #1474 ]

Mon, 25 June 2007 08:00

adamb
Messages: 5
Registered: June 2007

Junior Member

There is no constraint about having mysql up and running, ie, i can shutdown the server for any amount of time and perform the backup. there is really only a critical window of about 5 hours a day when it needs to be running. is there a guide somewhere about backing up binary InnoDB tables? thanks.

Report message to a moderator

Re: InnoDB Backup [message #1477 is a reply to message #1427 ]

Mon, 25 June 2007 17:19

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

If you can shut down the server during backup then it's pretty easy.

All data is normally stored in the mysql data directory (unless you have performed any special configuration).
Which means that backing up the database is as easy as:
1. Shutdown the mysql server
2. Copy/run a backup client on the mysql data directory.
3. Start the mysql server again.

The problem with copying the files while the server is runnning is the internal caching of data.
But once the mysql server is shut down the files in the directory is "dead" and you can just copy them to wherever you want without any risk of getting corrupt files.

Report message to a moderator

Re: InnoDB Backup [message #1478 is a reply to message #1477 ]

Mon, 25 June 2007 17:22

adamb
Messages: 5
Registered: June 2007

Junior Member

follow-up question...

lets say in my many databases i have a few tables which are MyISAM like 10 of them, and the rest are InnoDB. would this technique work for databases with mixed storage engines ?

Report message to a moderator

Re: InnoDB Backup [message #1481 is a reply to message #1427 ]

Tue, 26 June 2007 03:30

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Yes without a doubt!

Think of it this way.

When the mysql server process is shut down it will not know anything more than what the files in the data directory contains (except the my.cnf file which is usually placed on a different location).

So that means that the files in the data directory contains all information that is needed to start a mysql server process.

The main reason why you can't just copy the files when running InnoDB is that InnoDB is using a write cache (myisam doesn't have this) so the files are not consistent since it hasn't written everything to them yet.

But when the mysql server process is shut down it will trigger a flushing of the write cache and it will write the changes to disk since itself needs to have the files consistent when it starts up again later.

So the summary is that once the server process is shut down you can always just make a copy of the files somewhere as a backup because they are consistent and "dead" since nothing is in the process of writing something to them.

Report message to a moderator

Re: InnoDB Backup [message #1485 is a reply to message #1481 ]

Tue, 26 June 2007 08:04

adamb
Messages: 5
Registered: June 2007

Junior Member

got it. thank you very much for all your help. backup up the files is significantly quicker then doing a mysql dump!

Report message to a moderator

Previous Topic:	PRIMARY KEY or FOREIGN KEY?
Next Topic:	char based partitioning

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 06:47:01 EDT 2009

Total time taken to generate the page: 0.02275 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.