Cron Job mysqldump to daily folder

Question

I've created a cron job to dump a table from a database and it works fine:

mysqldump -u username -ppassword dbname tablename > .../backups/matches.sql

This saved the file (note the ... is just the prefix of the location).

What I'd like to do is save this daily to a folder within backups. For example, backups/20150909/matches.sql

How can I achieve this with a variable? Will the cron job create the folder if it does not exist?

Any help would be appreciated. There will be numerous tables that I want to backup daily, so I'd like to simply set up each cron job to do this.

Why do you save on same dir and use dat on filename such backups/20150909.sql with mysqldump -u username -ppassword dbname tablename > .../backups/$(date +%Y-%m-%d)-matches.sql : Another question, use -u username -ppassword it's insecure. Best way use .my.cnf on user that run cron. http://stackoverflow.com/questions/6861355/mysqldump-launched-by-cron-and-password-security — abkrim, Sep 10 '15 at 09:48
without php, only cron... date=$(date +%Y-%m-%d); mkdir /path/backups/$(date); mysqldump -u username -ppassword dbname tablename > /path/backups/$(date); — abkrim, Sep 10 '15 at 09:51

score 0 · Accepted Answer · answered Sep 09 '15 at 18:00

I was able to resolve this by using PHP for my backup.

if(!file_exists($dir)) {
    mkdir($dir);
}
if(!file_exists($dir)) {
    echo "Directory (" . $dir . ") was not created.<br />";
} else {
    echo "Directory (" . $dir . ") successfully created.<br />";
}

I then proceeded to get the tables

mysqli_select_db($conn, $dbname);
$search = "SHOW TABLES";
$result = mysqli_query($conn, $search);
while($row = mysqli_fetch_assoc($result)) 
  {
    $tables[] = $row['Tables_in_p3pro_p3live'];
}

Next was to write the sql files

foreach($tables as $key=>$value) {
    $string = "mysqldump -u *username* -p*password* *dbname* " . $value . " > *path_to_dir*/backups/" . $foldername . "/" . $value . ".sql";

    exec($string);
    echo $value . " backup completed. <br />";
}

Cron Job mysqldump to daily folder

1 Answers1