A user on a linux user group mailing list asked about this, and I was one of the people replying. Re-posting here as I reckon it’s of wider interest.

> [...] tens of gigs of data in MySQL databases. > Some in memory tables, some MyISAM, a fair bit InnoDB. According to my > understanding, when one doesn’t have several hours to take a DB > offline and do dbbackup, there was/is ibbackup from InnoBase.. but now > that MySQL and InnoBase have both been ‘Oracle Enterprised’, said > product is now restricted to MySQL Enterprise customers.. > > Some quick searching has suggested Percona XtraBackup as a potential > FOSS alternative. > What backup techniques do people employ around these parts for backups > of large mixed MySQL data sets where downtime *must* be minimised? > > Has your backup plan ever been put to the test?

You should put it to the test regularly, not just when it’s needed. …

Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.

Previous podcasts in the backup series:
Backup Series #1: Backup Glossary
Backup Series #2: Backup Tools You Already Have (mysqlhotcopy and mysqldump)
Backup Series #3: Looking Through the Lenz (snapshots including LVM)
Backup Series #4: Advanced Logical Export Features (advanced mysqldump)

Special Guest Valentine Gostev, QA engineer at Percona, talks about Percona's Xtrabackup, an open source tool for consistent online physical backup of InnoDB and XtraDB.

…

Previous podcasts in the series:
Backup Series #1: Backup Glossary
Backup Series #2: Backup Tools You Already Have (mysqlhotcopy and mysqldump)
Backup Series #3: Looking Through the Lenz (snapshots including LVM)

ERRATA:
Giuseppe Maxia points out via twitter "The mysqldump option to skip table creation is not '--no-create-table' but '--no-create-info' " -- he's right!

Feedback/News:
We had some great feedback on the snapshots podcast, and we explain how to get some swag from us.

read more

Every ones loves hands-on tutorials with code snippets and stuff to establish the knowledge that something can be done. So here is my first one; MySQL Enterprise Backup 3.5. The new and shiny backup solution for MySQL. Our clients, for a long time, are asking for an enterprise ready, stable, safe, quick, easy, feature rich, [...]

Previous podcasts in the backup series:
Backup Series #1: Glossary
Backup Series #2: Backup Tools You Already Have (mysqlhotcopy and mysqldump)

Show Notes:

This week we have Lenz Grimmer with us to speak about snapshot backups in general, MySQL backups with snapshots, and mylvmbackup, a script Lenz wrote and maintains to easily take consistent MySQL snapshot backups.

Rate/review us in iTunes (website with a link to open in iTunes, or link to iTunes [may not work in Chrome, does work in Firefox]).

…

On Thursday, February 10, at 10am PST, there is a free webinar about Managing Big Data with Percona Server, XtraBackup and Tungsten. Quoting from the announcement:

Big data is a big problem for growing SaaS businesses and large web applications. In this webinar, we'll teach you how to set up Percona Server, XtraBackup, and Tungsten to manage Terabyte+ databases and scale to millions of transactions a day. We'll discuss the latest features for high transaction performance like InnoDB buffer pool dump/restore and HandlerSocket, our favorite tricks for backup, restore, and provisioning of large data sets, and how to replicate scalably and safely using Tungsten Replicator with parallel apply.

The presenters are representatives of both Percona and …

Show notes:

Previous backup series podcast:
Backup glossary [Backup series #1]

Feedback:
In the backup glossary we asked if anyone knew how to get logical data out of physical InnoDB files. Mike Hamrick of Blue Gecko wrote in to say that Percona's InnoDB recovery tool helps to do just that. He writes:

"The 'page_parser' tool will read ibdata files or individual .ibd files and turn them into a series of 16k page files suitable for processing with a tool called 'constraints_parser' which spits out a number of tab separated lines of table data suitable for a LOAD DATA INFILE.

read more

Show Notes:
[note: about 11 minutes in, Sarah's audio gets a little noisier, and our audio engineer simply could not get rid of all the noise]

A slave is not a backup, although a slave can be used to get backups more easily.
You don't test backups, you test restores.
logical vs. physical
cold vs. hot vs. warm
consistent vs. inconsistent

strings(1) - Linux man page
"A foolish consistency is the hobgoblin of little minds" - Ralph Waldo Emerson [note: Sheeri misquotes this as "Consistency is the hobgoblin of little minds"]

read more

MySQL is a popular database management system, a most widely used for web databases, offers efficient techniques to ensure absolute integrity of the database. When you start MySQLD (MySQL Server), it automatically examines all the database tables for integrity. MySQL Server also fixes the tables if they are marked as ´not closed properly´ or ´crashed´. It backs up all the corrupted database tables and makes their entries in the error log. Though, it works in a number of cases, but cannot necessarily fix all the corruption issues and fails to recover MySQL database.

The MySQLD technique cannot repair MySQL database if it is severely damaged. As a practical scenario, where MySQL Server cannot handle the table corruption, you may encounter the below error message-

"Got an error from thread_id=1, mi_dynrec.c:368"

The above error message occurs when you start the MySQL server for accessing the database …