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 [url]https://github.com/gregster85/autoxtrabackup[/url] 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 ?
Hello [CENTER],
I am also taking the incremental backup based of fullbackup.
It’s easy to restore.
can you elaborate the issue point wise.[/CENTER]
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 --password=Mysql@123 --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 --password=Mysql@123 --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…