GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Partial backup - complete ibdata1

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

  • Partial backup - complete ibdata1

    Hi,

    I trying to backup some InnoDB databases, but I noticed that the backed-up ibdata1 is as large as the whole databases.

    What am I doing wrong ?
    MySQL 5.5

    I used :

    innobackupex --user=root --databases="db1 db2 db3" /my/home/
    innobackupex --apply-log /my/home/xtrabackup/

    and then innobackupex --copy-back /my/home/xtrabackup/

    Thanks !

  • #2
    Hi,

    Is innodb_file_per_table enable on your server? It must be enabled for taking partial backup (selected dbs).

    There is already open bug for --databases option as xtrabackup ignores it without --stream option.
    https://bugs.launchpad.net/percona-xtrabackup/+bug/569387

    You can either use a regexp matching all the desired databases with --include option.
    i.e innobackupex --include='^(aggiornamenti|corsi|corsi_storico|mysql |open)\. *' ...


    You can get more information here,
    http://www.percona.com/doc/percona-xtrabackup/xtrabackup_bin /partial_backups.html?id=percona-xtrabackup:xtrabackuparti al

    http://www.percona.com/doc/percona-xtrabackup/innobackupex/p artial_backups_innobackupex.html

    Comment


    • #3
      Hi,

      I tried with --include instead of --databases, but the whole ibdata1 is still backed-up, whether there's innodb tables or not.

      So the only way to not get a complete ibdata1 is the innodb_file_per_table ?

      Thanks !

      Comment


      • #4
        Hi,

        Yes, as per the documentation, "XtraBackup features partial backups, which means that you may backup only some specific tables or databases. The only requirement for this feature is having the innodb_file_per_table option enabled in the server."

        Comment


        • #5
          I have now innodb_file_per_file enable, so I can restore databases, but I'm facing another issue.
          What if I need to restore 1 database on a running server. I can't use the copy-back since it requires a clean mysql directory. Documentation says i need to create each table, discard table space... can this be automated ?

          Comment


          • #6
            Hi,

            Yes, for restoring 1 database you need clean mysql directory. I guess, for restoring each table, you can make some bash/perl script for restoring whole DB.

            Comment


            • #7
              Ok thanks, so standard mysqldump is easier afterwards. Xtrabackup is definitely a great tool, but for full backup/restore. When it comes to partial backup, it's not very convenient Hope more updates will come in the future

              Comment

              Working...
              X