Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » binary backup
binary backup [message #2349] Fri, 07 December 2007 05:00 Go to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
I've got a big database and mysqldumpIng it takes tooo long:-)

I played with tar and rsync and it seems to play well. But Im lacking experience. All I did was a "Flush tables with read lock" and doing a tar/rsync. In the long run I would prefer rsync. But Im not sure, if it is a bad idea.
Are there some experiences. Would you recommend something else for a binary backup? AFAIK no problems with MyISAM-Engine, but Im unsure regarding innodb-Engine.

Any advice?
Report message to a moderator
Re: binary backup [message #2357 is a reply to message #2349 ] Sat, 08 December 2007 13:23 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
On MyISAM it is very straightforward and you can just as you said issue "FLUSH TABLES WITH READ LOCK" and use tar, cp, scp, rsync or whatever that copies the binary files to some other place.

But with InnoDB it's not that easy.
You will have to use InnoDB Hot Backup or stop and start the mysqld process.

A long time ago when I was setting up replication I remember reading in the manual that you could "flush .. read lock" and copy the InnoDB tablespace also and that InnoDB would perform recovery on the broken tablespace as soon as you started mysql on the slave.
Although I tried to get it to work, it failed so nowadays I always shut down the mysql server before copying the InnoDB tablespace. Since it seems to be the only way to get a consistent snapshot.
Report message to a moderator
Re: binary backup [message #2358 is a reply to message #2357 ] Sat, 08 December 2007 20:56 Go to previous messageGo to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
same to me. I hoped a "flush tables with .." would be sufficient for backing up Innodb.
thx
Report message to a moderator
Re: binary backup [message #2379 is a reply to message #2357 ] Wed, 12 December 2007 21:19 Go to previous messageGo to next message
scoundrel is currently offline scoundrel
Messages: 58
Registered: August 2006
Location: Toronto, ON, Canada
Member
sterin wrote on Sat, 08 December 2007 13:23


A long time ago when I was setting up replication I remember reading in the manual that you could "flush .. read lock" and copy the InnoDB tablespace also and that InnoDB would perform recovery on the broken tablespace as soon as you started mysql on the slave.



If you can make a consistent snapshot of innodb's tablespace, then it would work. LVM backups work this way (flush, snapshot, tar, etc).

Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: binary backup [message #2380 is a reply to message #2379 ] Thu, 13 December 2007 03:41 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
scoundrel wrote on Thu, 13 December 2007 03:19


If you can make a consistent snapshot of innodb's tablespace, then it would work. LVM backups work this way (flush, snapshot, tar, etc).

Unfortunately that was exactly what didn't work.

I made very sure that I held the lock during the copy so:
I aquired the lock in one frontend
Took a binary copy using tar or cp in another terminal window
Released the lock in the frontend

I used both tar and cp several times to try to create a binary copy. And on each occasion when I started mysqld on the slave it complained about InnoDB tablespace not consistent (as it should do). And InnoDB started to try to recover from it. But it was never able to. The recovery bailed out reporting something like to grave errors.

Some day I intend to try it again but in the meanwhile I realized it's so much easier if you are able to shut down the server.
Report message to a moderator
Re: binary backup [message #2381 is a reply to message #2380 ] Thu, 13 December 2007 03:53 Go to previous messageGo to next message
scoundrel is currently offline scoundrel
Messages: 58
Registered: August 2006
Location: Toronto, ON, Canada
Member
sterin wrote on Thu, 13 December 2007 03:41

scoundrel wrote on Thu, 13 December 2007 03:19


If you can make a consistent snapshot of innodb's tablespace, then it would work. LVM backups work this way (flush, snapshot, tar, etc).

Unfortunately that was exactly what didn't work.

I made very sure that I held the lock during the copy so:
I aquired the lock in one frontend
Took a binary copy using tar or cp in another terminal window
Released the lock in the frontend




Lock does not guarantee CONSISTENT tablespace snapshot. LVM snapshot does (this is one of the most popular hot-backup solutions for mysql).

Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: binary backup [message #2404 is a reply to message #2381 ] Tue, 18 December 2007 11:38 Go to previous message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
scoundrel wrote on Thu, 13 December 2007 03:53



Lock does not guarantee CONSISTENT tablespace snapshot. LVM snapshot does (this is one of the most popular hot-backup solutions for mysql).



Talking to one from MySQL. He said dont use LVM it will work only almost of the time (concerning InnoDB). So all I can do is using a slave. But in fact Ive got to shut down mysqld:-(
Report message to a moderator
Previous Topic:Daily mysqldump locking database
Next Topic:Order by optimisation problems
Goto Forum:
  

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

Current Time: Thu Jul 9 20:31:37 EDT 2009

Total time taken to generate the page: 0.01292 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

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.