By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,120 Members | 1,799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,120 IT Pros & Developers. It's quick & easy.

InnoDB backup and restore

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Jul 20 '05 #2

P: n/a
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.

Jul 20 '05 #3

P: n/a
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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.