mysql backup

  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql backup

    what is best option to take mysql backup ( incremental backup ) without any database locking for a database having size more than 500 GB and increasing continuously.
    also how to keep backup on different geographical location.

    The method I used:

    mysqldump : lock the table, not suitable for big databases and for real time application.

    ibbackup : good tool but i do not find any incremental backup option there.

    using binary log for incremental backup is time consuming process.

    Please suggest.

  • #2

    If you are using only InnoDB tables, then you should consider xtrabackup. Xtrabackup is similar to ibbackup, but it does support incremental backups:

    Unfortunately Xtrabackup can't stream an incremental backup to a remote machine, so you will have to make a local backup then rsync it to a remote location.



    • #3
      there are few myisam tables also, can xtrabackup handles both innodb and myisam?


      • #4
        this is possible, i read it in documentation.
        http://www.percona.com/docs/wiki/percona-xtrabackup:xtraback up_manual#making_a_partial_backup

        If xtrabackup provides all required backup feature then how xtrabackup is different from ibbackup which is paid version?


        • #5
          ibbackup is not a paid version of XtraBackup. ibbackup is a closed-source backup utility owned by Oracle. XtraBackup is an open-source backup utility created by Percona about a year and a half ago. They are different products from different companies. They just happen to work in a similar fashion.


          • #6
            How to build XtraBackup

            !!You should build your MySQL before the building XtraBackup!!

            1. Put this directory to innobase/ directory.
            (e.g. mysql-5.0.75/innobase/xtrabackup-0.0 )

            2. From the innodbase directory, apply the xtrabackup patch and then re-make.
            The patch is fix_innodb_for_backup.patch for MySQL 5.0 and
            fix_innodb_for_backup51.patch for MySQL 5.1.
            ('make clean' at innobase/ dir may be needed for 5.0, because of the lack of dependency)

            3. Run make in the XtraBackup directory.

            4. enjoy... )

            above steps are given to build XtraBackup, i do not find any innobase/ folder under mysql installation structure.

            I am using mysql 5.1 installed from RPM

            do i need to compile mysql for XtraBackup?


            • #7
              compilation error

              [root@qaserver1 xtrabackup-1.2]# make
              cc -O2 -g -fmessage-length=0 -D_FORTIFY_SOURCE=2 -I. -I.. -I./../include -I./../../include -I./../../../include -DUNIV_LINUX -DMYSQL_SERVER -DXTRADB_BASED -c xtrabackup.c
              xtrabackup.c:16:21: error: my_base.h: No such file or directory
              xtrabackup.c:17:23: error: my_getopt.h: No such file or directory
              xtrabackup.c:18:27: error: mysql_version.h: No such file or directory
              xtrabackup.c:19:23: error: mysql_com.h: No such file or directory
              xtrabackup.c:27:18: error: univ.i: No such file or directory
              xtrabackup.c:28:21: error: os0file.h: No such file or directory
              xtrabackup.c:29:23: error: os0thread.h: No such file or directory
              xtrabackup.c:30:23: error: srv0start.h: No such file or directory
              xtrabackup.c:31:21: error: srv0srv.h: No such file or directory
              xtrabackup.c:32:22: error: trx0roll.h: No such file or directory
              xtrabackup.c:33:21: error: trx0trx.h: No such file or directory
              xtrabackup.c:34:21: error: trx0sys.h: No such file or directory
              xtrabackup.c:35:21: error: mtr0mtr.h: No such file or directory
              xtrabackup.c:36:21: error: row0ins.h: No such file or directory
              xtrabackup.c:37:23: error: row0mysql.h: No such file or directory
              xtrabackup.c:38:21: error: row0sel.h: No such file or directory
              xtrabackup.c:39:21: error: row0upd.h: No such file or directory
              xtrabackup.c:40:21: error: log0log.h: No such file or directory
              xtrabackup.c:41:23: error: lock0lock.h: No such file or directory
              xtrabackup.c:42:23: error: dict0crea.h: No such file or directory
              xtrabackup.c:43:21: error: btr0cur.h: No such file or directory
              xtrabackup.c:44:21: error: btr0btr.h: No such file or directory
              xtrabackup.c:45:21: error: fsp0fsp.h: No such file or directory
              xtrabackup.c:46:23: error: sync0sync.h: No such file or directory
              xtrabackup.c:47:21: error: fil0fil.h: No such file or directory
              xtrabackup.c:48:20: error: trx0xa.h: No such file or directory


              • #8


                • #9

                  I might be looking at your situation the wrong way. But if you install mysql 5.1 from RPM, xtrabackup have RPMs available as well.

                  Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

                  Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/


                  • #10
                    I am able to install RPM xtrabackup,
                    some more error after running backups.

                    100814 10:20:21 InnoDB: Operating system error number 9 in a file operation.
                    InnoDB: Error number 9 means 'Bad file descriptor'.
                    InnoDB: Some operating system error numbers are described at
                    InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-erro r-codes.html
                    InnoDB: File operation call: 'close'.
                    InnoDB: Cannot continue operation.


                    • #11
                      any body facing this issue while taking backup?