Mysql – Point in time forward MySQL Dump

backupMySQLmysqldump

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?

Best Answer

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?

Related Solutions

MySql – selecting into file and re-loading it with special characters

I would suggest remove all those REPLACE(text,',','****') stuff, and use FIELDS ENCLOSED BY...:

SELECT 
  id,
  status_twitter_id,
  twitter_id, 
  creator_twitter_identity_id, 
  text, 
  in_reply_to_status_id, 
  in_reply_to_user_id, 
  urls,
  hashtags,
  created_at, 
  updated_at, 
  sent_at,
  user_mentions
FROM 
  twitter_statuses 
into outfile '/tmp/twitter_statuses.csv'  
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

You then LOAD DATA INFILE like this:

LOAD DATA LOCAL INFILE '/srv/$SPLIT_FOLDER/$f' 
INTO TABLE my_db.twitter_statuses_new 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
...

This should do the work.

Mysql – Restoring MySQL database with db dump and binlogs

They are two things that can be good for you in this instance

GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
GOOD THING #2 : The two MyISAM tables are never changing

If either one of these good things apply, then I have good news for you

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the `MySQLSlaveReload.sql` to the Slave

STEP04) Load `MySQLSlaveReload.sql` on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.

Give it a Try !!!