backup remote database locally

Question

I have to backup my database in live server in certain interval of time. For this I created a batch file and scheduled it's execution using normal Windows' Schedular. I have called a php file in the batch file to perform the backup function. In the php file, I used the normal mysql queries to connect to the database and fetch the data structure but this always threw error saying

Warning: mysql_connect() [function.mysql-connect]: [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://[remote database server]) in [file on the local server] on line xx

Please help me out overcome this error so that i can backup remote database locally.

Answer 1

<?php
ini_set('display_errors', 1);
ini_set('max_execution_time', 0);

$notify_me = 'true';
$email = 'notify email';
$this_server = 'FQDN of local server';
$remote_server = 'FQDN of remote server';

$dbserver = "localhost";
$dbuser = "root";
$dbpass = "";
$time_of_day = '22';
$day_of_week = 'Sat';
$backup_user = 'backups';
$who_am_i = 'Name for email to go to';
$backup_server = 'remote ftp server to upload to';
$ftp_user = 'ftp user name';
$ftp_pass = 'ftp password';



$command="php -q /home/$backup_user/create_mysql_dumps.php";
$job="0 $time_of_day * * * $command";
echo "( crontab -l | grep -v \"$command\" ; echo \"$job\" ) | crontab -";






shell_exec("( crontab -l | grep -v \"$command\" ; echo \"$job\" ) | crontab -");


@shell_exec("adduser $backup_user");



mysql_connect($dbserver, $dbuser, $dbpass);

mysql_select_db("mysql");


function dirToArray($directory) {

    $array_items = array();
    $handle = @ opendir($directory);
        while (false !== ($file = @ readdir($handle))) {
            if ($file != "." && $file != "..") {
                if (is_dir($directory)) {
                    $array_items[] = $file;
                }

            }
        }
        @ closedir($handle);

    return $array_items;

}





$sql = mysql_query("show databases");


while($row = mysql_fetch_array($sql)){
@shell_exec("mkdir /home/$backup_user/data/");
shell_exec("chmod 777 /home/$backup_user/data/");


shell_exec("rm /home/$backup_user/data/$row[0].sql -f");
    shell_exec("mysqldump -u $dbuser -p$dbpass $row[0] > /home/$backup_user/data/$row[0].sql");

    echo "Dbase Backup created for $row[0]\n";
    usleep(2000);
    if(file_exists("/home/$backup_user/data/$row[0].sql")){

    if($notify_me == 'true'){
    mail($email, "Dbase Backup created for $row[0]", "Hello $who_am_i This is your backup script telling you that its working and saving a backup for $row[0] in /home/$backup_user/data/$row[0].sql", null, "-f root@$this_server"); 

      }
    }

}


@shell_exec("mkdir /home/$backup_user/data/");
shell_exec("chmod 777 /home/$backup_user/data/");

shell_exec("rm /home/$backup_user/data/all-databases.sql");
shell_exec("mysqldump -u $dbuser -p$dbpass --all-databases > /home/$backup_user/data/all-databases.sql");
echo "Dbase Backup created for All Databases\n";
    if(file_exists("/home/$backup_user/data/$row[0].sql")){
      if($notify_me == 'true'){
    mail($email, "Dbase Backup created for All Databases", "Hello $who_am_i This is your backup script telling you that its working and saving a backup for ALL SERVERS HAS BEEN CREATED", null, "-f root@$this_server"); 

      }
    }

$homes = dirToArray("/home");
$exclude = array('backups', 'mail_admin', 'trials', 'vmail', 'lost+found', 'aquota.user', 'aquota.group');
foreach($homes as $home){

  if(!in_array($home, $exclude) & is_dir("/home/$home")){
  echo "File System Backup created for /home/$home\n";


      @mkdir("/home/$backup_user/files/");
      @mkdir("/home/$backup_user/files/$home");
      @shell_exec("rm /home/$backup_user/files/$home/* -R -f");
      shell_exec("cp /home/$home/* /home/$backup_user/files/$home/ -R -p");
      if($notify_me == 'true'){
    mail($email, "File System Backup created for /home/$home", "Hello $who_am_i This is your backup script telling you that its working and saving a backup for /home/$home in /home/$backup_user/files/$home", null, "-f root@$this_server"); 

      }  
  usleep(2000);
  }



}
if(date("D") == $day_of_week){

@shell_exec("rm /home/$backup_user/named/* -R -f");
@shell_exec("rm /home/$backup_user/httpd/* -R -f");
@shell_exec("rm /home/$backup_user/mysql/my.cnf -f");
@shell_exec("rm /home/$backup_user/php/php.ini -f");


@mkdir("/home/backups/named");
@mkdir("/home/backups/httpd");
@mkdir("/home/backups/named");
@mkdir("/home/backups/mysql");
@mkdir("/home/backups/php");


shell_exec("cp /var/named/* /home/$backup_user/named/ -R -p");
shell_exec("cp /etc/httpd/conf/* /home/$backup_user/httpd/ -R -p");
shell_exec("cp /etc/my.cnf /home/$backup_user/mysql/my.cnf -p");
shell_exec("cp /etc/php.ini /home/$backup_user/php/php.ini -p");
shell_exec("cp /etc/named.conf /home/$backup_user/named/named.conf -p");

@mkdir("/home/$backup_user/zip");


@shell_exec("rm /home/$backup_user/$this_server-backup-weekly.tar.gz -f");
shell_exec("tar -czvf /home/$backup_user/$this_server-backup-weekly.tar.gz /home/$backup_user --exclude=/home/$backup_user/$this_server-backup-weekly.tar.gz");


if(file_exists("/home/$backup_user/$this_server-backup-weekly.tar.gz")){
echo "Weekly Archive created for $this_server\n";
      if($notify_me == 'true'){
    mail($email, "Weekly Archive created for $this_server", "Hello $who_am_i This is your backup script telling you that its working and saving a weekly archive for $server at /home/$backup_user/$this_server-backup-weekly.tar.gz", null, "-f root@$this_server"); 

      }  
    echo "sending backup to " . "http://$backup_server/backme_up.php\n";  

    $file = "/home/$backup_user/$this_server-backup-weekly.tar.gz";

    $fp = fopen($file, 'r');

    $conn_id = ftp_connect($backup_server) or die("Couldn't connect to $ftp_server"); 
    echo "$conn_id, $ftp_user, $ftp_pass";
    ftp_login($conn_id, $ftp_user, $ftp_pass);
    ftp_chdir($conn_id, "/home/$ftp_user");
    $contents = ftp_nlist($conn_id, ".");
    if (ftp_delete($conn_id, "$this_server-backup-weekly.tar.gz")) {
      echo "$this_server-backup-weekly.tar.gzdeleted successful\n";
      } else {
      echo "could not delete $file\n";
      }
    if (ftp_put($conn_id, "$this_server-backup-weekly.tar.gz", $file, FTP_ASCII)) {
      echo "successfully uploaded $file\n";
      } else {
      echo "There was a problem while uploading $file\n";
      }


    ftp_close($conn_id);
    fclose($fp);
}
}


die();
exit;

Answer 2

Another option you could use is have the live server send JSON data to your local server and insert locally this way you don't have to deal with configurations and you can easily extends the same process for other modules in your application or any other applications that might need that same data without start the work afresh.

Answer 3

Error code 2002 means that either MySQL is not running on the remote server or maybe you are using the wrong port number? Are you sure the port number you are using to connect through are open and not blocket with a firewall or something?

Read more about it here in the MySQL reference where they explain the 2002 error: http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html