Full backup , bin log backup and point in time recovery process
Posted by: Udara Ratnayake
Date: April 27, 2020 04:54PM

Hi I have setup mysql bacup in my production as follows

DB full backup every day 8.00pm
mysqldump --single-transaction --flush-logs --master-data=2 -u $mysql -p -f crmdb1 > /backup/crmdb1_26_04_2020_20_00.sql

mysqldump -u $mysql -p crmdb1 > fullcrmdb1_26_04_2020_20_00.sql -safe side

Bin backup every day 8.00pm
#Backing up bin logs process
/usr/bin/mysql -u $mysql -p -s --skip-column-names -Bse 'Show binary logs' > /backup/Showbinarylogs.txt
binlog=`/usr/bin/head -1 /tmp/Showbinarylogs.txt | awk '{print $1}'`
mysqlbinlog -u $mysql -p --to-last-log "$binlog" > /backup/binlog_backupfile_26_04_2020_20_00_01.sql

Additional info and this information stored in every backup folder
cat /etc/Showbinarylogs26_04_2020_20_00_01.txt
bin.000598 204 No
ServerA-bin.000599 9625063 No
ServerA-bin.000600 204 No
ServerA-bin.000601 204 No
ServerA-bin.000602 204 No
ServerA-bin.000603 5825701 No
ServerA-bin.000604 204 No
ServerA-bin.000605 204 No
ServerA-bin.000606 204 No
ServerA-bin.000607 6046472 No
ServerA-bin.000608 204 No
ServerA-bin.000609 204 No
ServerA-bin.000610 204 No
ServerA-bin.000611 85361744 No
ServerA-bin.000612 204 No
ServerA-bin.000613 204 No
ServerA-bin.000614 204 No
ServerA-bin.000615 7107625 No
ServerA-bin.000616 204 No
ServerA-bin.000617 204 No
ServerA-bin.000618 204 No
ServerA-bin.000619 10996329 No
ServerA-bin.000620 204 No
ServerA-bin.000621 204 No
ServerA-bin.000622 204 No
ServerA-bin.000623 6239054 No
ServerA-bin.000624 204 No
ServerA-bin.000625 204 No
ServerA-bin.000626 204 No
ServerA-bin.000627 6543720 No
ServerA-bin.000628 204 No
ServerA-bin.000629 204 No
ServerA-bin.000630 204 No
ServerA-bin.000631 7815077 No
ServerA-bin.000632 204 No
ServerA-bin.000633 204 No
ServerA-bin.000634 204 No
ServerA-bin.000635 6091016 No
ServerA-bin.000636 204 No
ServerA-bin.000637 204 No
ServerA-bin.000638 204 No
ServerA-bin.000639 6683 No

This all backup located with date folder on each day in /backup/28042020
following information available on each backup folder
Full backup
binlog backup

Q1.Is that fullbackups/ binlog backup are correct?
Q2. What is the step for point in time recovery the DB incase of table drop or DB crash beteen two full backups?
ex: restore 8pm full backup on previous day full backup(25_04_2020)
mysql -u root -p www_hwc < /backup/crmdb1_25_04_2020_20_00.sql
then resotre logs (taken 26-04-2020 20_00_01)
mysqlbinlog --stop-datetime="2020-04-20 9:59:59" /backup/binlog_backupfile_26_04_2020_20_00_01.sql | mysql -u mysql -p

Q3. how to do point in time recovery if DB creash between now(no backup taken) and last full backups which is 26-04-2020(as we didnt have tail binlog backup)

Will any one can advise if find any issues and workaround

many thanks

Options: ReplyQuote

Written By
Full backup , bin log backup and point in time recovery process
April 27, 2020 04:54PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.