Backup your website database and send it to remote server with PHP code

In this mini-tutorial we will build little php file which can take your website database backup and send it to remote ftp server.
We will write 3 functions for it.
1st one will take backup from our database.
2nd one will send it to remote ftp server.
3rd one will make 1-2 steps automatizied.

1. The function which takes backup:

function backup_tables($filename, $host, $user, $pass, $name, $tables = '*')
{
   //...
}

2. The function which sends backup to remote server.

function upload_to_ftp($ftp_hostname, $ftp_user, $ftp_pass, $remote_file, $file)
{
   //...
}

Now let’s write full code:

<?php
$dir_remote  = '/path/to/remote/ftp/directory/';
$file        = 'db-backup-' . time() . '.sql.gz';
$remote_file = $dir_remote . $file;
backup_tables($file, 'localhost', 'db_user', 'db_padd', 'db_name');
upload_to_ftp('ftp_hostname', 'ftp_user', 'ftp_pass', $remote_file, $file);

function upload_to_ftp($ftp_hostname, $ftp_user, $ftp_pass, $remote_file, $file)
{
    $conn_id      = ftp_connect($ftp_hostname);
    $login_result = ftp_login($conn_id, $ftp_user, $ftp_pass, $remote_file, $file);
    
    if (ftp_put($conn_id, $remote_file, $file, FTP_ASCII)) {
        echo "Success!\n";
    } else {
        echo "Some problem occured\n";
    }
    ftp_close($conn_id);
    
    unlink($file);
}
function backup_tables($filename, $host, $user, $pass, $name, $tables = '*')
{
    $return = '';
    $link   = mysql_connect($host, $user, $pass);
    mysql_select_db($name, $link);
    
    if ($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while ($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }
    
    foreach ($tables as $table) {
        $result     = mysql_query('SELECT * FROM ' . $table);
        $num_fields = mysql_num_fields($result);
        
        $return .= 'DROP TABLE ' . $table . ';';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
        $return .= "\n\n" . $row2[1] . ";\n\n";
        
        for ($i = 0; $i < $num_fields; $i++) {
            while ($row = mysql_fetch_row($result)) {
                $return .= 'INSERT INTO ' . $table . ' VALUES(';
                for ($j = 0; $j < $num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = preg_replace("/\n/","/\\n/",$row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j < ($num_fields - 1)) {
                        $return .= ',';
                    }
                }
                $return .= ");\n";
            }
        }
        $return .= "\n\n\n";
    }
    $return = gzencode($return, 6);
    $handle = fopen($filename, 'w');
    fwrite($handle, $return);
    fclose($handle);
}

In the given code ‘/path/to/remote/ftp/directory/’, ‘db_user’, ‘db_padd’, ‘db_name’, ‘ftp_hostname’, ‘ftp_user’, ‘ftp_pass’ strings must be changed to real ones.
Let’s save it as backuper.php file.

Now time for automatization. Let’s look to 2 cases:

1. You have WordPress website.

We add simple code below to the backuper.php’s 1st line:
if ( ! defined( ‘WPINC’ ) ) die;

Then we add this code below to the functions.php of our theme.

if ( ! wp_next_scheduled( 'my_backuper' ) ) {
  wp_schedule_event( time(), 'daily', 'my_backuper' );
}
add_action( 'my_backuper', 'my_backuper_function' );
function my_backuper_function() {
  require(get_template_directory()."/backuper.php");
}

That’s all. One time in a day website will make its own DB backup and will send it to remote ftp server.

2. You have non-WordPress website and you have access to your server management(not for sharing hostings.
In this case just add backuper.php to your cron jobs. For this purpose add command line below to your cron jobs file:

00 12 * * * php /path/to/backuper.php

That’s all. One time in a day server’s scheduled cron job will make its own DB backup and will send it to remote ftp server.
With the same logic we can send backup file to the Dropbox cloud server, instead of FTP. We will write another tutorial for that.
source