HI

we are having a mysql database which is around 200gb.iam using mysqldump for backup this database.

the restoration is taking so much of time.

Could anyone suggest me to speed up backup using mysqldump and also how can i speed up the restoration process.

Do i need to set any parameters in my.cnf to speed up backup as well as restoration or need to make any changes to the config file.

Please give me your thoughts

Thanks&Regards
shreyas varma

Hi,

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.

HI


first of all thank you for the response.
we use innodb storage engine and database size is 200gb.

dumping of databases using mysqldump client.

could you please suggest me, how I can speedup the restoration.

Do i need to change any config parameters to speedup the process.

Please provide me the thoughts

Thanks in advance

Shreyas varma

Hi,

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.

HI

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',')'

Could someone please give your thoughts

Thanks&Regards
shreyas varma

Just do one thing take backup of your schema only than restore that schema
If it successfully without any error
than restore the data

while taking dump use --no-autocommit
restoring big database depends on you system also
use Linux as OS (64 bit),HIGH RAM,6-8 multiple CPU

set innodb_buffer_pool HIGH
make innodb_log_file_size HIGH
innodb_flush_log_at_trx_commit to 0

Then try ...

Thank you very much ankur.

HI,

If I have seperate dump file for schema,data,routines and triggers, Do i need to restore them in order like first schema then data then routinrs and triggers
or else I can restore in any order.

Please provide your thoughts on this,

Thanks in advance
Shreyas Varma

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.