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 …

Bugzilla is a web-based bug tracking and testing application that is used by many software development companies. It extensively various databases including MySQL for its backend support especially because both of these applications are freely available. Although this tool runs pretty fine in most cases, it can get stalled when the some of the MySQL tables get corrupt. In such cases, you are unable to perform various tasks on the database. You can use the 'myisamchk' command with different parameters to repair MySQL database. If it does not work, then you should use a professional MySQL recovery software to recover MySQL database.

Discussing a similar situation in Bugzilla, consider you get the following error message while generating a bugs report:

"undef error - DBD::mysql::db selectrow_array failed: Table 'attach_data' is
marked as crashed …

A question that came up during the MySQL track at the UKOUG conference in Birmingham was "Can I exclude only a few databases from mysqldump? Let's say that I have 50 databases, and I want to dump all of them, except a few."
As many know, mysqldump has an option to ignore specific tables. SO if you have 1,000 tables in a databases, you can tell mysqldump to dump all the tables except a few ones.
There is no corresponding option to exclude one or more databases.
However, if you know your command line tools, the solution is easy:
First, we get the list of all databases:

mysql -B -N -e 'show databases'
information_schema
employees
five
four
mysql
one
performance_schema
six
test
three
two

-B forces batch mode (no dashes box around the data), while -N gets the result without the headers.
Now, let's say that we want to exclude databases four, …

The open-source xtrabackup tool from Percona brings much needed hot backup functionality to MySQL deployments.  In this database journal article we discuss logical, cold, and hot backups, then explain how to use xtrabackup on your MyISAM, InnoDB, and XtraDB tables to create at-the-ready backups.  We then take you through the step-by-step process to restore them, and even the process of point-in-time recovery too.

Database Journal – Hotbackups with Percona’s Xtrabackup

In my continuing MySQL 5.5 blog series, today I am covering what's new on the replication front.  MySQL replication is my favorite server feature and what drew me to MySQL during my tenure with Embarcadero Technologies.  Others seem to agree as based on community and customer surveys, MySQL replication is the most popular and widely used database feature.  Mostly because it is easy to set up and ease, it enables scalability and provides a pretty robust solution for data redundancy, backup and overall availability.  In MySQL 5.5 replication has been enhanced in response to user requests that MySQL replication:

• Ensure data consistency between master and slave servers
• Immediately detect if replication is not working
• Allow a crashed slave to automatically recover from the master relay log
• Allow users to filter …

I have a 5G mysqldump which takes 30 minutes to restore from backup.  That means that when the database reaches 50G, it should take 30x10=5 hours to restore.  Right?  Wrong.

Mysqldump recovery time is not linear.  Bigger tables, or tables with more indexes will always take more time to restore.

If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take:

Backup is 80G
Copy is at 70MB/s.
10G is already complete.
= ((80-10) * 1024)/70/60 = ~17 minutes


I can tell progress with mysqldump by monitoring the rate at which show global status like 'Handler_write'; increases and compare it to my knowledge of about how many rows are in each table.  But progress != a magic number like "17 minutes".  Not unless I do a lot of complex modeling.

I am …

During the MySQL Users Conference, Edward Screven did a keynote presentation that made many of us feel warm and fuzzy about Oracle's future plans for MySQL. If you advance 16m 25s into the presentation, it even gives something to rejoice the MySQL Enterprise customers: "Backup is now included". He didn't say much more after that. Asking around at the conference the days following this announcement, I couldn't get a straight answer about when and how would it be available for existing customers.

Now, 6 months later (give or take a couple of weeks), the MySQL Enterprise Features page has no signs of the now included MySQL Enterprise Backup (the utility previously known as InnoDB Hot Backup) and there has been no other news supporting Edward's announcement …

Any corruption in MyISAM table is a terrible situation for Database Administrator. These MyISAM tables are the most important components in the MySQL Database to store personal as well as professional data, and the corruption in these tables may inaccessibility to the database. In this situation, a database backup allows you to restore the database and access the records in the table. Duplicate file (Backup) of the database is mostly stored on a different storage media, the changes of database corruption still exist. In that situation, you will need to perform MySQL database repair by the using of third party software.

Some error messages that enable you to identify that your MyISAM table (student) is corrupt:

“Student.frm is locked against change”
Or
“Got error message student from table handler”

Or
“Can't find file student.MYI (Errcode: nnn)”

The record …

Let’s suppose that your backup process looks like this: you stop a replication slave, shut down MySQL, and copy away the data directory. Assume that the slave is perfect and has the same data as the master. Nothing is broken, nothing is wrong, everything is working fine. In most cases, this should work, right?

Under what kinds of circumstances will you not get all your data back if you restore the file copy and start MySQL?

Related posts:

1. Pop quiz: how can one slave break another slave
2. Progress on High Performance MySQL Backup and Recovery chapter
3. …