db backup sftp

backup.php

<?php
// DB Backup

set_time_limit(500);
$success = false;
$localPath = './tmp/';

$database['db_user'] = 'xxx';
$database['db_password'] = 'xxx';
$database['db_name'] = 'xxx';
$database['sql_file'] = "dump_" . date('Y-m-d--G-i-s') . "_{$database['db_name']}.sql";

$sqlFileFullPath = $localPath . $database['sql_file'];

$dataFile = $database['sql_file'] . '.gz';

// Remote creds
$server = 'xxx';
$port = 22;
$username = 'xxx';
$password = 'xxx';
$remoteDir = '/xxx/';

$localGZFile = $localPath . $dataFile;
$remoteGZFile = $remoteDir . $dataFile;

$email = "[email protected]";

$headers = [
    'MIME-Version: 1.0', 
    'Content-type: text/html; charset=iso-8859', 
    'X-Mailer: PHP/'. phpversion(),
];
$mailHeaders = implode(PHP_EOL, $headers);

exec("mysqldump -u {$database['db_user']} -p{$database['db_password']} --allow-keywords --add-drop-table --complete-insert --hex-blob --quote-names --triggers {$database['db_name']} > " . $localPath . "{$database['sql_file']}");
if (file_exists($sqlFileFullPath)) {
	$output = '* Generated file => ' . $sqlFileFullPath . '<br>';
	try {
		$output .= '* Compressing file : ';
		exec("gzip " . $localPath . "{$database['sql_file']}");
		$output .= 'OK<br>';
	} catch (Exception $e) {
		$output .= 'KO -> Error : ' . $e->getMessage() . '<br>';
		$output .= 'Error code : ' . $e->getCode() . '<br>';
		$output .= $e->getFile();
	}
}

$output .= '* Upload to :' . $remoteDir . '/' . basename($dataFile) . '<br>';

$connection = ssh2_connect($server, $port);

if(ssh2_auth_password($connection, $username, $password)){
    if(ssh2_scp_send($connection, $localGZFile, $remoteGZFile)){
        $success = true;
    } else {
        $output .= "[KO] Error while uploading file to $server" . '<br>';
    }
} else {
    $output .= "[KO] Error while connecting to $server" . '<br>';
}

$output .= (unlink($localGZFile)) ? '[OK] Tmp file deleted' : '[KO] Error while removing tmp file';
$output .= '<br>';

// result
if (!$success) {
    mail($email, 'Error DB Backup', $output, $mailHeaders);
} else {
    mail($email, 'DB Backup success', $output, $mailHeaders);
}
?>