Is Xtrabackup suitable for this environment?

MySQL & MariaDB Percona XtraBackup
1

Hello,

I am evaluating a number of products and methods for backing up a production MySQL DB.
I was looking at XtraBackup but am not that familiar with MySQL’s methods of back up so though i would ask here.

Hopefully someone will be able to read this (it is a little long) and either point out where I am going wrong or just give me some pointers.

A. Our environment:

  • Windows Version of MySQL v5.5.9 Community version

  • Running on Windows 2008 Server

  • in a VMWare environment, i.e. MySQL is running on a VMware guest

  • RAM = 24 GB

  • data stored on a SAN (in RAID 1+0)

  • InnoDB

  • one file per table

  • Total DB size = 1 TB (roughly)

  • DB size grows by about 2 GB per day

  • about 1100 tables

  • we add about 20 tables each month (see below for ARCHIVING process)

  • table sizes range from kilobytes up to about 15 GB

  • network is Gigabit or Fibre channel between servers

  • we are near 24 x 7 business

currently we do NOT use Binary logs, although that can change

B. TABLES and ARCHIVING
(i) our DB has ACTIVE tables, storing data for a rolling 5-day period

  • example active tables are “jobs”, “names”
    (ii) our DB has MONTHLY tables
  • example Monthly tables are
    “jobs_2011_10”
    “jobs_2011_11”
    “names_2011_10”
    “names_2011_11”
    and so on

(iii) Archiving process:
Every night, an ARCHIVING PROCESS (a windows application running on another server)

  • reads the earliest days’ worth of data from the ACTIVE tables (i.e. 5 day old data)
  • writes it to the latest MONTHLY tables
  • deletes it from the ACTIVE tables
  • The archive process takes about 3 hours each night

(iv) note that all ACTIVE and MONTHLY tables are used for READ purposes but only the ACTIVE tables have data written to them

(v) data moved in ARCHIVING process

  • we move about 1 GB of data between tables each night in the archiving
  • with about 1 million rows read, inserted into other tables and then deleted
  • That is, about 3 million transactions each night are performed.

(vi) At the same time, there will be several thousand rows written to the ACTIVE tables as data is inserted by other processes within the business

C. Current Backup

(i) currently we do NOT use Binary logs, although that can change
(ii) following the ARCHIVE process we use MySQL Administrator to just do a backup of selected tables, as most tables do not change, just the ACTIVE and current MONTHLY table.
e.g. we backup: jobs, names, jobs_2012_04, names_2011_04, and so on

D. Backup question
(i) So, would XtraBackup be suitable for such an environment?
(ii) we can use Binary logs
(iii) We are able to install XtraBackup on a Linux server and provide appropriate permissions to the windows server and any mount points
(iv) a FULL backup of a smaller DB (400 GB data, only 4 GB RAM) using another backup product did a full backup across our network in about 9 hours, where the backup product was on a Linux box and writing the backup to a second Linux server (due to space limitations on the first)
i.e. MySQL server → backup server → data storage server

(v) we cannot do FULL backups each night if this is the time it will take
(vi) still, with XtraBackup, are we able to backup the ACTIVE tables, without locking the tables/database to WRITES from our other applications?
(vii) given the ARCHIVE process above, would it be best to do a FULL backup each weekend and then INCREMENTALS each night?

I hope that is not too much info!

thanks for any input.

Adrian