Restore partial backup

Comments

1 comment

  • Official comment
    Avatar
    Johan

    Hi,

    I can give a quick example. Hope it helps.

    I have 3 galera nodes:
    10.10.10.10
    10.10.10.11
    10.10.10.12

    MariaDB [(none)]> select * from johan.t1;
    +---+
    | a |
    +---+
    | 1 |
    | 2 |
    | 3 |
    +---+
    3 rows in set (0.00 sec)

    Now, the a=3 is a big mistake, i must restore a partial backup. so that only have a=1 and a=2 in my table t1.

    I restore the partial backup on 10.10.10.10 and i get this:

    ls /home/vagrant/s9s_tmp/restore/PARTIAL/johan
    db.opt t1.cfg t1.exp t1.frm t1.ibd

    Now, i need to apply those files onto mysql mysql datadir /var/lib/mysql/johan
    However, a big problem is that Galera does not replicate physical file changes.

    So , i must to like this (i do this from the UI)

    shutdown 10.10.10.11
    shutdown 10.10.10.12

    I login to the mysql server on 10.10.10.10

    Discard the table space for each table you want to restore.
    Now, in this example there is only one table (johan.t1).

    MariaDB [(none)]> ALTER TABLE johan.t1 DISCARD TABLESPACE;
    Query OK, 0 rows affected (0.01 sec)

    Then copy the t1.* (or all the database tables you want to restore, v).

    cp /home/vagrant/s9s_tmp/restore/PARTIAL/johan/t1.* /var/lib/mysql/johan/

    Change the owner:

    chown mysql -R /var/lib/mysql/johan/

    Import the table space:

    MariaDB [(none)]> ALTER TABLE johan.t1 IMPORT TABLESPACE;
    MariaDB [(none)]> select * from johan.t1;
    +---+
    | a |
    +---+
    | 1 |
    | 2 |
    +---+
    2 rows in set (0.00 sec)

    Now, lets' start up the other nodes:
    From the UI i start the nodes and tick the Initial to make an SST, so that the physical changes i did are copied over.

    I then verify on 10.10.10.11:
    mysql> select * from johan.t1;
    +---+
    | a |
    +---+
    | 1 |
    | 2 |
    +---+
    2 rows in set (0,00 sec)

    I i forgot to start with Initial (i forgot this on purpose on this node, just to illustrate) , then we can check on 10.10.10.12:
    mysql> select * from johan.t1;
    +---+
    | a |
    +---+
    | 1 |
    | 2 |
    | 3 |
    +---+
    3 rows in set (0,01 sec)

    Ouch, i forgot to start 10.10.10.12 with Initial to make the full SST, so i stop this node again, and start it with Intial (to get the SST, else the node wlll not get the changes.

    Now, it may be better ways, if you have a lot of data, and an SST is not an option

    Eg., you could:

    1) lock this table johan.t1 (on all galera nodes), you must do this to avoid inconsistencies, eg, if some app is still writing to this table, but it would be problematic in any case.
    LOCK TABLES johan.t1;

    2) On all galera nodes:
    ALTER TABLE johan.t1 DISCARD TABLESPACE
    3) copy the files (johan/t1.*) to every galera node (scp from the partial restore dir, and copy in the files needed to the datadir, in the correct place)
    4) On all galera nodes:
    ALTER TABLE johan.t1 IMPORT TABLESPACE

    BR
    johan

    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk