MySQL- Is it possible to backup mySQL database within a period of time?

Question

I am going to backup mySQL database in a period of time. For example: Only backup the data from January to June.

Is it possible to do that? The reason I want to do this is I don't want to back up the database that is too old. If it is possible please give me some suggestion how to do that. Thanks.

Answer 1

I am assuming you want to backup table ROWS based on date, not tables themselves.

If your tables have a date or timestamp column, then you can backup certain rows based on date using an INTO OUTFILE query, for example.

Or, from the command line, you could use mysqldump with the --where option.

See this answer: MYSQL Dump only certain rows

Answer 2

Try this :

exec("mysqldump --opt -u$db_user -p$db_pass --no-create-info --where date >= '2013-08-01' my_database my_table > backup.sql");

Answer 3

You have several ways to do it,you can use shell script and run mysqldump as cronjob,you should only write something like following script ,then add this to your crobjob.

#!/bin/bash
mysqldump --add-drop-table -h <hostname> -u <username> --password=<password> --all-databases > backup.sql
filename="backup.sql."`eval date +%Y%m%d`".tgz"
tar -czf $filename backup.sql 
rm backup.sql 

then edit crobjob by crontab -e and add something like following line:

* * * * * /usr/local/bin/php /home/moein/projects/WebService/index.php
#this cronjob executed every min.

other way you can use event in mysql to get backup.but your mysql version should be > 5.1.8

better description at mysql dev page.