1

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?

2

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?

  • How do I update the date_pulled after extracting the dump? – sridhar pandurangiah Jul 21 '14 at 13:19
  • You cab create a procedure and just pass it as a parameter. – Abdul Manaf Jul 21 '14 at 13:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.