This is what I need to do, what is the best approach for doing this?
I need to take backup of few tables in mysql, then I need to drop that entire DB, recreate it, (and sth else that's not relevant here) and then restore these backed up tables. This backup and restoring should be as fast as possible. How can I do this?
Here's a few ideas. I'm sure there are more ways to do it.
1) MyISAM or InnoDb? Which version? How many rows in the tables?
2) Is the backup database accessible on the network?
If so why not just create the backup database tables on the fly using something like:
use bu_database; lock tables old_database.table1, ... old_database.tablen; flush tables old_database.table1, ..., old_database.tablen; create table1 like old_database.table1; insert into table1 select * from old_database.table1;
....
create tablen like old_database.tablen; insert into tablen select * from old_database.tablen;
compare the table row counts from both databases to make sure all rows were transferred.
Then if ok, drop the old_database.
If the table has a lot of indexes, a faster method would be to not define the indexes until later and just do a
use bu_database;
create table1 select * from old_database.table1;
alter table1 add index ..., add index ..., add index. /*build all indexes with 1 statement*/
Of course you need to know what the old index structure was for the Alter Table command. Keep in mind that an Alter Table will create a copy of the table while it is building the indexes. I'm not sure why it has to do this when building indexes, but it does. (Unless they've changed it in 4.1 and if so someone can jump in and correct me if I'm wrong.)
3) If the backup database is not accessible on the network then you will need to do a MySQLDump (or MySQLHotCopy for ISAM/MyISAM tables) and transport the file over to the new machine and run the script there. You will then have to check the row counts manually before dropping the old database. This is usually the preferred method for large tables (several million rows).
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
