tags:

views:

548

answers:

1

The short question:
Is it safe skip ib_logfile* files while creating custom-made backup of MySQL installation with InnoDB engine used?

Custom-made backup is just copy all (or subset of) /var/lib/mysql + /etc/my.cnf to safe place.

As I know ib_logfile* files are recreated by MySQL on start + the files are really huge, so that may not be wise to store them in backup. And yes, assume MySQL is (correctly) stopped before backup.

+2  A: 

It's not safe to back up a database in the manner you're describing.

The reason is that you cannot know if some data pages are still in MySQL Server's memory, pending an I/O flush to disk. So copying the files has a good chance of copying garbage, whether or not you include the ib_log files.

Don't do what you're doing. Use mysqlbackup or InnoDB's Hot Backup product.

edit:

Read Farhan Mashraqi's blog article about InnoDB Performance Optimization, in which he says you can delete the ib_log files if you have shut down MySQL Server.

InnoDB log resizing: is not as simple as changing option and restarting. You need to shut down MySQL server and make sure it shuts down normally (check error log for errors to confirm). Then move away InnoDB log files ib_log*. Then start the MySQL server and check error log files to see if it successfully created new log files.

So yes, you should be able to do a filesystem copy of the ibdata files only. You don't need to include the ib_log files, because they should be recreated as MySQL Server starts up. I reiterate, however, that I do not recommend backing up databases with filesystem copy.

Suppose you back up a database today in this manner, using MySQL 5.0. Someday later you need to restore the backup, but by that time you'll be using some future version like MySQL 6.5 for example. Do the ib_data files still use the same internal format in that future version? Will MySQL 6.5 read those files you backed up today? You can't be sure. The backup format is intended to be more portable.

Bill Karwin
Even if I stopped the MySQL server?
Yurii Soldak
That's safer if you stop the MySQL Server but I still do not recommend it. See comment above. There are good tools available that *can* do backups safely, why don't you want to use them??
Bill Karwin
This is how existing system works now -- that easy. And I just can't change the whole backup subsystem today.
Yurii Soldak