469,572 Members | 1,306 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

InnoDB backup and restore

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
3 10919
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
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
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.

Similar topics

3 posts views Thread by Martin Gill | last post: by
reply views Thread by Rajesh Kapur | last post: by
1 post views Thread by Rajesh Kapur | last post: by
4 posts views Thread by Hardy | last post: by
1 post views Thread by fuzzybr80 | last post: by
1 post views Thread by fjm | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.