Hello!<br /><br />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:<br /><div class='codetop'>CODE</div><div class='codemain' style='height:200px;white-space
re;overflow:auto'><?PHP<br />$dbhost = "localhost";<br />$dbusername = "*********";<br />$dbpassword = "*******";<br />$dbname = "*******";<br />$sftphost = "**********";<br />$sftpport = *****;<br />$sftpuser = "*******";<br />$sftppass = "*******";<br />$errorlog = "mysqlbackup-errors.txt";<br />$backuplog = "mysqlbackup-last.txt";<br />$interval = 7; // days<br /><br />$last = file_get_contents($backuplog);<br />$today = date("Ymd");<br />if ($last > date("Ymd",mktime (0,0,0,date("m"),date("d")-$interval,date("Y")))) {<br />exit;<br />} else {<br />$logfile = fopen($backuplog,"w");<br />fwrite($logfile,$today);<br />fclose($logfile);<br />}<br />$backupFile = $dbname.date("Y-m-d-H-i-s").'.gz';<br />$command = "mysqldump --opt -h $dbhost -u $dbusername -p $dbpassword $dbname | gzip > $backupFile";<br />system($command);<br />try<br />{<br /> $sftp = new SFTPConnection($sftphost, $sftpport);<br /> $sftp->login($sftpuser, $sftppass);<br /> $sftp->uploadFile($backupFile, $backupFile);<br />}<br />catch (Exception $e)<br />{<br /> $errfile = fopen($errorlog,"a");<br /> fwrite($errfile, "[".date("n/j/y-g:iA")."] - ".$e->getMessage()."\n");<br /> fclose($errfile);<br />}<br />unlink($backupFile);<br />exit;<br /><br />class SFTPConnection<br />{<br /> private $connection;<br /> private $sftp;<br /><br /> public function __construct($host, $port=22)<br /> {<br /> $this->connection = @ssh2_connect($host, $port);<br /> if (! $this->connection)<br /> throw new Exception("Could not connect to $host on port $port.");<br /> }<br /><br /> public function login($username, $password)<br /> {<br /> if (! @ssh2_auth_password($this->connection, $username, $password))<br /> throw new Exception("Could not authenticate with username $username " .<br /> "and password $password.");<br /><br /> $this->sftp = @ssh2_sftp($this->connection);<br /> if (! $this->sftp)<br /> throw new Exception("Could not initialize SFTP subsystem.");<br /> }<br /><br /> public function uploadFile($local_file, $remote_file)<br /> {<br /> $sftp = $this->sftp;<br /> $stream = @fopen("ssh2.<!-- m --><a class="postlink" href="sftp://$sftp&">sftp://$sftp&</a><!-- m -->#036;remote_file", 'w');<br /><br /> if (! $stream)<br /> throw new Exception("Could not open file: $remote_file");<br /><br /> $data_to_send = @file_get_contents($local_file);<br /> if ($data_to_send === false)<br /> throw new Exception("Could not open local file: $local_file.");<br /><br /> if (@fwrite($stream, $data_to_send) === false)<br /> throw new Exception("Could not send data from file: $local_file.");<br /><br /> @fclose($stream);<br /> }<br />}<br />?></div><br /><br />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.<br /><br />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 (<!-- m --><a class="postlink" href="http://www.coreftp.com/server/">http://www.coreftp.com/server/</a><!-- m -->) with a dynamic DNS name and the appropriate port routed (I'm using a non-standard port for security purposes).<br /><br />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)?<br /><br />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).<br />Thanks for any help!<!--content-->
If you are on a shared server you will not be able to get SFTP to work because it is not available as it requires ssh (secure shell access). Also non-standard ports on the servers are locked.<br /><br />You will have to use FTP or email.<!--content-->
I know it's blocked for incoming, but it's also blocked for outgoing?<br /><br /><br />Also, any idea why is giving me an empty file:<br />$command = "mysqldump --opt -h $dbhost -u $dbusername -p $dbpassword $dbname | gzip > $backupFile";<br />system($command);<!--content-->
Yes, the ports are blocked for both in/out.<br /><br />As for your command check out the <a href="http://www.totalchoicehosting.com/forums/index.php?showtopic=30139" target="_blank">Backup FAQ</a> and download a copy of the dbsender.zip file and see how it does it. Basically you output to a file and then zip the file not pipe the output through compression and then output it.<!--content-->
Ah, okay. Thought I could try something fancy.<br /><br />For security purposes, any suggestions for encrypting or password-protecting the resulting file before it gets transferred? Would this work:<br />system('zip -P $password $zip_file $mysql_file');<br /><br />Or is there a better solution? The resulting files would need to be able to be extracted on a Windows machine.<!--content-->
Don't know the answer to that sorry.<!--content-->
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?<!--content-->
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)<br />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.<br /><br />Thanks for the help Bruce! I used the dbsender.php as a reference and that yielded my flaw.<!--content-->
Glad you sorted that out and got the password part too.<!--content-->
re;overflow:auto'><?PHP<br />$dbhost = "localhost";<br />$dbusername = "*********";<br />$dbpassword = "*******";<br />$dbname = "*******";<br />$sftphost = "**********";<br />$sftpport = *****;<br />$sftpuser = "*******";<br />$sftppass = "*******";<br />$errorlog = "mysqlbackup-errors.txt";<br />$backuplog = "mysqlbackup-last.txt";<br />$interval = 7; // days<br /><br />$last = file_get_contents($backuplog);<br />$today = date("Ymd");<br />if ($last > date("Ymd",mktime (0,0,0,date("m"),date("d")-$interval,date("Y")))) {<br />exit;<br />} else {<br />$logfile = fopen($backuplog,"w");<br />fwrite($logfile,$today);<br />fclose($logfile);<br />}<br />$backupFile = $dbname.date("Y-m-d-H-i-s").'.gz';<br />$command = "mysqldump --opt -h $dbhost -u $dbusername -p $dbpassword $dbname | gzip > $backupFile";<br />system($command);<br />try<br />{<br /> $sftp = new SFTPConnection($sftphost, $sftpport);<br /> $sftp->login($sftpuser, $sftppass);<br /> $sftp->uploadFile($backupFile, $backupFile);<br />}<br />catch (Exception $e)<br />{<br /> $errfile = fopen($errorlog,"a");<br /> fwrite($errfile, "[".date("n/j/y-g:iA")."] - ".$e->getMessage()."\n");<br /> fclose($errfile);<br />}<br />unlink($backupFile);<br />exit;<br /><br />class SFTPConnection<br />{<br /> private $connection;<br /> private $sftp;<br /><br /> public function __construct($host, $port=22)<br /> {<br /> $this->connection = @ssh2_connect($host, $port);<br /> if (! $this->connection)<br /> throw new Exception("Could not connect to $host on port $port.");<br /> }<br /><br /> public function login($username, $password)<br /> {<br /> if (! @ssh2_auth_password($this->connection, $username, $password))<br /> throw new Exception("Could not authenticate with username $username " .<br /> "and password $password.");<br /><br /> $this->sftp = @ssh2_sftp($this->connection);<br /> if (! $this->sftp)<br /> throw new Exception("Could not initialize SFTP subsystem.");<br /> }<br /><br /> public function uploadFile($local_file, $remote_file)<br /> {<br /> $sftp = $this->sftp;<br /> $stream = @fopen("ssh2.<!-- m --><a class="postlink" href="sftp://$sftp&">sftp://$sftp&</a><!-- m -->#036;remote_file", 'w');<br /><br /> if (! $stream)<br /> throw new Exception("Could not open file: $remote_file");<br /><br /> $data_to_send = @file_get_contents($local_file);<br /> if ($data_to_send === false)<br /> throw new Exception("Could not open local file: $local_file.");<br /><br /> if (@fwrite($stream, $data_to_send) === false)<br /> throw new Exception("Could not send data from file: $local_file.");<br /><br /> @fclose($stream);<br /> }<br />}<br />?></div><br /><br />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.<br /><br />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 (<!-- m --><a class="postlink" href="http://www.coreftp.com/server/">http://www.coreftp.com/server/</a><!-- m -->) with a dynamic DNS name and the appropriate port routed (I'm using a non-standard port for security purposes).<br /><br />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)?<br /><br />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).<br />Thanks for any help!<!--content-->If you are on a shared server you will not be able to get SFTP to work because it is not available as it requires ssh (secure shell access). Also non-standard ports on the servers are locked.<br /><br />You will have to use FTP or email.<!--content-->
I know it's blocked for incoming, but it's also blocked for outgoing?<br /><br /><br />Also, any idea why is giving me an empty file:<br />$command = "mysqldump --opt -h $dbhost -u $dbusername -p $dbpassword $dbname | gzip > $backupFile";<br />system($command);<!--content-->
Yes, the ports are blocked for both in/out.<br /><br />As for your command check out the <a href="http://www.totalchoicehosting.com/forums/index.php?showtopic=30139" target="_blank">Backup FAQ</a> and download a copy of the dbsender.zip file and see how it does it. Basically you output to a file and then zip the file not pipe the output through compression and then output it.<!--content-->
Ah, okay. Thought I could try something fancy.<br /><br />For security purposes, any suggestions for encrypting or password-protecting the resulting file before it gets transferred? Would this work:<br />system('zip -P $password $zip_file $mysql_file');<br /><br />Or is there a better solution? The resulting files would need to be able to be extracted on a Windows machine.<!--content-->
Don't know the answer to that sorry.<!--content-->
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?<!--content-->
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)<br />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.<br /><br />Thanks for the help Bruce! I used the dbsender.php as a reference and that yielded my flaw.<!--content-->
Glad you sorted that out and got the password part too.<!--content-->