what type of storage engine are you using? If you are using InnoDB switch off the binary logging as this is being generated for recovery purposes. In this case you are recovering so for the duration of the restore disable.
It has been a while since I have looked at a dump file to see the actions that are being taken but it is more efficient to have the table created without the indexes and then to add the indexes once the data has been loaded.
I had written an article Data Recovery IT Integrated Business Solutions about backups and in particular recovery which drives the backup solution that you need to have in place. Your focus should be on the amount of time needed to recover and this will determine your database setup.
unfortunately there is no 1 parameter that will boost the performance of your database. You need to look closely at what is happening even looking at the I/O that is being performed on your disks. For instance, if your database exists on the same disk as your transaction logs then you will have huge I/O both into the table and during the generation of the transaction log.
There is a parameter innodb_flush_log_at_trx_commit which when set can determine when transactions are flushed to the transaction log. The default is to output every second. However, in this case it could be set to another value.
You may also need to look at the memory available to the database and to have the restore (which is simply an INSERT statement) run as a transaction with a BEGIN and an END.
database size : 100gb
storage engine : innodb
backup using mysqldump
restoration time : >5days
1.when iam restoring the database of 100gb it is taking more than 5days to restore everything.could some one tell me how can i minimize this time?
2.when checking the table type after restoring it is showing the table type as Myisam, but dump file has all the table are in innodb.Why the table type has changed after restoration?
3.when restoring the schema from the schema.dump file iam getting an error
like this ERROR 1166 (42000) at line 2786: Incorrect column name 'CAST( GROUP_CONCAT( CONCAT(column_name) ORDER BY columns',')'
The order of restoring is extremely important. The schema is needed before you can restore the data. The schema ideally should only consist of the tables, without any indexes, foreign key constraints. This will speed up the load process and the indexes and foreign keys constraints can be enabled after the data has loaded.
The stored procedures can be applied at this point.
The triggers should be the last thing to load as it will corrupt your data if you have these enabled before the data load.