binary backup

[erkules]

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?

[sterin]

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.

[erkules]

same to me. I hoped a “flush tables with …” would be sufficient for backing up Innodb.
thx

[scoundrel]

[B]sterin wrote on Sat, 08 December 2007 13:23[/B]

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).