Announcement

Announcement Module
Collapse
No announcement yet.

binary backup

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • binary backup

    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?

  • #2
    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.

    Comment


    • #3
      same to me. I hoped a "flush tables with .." would be sufficient for backing up Innodb.
      thx

      Comment


      • #4
        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).

        Comment


        • #5
          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.

          Comment


          • #6
            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).

            Comment


            • #7
              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:-(

              Comment

              Working...
              X