Question :
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.
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;