Jump to content

Help With Mysql Backup To Sftp Script


Recommended Posts



I'm trying to write a script which eventually will run as a cron job to backup a MySQL database and SFTP it directly to my machine at home. Here's the script so far:

$dbhost = "localhost";
$dbusername = "*********";
$dbpassword = "*******";
$dbname = "*******";
$sftphost = "**********";
$sftpport = *****;
$sftpuser = "*******";
$sftppass = "*******";
$errorlog = "mysqlbackup-errors.txt";
$backuplog = "mysqlbackup-last.txt";
$interval = 7; // days

$last = file_get_contents($backuplog);
$today = date("Ymd");
if ($last > date("Ymd",mktime (0,0,0,date("m"),date("d")-$interval,date("Y")))) {
} else {
$logfile = fopen($backuplog,"w");
$backupFile = $dbname.date("Y-m-d-H-i-s").'.gz';
$command = "mysqldump --opt -h $dbhost -u $dbusername -p $dbpassword $dbname | gzip > $backupFile";
   $sftp = new SFTPConnection($sftphost, $sftpport);
   $sftp->login($sftpuser, $sftppass);
   $sftp->uploadFile($backupFile, $backupFile);
catch (Exception $e)
  $errfile = fopen($errorlog,"a");
  fwrite($errfile, "[".date("n/j/y-g:iA")."] - ".$e->getMessage()."\n");

class SFTPConnection
   private $connection;
   private $sftp;

   public function __construct($host, $port=22)
       $this->connection = @ssh2_connect($host, $port);
       if (! $this->connection)
           throw new Exception("Could not connect to $host on port $port.");

   public function login($username, $password)
       if (! @ssh2_auth_password($this->connection, $username, $password))
           throw new Exception("Could not authenticate with username $username " .
                               "and password $password.");

       $this->sftp = @ssh2_sftp($this->connection);
       if (! $this->sftp)
           throw new Exception("Could not initialize SFTP subsystem.");

   public function uploadFile($local_file, $remote_file)
       $sftp = $this->sftp;
       $stream = @fopen("ssh2.sftp://$sftp$remote_file", 'w');

       if (! $stream)
           throw new Exception("Could not open file: $remote_file");

       $data_to_send = @file_get_contents($local_file);
       if ($data_to_send === false)
           throw new Exception("Could not open local file: $local_file.");

       if (@fwrite($stream, $data_to_send) === false)
           throw new Exception("Could not send data from file: $local_file.");



Nothing seems to be working right, and I'm not getting any errors in my log file to tell me what's happening either (and it is chmod 666). The .gz file is being created, but it's empty. No connection to my SFTP server appears to be happening on my computer, and I have the port open and routed properly on my firewall. Also, the .gz file does not delete after it's done like it should.


I have the database login info correct (same info I use on my PHP scripts to interact with the database), and I have my server configured using Core FTP's free Mini SFTP server (http://www.coreftp.com/server/) with a dynamic DNS name and the appropriate port routed (I'm using a non-standard port for security purposes).


Does anyone see anything wrong with the code? Does 'mysqldump' not work from a system() command like I'm using it? Is my SFTP outgoing connection not allowed from the server? Are there upper limitations to SFTP ports (I'm using a port over 20000)?


I'd like to get this script working because I prefer to use SFTP instead of FTP or E-mail (cuz neither is very secure, plus it's annoying to have tons of backup files clutter my inbox).

Thanks for any help!

Link to comment
Share on other sites

Ah, okay. Thought I could try something fancy.


For security purposes, any suggestions for encrypting or password-protecting the resulting file before it gets transferred? Would this work:

system('zip -P $password $zip_file $mysql_file');


Or is there a better solution? The resulting files would need to be able to be extracted on a Windows machine.

Link to comment
Share on other sites

Okay, I think my issue is that I'm trying to test this script from the web browser, but it mustn't have the permissions to run these commands from there. I don't want to set a cron job for it until I'm sure it's working properly, but it looks like it might only work if ran from the server directly. So how do I test it?

Link to comment
Share on other sites

Hold the phone, I just got it to work! My mysqldump command had flaws in it. I had spaces where there shouldn't have been spaces (eg. I had -u[space]$dbusername instead of -u$dbusername)

So it works! (my rewrite using FTP that is - not SFTP) And the Zip password works too. Not the greatest security, but better than nothing. I'm still open to anyone's suggestions for encrypting this file (either directly or just during transit) while still making it able to be easily extracted on a Windows machine.


Thanks for the help Bruce! I used the dbsender.php as a reference and that yielded my flaw.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...