I still remember the day. I was working for a large systems integrator on a very major account, and had responsibility for some DB2 databases, but also had the unfortunate duty of also taking care of some legacy IMS databases (hierarchical IBM databases used quite a lot ‘back in the day’), which I knew next to nothing about. One morning I was asked to make a small change to a very large and prominent IMS database – just remove one record – what could go wrong? The guy who knew IMS real well was gone so I put together a program to do the job (yes, you actually had to write a program to do it), and ran it after hours. I came in the next day and tried to access some records in the database. Nothing.
All gone, except for – ta-da – the one record I wanted to remove! At the instant I realized my mistake, I was acutely aware of two things :
(1) I was in serious trouble if I didn’t have a backup, and (2) I was in desperate need of a complete change in underwear.
Well, fortunately for me, I did have a backup that I restored (although it took a while to run), and I learned my lesson well that day: Always practice good disaster recovery techniques. DBA’s usually focus on things like performance tuning and the like, which to be sure, are important, but in the end a DBA is paid to do one thing very well: protect the data. Don’t do that, and you’ll get an all expense paid trip to the unemployment office. This being the case, as a DBA, I always made sure I knew my database’s backup/restore utilities inside and out and that I routinely practiced various restore scenarios on my test servers with the backups I took of my databases each day.
When it comes to MySQL, there are a variety of different methods you can use to backup your databases, with the most popular being the mysqldump utility as shown in one of our polls that ran on the MySQL website a while back:



Although the above methods serve many backup/restore use cases well, there’s always room for improvement. And in MySQL 6.0 (now in alpha), a new backup and restore utility is being introduced that many will find beneficial. Let’s take a quick look at the new backup/restore tool and take it for a quick test drive through a number of common scenarios to see how well it performs. Overview of MySQL 6.0’s Backup and Restore Utility

MySQL 6.0’s backup/restore utility is a completely new tool in the server that you should find quite easy to use and work with. One feature of the new utility that I especially like is that it allows for backup and restore commands to be issued right from a mysql client command prompt, which means no shelling out to an operating system prompt and/or scripting shell scripts to run a backup. In this aspect, the new backup tool mirrors what a DBA would find in Microsoft SQL Server’s backup/restore repertoire (which, of all the other database vendors, I found in my experience to be the best/easiest/most idiot-proof to use).

The syntax in the current alpha release looks like the following:

BACKUP {DATABASE | SCHEMA} { * | db_name [, db_name] ... } TO 'image_file_name'
[WITH COMPRESSION [COMPRESSION_ALGORITHM [=] algorithm_name]];


RESTORE FROM 'image_file_name';
Keep in mind that as of this writing, MySQL 6.0 is in alpha and additional syntax changes will likely be made for upcoming beta releases. To see the most current syntax for MySQL 6.0’s backup and restore utility, see the 6.0 manual at: http://dev.mysql.com/doc/refman/6.0/en/backup-database-restore.html.

Beside having easy to use and remember syntax, one much anticipated feature of the new 6.0 backup is the ability to do online backups of the MyISAM storage engine, which, up until this time, had to backed up in an offline fashion. When I say the 6.0 backup for MyISAM is online, I mean it is non-blocking for DML activity – so any INSERT, UPDATE, and DELETE activity can run unhindered while the backup is in progress. It should be noted that any DDL (ALTER TABLE, etc.) will be suspended and blocked while the backup is process.

In addition to being online for DML activity, the MyISAM backup in 6.0 is also a “native” backup and is not “logical” in nature. A native backup differs from a logical backup in that the backup file is taken from the actual underlying O/S files and is not a readable SQL-based file like a logical backup is. For example, the backup files produced by the mysqldump utility are logical-based backups. The benefit of a native backup is that it typically runs faster and take less storage space that a logically-based backup.

In addition to being able to do MyISAM online (non-blocking DML) backups, the 6.0 backup utility can also do the same for transactional databases such as InnoDB, Falcon, and other transactional-oriented engines that support consistent snapshot. For those of you using MySQL Cluster, note that the new 6.0 backup won’t support Cluster primarily because Cluster already has its own online backup.

The difference for MyISAM vs. transactional engines at this time is that while MyISAM has a native backup, transactional engine backups are logical in nature. In upcoming versions of the 6.0 backup, native drivers will be developed for MySQL’s key transactional engines.

In terms of how the new 6.0 backup utility differs from mysqldump, the following table communicates the core differences:

Full article...