Restore partial backup
Hi,
I have a full backup, how we can restore simultaneous in every node??
I do this:
1- Full Backuo from UI
2- Restore Backup from UI (in log I see this "The required datafiles (see previous log message) must be manually imported to the node(s).")
3- I had replace the file in database folder of a node
4- Restart mysql
But after this operation the cluster is not synchronized.
How I can do restore in all nodes at the same time without lose sync??
Regards
Salvatore
-
Official comment
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.12MariaDB [(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.ibdNow, 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.12I 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 TABLESPACEBR
johanComment actions
Please sign in to leave a comment.
Comments
1 comment