MySQL database point-in-time recovery with thesqlpump, possible

backupMySQL

We have some medium size MySQL databases (500 GB) on Oracle MySQL community edition, version 5.7.30.

How can we perform point-in-time recovery with mysqlpump?

It doesn't seem to have any option to specify binary log information in the dumped data like mysqldump or mydumper does.

However, neither mysqldump nor mydumper fits well our situation.
mysqldump is single threaded and too slow for us.
mydumper has only "standard" gzip compression, which wastes too much space and in addition, if compression is requested, takes all CPU resources which is causing monitoring alerts.
With external compressor utility I can limit number of cores used, but I don't know how to pipe mydumper output to an external utility.
There is currently no space available to handle uncompressed backup which could be compressed later. Also, this then is a two step process, dump first, compress later.

Best Answer

The following methods only work if the Database has binary logging enabled

METHOD #1 : Dump During Downtime

Step 1 : Stop all writes to the Database

Step 2 : Run FLUSH TABLES; FLUSH BINARY LOGS;

Step 3 : Run SHOW MASTER STATUS; and record the Binary Log Filename and Position in a text file

Step 4 : Launch mysqlpump with --single-transaction --set-gtid-purged=AUTO

Step 5 : Start up all your writes

METHOD #2 : Dump from a Replica (or Slave) with Binary Logging Enabled

Step 1 : Run STOP REPLICA; (or STOP SLAVE;)

Step 2 : Run FLUSH TABLES; FLUSH BINARY LOGS;

Step 3 : Run SHOW MASTER STATUS; and record the Binary Log Filename and Position in a text file

Step 4 : Launch mysqlpump with --single-transaction --set-gtid-purged=AUTO

Step 5 : Run START REPLICA; (or START SLAVE;)

NOTES

Please note that the binary log filename and filesize make up the replication coordinates at any given moment in time. This is why recording the filename and position is what you need to do prior to running mysqlpump.

If you wish to setup replication without GTID you can run

CHANGE MASTER TO
   MASTER_LOG_FILE='binarylogfilefromStep3'
  ,MASTER_LOG_POS=binlogpostionfromStep3
...

If you are setting up replication with GTID, you don't need the coordinates. Just use

CHANGE MASTER TO
...
...
   AUTO_POSITION=1;
Related Question