Daily Ctontab mysqldump to FTP with date

Ubunter · 07-14-2012, 05:58 AM

Hello all,

I'm newbie in php, and i just wrote a small script to run mysqldump all DB into local folder, which is working fin, being written daily with date("Y-m-d-H-i-s").

Q.
I wanted to transfer that backup file, after it's done, to remote ftp server, where my issue is how can I read the daily date increment variable?

I know, it's a stupid question, sorry, i'm newbie, as I said, and no idea how write that variable. My script is below:

Quote:

<?php
$username = "root";
$password = "my_pw";
$hostname = "localhost";
$database = "my-refered-db";
$username =escapeshellcmd($username);
$password =escapeshellcmd($password);
$hostname =escapeshellcmd($hostname);
$database =escapeshellcmd($database);
$ftpusername = "username";
$ftppassword = "ftpw";
$ftphostname = "ftp-remote-server";
$backupFile='/home/backups/'.date("Y-m-d-H-i-s"). $database.'.sql';
$command = "mysqlcheck -Aao --auto-repair -u$username -p$password -h$hostname";
$command = "mysqldump -R -u$username -p$password -h$hostname $database > $backupFile";
$command = "ncftp"
$command = "open -u ftpusername -p ftppassword ftphostname"
$command = "put /home/backups/ -->>my doubt is here ¿¿How to read the date file variable??
$command = "quit"
system($command, $result);
echo $result;
?>

Thank you in advance

jlinkels · 07-14-2012, 07:15 AM

You should put the date in a variable and use it for both creating the backup and moving it later.

PHP Code:



$backupdate=date("Y-m-d-H-i-s"); 
$backupFile='/home/backups/'.$backupdate . $database.'.sql';

Note that you are overwiting $command all the time before executing it.

This BTW is an excellent example where scripting directly in bash is a better solution than doing this from any other scripting language. Altough your solution might eventually work, debugging and code maintenance is more difficult.

jlinkels

Ubunter · 07-14-2012, 07:28 AM

Thank you for your prompt jlinkels,
so according your suggestion, I'd re-write the script as below:

Quote:

<?php
$username = "root";
$password = "my_pw";
$hostname = "localhost";
$database = "my-refered-db";
$username =escapeshellcmd($username);
$password =escapeshellcmd($password);
$hostname =escapeshellcmd($hostname);
$database =escapeshellcmd($database);
$ftpusername = "username";
$ftppassword = "ftpw";
$ftphostname = "ftp-remote-server";
$backupdate=date("Y-m-d-H-i-s");
$backupFile='/home/backups/'.$backupdate . $database.'.sql';
$command = "mysqlcheck -Aao --auto-repair -u$username -p$password -h$hostname";
$command = "mysqldump -R -u$username -p$password -h$hostname $database > $backupFile";
$command = "ncftp"
$command = "open -u ftpusername -p ftppassword ftphostname"
$command = "put /home/backups/*" --> Here, would load all the existing backups to the ftp, right?
$command = "rm -rf /home/backups/*" --> Here to clean the rest of the existing file before go out, right?
system($command, $result);
echo $result;
?>

So, you think now it would be fine? What about if I want to delete old backups, for example, from the last 2 weeks, as we're doing daily backup, so no need to keep the old, and can go deleting the old over 15 days, for example, how should be in this case?

Regards,

jlinkels · 07-14-2012, 07:56 AM

You can not issue commands like:

Code:

$command = 'ncftp'
$command=$passwd
system($command,$result)

First, the first command 'ncftp' is never executed. Secondly, every time you execute the system call, a new shell is being openened. So the first call you open an ncftp connection, and close it. The next time you issue the password, it would open another shell and try to execute password as shell command.

If you insist on using PHP you could use ncftpput to do it all at once. Anyway, every time you compose a command with $command= you have to execute it by calling system. And then again, after you made your call, that command is finished. You can't open an FTP in one call and use it in the next call.

jlinkels

Ubunter · 07-14-2012, 08:09 AM

Thank you again jlinkels,

So, can i do it in one single command as:

Quote:

$command = "ncftp - open -u ftpusername -p ftppassword ftphostname

put /home/backups/*"

I don't know ho do it with the ncftpput, and using php or perl for me, it's equal, i just went into php as faster, in perl I have no idea..

Regards,

jlinkels · 07-14-2012, 08:16 AM

Typically, the bash program would be something like:

Code:

#! /bin/bash

password="my_pw"
hostname="localhost"
database="my-refered-db"
username="root"
ftpusername="username"
ftppassword="ftpw"
ftphostname="ftp-remote-server"
ftprmotedir="/home/remotebackups/" 
backupdate=$(date "+%F %T")
backupFile="/home/backups/${backupdate}${database}.sql"
mysqlcheck -Aao --auto-repair -u$username -p$password -h$hostname
mysqldump -R -u$username -p$password -h$hostname $database > $backupFile
ncftpput -u $ftpusername -p $ftppassword $ftphostname $ftpremotedir $backupfile
rm -rf /home/backups/*

The code is untested, you'll get errors. Run this script by calling bash -x /path/to/your/script.

jlinkels

jlinkels · 07-14-2012, 08:19 AM

Quote:

Originally Posted by Ubunter

So, can i do it in one single command as:
$command = "ncftp - open -u ftpusername -p ftppassword ftphostname

No

man ncftpput

Quote:

Originally Posted by Ubunter

I don't know ho do it with the ncftpput, and using php or perl for me, it's equal, i just went into php as faster, in perl I have no idea..
Regards,

I said bash, not perl

jlinkels

Ubunter · 07-14-2012, 08:55 AM

Quote:

Originally Posted by jlinkels

Typically, the bash program would be something like:

Code:

#! /bin/bash

password="my_pw"
hostname="localhost"
database="my-refered-db"
username="root"
ftpusername="username"
ftppassword="ftpw"
ftphostname="ftp-remote-server"
ftprmotedir="/home/rbackups/" 
backupdate=$(date "+%F %T")
backupFile="/home/backups/${backupdate}${database}.sql"
mysqlcheck -Aao --auto-repair -u$username -p$password -h$hostname
mysqldump -R -u$username -p$password -h$hostname $database > $backupFile
ncftpput -u $ftpusername -p $ftppassword $ftphostname $ftpremotedir $backupfile
rm -rf /home/backups/*

The code is untested, you'll get errors. Run this script by calling bash -x /path/to/your/script.

jlinkels

Thank you, but after testing, i got this error:

Quote:

error: line 14: $backupFile: ambiguous redirection
NcFTPPut 3.2.4

Usages:
ncftpput [flags] remote-host remote-dir local-files... (mode 1a)
ncftpput [flags] bookmark-name remote-dir local-files... (mode 1b)
ncftpput -f login.cfg [flags] remote-dir local-files... (mode 2)
ncftpput -c remote-host remote-path-name < stdin (mode 3)
ncftpput -C remote-host local-path-name remote-path-name (mode 4)

As this line is wrong:

Quote:

ncftpput -u $ftpusername -p $ftppassword $ftphostname $ftpremotedir $backupfile

But no idea why it's wrong, it's as described in mode mode 1a, no?

I tried just to run the ncftpput directly in shell, as:

Quote:

# ncftpput -u username -p pwd ftp-host /backups /home/backups/*

And I got it properly, all loaded, and fine... don't know where's the syntax error in your script!!

But, to get the script working, I just put the flags together in one line, and got it working properly... now it's working having them together... Even, it's not wrong, no? But not so nice, but work

Chers, thank you

Ubunter · 07-14-2012, 10:27 AM

Hello again,

Sorry, to ask again, but I have some errors in the variables, as I first came to ask about, as below:

Quote:

backupdate=$(date "+%F %T")
backupFile="/home/backup/${backupdate}${database}.sql"

Something in this arguments are wrong, what I'm doing wrong in this synthesis?

jlinkels · 07-14-2012, 11:59 AM

You didn't run the script with bash -x as I proposed.

jlinkels

lithos · 07-14-2012, 12:21 PM

Hi,

I'm sorry to "break" the posts trying to find the solution, but...

I think that instead of "ncftp" I would use "lftp" (that is Lftp) which has options to provide the usr&pass and the commands in one line (which I'm using) and it tries to transfer the files until finished (reconnecting, resuming) opposing the usual ftp which can break and lose connection and then stops even if not complete file was transfered.

My "lftptransfer_script.sh"

Code:

#!/bin/sh
HOST='ftp.domain.com'
USER='ftpuser'
PASSWD='idontknow'
backupdate=$(date "+%F %T")
FILE="/home/backups/${backupdate}${database}.sql"   <--- your file to transfer

lftp -c "open $HOST && user $USER $PASSWD && cd FOLDER_NAME_FOR_STORING/backups/ && put -c $FILE" <<END_SCRIPT
# FOLDER_NAME_FOR_STORING/backups/ change to directory name you are using
# all in one command that connects to HOST=ftp.domain.com with Username/password
# and changes directory to whatever you need
# then transfers the file
bye
exit
END_SCRIPT
exit 0

this script transfers my "backup" file TARred and Gzipped even if 20GB filesize and takes about 6 to 10 hours with avg. speed of 350kB/s (I think).

Ubunter · 07-14-2012, 05:04 PM

Hi jlinkels,
Sure, I run it as bash -x backupcron...

lithos, Thank you for the break, your suggestion is interesting also, as i was just looking around for the lftp, it seem to be more reasonable, as my db are large, one of them have even over 1gb.. so, i'd use this...

But, in both cases, i still doig something wrong, having the same file location error, as I give:

Quote:

FILE="/home/backups/${backupdate}${database}.sql"

And I still getting when i run the:

Quote:

bash -x backupcron
backupcron: línea 13: $FILE: redireccionamiento ambiguo
put: /home/backups/2012-07-14: No existe el fichero o el directorio

+ bye
backupcron: línea 15: bye: no se encontró la orden
+ exit

Thanks in advance for help for both

jlinkels · 07-14-2012, 07:16 PM

Please show the complete output of the command bash -x backupcron

I think some variable is not set or so.

jlinkels

lithos · 07-15-2012, 03:10 AM

Quote:

Originally Posted by Ubunter

Hello again,

Sorry, to ask again, but I have some errors in the variables, as I first came to ask about, as below:

Code:

backupdate=$(date "+%F %T")
backupFile="/home/backup/${backupdate}${database}.sql"

Something in this arguments are wrong, what I'm doing wrong in this synthesis?

The syntax of these two lines could be wrong:

Code:

backupdate=$(date +"%F %T")   <---  see the order of  +  and  "
backupFile="/home/backup/$backupdate-$database.sql"        <-- this is how I would write the filename - without { }

This is the test of the 2 lines:

Code:

#!/bin/sh
backupdate=$(date +"%F %T")
database="my-refered-db"
backupFile="/home/backup/$backupdate-$database.sql"

echo "Filename: "$backupFile


Output:
Filename: /home/backup/2012-07-15 09:59:26-my-refered-db.sql

which gives something I would not like to have , so I would rearrange the syntax for filename, like this:

Code:

backupdate=$(date +"%F-%H.%M.%S")

Output:
Filename: /home/backup/2012-07-15-10.05.15-my-refered-db.sql

now that is something I like.

Ubunter · 07-15-2012, 05:15 AM

Quote:

Originally Posted by lithos

Code:

#!/bin/sh
backupdate=$(date +"%F %T")
database="my-refered-db"
backupFile="/home/backup/$backupdate-$database.sql"

echo "Filename: "$backupFile


Output:
Filename: /home/backup/2012-07-15 09:59:26-my-refered-db.sql

After saving this, I do:

Quote:

# bash -x test
++ date '+%F %T'
+ backupdate='2012-07-15 12:09:39'
+ database=my-refered-db
+ backupFile='/home/backup/2012-07-15 12:09:39-my-refered-db.sql'
+ echo 'Filename: /home/backup/2012-07-15' 12:09:39-my-refered-db.sql
Filename: /home/backup/2012-07-15 12:09:39-my-refered-db.sql

But, when i check as:

Quote:

#ls /home/backup

Nothing there, empty directory...