PHP + Rsync + MySQL – building your own data/file remote syncing app

This is to show you how you can build a little rsync app in PHP that is to be run by a Linux/Unix cron job that syncs files on your data or web server to a remote server. PHP is allowed to execute the Unix/Linux system commands such as “rsync” and it gives you the easiness of accessing database such as MySQL.

Before getting rsync to run properly without needing to prompt for login and get it automated, you’ll have to have some rsync setup. I’ll just include very brief description and I suppose you know what rsync is for and you should ensure rsync or sshd is properly installed on both your server A (the server you wanna copy files from, the server that sends files) and server B (the server you wanna copy your files to, the server that receives the files) here.

In the case, server A is where the server you intend to sync or copy its files to server B as a backup or any other purposes. Ok the very simple steps you wanna setup to get rsync client and rsync server to work properly on server A and server B respectively are as follows, click here to expand it due to it’s too lengthy:

1. First, check server B (the server that receives the files) that the sshd (Secure Shell daemon) is running properly, which can be ensured by checking “sshd_config” on the server B, typically found in /etc/ssh. There are cases that some servers do NOT have the settings in the file sshd_config, so you’d need to add in the following two lines by yourself.

RSAAuthentication yes
PubkeyAuthentication yes

To enable the public/private key authentication mechanism that you need for server A to sync files to server B.

2. Now you have to generate the key pair on server A. Run the genkey as follows on your shell:

#> ssh-keygen -t rsa

You’ll get the prompt as follows:

Generating public/private rsa key pair.
Enter file in which to save the key (/home/user1/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in .ssh/id_rsa.
Your public key has been saved in .ssh/id_rsa.pub.
The key fingerprint is:
c1:21:e 3:01:26:0d:f7:ec:52:0e:0c:90:9b:6e:d8:47 user1@server-A.com

Please note the above you just respond to the prompt by hitting RETURN only, and do NOT enter passphrase in order for the rsync to be automated that needs no passphrase for using it. Now you’ve got the public/private key pair after doing the above-mentioned. (Do safeguard your private key as it’s not passphrase meaning anyone who’s got the private key will be able to gain access to what ever resources into which you’ve placed the corresponding public key.)

In this example for user1 in server A, we’ve got the private key and public key residing in the directory as follows:

Private key – /home/user1/.ssh/id_rsa
Public key – /home/user1/.ssh/id_rsa.pub

3. Now on server B (the remote server that receives files), pick one account that you wanna connect as, lets call it user2 here. So now, in user2′s home folder, create a “.ssh” sub-directory and create a new text file “authorized_keys” in the “.ssh” folder. If it already exists, that’s fine, use the existing file. Be sure, to chmod the “.ssh” directory to 700 and 600 for the file “authorized_keys” that allows only accessible by the owner. Now open the “authorized_keys” file and add content of the id_psa.pub (public key generated in server A) to it. And it’ll be a single line that looks as follows:

ssh-rsa user1@server-A.com

Save the “authorized_keys” file and now anyone that possesses the private key which matches the public key can login using the “user2″ account without being prompted for password. You can test out if it’s properly setup by the following steps:

- Login to server A using account “user1″ or just “su user1″ , as the private key is stored in user1′s home directory as described above
- Then initiate an SFTP session using account “user2″ connecting to server B.

sftp user2@server-B.com

Without needing you to type any password, you’ll be automatically authenticated and logged in now on server B!

If you want more detailed explanation about the setup here, you can refer to Ask-Leo.com, which is what I first learned how to setup automated SFTP or RSYNC from server A to server B.

Finished reading? Close this now?

Okay, now we have to get back to the PHP app that does the auto rsynching, which is the main purpose of this post. In this little app, it will just consist of a single PHP script that will browse through the MySQL tables in MySQL for the files or directories that need to be synced to the remote server. And it’ll keep a log in one of the tables in MySQL for keeping track the last modified date of the files and any error that would happen during transmission. Lets get into clearer picture.

The little PHP Rsync app will only sync those files which the last modified date is different from what is being logged in the MySQL table, to minimize bandwidth usage. We have only three MySQL tables here, which are as follows:

This post will be continued soon, as it’s too lengthy, now just get it posted, sure will give you the rest…stay tuned and come back here for the rest of this post

Quick Update: My apology as this post has been left too long without updating the remaining post. Since there is a recent request by a reader. I hereby have a quick update.

# The table to store the directories or folders which you want the data file to be
# synced over to the remote server.
create table dir
(
id bigint(10) NOT null auto_increment,
name varchar(50),
dir_path varchar(200),
PRIMARY KEY(id)
);

# The table that stores each file with its last modified name
# This table is used to check if a file has been modified, if it has, it’ll be synced over to the remote
# server or else there will be no action
create table flog
(
fname varchar(200) default ” NOT null,
lmtime bigint(20),
primary key (fname)
);

# The table that stores the log of the rsync
create table fslog
(
fname varchar(200) default ” NOT null,
sdate bigint(20),
rmk varchar(250),
synced enum(‘Y’,'N’),
PRIMARY KEY(fname,sdate)
);

The following is the PHP code that does the rsync.

<?php
define(‘DB_USER’,'user’);
define(‘DB_PASS’,'passwd’);
define(‘DB_NAME’,'sync_database’);
define(‘DB_HOST’, ‘localhost’);

$conn=mysql_connect (DB_HOST, DB_USER, DB_PASS) or die(mysql_error($conn));
mysql_select_db(DB_NAME);

$res=mysql_query(“SELECT id,name,dir_path FROM dir ORDER BY id”) or die (mysql_error($conn));
$nrows = mysql_num_rows($res);

for ($r=0; $r< $nrows; $r++)
{
$arow= mysql_fetch_row ($res);
$id = $arow[0];
$name = $arow[1];
$dir_path = $arow[2];
$files = dirList ($dir_path);
}

function logexists($filename)
{
$res=mysql_query("SELECT fname,lmtime FROM flog where fname='$filename'")
or die (mysql_error($conn));
$nrows = mysql_num_rows($res);
mysql_free_result($res);
return $nrows;
}

function PsExec($command)
{
$str=exec( $command, $output);
if (count($output)>0)
{
for($r=0; $r < count($output);$r++)
{
$str.=$output [$r].' '; return $str;
}
}

return false;
}

function logAndSyncFile($file, $filedir)
{

$lmtime=filemtime ($file); if (!$lmtime){ $lmtime=time(); echo "filemtime() error use current time: $lmtime\n";}

$res=mysql_query("SELECT fname,lmtime FROM flog where lmtime='$lmtime' and fname='$file'")
or die (mysql_error($conn));
$nrows = mysql_num_rows($res);
echo "\nProcessing file $file in directory $filedir";

if ($nrows==0 || isLatestSyncedFailed($file))
{

echo "\nStart synching file [$file] to remote directory [$filedir]";
/** Sync each file to the remote server*/
$str=PsExec("rsync -R -avx -e ssh \"$file\" xferer@theremoteserver.com:/");
$estat='Y';
if ( !$str) $estat='E';

$sdate=time(); $sdstr = date('Y-m-d H:i:s', $sdate); $lmdstr = date ('Y-m-d H:i:s',$lmtime);
if ( logexists ( $file))
mysql_query("update flog set lmtime='$lmtime' WHERE fname='$file'");
else
mysql_query("insert into flog (fname,lmtime,lmdstr) values('$file','$lmtime','$lmdstr')");

mysql_query("insert fslog (fname,sdate,rmk,synced,sdstr) values('$file','$sdate','Replicated - mesg:$str','$estat','$sdstr')");
echo "\nExcuted message:$str It's been synced over";

}
else
{
$sdate=time(); $sdstr = date('Y-m-d H:i:s', $sdate);
echo "\nFile $file not to be synced as no changes";
mysql_query("insert fslog (fname,sdate,rmk,synced,sdstr) values('$file','$sdate','No Replication, As No Changes','N','$sdstr')");

}

echo "\n";
}

function isLatestSyncedFailed($fname)
{
$res=mysql_query("SELECT fname,synced FROM fslog where fname='$fname' ORDER BY sdate DESC LIMIT 0,1")
or die (mysql_error($conn));

$nrows = mysql_num_rows($res);
if ($nrows>0)
{
$arow= mysql_fetch_row ($res);
if ($arow[0]==’E') return true;
}
mysql_free_result($res);
return false;

}

function dirList ($directory)
{

echo “\n\nListing directory :$directory”;
// create an array to hold directory list
$results = array();

// create a handler for the directory
$handler = opendir($directory);

// keep going until all files in directory have been read
while ($file = readdir($handler))
{

$f0 = $directory.$file;
echo “\nFile being read now: $f0″;
if (is_dir($f0) && $file!=’.’ && $file!=’..’)
{
$f0=$f0.”/”;
echo “\nPenetrade into sub directory: $f0″;
$res2= dirList($f0);
}
else
{

if ($file != ‘.’ && $file != ‘..’ && trim($file)!=” )
{
$results[] = $f0;
logAndSyncFile($f0, $directory);
}

}
}

// tidy up: close the handler
closedir($handler);

// done!
return $results;

}
?>

Save the above PHP file as /home/dxferer/bin/rsync.php. Create a shell script, save as “runrsync.sh” or any other name you like, to run the above PHP code that contains a line as follows:

/usr/bin/php /home/dxferer/bin/bkglob.php

Create another shell script that uses sudo to run the above script using a Linux user (in this case is dxferer) which will then be run by the Linux cron job. This file contains a line as follows:

sudo -H -u dxferer /home/dxferer/bin/xrunbkglob.sh

Save the above shell script under a file name you like, and get cron job to run the above script.

Note: Due to my overly busy schedule, I’ve just had a quick update for the rest of this post that had been promised a couple of months ago. It might lack of explanation or there might be some mistakes in the code. But the way shall be close what you want for writing an rsync app using PHP and MySQL. I’ll refine this article when I have more time later. :D

Enter your email address to subscribe our newsletter or feed for FREE:

Delivered by FeedBurner


Bookmark with:

[Delicious]    [Digg]    [Reddit]    [Facebook]    [StumbleUpon]

0 Responses to “PHP + Rsync + MySQL – building your own data/file remote syncing app”


  1. No Comments

Leave a Reply

You must login to post a comment.