I take the backup of mysql server using mysqldump command.The size of my database is around 70GB and the size of the .sql file (generated after backup) is around 30GB.

Whenever I need to restore the entire database it takes around 3-4 days to restore the complete database.

I am restoring the database on system having mysql-server 5.0,RHEL 5 OS, 4 GB of RAM, 8 Virtual CPUs 2.66 GHz.

Most of the tables are Innodb very few are MyISM.

Is there any performance tuning to be done to restore the DB Quickly.

That is, most likely, that you have triggers in your data base. You might want to read up on the --skip-triggers option to mysqldump.

It would be a good idea to get a complete schema and extract the triggers from that schema into a separate file, say, triggers.sql, that you can use to restore them after the data base is loaded.

When you have triggers enabled, every single row that is loaded into a table that a trigger is enabled for is going to execute that trigger; takes forever plus a week, that. You don't need the triggers to execute because whatever they did was already done in your main data base and you're copying the results to the other machine.

You might also want to think about mysqlhotcopy if it's applicable to your site.

Hope this helps some.

I am not using any triggers,but few of my Innodb tables are of 10-15 GB with the indexes of around 8-10 GB,during restoration when I watch im mysqladmin process I see that each insertion corresrponding to these tables take time around 15 to 40 seconds.

and mysqlhotcopy u have suggested I guess that can be used only for MyISM tables but I have very few MyISM and more Innodb tables.

Well, if it ain't triggers it's over-indexing that can kill you.

Something to try (what the heck, takes less time to do this than wait forever).

Get a full schema and yank the indexes out to a separate file, say, indexes.sql, then delete them all from the full schema. Be sure to include "drop database if exists" and "drop table blah if exits" while you're at it for your back up data base (you can do that with an option to mysqldump. Unload (dump) your tables into individual table.txt (or whatever format you like) files. Drop and build the data base with the schema minus the indexes. Load the data base tables from the individual dump files (make a shell program to do that one at a time; they'll load fast). Finally, create the indexes; e.g., mysql --user=whoever --password=whatever dbname < indexes.sql.

Or, if it's just a back up data base, don't bother with the indexes at all. If you're only working with table information, the stuff you actually need, you don't need indexes (because you've saved them off as a separate indexes.sql file and can build them whenever necessary). Of course if somebody's going to actually use that data base then you probably ought to build the indexes but otherwise, why bother? Indexes do not affect dumps (which are just start at record zero and go till you run out of data). For that matter, if the data base is not used for anything at all, settle for unloading everything into flat files and be done with it -- of course that depends on how you intend to use the back up data base.

Another thing you might want to do is sit down and evaluate the indexes -- sounds like they're kind of overdone? Maybe? Index-creep is quite common; somebody will throw on an index without really thinking trough why they're doing it (or not adding a column or two or a join instead of an index). Doesn't hurt to have somebody that knows what they're doing take a good, hard look.

Hope this helps some.

Can you please guide me step by step procedure or with any example ? as I have not worked too much on this.

If you use the mysqldump --no-data or -d option (do not dump table contents), you will have a data base schema file (with no data in it).

If you look at that file in a text editor, you're looking for lines of this form:
Those are the indexes.

The ones that are going to give you trouble (take forever) will be those with UNIQUE KEY. As rows are added, the entire table is scanned from the top to determine whether the unique value already exists; row-by-row, every single row.

So, what you need to do is scan through the entire schema file looking for "KEY" and make sure that the index declarations do do not spread of two or more lines -- if they do, join the lines so the entire KEY is on one line. That is, the line should begin with KEY and end with ).

When you're done with that, save that file then use the grep utility to extract all the lines that have the pattern KEY in them, some thing like
The file indexes.sql will have your indexes in it.

Then, delete the indexes from your schema file:
Now you've got a schema with no indexes (you may want to make sure that a "drop database if exists 'dbname'" is at the top of that file -- before the "create database 'dbname'" statement).

So, create your back up data base
Then unload the tables using mysqldump; just the data, no schema. Copy those files over to the back up server. Then load your tables using those files (they'll load quickly with no indexes present). Finally, create the indexes:
That ought to go a lot faster.

Now it would be a really good idea to get into the MySQL manual and look through the sections about backing up a data base -- there's a lot of good information there. It would also be a good idea to examine the indexes, look at them with a jaundiced eye for whatever is overkill. Ask yourself if UNIQUE indexes are really necessary -- any table may have a unique index, but that table doesn't need more than one unique index. If there is a main table with a unique index, that may all right but any table that joins to it based on that unique index does not need a unique index. Look for lots of indexes on a given table, ask if they make sense. Experiment a little. Read the user guide about indexes and indexing strategies.

This isn't something you'll learn in five minutes but given the amount of time it takes to load your back up data base now, a day or two studying up a little might be time well spent.

Hope this helps some.

1 members found this post helpful.