A few days ago I had to create backup system for one of the projects (do not want to describe details). And I had to be able to configure what should be backed up and what should not backed up - like backup by date, and for sure I do not need the table structure as I can get it just once. I had also to be able to restore the data for the certain period. So, I decided to create a couple of simple php script that would allow me to create backup and restore the data from the backup. The scripts allowed me to backup only data for the certain day or whole data for the certain table. And what is important for me - backup files are in pure SQL, not just some binary files.
I am not going to publish whole text of the scripts here, you can download them from GitHub via the links at the bottom of the page. Here in my small article I am going jut to tell a few words about the configuration.
Sure both scripts do include database connection fonfiguration lines:
$db_name = "test"; $db_user = "root"; $db_pass = "";
For this scripts it is important to configure time zone properly to backup and restore the data for the proper period:
Here are configuration lines from backup.php file.
$folder = "./backup"; $tables = array("symbols"=>"", "test_rand"=>"DateField"); $date = date("Y-m-d", time());
First line defines the folder which will hold the backup data. Variable $tables defines what tables to backup, like table name and date column. If the column name is empty than the script will backup all the table (may be useful if the table gets lot of changes during the day and it have sense to backup all the data from the table). For example all the data from the symbols table will be dumped and only the data for the current day from the test_rand table, defined by the column DateField will be dumped to the file.
Configuration lines of the restore.php file are slightly different.
$folder = "./backup"; $start_date = '2012-06-05'; $end_date = '2012-06-05';
Here you can see the same folder that will contain the backup data. Other important configuration options allow to define start & end date of the period data for which should be restored.
As I have already mentioned you can download the scripts from the GitHub here. Let me know if you have found the scripts useful and you think that I should improve them, as I have created them just for myself.