InnoDB backup and restore

[Reuben Pearse]

Hi all,

I've just converted the tables in a big database (approx 27 million
records) from MyISAM to InnoDB. When I was using MyISAM I backed up
the database by stopping MySQL and then copying the MYI, MYD, frm
files in the database directory, and restored the database in the same
way.

Is there a similar way to backup and restore InnoDB tables, or do I
have to use the InnoDB Hot Backup tool?

Reuben
re****@presence-systems.com

[Bill Karwin]

Reuben Pearse wrote:

Hi all,

I've just converted the tables in a big database (approx 27 million
records) from MyISAM to InnoDB. When I was using MyISAM I backed up
the database by stopping MySQL and then copying the MYI, MYD, frm
files in the database directory, and restored the database in the same
way.
FWIW, I recommend using mysqldump even for MyISAM tables, instead of
relying on filesystem copy of the live database files. I've used other
RDBMS products that use a slightly different internal file format from
platform to platform, or from version to version.

If you back up data in its "live" binary format, and then months or
years later you need to restore, but at that time the MySQL server has
been moved to a different host, you risk being unable to restore.
Is there a similar way to backup and restore InnoDB tables, or do I
have to use the InnoDB Hot Backup tool?

InnoDB Hot Backup sounds nice, but it costs lots of money and it's
probably overkill for most users.

For reasons above, I'd recommend using `mysqldump --single-transaction'
for backing up InnoDB tables.

Regards,
Bill K.

[Reuben Pearse]

Thanks for that Bill.

The main problem I've got now is that it takes forever to restore a 27
million record database from a backup file created with mysqldump.

Is there a quicker way to restore InnoDB databases?

Reuben
re****@presence-systems.com
Bill Karwin <bi**@karwin.com> wrote in message news:<cn********@enews2.newsguy.com>...

Reuben Pearse wrote:

Hi all,

I've just converted the tables in a big database (approx 27 million
records) from MyISAM to InnoDB. When I was using MyISAM I backed up
the database by stopping MySQL and then copying the MYI, MYD, frm
files in the database directory, and restored the database in the same
way.

FWIW, I recommend using mysqldump even for MyISAM tables, instead of
relying on filesystem copy of the live database files. I've used other
RDBMS products that use a slightly different internal file format from
platform to platform, or from version to version.

If you back up data in its "live" binary format, and then months or
years later you need to restore, but at that time the MySQL server has
been moved to a different host, you risk being unable to restore.

Is there a similar way to backup and restore InnoDB tables, or do I
have to use the InnoDB Hot Backup tool?

InnoDB Hot Backup sounds nice, but it costs lots of money and it's
probably overkill for most users.

For reasons above, I'd recommend using `mysqldump --single-transaction'
for backing up InnoDB tables.

Regards,
Bill K.

[Bill Karwin]

Reuben Pearse wrote:

The main problem I've got now is that it takes forever to restore a 27
million record database from a backup file created with mysqldump.
Is there a quicker way to restore InnoDB databases?

This web page is probably worthwhile reading:
http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html

There are several tips for speeding up bulk data loads to InnoDB tables.

Regards,
Bill K.