← Back to team overview

maria-discuss team mailing list archive

Re: restore db from dump?


Am 05.04.2017 um 21:19 schrieb Karthick Subramanian:
That's the work around though.

But what OP asks is also a valid scenario in real life scenario. If we
have such options like selective load, remapping databases then its
really a wonderful.

"poblem" is that the dump is not more than a ton of sql statements including 'drop table', 'create table', 'insert into'.... and restore the dump, well, executes that statements

nothing to do on the server side

just write better backup-scripts which iterate through the available databases and tables, creates subfolders with the database name and exetcutes mysqldump for each of the tables - then you have your single files for every table in a seperate folder and for restore the whole database "cat *.sql > database.sql" - that's safe and much easier then split the big file because you only need a part of it

On Thu, Apr 6, 2017 at 12:27 AM, Reindl Harald <h.reindl@xxxxxxxxxxxxx
<mailto:h.reindl@xxxxxxxxxxxxx>> wrote:

    Am 05.04.2017 um 20:43 schrieb l vic:

        I have "mydump.sql" with dump of all databases in cluster. I am
        to selectively restore one database only from the scenario below:
        Have created  2 databases: test1 and test2 with table "person":
        | Field | Type         | Null | Key | Default | Extra |
        | name  | varchar(255) | YES  |     | NULL    |       |

        Insert name "one" into table "person" for both databases:
        insert into person values ('name');
        Backup all data into "mysqldump.sql":
        mysqldump -P3306  -uroot -pdbpass --all-databases >mysqldump.sql

        drop database test2 (my mistake);
        and insert additional data into "test1":
        insert into person values ('name1');
        insert into person values ('name2');

        Now, i'd like to restore database test2 only:
        mysql -uroot -pdbpass -e "create database if not exists test2;"
        mysql -uroot -pdbpass test2 < mysqldump.sql

        Success !! test2 is restored with all the data, but when I'checked
        "test1" i only see one record "name" - two records that I've
        added after
        backup are lost
        It looks as mysql just restore all data in dump file, regardless of
        specifying the "target" database.

        Is that expected, or do i have some error in my backup/restore
        If this is expected, is there some comprehensive method to
        restore one
        database selectively?

    when you say "--all-databases", well, than all databases and their
    drop/create statements are included because you said to do so

    if you want a specific database, well, the specify it or at least
    manually edit the dump before restore and remove anything you don't ant