Database backup suggestion

    Hello,


    today I had to restore a backup of a database into another database and encountered an interesting problem. Although I provided the name of the new database in the command line, but the sql loaded into the original database and drop all data.


    I checked the sql backup and found these lines:


    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `original_database` /*!40100 DEFAULT CHARACTER SET latin1*/;


    USE `original_database`;



    I recommend that you remove them from the backup because it is very dangerous, it is easy to accidentally drop the current live database and overwrite with the backup if you would like to load the backuped sql file into a new database.

    OS: Debian Buster

    i-MSCP: GIT 1.5.3-maintenance branch

    What you found in your backup folder if you browse it using FTP, is exactly what you need to restore everything when the backup snapshot was taken.

    Generally, using the restore button you find in your web UI.


    If you need something different, or something you want to firfe up using command line, it's up to you to check what's inside the SQL file.


    To conclude, I think the current behaviuor is correct. If someone needs something different he should know what he is doing...

    What kess said is actually correct and it shouldnt be changed SINCE the automated backup system is there that you can simply undo any mistakes or tests you did (or whatever).

    The purpose of that backup and restore system is that its easy to use and that it restores data so that seemingly nothing happened.

    If you want something else, do a manual backup (or own automated backups). If you gonna use backups from an another system (not backups you done yourself) and want them restore manually you always NEED to check that file is useable or if something needs to be done.


    So yes, it might be interesting to be able to chose how and what from the backup gets restored but if you are in the situation that you only partially need to restore it you will ALWAYS do it manually.

    Have a nice day. :)

    I think that the backup sql usualy used for two things :

    - restore the database

    - restore a part of the database or only some datas


    If you use large databases then not too easy remove these lines from exported sqls and this is dangerous if somebody doesn't check the sql before load.


    If somebody would like to restore the database then he could specify the database name if the export doesn't contains that, but if somebody forget to remove these lines then this will cause lot of problems.

    OS: Debian Buster

    i-MSCP: GIT 1.5.3-maintenance branch

    Your assumption includes that someone wants to restore something in a way it wasnt before. Means he/she knows what he/she does and if not, its actually their own mistake not to check something before doing anything to a live-system.

    If you want to restore only parts of it, you take a look at the backup and not just blindly do something.

    Have a nice day. :)

    I understand that if a developer thinks this is a standard backup and loads it then this is his fault. However, I think that since a standard mysql dump does not include these lines, the developer will only later discover what he has done and we can prevent it. From the system point of view, this pair of lines is not necessary for restoration.

    OS: Debian Buster

    i-MSCP: GIT 1.5.3-maintenance branch

    Well, I just checked to be sure and its exactly as it supposed to be.


    Nuxwin did what he did, because the backup and restore function should simply restore files and databases as like nothing happened. If the database as an example is currupted and the person doesnt know to how to fix it, he/she simply restores the backup and everything works again. Just one or two clicks.


    With a default mysql backup (you can still go to advanced and set the two options to drop and re-create the database yourself), it doesnt contain these lines which drop and re-create the database. Means that the person in need needs to to additional tasks.

    Have a nice day. :)

    Good evening



    As stated by kess and DrCarsonBeckett an SQL dump created by i-MSCP backup script includes the database name, as expected by the restore feature which you can trigger from the UI. Such a dump is not attended to be restored manually. If you're doing so, it is your responsability to make sure the dump will not collides with another database which could lead, as in your case, to data loss.


    From the current restore feature implementation, we cannot infer the database names. What we are doing is just to restore a given SQL dump which include all required informations.


    Make possible to restore a dump under a specific database name, given either through the UI or on CLI is a feature. You're free to create a feature request on our bug tracker ;)


    Quote from DrCarsonBeckett

    Nuxwin did what he did, because the backup and restore function should simply restore files and databases as like nothing happened. If the database as an example is currupted and the person doesnt know to how to fix it, he/she simply restores the backup and everything works again. Just one or two clicks.

    That's correct ;)

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

    SSH key for online support
    Quote from Nuxwin

    Make possible to restore a dump under a specific database name, given either through the UI or on CLI is a feature. You're free to create a feature request on our bug tracker ;)

    Hello Nuxwin, this is a good idea ;)

    OS: Debian Buster

    i-MSCP: GIT 1.5.3-maintenance branch