Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

binary backup

erkuleserkules ContributorInactive User Role Beginner
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?

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    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.
  • erkuleserkules Contributor Inactive User Role Beginner
    same to me. I hoped a "flush tables with .." would be sufficient for backing up Innodb.
    thx
  • scoundrelscoundrel Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">sterin wrote on Sat, 08 December 2007 13:23</td></tr><tr><td class="quote">

    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.

    </td></tr></table>

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

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.