Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    10

    Lightbulb Unanswered: Mysql backup and restoration

    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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jun 2009
    Posts
    10
    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

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jun 2009
    Posts
    10
    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

  6. #6
    Join Date
    Jun 2007
    Posts
    197

    Smile

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

  7. #7
    Join Date
    Jun 2009
    Posts
    10
    Thank you very much ankur.

  8. #8
    Join Date
    Jun 2009
    Posts
    10

    Question

    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

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •