PHP script backs up MySQL files - need to add delete command

[fiddler80]

Hello.
I have a php script that will backup my databases and ftp them to another server.
All works fine via a cron job - but the backups are piling up and I'd like to add some code to the script to delete all the old backups - and just save the 5 newest backsup.

I've added some code to the config file - but I don't know if the code is correct. I don't get any errors, but I figure that may be because I need to add some code to the script itself in order for it to all work.

Here is my config file:

<?php

// Don't allow direct access
if ( basename( $_SERVER['REQUEST_URI'] ) == 'config.php' ) {
    exit( 'config.php does nothing' );
}

// Set this to YES if you want to keep the database backup files locally (where you run this script). This is the default.
// Set this to NO if you want to delete the files after you FTP copy them some where else
define( 'KEEP_LOCAL_BACKUP_FILE',           'YES' );
//Make Numbered Backups of Files
    define( 'setq version-control t',               'YES' );
   //Don't ask when deleting old backups
    define( 'setq delete-old-versions t',       'YES' );
  //Number of Numbered Backups to Keep
  //Delete Oldest Version First
    define( 'setq kept-old-versions'   '5' );
    define( 'setq kept-new-versions'   '5' );

// Set this to YES if you want to FTP copy your files to another server.
// Set this to NO if you don't want to FTP copy your files to another server. This is the default.
define( 'DO_REMOTE_FTP_COPY',               'yes' );

// CPANEL
define( 'CPANEL_SERVER_ADDRESS',            'mywebsite.com' );      // IP address or domain name for the server with the cPanel account
define( 'CPANEL_PORT_NUM',                  '2082' );                   // The port number for the cPanel. If you have problems, try 2082
define( 'CPANEL_ADMIN_USERNAME',            'username' );           // the admin username for your cPanel account
define( 'CPANEL_ADMIN_PASSWORD',            'password' );       // the admin password for your cPanel account

// REMOTE FTP
define( 'FTP_SERVER_ADDRESS',               'remotewebsite.com' );      // IP address or URL of the FTP server
define( 'FTP_SERVER_PORT',                  '21' );                     // FTP(S) Port. Default is 21.
define( 'FTP_USERNAME',                     'username' );           // FTP Username
define( 'FTP_PASSWORD',                     'password' );           // FTP Password
define( 'FTP_PATH_TO_COPY',                 '/path/to/backupfolder/' );     // FTP Path (where do you want to copy the files?)

// Set this to NO to use "active" FTP. This is the default.
// Set this to YES to use FTP in passive mode. Only necessary if you're having some FTP problems.
define( 'FTP_USE_PASSIVE',                  'YES' );

// Set this to NO to use standard FTP.
// Set this to YES to use FTPS. Your target FTP server MUST also support this. Will attempt to fallback to normal FTP if connection fails.
define( 'FTP_USE_SSL',                      'NO' );

//END OF CONFIG FILE

And here is the actual script I set for the cron job:

#! /usr/bin/php

<?php

ini_set('display_errors', 1);
ini_set('log_errors', 1);
ini_set("error_log" , dirname(__FILE__)."/cpanel_mysql_backup.log.txt");

// Maximum script execution time in seconds, default is 600 (10 minutes).
// If you have really large databases, you may need to increase this value substantially.
set_time_limit( 600 );

class Worpit_Cpanel_MySql_Backup {

    const CONFIG_FILE = 'config.php';

    protected $m_aConfigKeys;
    protected $m_aDatabaseList;

    protected $m_oFtpConnection;

    public function __construct() {

        $this->m_aConfig = array();

        $this->m_aConfigKeys = array(

                'CPANEL_SERVER_ADDRESS',
                'CPANEL_PORT_NUM',
                'CPANEL_ADMIN_USERNAME',
                'CPANEL_ADMIN_PASSWORD',

                'FTP_SERVER_ADDRESS',
                'FTP_SERVER_PORT',
                'FTP_USERNAME',
                'FTP_PASSWORD',
                'FTP_PATH_TO_COPY',

                'KEEP_LOCAL_BACKUP_FILE',
                'DO_REMOTE_FTP_COPY',
                'FTP_USE_PASSIVE',
                'FTP_USE_SSL'
        );

        $this->writeLog( 'Process ID: '.getmypid() );

    }//__construct

    public function readConfig() {

        if ( !is_file( self::CONFIG_FILE ) ) {
            $this->writeLog(' There is no configuration file in expected location: '.self::CONFIG_FILE );
            return false;
        }

        $sConfigContent = file_get_contents( self::CONFIG_FILE );

        if ( $sConfigContent === false ) {
            $this->writeLog(' The config file is there, but I could not open it to read: '.self::CONFIG_FILE );
            return false;
        }

        foreach ( $this->m_aConfigKeys as $sKey ) {
            preg_match( "/".strtoupper( $sKey )."(\'|\\\")\s*,\s*(\'|\\\")(.+)\g{-2}/i", $sConfigContent, $aMatches );
            if ( !isset($aMatches[3]) ) {
                $this->m_aConfig[$sKey] = '';
            } else {
                $this->m_aConfig[$sKey] = $aMatches[3];
            }
        }

        return true;
    }//readConfig

    public function runBackup() {

        $sTimeStamp = date( "Y-m-d-H-i-s" );

        $this->writeLog( "** START @ $sTimeStamp **" );

        // 1. Read the config
        if ( !$this->readConfig() ) {
            $this->writeLog( "No valid configuration read. Quitting." );
            return;
        }

        // 2. Get a list of Databases
        $this->writeLog( "Attempting to retrieve a list of Databases." );
        $this->getAllCpanelDatabases();
        if ( empty($this->m_aDatabaseList) ) {
            $this->writeLog( "It appears you don't have any databases. Quitting." );
            return;
        }
        else {
            $this->writeLog( "Successfully obtained list of databases." );
        }

        // 3. Open up the FTP connection if needed.
        $fDoFtp = strtoupper($this->m_aConfig['DO_REMOTE_FTP_COPY']) == 'YES';
        if ( $fDoFtp ) {
            $this->m_aConfig['FTP_PATH_TO_COPY'] = rtrim( $this->m_aConfig['FTP_PATH_TO_COPY'], '/' ) . '/';
            $fDoFtp = $this->openFtpConnection();
            $fFtpSuccess = true; //flag used later to stop repeated FTP failures.

            //FTP Connection Failed and you intend to delete local copies, no point in continuing.
            if ( !$fDoFtp && ($this->m_aConfig['KEEP_LOCAL_BACKUP_FILE'] != 'YES') ) {
                $this->writeLog( "FTP connection failed AND you don't plan to keep your backups locally. No point in continuing. Quitting." );
                return;
            }

        }

        // 4. For each DB, download it and then copy it to FTP if requested. Then delete if requested.
        for ( $i = 0; $i < count( $this->m_aDatabaseList ); $i++ ) {

            $sDbName = $this->m_aDatabaseList[$i];
            $sDbFileName = $sTimeStamp.'_'.$sDbName.'.sql.gz';

            $this->writeLog( 'Being Processing Database: '.$sDbName );
            $fSuccess = $this->loginAndDownloadFile( $sDbName, $sDbFileName );

            if ( $fSuccess !== true ) {
                continue;
            }

            $this->writeLog( 'Downloaded Database Locally: '.$sDbName );

            //Only attempt to FTP if it's been set in the config, and the last copy was a success.
            if ( $fDoFtp && $fFtpSuccess ) {
                $this->writeLog( 'Starting Database FTP Copy: '.$sDbName );
                $fFtpSuccess = $this->ftpFileRemotely( $sDbFileName );

                //An FTP transfer failed indicating all future transfers will fail. Quiting processing.
                if ( !$fFtpSuccess ) {
                    if ($this->m_aConfig['KEEP_LOCAL_BACKUP_FILE'] != 'YES') {
                        $this->writeLog( "FTP copy failed AND you don't plan to keep your backups locally. Quitting." );
                        break;
                    }
                    else {
                        $this->writeLog( "FTP copy failed, so will not attempt any more FTP actions." );
                    }
                }
            }

            if ( strtoupper($this->m_aConfig['KEEP_LOCAL_BACKUP_FILE']) == 'NO' ) {
                $this->writeLog( 'Delete Database File: '.$sDbFileName );
                unlink( dirname(__FILE__).'/'.$sDbFileName );
            }

        }//for

        if ( $fDoFtp ) {
            // close the FTP stream 
            ftp_close($this->m_oFtpConnection);
        }

        $this->writeLog( "** FINISH **\n" );
    }

    protected function getAllCpanelDatabases() {

        include_once( dirname(__FILE__).'/xmlapi-php/xmlapi.php' );

        $oXmlApi = new xmlapi( $this->m_aConfig['CPANEL_SERVER_ADDRESS'] );
        $oXmlApi->password_auth( $this->m_aConfig['CPANEL_ADMIN_USERNAME'], $this->m_aConfig['CPANEL_ADMIN_PASSWORD'] );
        $oXmlApi->set_port( $this->m_aConfig['CPANEL_PORT_NUM'] );

        $this->m_aDatabaseList = array();

        $oResult = $oXmlApi->api2_query( $this->m_aConfig['CPANEL_ADMIN_USERNAME'], 'MysqlFE', 'listdbs' );
        if ( !isset( $oResult->data[0] ) ) {
            $this->m_aDatabaseList[] = (string)($oResult->data->db);
        }
        else {
            foreach ( $oResult->data as $oDatabase ) {
                $this->m_aDatabaseList[] = (string)($oDatabase->db);
            }
        }
    }//getAllCpanelDatabases

    protected function loginAndDownloadFile( $insDbName, $insDbFileName ) {

        $sProtocol = ( $this->m_aConfig['CPANEL_PORT_NUM'] == '2083' )? 'https://' : 'http://';

        $sLoginUrl = $sProtocol.$this->m_aConfig['CPANEL_SERVER_ADDRESS'].':'.$this->m_aConfig['CPANEL_PORT_NUM'].'/getsqlbackup/'.$insDbName.'.sql.gz';

        $this->writeLog('Download URL: '.$sLoginUrl);

        $hOut = fopen( dirname(__FILE__).'/'.$insDbFileName, 'wb' );

        $oCurl = curl_init();
        curl_setopt( $oCurl, CURLOPT_HEADER,            false );
        curl_setopt( $oCurl, CURLOPT_NOBODY,            false );
        curl_setopt( $oCurl, CURLOPT_URL,               $sLoginUrl );
        curl_setopt( $oCurl, CURLOPT_SSL_VERIFYHOST,    0 );

        curl_setopt( $oCurl, CURLOPT_USERPWD,           $this->m_aConfig['CPANEL_ADMIN_USERNAME'].':'.$this->m_aConfig['CPANEL_ADMIN_PASSWORD'] ); 

        curl_setopt( $oCurl, CURLOPT_USERAGENT,         "Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.12) Gecko/20050915 Firefox/1.0.7");
        curl_setopt( $oCurl, CURLOPT_RETURNTRANSFER,    1 );
        curl_setopt( $oCurl, CURLOPT_SSL_VERIFYPEER,    0 );
        curl_setopt( $oCurl, CURLOPT_FOLLOWLOCATION,    1 );

        curl_setopt( $oCurl, CURLOPT_FILE,              $hOut );

        curl_exec( $oCurl );
        $sResult = curl_exec( $oCurl );
        fclose( $hOut );

        if ( $sResult == false ) {
            $sError = curl_error( $oCurl );
            curl_close( $oCurl );
            return $sError;
        }

        curl_close( $oCurl );

        return true;
    }

    protected function openFtpConnection() {

        $fSuccess = false;

        $sServerAddress = $this->m_aConfig['FTP_SERVER_ADDRESS'];
        $iServerPort = (int)( $this->m_aConfig['FTP_SERVER_PORT'] );

        // set up FTP connection
        if ( ($this->m_aConfig['FTP_USE_SSL'] == 'YES') ) {

            if ( function_exists('ftp_ssl_connect') ) {
                $this->m_oFtpConnection = ftp_ssl_connect( $sServerAddress, $iServerPort );

                if ( !$this->m_oFtpConnection ) {
                    $this->writeLog( "Attempt to connect to ".$sServerAddress.":".$iServerPort." with SSL+FTP failed. Will fallback to normal FTP." );
                }
                else {
                    $this->writeLog( "Attempt to connect to ".$sServerAddress.":".$iServerPort." with SSL+FTP Succeeded. Now logging in ..." );
                }
            }
            else {
                $this->writeLog( "This server doesn't support FTPS (FTP with SSL). Will fallback to normal FTP." );
            }
        }

        //Fallback
        if ( !$this->m_oFtpConnection ) {
            $this->m_oFtpConnection = ftp_connect( $sServerAddress, $iServerPort );
        }

        // login after a successful connection
        if ( $this->m_oFtpConnection ) {
            $fLoginResult = ftp_login( $this->m_oFtpConnection, $this->m_aConfig['FTP_USERNAME'], $this->m_aConfig['FTP_PASSWORD'] ); 
        }
        else {
            $this->writeLog( "Attempt to connect to ".$sServerAddress.":".$iServerPort." failed." );
        }

        // check connection
        if ( (!$this->m_oFtpConnection) || (!$fLoginResult) ) { 
            $this->writeLog( "FTP connection has failed!" );
        } else {
            $this->writeLog( "FTP connection was successful with ".$sServerAddress.":".$iServerPort );
            $fSuccess = true;
        }

        // Set to Passive connection if login was successful and this setting was set.
        if ( $fSuccess && ($this->m_aConfig['FTP_USE_PASSIVE'] == 'YES') ) {

            if ( ftp_pasv( $this->m_oFtpConnection, true ) ) {
                $this->writeLog( "FTP connection was set to PASSIVE mode." );
            }
            else {
                $this->writeLog( "Attempted to set FTP connection to PASSIVE mode but failed. Going to continue with copy anyway. If the script fails, review this as a possible source." );
            }
        }

        return $fSuccess;

    }//openFtpConnection

    protected function ftpFileRemotely( $insDbFileName ) {

        $fSuccess = false;

        $sDestinationFile = $this->m_aConfig['FTP_PATH_TO_COPY'] . $insDbFileName;

        // upload the file
        $fUpload = ftp_put( $this->m_oFtpConnection, $sDestinationFile, $insDbFileName, FTP_BINARY ); 

        // check upload status
        if (!$fUpload) { 
            $this->writeLog( "FTP upload has failed! Check the log file for errors. Try changing PASSIVE and SSL options in the config." );
            return false;
        } else {
            $this->writeLog( "Uploaded $insDbFileName to ".$this->m_aConfig['FTP_SERVER_ADDRESS']." as $sDestinationFile" );
            $fSuccess = true;
        }

        return $fSuccess;
    }

    protected function writeLog( $insLogData = '', $infWriteUser = false ) {
        echo "$insLogData\n";
    }

}//Worpit_Cpanel_MySql_Backup

$oBackupJob = new Worpit_Cpanel_MySql_Backup();
$oBackupJob->runBackup();

//END OF SCRIPT

A copy of all my databases gets backed up on the original website, as well as the remote website.
What do I need to add or change so that any backups older than the 5 newest gets pruned?

[cereal]

What do I need to add or change so that any backups older than the 5 newest gets pruned?

In the local or in the backup server?

In addition: try to avoid the use of spaces in name constants, otherwise you have to use the constant() function, I'm referring to these:

define( 'setq version-control t',               'YES' );
//Don't ask when deleting old backups
define( 'setq delete-old-versions t',       'YES' );
//Number of Numbered Backups to Keep
//Delete Oldest Version First
define( 'setq kept-old-versions'   '5' );
define( 'setq kept-new-versions'   '5' );

Also, it seems that the readConfig() method reads the config file to populate an array, so, unless you have other reasons, you can avoid to define them as constants and match them through a regular expression. Instead, you can change the config file to an array, for example:

<?php

return array(

    'KEEP_LOCAL_BACKUP_FILE'        => 'YES',
    'setq-version-control-t'        => 'YES',
    'setq-delete-old-versions-t'    => 'YES',
    'setq-kept-old-versions'        => '5',
    'setq-kept-new-versions'        => '5',
    'DO_REMOTE_FTP_COPY'            => 'yes',
    'CPANEL_SERVER_ADDRESS'         => 'mywebsite.com',
    'CPANEL_PORT_NUM'               => '2082',
    'CPANEL_ADMIN_USERNAME'         => 'username',
    'CPANEL_ADMIN_PASSWORD'         => 'password',
    'FTP_SERVER_ADDRESS'            => 'remotewebsite.com',
    'FTP_SERVER_PORT'               => '21',
    'FTP_USERNAME'                  => 'username',
    'FTP_PASSWORD'                  => 'password',
    'FTP_PATH_TO_COPY'              => '/path/to/backupfolder/',
    'FTP_USE_PASSIVE'               => 'YES',
    'FTP_USE_SSL'                   => 'NO'

    );

And change your readConfig() method to include the config file, for example:

public function readConfig()
{
    $this->m_aConfig = include self::CONFIG_FILE;
    return ;
}

Now, since the backup file names are based on a timestamp you can use a regular expression to match them and then use the diff() method of the DateTime library to give an order based on the hours between the file name string and the current date-time, an example:

public function deleteOldFiles()
{
    # current date
    $current = new Datetime();

    # set the path
    $files = glob('*.sql.gz');
    $pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{2}-[0-9]{2}-[0-9]{2}/";

    $i = 0;
    $result = array();
    $savelist = array();

    # generate file list
    foreach($files as $file)
    {
        if(preg_match($pattern, $file, $match) == 1)
        {
            $dt = substr($match[0], 0, 10) . ' ' . str_replace('-',':',substr($match[0], 11));
            $dDate = new Datetime($dt);
            $diff = $dDate->diff($current);

            $result['filename'][$i] = $file;
            $result['diff'][$i] = $diff->h + ($diff->d * 24);
        }
        $i++;
    }

    # sort them from the newest to the oldest
    natsort($result['diff']);
    $keys = array_keys($result['diff']);

    # create the save list
    for($z = 0; $z <= $this->m_aConfig['setq-kept-new-versions'] - 1; $z++)
    {
        $savelist[] = $result['filename'][$keys[$z]];
    }

    # delete the rest of the files
    $deleteList = array_diff(array_values($result['filename']), $savelist);
    array_map('unlink', $deleteList);

    return true;
}

The above method will create a white list of files to save, which is limited to the value of setq-kept-new-versions, i.e. 5, then the array_diff() function will remove them from the original $result['filename'] array and will unlink the rest of them.

Note this is an example, test it before of applying it in your production server.

[fiddler80]

Thanks -
to answer your question, I need the old backups deleted on both the localhost and remote server. Otherwise, the backups just keep piling up until I manually delete them, which I want to avoid doing, since the whole point of the cron job is to take care of the backups automatically.