Auto backup MySQL dbase to FTP server?

Hi,

I am trying to create a small script that will backup a MySQL database. I can successfully create the backup file at the root of my Website using:

<?php

  // This PHP Script will backup the content of a MySQL database in a GZip file

  // Enter Database access details
  $host= 'HOSTNAME';
  $user= 'USERNAME';
  $pass= 'PASSWRD';
  $db=   'DBNAME';
  
  // Instructing the system to zip and store the database
  system(sprintf(

    'mysqldump --opt -h%s -u%s -p%s %s | gzip > $s/dumpDB.sql.gz',
    $host,
    $user,
    $pass,
    $db,
    getenv('DOCUMENT_ROOT')
  ));
  echo '+DONE';
?>

Code (markup):

However, I would like the script to save the file to a FTP server elsewhere. I have added the variables for the FTP server, like so:

<?php

  // This PHP Script will backup the content of a MySQL database in a GZip file

  // Enter Database access details
  $host= 'HOSTNAME';
  $user= 'USERNAME';
  $pass= 'PASSWRD';
  $db=   'DBNAME';
  
  // FTP Server access details
  $ftphost= 'FTPHOSTHAME';
  $ftpdir= 'FTPDIR';
  $ftpuser= 'FTPUSERNAME';
  $ftppass= 'FTPPASSWRD';

  // Instructing the system to zip and store the database
  system(sprintf(

    'mysqldump --opt -h%s -u%s -p%s %s | gzip > $s/dumpDB.sql.gz',
    $host,
    $user,
    $pass,
    $db,
    getenv('DOCUMENT_ROOT')
  ));
  echo '+DONE';
?>

Code (markup):

But what do I need to add to mysqldump for the file to be sent to the FTP server?

I've been searching for hours and can't find a solution that works. Any help will be much appreciated.

Many thanks,

T

 

With any luck your server has a scriptable FTP client on it. ncftpput is an easy one to work with. But it's hard to guess what software is installed on your server.

 

	
$file = "dumpDB.sql.gz";
$remotefile = "dumpDB.sql.gz";
//ftp connection
$conn_id = ftp_connect($ftphost);
$login_result = ftp_login($conn_id, $ftpuser, $ftppass);
if ((!$conn_id) || (!$login_result))
{
    echo "Cannot Connect To FTP!!<br>";
    exit;
} else
{
    echo "FTP Connection Established; ".$ftp_server.", User : ".$ftp_user_name." <br>";
}
//change dir
if (ftp_chdir($conn_id, $ftpdir))
{ 
    echo "Dir Changed To: " . ftp_pwd($conn_id) . "\n";
} else
{
    echo "Dir Cannot Changed\n";
}
//upload file
if (ftp_put($conn_id, $remote_file, $file, FTP_ASCII))
{
    echo "File Uploaded! $file\n <br>";
} else
{
    echo "File Cannot Uploaded! : $file\n <br>";
}
//close connection
ftp_close($conn_id);

PHP:

i write a sample for you. put codes before echo '+DONE';

 

Many thanks LaCReMel,

I added the code, but unfortunately it isn't working properly. It saves a local copy, but not to the FTP server.

It appears to connect and change directory ok, but not transfer the actual file. The message 'File Cannot Uploaded!' is displayed and when I check the FTP server the file isn't there.

Any ideas?

Thanks again in advance,

T

 

change FTP_ASCII to FTP_BINARY and try again please

 

I tried that. I also changed the $remote_file variable to $remotefile but still no luck unfortunately.

T

 

Are you changing his variables to match your variables?

 

Yes. I now have:

<?php

  // Enter Database access details
  $host= 'HOSTNAME';
  $user= 'USERNAME';
  $pass= 'PASSWRD';
  $db=   'DBNAME';
  
  // FTP Server access details
  $ftphost= 'FTPHOSTNAME';
  $ftpdir= 'FTPDIR';
  $ftpuser= 'FTPUSERNAME';
  $ftppass= 'FTPPASSWRD';

  // Instructing the system to zip and store the database
  system(sprintf(
    'mysqldump --opt -h%s -u%s -p%s %s | gzip > %s/dumpDB.sql.gz',
    $host,
    $user,
    $pass,
    $db,
    getenv('DOCUMENT_ROOT')
  ));
  
  $file = "dumpDB.sql.gz";
  $remotefile = "dumpDB.sql.gz";
  
  //ftp connection
  $conn_id = ftp_connect($ftphost);
  $login_result = ftp_login($conn_id, $ftpuser, $ftppass);
  if ((!$conn_id) || (!$login_result))
  {
      echo "Cannot Connect To FTP!!<br>";
      exit;
  }
  else{
      echo "FTP Connection Established; ".$ftphost.", User: &nbsp;".$ftpuser." <br>";
       }

  //change dir
  if (ftp_chdir($conn_id, $ftpdir))
  {
       echo "Dir Changed To: " . ftp_pwd($conn_id) . " <br>";
  }
  else{
      echo "Dir Cannot Be Changed\n";
      }

  //upload file
  if (ftp_put($conn_id, $remotefile, $file, FTP_BINARY))
  {
      echo "File $file\n Uploaded! <br>";
  }
  else{
      echo "File $file\n Cannot Be Uploaded! <br>";
      }

  //close connection
  ftp_close($conn_id);
  
  echo '+DONE' ;
?>

Code (markup):

But unfortunately it doesn't work

T