Converting some code to php

peaderfi

Hey all,

I've written a .sh database backup script
which i would like to convert into php...unfortunately i don't have the first clue about php coding...

here's my code

#==============================================
#Save the script below as backup_db.sh
#==============================================

#!/bin/bash

#
# Script Created by Peter
# Last edited 31/12/2007 by Peter

# Script Function:
# This bash script backups up the specified database(s) below at regular intervals as specified in the cron job
# The database [i]yourdatabase here[/i] will be saved in /[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i]
# The database [i]yourdatabase here[/i] will be saved in /[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i]
# The script also deletes backups which are more than 30 days old

#[Changes Directory]
cd /[i]yourpathhere[/i]/backups/

#[Old DB Deletion Script]
#find /[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i] -name "*.tar.gz" -mtime +30 -exec rm -f {} \;

#[Stamps the file name with a date]
TIMESTAMP=`date +%m-%d-%y-%H%M`


#[DB Backup Scripts]

 "DBname"
HOST=localhost
DBNAME="DBName"
USER="DBusername"
PASSWORD="DBpassword"
DUMP_PATH=/[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i]
mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > DBNAME.sql
tar -czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
rm -f $DBNAME.sql


# "DBname"
 #HOST=localhost
 #DBNAME="DBName"
 #USER="DBusername"
 #PASSWORD="DBpassword"
 #DUMP_PATH=/[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i]
 #mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > #DBNAME.sql
 #tar -czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
 #rm -f $DBNAME.sql


#=======================================
#END OF BACKUP SCRIPT
#=======================================

If ye could point me in the direction of some good books/php coding resources which might help me, please post em up

Also if ya wanna give a bash at it yourself fire away...please and thank you

Zombrex

The MySQL functions in PHP are not really designed for that type of thing. As far as I know there is no function to perform a MySQL dump (http://ie2.php.net/mysql)

So the best way would just end up running the mysqldump shell command from PHP using the system command (http://ie2.php.net/manual/en/function.system.php)

Example here (Google cache only, the website seems off line)

http://64.233.183.104/search?q=cache:PXMcfr2-e5gJ:www.php-mysql-tutorial.com/perform-mysql-backup-php.php+mysql+php+dump&hl=en&ct=clnk&cd=1&client=firefox-a

But that would just be using PHP to do exactly the same thing as you are doing with your bash program, so its rather pointless.

Can I ask why this has to be in PHP?

peaderfi

well i run a couple of sites and one of those sites is located on a hosting account where i have not got access to a root account or cron on my control panel and the only way i can set up a backup script is through the task manager in the vbull site software...but the vbull task manager only seems to like php scripts so hence my problem.

Thanks a million for your help

peaderfi

my code:

(note this is specifically for running a cron job in vb and as such is specific to vb's config file)

<?php

/* Script Created by Peter
Last edited 31/12/2007 by Peter

Script Function:
This bash script backups up the specified database(s) below at regular intervals as specified in the cron job
The database yourdatabase here will be saved in /[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i]
*/
//Include the config for password and stuff

include '../config.php';

//Set up the variables
$dbhost = $config['MasterServer']['servername']
$dbuser = $config['MasterServer']['username']
$dbpass = $config['MasterServer']['password']
$dbname = $config['Database']['dbname']

//Connect to the database
$link = mysql_connect('$dbhost', '$dbuser', '$dbpass') or die('Could not connect: ' . mysql_error());
mysql_select_db('$dbname') or die('Could not select database');

//set up the backup
$DUMP_PATH=/[i]yourpathhere[/i]/backups/database_backups/[i]your database here[/i]
$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $DUMP_PATH/$backupFile";

//Run the Backup
system($command);


// Closing connection
mysql_close($link);
?>

Zombrex

A couple of problems.

You are setting up a connection to a database, but you aren't actually doing anything with it.

The system command is nothing to do with the database commands. It simply runs a shell command. You don't need to set up the database connection within PHP to run it. And if you don't have access to shell commands on your hosting service you won't be able to run it anyway.

To back up a database through PHP itself is a bit more complicated. You basically need to cycle through each table in the database and back them up using a SELECT * FROM tablename statement, with the results written out to a file. If you want this file in nice SQL for reinserting back into the database that is more complicated.

I would be surprised though if your hosting service didn't provide you with some front end system to your database, such as phpMyAdmin. This has an option to backup your database to a file and takes care of generating the file for you.

Webmonkey

To be honest, its tedious going through all tables individually selecting all and outputting when it can be done using the dump* command

[php]
<?php
// mysql & minor details..
$tmpDir = "/tmp/";
$user = "root";
$password = "pass";
$dbName = "db";
$prefix = "db_";


$sqlFile = $tmpDir.$prefix.date('Y_m_d').".sql";
$file = $tmpDir.$prefix.date('Y_m_d').".tgz";

$creatBackup = "mysqldump -u ".$user." --password=".$password." ".$dbName." > ".$sqlFile;
$createZip = "tar cvzf $attachment $sqlFile";
exec($creatBackup);
exec($createZip);
?>

[/php]

Look here: http://www.sematopia.com/?p=61
You should be able to email it to yourself as well provided you have the mail.php class but this can easily be changed to work with the mail function.

*provided you have access to the exec() command with your host

Zombrex

Webmonkey wrote: »

*provided you have access to the exec() command with your host

He doesn't, that is the problem

Webmonkey

Wicknight wrote: »

He doesn't, that is the problem

Oh Yes I see now, I'm blind
Does the task manager on vbulletin actually work? - I would have thought this would be implemented using a CRON job as well?

dob99

First of all, if you have CGI access (which most hosts do), it should be relatively trivial to convert your shell script to a CGI script.

If not, here's some code in PHP to dump the contents of a database:

[PHP]<?php

function dump_db($dbname, $login, $passwd='', $host='localhost', $create=true, $data=true)
{
$ret_val = "# Database: $dbname\n\n";

$id_link = mysql_pconnect($host, $login, $passwd);

$tables = mysql_list_tables($dbname, $id_link);

$num_tables = mysql_num_rows($tables);

// store table names in an array
$arr_tablenames[] = '';

// store number of fields per table(index 0,1,2..) in an array
$arr_num_fields[] = '';
for ($i=0; $i < $num_tables; $i++) {
$arr_tablenames[$i] = mysql_tablename($tables, $i);
$arr_num_fields[$i] = mysql_num_fields(mysql_db_query($dbname, "select * from $arr_tablenames[$i]", $id_link));
}

// store field names in a multidimensional array:
// == table number, [ii] == field number for that table
for ($i=0; $i < $num_tables; $i++) {
for ($ii=0; $ii < $arr_num_fields[$i]; $ii++) {
$result = mysql_db_query($dbname, "select * from $arr_tablenames[$i]", $id_link);
$hash_field_names[$i][$ii] = mysql_field_name($result, $ii);
}
}


for ($i=0; $i<$num_tables; $i++)
{
$ret_val .= "\n# Table: {$arr_tablenames[$i]}\n";
if ($create)
{
// Dump the CREATE statement
$result = mysql_db_query($dbname, "SHOW CREATE TABLE {$arr_tablenames[$i]}", $id_link);
$record = mysql_fetch_row($result);
$ret_val .= $record[1].";\n\n";
}

// Create an INSERT statement for each row in the table
if ($data)
{
$result = mysql_db_query($dbname, "select * from $arr_tablenames[$i]", $id_link);
$number_of_rows = @mysql_num_rows($result);
for ($j=0; $j<$number_of_rows; $j++)
{
$record = @mysql_fetch_row($result);
$ret_val .= "INSERT INTO {$arr_tablenames[$i]} (";

for ($k=0; $k<$arr_num_fields[$i]; $k++)
{
$ret_val .= "{$hash_field_names[$i][$k]}";
if ($k < $arr_num_fields[$i]-1)
$ret_val .= ", ";
}
$ret_val .= ") VALUES(";

for ($k=0; $k<$arr_num_fields[$i]; $k++)
{
$v = mysql_real_escape_string($record[$k]);
$ret_val .= "'{$v}'";
if ($k < $arr_num_fields[$i]-1)
$ret_val .= ", ";
}

$ret_val .= ");\n";
}
}
}

return $ret_val;
}
?>[/PHP]

I use this function myself to do manual backups on my host. I could be a bit memory intensive if you're working with a large database, but it should be possible to compress it on-the-fly if necessary.

NOTE: I adapted this from something I found on the net, but I don't remember where - it may have been phpbuilder.net, which I find pretty useful.

peaderfi

thanks for all your replies...its really appriciated...

It appears that i don't as you pointed out have access to the exec() command

Just on the php code posted in the last post...where does it save the backup too?

Webmonkey

Without looking into the code I would guess its on the return of that function.

So simply use the function like:
[php]
$dbname = 'mydb';
$login = 'username';
$passwd = 'mypass';
$host = 'myhost' //Normally localhost
$myfile = '/tmp/backup';

$output = dump_db($dbname, $login, $passwd, $host, $create=true, $data=true) ;

if($file=fopen($myfile, "w"))
fwrite($file, $output);
[/php]

peaderfi

ok when i try to run that i get this

Parse error: parse error, unexpected T_IF in /home/virtual/site28/fst/var/www/html/includes/cron/db_dump.php on line 80

line 80 is

if($file=fopen($myfile, "w"))

peaderfi

k checked the code and i must have missed something before

updated it

and now i am getting the following error

Warning: fopen(backup.sql): failed to open stream: Permission denied in /includes/cron/db_dump.php on line 86

Webmonkey

Make sure the file exists and CHMOD to 777

peaderfi

ok so i found something out today

the reason why the backups weren't working with my original script was not because vb can't run the php script...its because vb runs its cron jobs via the apache user and can see outside the honeypot that my site is located in...so the directories were all wrong for what i was trying to do...i've fixed that issue now and the script i'm using now is an emalgamation of the various ones above

<?php
$dbname = 'dbname';
$login = 'username';
$passwd = 'password';
$host = 'localhost'; //Normally localhost
$myfile = '[i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/backup.sql';
$stamp = date('H:i_d_m_Y');

$creatBackup = "mysqldump --opt -c -e -Q -u ".$login." --password=".$passwd." ".$dbname." > ".$myfile;
$createZip = "tar -czpf [i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/$dbname.$stamp.tar.gz [i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/backup.sql";
$rm = "rm [i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/backup.sql";

exec($creatBackup);
exec($createZip);
exec($rm);
?>

and yep the vb scheduled task manager thing does work...its works very nicely

peaderfi

ohh and thank you to everyone who helped me out with this

Webmonkey

peaderfi wrote: »

ok so i found something out today

the reason why the backups weren't working with my original script was not because vb can't run the php script...its because vb runs its cron jobs via the apache user and can see outside the honeypot that my site is located in...so the directories were all wrong for what i was trying to do...i've fixed that issue now and the script i'm using now is an emalgamation of the various ones above

<?php
$dbname = 'dbname';
$login = 'username';
$passwd = 'password';
$host = 'localhost'; //Normally localhost
$myfile = '[i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/backup.sql';
$stamp = date('H:i_d_m_Y');

$creatBackup = "mysqldump --opt -c -e -Q -u ".$login." --password=".$passwd." ".$dbname." > ".$myfile;
$createZip = "tar -czpf [i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/$dbname.$stamp.tar.gz [i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/backup.sql";
$rm = "rm [i]honeypot path[/i]/home/backups/database_backups/[i]my database name[/i]/backup.sql";

exec($creatBackup);
exec($createZip);
exec($rm);
?>

and yep the vb scheduled task manager thing does work...its works very nicely

Oh great stuff. Take Care.

Donal