1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice
  2. Dear Pleskians, please read this carefully! New attachments and other rules Thank you!
    Dismiss Notice
  3. Dear Pleskians, I really hope that you will share your opinion in this Special topic for chatter about Plesk in the Clouds. Thank you!
    Dismiss Notice

Backup fails to restore MySQL views properly

Discussion in 'Plesk 9.x for Linux Issues, Fixes, How-To' started by Patrick Mulvany, Nov 18, 2010.

  1. Patrick Mulvany

    Patrick Mulvany New Pleskian

    15
    60%
    Joined:
    Sep 23, 2010
    Messages:
    2
    Likes Received:
    0
    I am getting backups failing to restore properly with Plesk even though they appear to complete cleanly.

    Running the following command :-
    /usr/local/psa/bin/pleskrestore --restore ~/backup.tar -level all -map ~/restore.map

    Appears to work fine.

    However if you have any views in your schema that cross schemas these may fail to restore.
    e.g. schema1 has a view pointing at a table in schema2 this may fail.

    I am not certain of the exact sequence of what is happening as I am restoring multiple schema.

    I do know that a mysqldump of all the databases is able to handle this scenario as it creates temporary tables during the main schema generation and then once all the temp and real tables in all the schemas are created replaces the temp tables with views.

    Based on this I am guessing that Plesk backups up the databases individually and then restores them either one at a time or in parallel.

    If this is the case is there anyway of forcing the sequence of database restores other than doing them as individual manual restores one at a time? i.e. Restore schema1 then schema2 then all the others there order does not matter.

    This still would not cover the more complex scenarios which mysqldump can handle (i.e. schema1 has a view on schema2 and vice versa) but it would minimise the amount of repair work required.

    My current work around is to manually do a database dump and then extract the temporary tables and views from the mess.
    mysqldump -uadmin -p --no-data --no-create-db --no-tablespaces --all-databases > all-schema.sql

    Any ideas welcome

    Paddy


    Parallels Plesk Panel version 9.0.1

    MySQL ... Various versions 5.1.37-5.1.52
     
Loading...