Not the answer you need?
Register and ask your own question!

Incremental backup and recovery of single database

ganzuoniganzuoni EntrantCurrent User Role Beginner
I'm planning to use XtraBackup in production environment but there are a number of aspects that are puzzling me.
I've have seen the scheduling script at https://github.com/gregster85/autoxtrabackup that looks very fine for my purposes.
As I said, there are a number of aspects related to the whole backup/recovery activity that are not so clear.
Assuming I schedule 1 daily full backup and 23 hourly incremental backup and and a 7 days retention what are the minimum purging days
of the binlog in MySQL, 7 ? (I can't keep the whole history)
This means that after one week the oldest full backup and all its increments are unusable, so I can safely delete it ?
Now let's pass to the recovery scenario.
When I restore to a particular point in time, I understand that this must be done on an empty datadir.
But what about binlog directory in case it is not located in datadir ? It should be empty as well ?
Now the very difficult part, i guess: specific database incremental backup *and* recovery.
Well, it seems that backup part it is quite easy to setup since there is a specific option for the purpose, but what about recovery ?
Since I have to restore to an empty datadir, what happens to other databases that are managed by the same server ?
What happens to mysql database and information schema ?

Comments

  • pranitpranit Entrant Current User Role Beginner
    Hello
    ,

    I am also taking the incremental backup based of fullbackup.

    It's easy to restore.

    can you elaborate the issue point wise.
  • pranitpranit Entrant Current User Role Beginner
    Hi ganzuoni,

    Please follow the simple steps hope you get any solution on your issue.

    I have done this in testing scenario.














    Restore FullBackup


    innobackupex --datadir=/else/datastore_2 --user=root [email protected] --parallel=18 --copy-back /else/percon-rnd/2016-07-14_17-57-08/



    drwxr-x--- 2 root root 12288 Jul 14 18:14 cacti
    drwxr-x--- 2 root root 4096 Jul 14 18:14 employees
    -rw-r
    1 root root 6836 Jul 14 18:14 ib_buffer_pool
    -rw-r
    1 root root 3032481792 Jul 14 18:14 ibdata1
    -rw-r
    1 root root 50331648 Jul 14 18:12 ib_logfile0
    -rw-r
    1 root root 50331648 Jul 14 18:12 ib_logfile1
    -rw-r
    1 root root 12582912 Jul 14 18:14 ibtmp1
    drwxr-x--- 2 root root 4096 Jul 14 18:24 innodb_memcache
    drwxr-x--- 2 root root 4096 Jul 14 18:14 mysql
    drwxr-x--- 2 root root 16384 Jul 14 18:24 OmNet
    drwxr-x--- 2 root root 4096 Jul 14 18:14 performance_schema
    drwxr-x--- 2 root root 12288 Jul 14 18:14 sys
    drwxr-x--- 2 root root 4096 Jul 14 18:14 test
    -rw-r
    1 root root 548 Jul 14 18:14 xtrabackup_info








































































































    innobackupex --apply-log /else/percon-rnd/2016-07-14_19-00-23/

    Restore the full backup

    innobackupex --datadir=/else/datastore_2 --user=root [email protected] --parallel=18 --copy-back /else/percon-rnd/2016-07-14_15-56-24/


    chown -cR mysql.mysql /datastore_2/

    /etc/init.d/mysql_2 start


    Starting MySQL...
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.