mysqldump to a hostname which is proxy/router (not MySQL server)

Question

I do a mysqldump with those options --single-transaction --routines --events --max_allowed_packet=1G --flush-logs --master-data=2. The desination hostname is not a MySQL server, it's a proxy/router. I don't know to which server the proxy routes the mysqldump command.

I am now testing a PITR restore with binary logs.

In dump file there is this binary log position:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000302', MASTER_LOG_POS=366;

I have a 3 node Galera with MariaDB 10.1.x and all have different binary logs numbers.

hostname1 # ls -lrt mysql-bin.*
-rw-rw---- 1 vcap vcap 1073742616 Nov  3 12:35 mysql-bin.000439
-rw-rw---- 1 vcap vcap 1073745434 Nov  4 02:59 mysql-bin.000440
-rw-rw---- 1 vcap vcap 1073742123 Nov  4 18:34 mysql-bin.000441
-rw-rw---- 1 vcap vcap 1073758802 Nov  5 09:42 mysql-bin.000442
-rw-rw---- 1 vcap vcap 1073742164 Nov  6 00:42 mysql-bin.000443
-rw-rw---- 1 vcap vcap 1073749385 Nov  6 14:58 mysql-bin.000444
-rw-rw---- 1 vcap vcap 1073798651 Nov  7 04:54 mysql-bin.000445
-rw-rw---- 1 vcap vcap 1073743259 Nov  7 17:41 mysql-bin.000446
-rw-rw---- 1 vcap vcap 1073741936 Nov  8 08:26 mysql-bin.000447
-rw-rw---- 1 vcap vcap        190 Nov  8 08:26 mysql-bin.index
-rw-rw---- 1 vcap vcap  117458827 Nov  8 09:59 mysql-bin.000448


hostname2 # ls -lrt mysql-bin.*
-rw-rw---- 1 vcap vcap 1073741998 Nov  3 12:39 mysql-bin.000262
-rw-rw---- 1 vcap vcap 1073750219 Nov  4 03:02 mysql-bin.000263
-rw-rw---- 1 vcap vcap 1073741924 Nov  4 18:38 mysql-bin.000264
-rw-rw---- 1 vcap vcap 1073741994 Nov  5 09:47 mysql-bin.000265
-rw-rw---- 1 vcap vcap 1073751883 Nov  6 00:47 mysql-bin.000266
-rw-rw---- 1 vcap vcap 1073743231 Nov  6 15:00 mysql-bin.000267
-rw-rw---- 1 vcap vcap 1073742137 Nov  7 04:58 mysql-bin.000268
-rw-rw---- 1 vcap vcap 1073762789 Nov  7 17:45 mysql-bin.000269
-rw-rw---- 1 vcap vcap 1073742147 Nov  8 08:30 mysql-bin.000270
-rw-rw---- 1 vcap vcap        190 Nov  8 08:30 mysql-bin.index
-rw-rw---- 1 vcap vcap  108886838 Nov  8 09:56 mysql-bin.000271

hostname3 # ls -lrt mysql-bin.*
-rw-rw---- 1 vcap vcap  945381042 Nov  3 02:00 mysql-bin.000286
-rw-rw---- 1 vcap vcap  896241094 Nov  3 14:00 mysql-bin.000287
-rw-rw---- 1 vcap vcap   26290226 Nov  3 14:17 mysql-bin.000288
-rw-rw---- 1 vcap vcap  852890770 Nov  4 02:00 mysql-bin.000289
-rw-rw---- 1 vcap vcap  820878638 Nov  4 14:00 mysql-bin.000290
-rw-rw---- 1 vcap vcap   26578488 Nov  4 14:17 mysql-bin.000291
-rw-rw---- 1 vcap vcap  834219929 Nov  5 02:00 mysql-bin.000292
-rw-rw---- 1 vcap vcap  828066347 Nov  5 14:00 mysql-bin.000293
-rw-rw---- 1 vcap vcap   25501584 Nov  5 14:17 mysql-bin.000294
-rw-rw---- 1 vcap vcap  851612877 Nov  6 02:00 mysql-bin.000295
-rw-rw---- 1 vcap vcap  898792694 Nov  6 14:00 mysql-bin.000296
-rw-rw---- 1 vcap vcap   28571742 Nov  6 14:17 mysql-bin.000297
-rw-rw---- 1 vcap vcap  920553238 Nov  7 02:00 mysql-bin.000298
-rw-rw---- 1 vcap vcap 1002800682 Nov  7 14:00 mysql-bin.000299
-rw-rw---- 1 vcap vcap   29489186 Nov  7 14:17 mysql-bin.000300
-rw-rw---- 1 vcap vcap        323 Nov  8 02:00 mysql-bin.index
-rw-rw---- 1 vcap vcap  862132038 Nov  8 02:00 mysql-bin.000301
-rw-rw---- 1 vcap vcap  574366864 Nov  8 09:57 mysql-bin.000302

It seems to mysqldump command was routed via proxy to hostname3 which means I need to copy the bin logs of hostname3 for PITR restore? Can I also do a bin log restore with hostname2 or hostname1? The binlogs should have the same contents (but other filename)?

Is it recommended to point mysqldump to a virtual node?

The bin logs are local on disk, not streamed to S3 or other blob store. If hostname3 100% dead I can't do a PITR restore?

Update:

Quote from Point-in-time Recovery in MySQL Galera Cluster

Binary Logging in MySQL Galera Cluster

By default, binary logs (if enabled) will be located under the MySQL data directory. Binary logs are not synced when a Galera node performs SST or IST during node initialization, or when using XtraBackup to backup the datadir. It is mandatory to enable log_slave_updates on a Galera node so events originating from other nodes in the cluster will be captured when local slave threads apply writesets.

To enable MySQL binary log on the cluster nodes, add the following lines to the [mysqld] section of MySQL configuration file (for ClusterControl users, use the Manage Configurations tab) on the database node(s) that you want to write binary logs (you can enable on all nodes since Galera is as fast as the slowest node). Thus, in any case, binary logging will come with a performance penalty:

log-bin= log-slave-updates Perform a stop/start node or rolling restart of the cluster to apply changes. For ClusterControl users, use the Manage >> Upgrades >> Rolling Restart.

We enabled it, but still issue.

> show global variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

Answer 1

Caveat: I do not know that this Answer is correct; take it as something to ponder.

1. Hang onto the binlogs from the machine where the dump was taken.
2. Create a fresh cluster with no data.
3. Load the dump onto one node; this will propagate to the other nodes.
4. Replay the binlogs up to the Point-In-Time you need. This step may require moving the binlogs to the node in question.

A possible failure in this... The gcache is used by Galera, not the binlogs?? As you point out, the writes between the dump and the PIT may be scattered across binlogs and not easy to find.

Research PITR and Galera.

See OP's quote about PITR in PXC.