Point in time forward MySQL Dump

Question

I have a MySQL table that stores a list of bookings that's made online. I need to have these bookings dumped to a CSV file which will then be picked up by a client server application through a SSH connection. Earlier we were dumping the entire table and so the CSV file had the transactions since beginning of time. Now we have to generate the dump several times in a day and the dump should contain only the newer records. Does my MySQL provide any options to generate a dump that contains only those records that were inserted after the last dump was taken?

Answer 1

If you know the time from where you need to start the backup then you can use mysqldump with --where clause like below to get the type of backup you are looking for

mysqldump --user=username --password=password db_name table_name  --where=date_pulled > '2014-07-21 16:25:00' > backup.sql

You can also do like

SELECT * FROM table_name 
WHERE date_pulled > '2014-07-21 16:25:00'
INTO OUTFILE 'table_name.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; 

You can create a simple automated script for this and change the date_pulled dynamically.

Have a look at below links that may help you as well

Mysqldump in CSV format.

How to output MySQL query results in csv format?

Answer 2

In order to do an incremental backup, you must start with two things

BINARY LOGGING ENABLED / AUTO EXPIRED EVERY 14 DAYS

[mysqld]
log-bin=/path/to/binlogs/mysql-bin
expire-logs-days=14

FULL BACKUP

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--routines --triggers --flush-logs"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=2 --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > FullBackup.sql

You could probably do a full backup once a week (like on Mondays at midnight)

INCREMENTAL BACKUP

You must extract the binlog events

In this example, you would look for the /path/to/binlogs

You would then use the mysqlbinlog program to copy everything since the last full backup.

First you execute FLUSH LOGS; every night at 11:59 PM

Then you go into /path/to/binlogs and run this on 2016-11-22 at midnight

mysqlbinlog --start-datetime="2016-11-21 00:00:00" --stop-datetime="2016-11-21 23:59:59" mysql-bin.[0-9]* > Incremental_20161121.sql

or you could simply make a backup of the binlogs folder and extract the incremental changes at yourt own convenience.