[SOLVED] PHP and MySQL backup

[pcw]

Hi,

 

I am using this script to make a backup of the specified table in a mysql database:

 

<?php
include_once("data/required.php");
$hostname = "localhost";
mysql_connect("$hostname", $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

$table  = 'profiles';
$backup = 'backup/profiles.sql';
$query      = "SELECT * INTO OUTFILE '$backup' FROM $table";
$result = mysql_query($query);

?> 

 

But it isnt creating the .sql file. What am I doing wrong?

 

Thanks

[Cory94bailly]

You are not even running anything..!

 

The only things that are actually being ran are include_once, mysql_connect, and mysql_select_db..

 

Try:

<?php
include_once("data/required.php");
$hostname = "localhost";
mysql_connect("$hostname", $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

$table  = 'profiles';
$backup = 'backup/profiles.sql';
$query      = "SELECT * INTO OUTFILE '$backup' FROM $table";
mysql_query($query) or die(mysql_error());

?> 

[trq]

You are not even running anything..!

 

The only things that are actually being ran are include_once, mysql_connect, and mysql_select_db..

 

Your eyes must be painted on.

[waynew]

Hi,

 

I am using this script to make a backup of the specified table in a mysql database:

 

<?php
include_once("data/required.php");
$hostname = "localhost";
mysql_connect("$hostname", $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

$table  = 'profiles';
$backup = 'backup/profiles.sql';
$query      = "SELECT * INTO OUTFILE '$backup' FROM $table";
$result = mysql_query($query);

?> 

 

But it isnt creating the .sql file. What am I doing wrong?

 

Thanks

 

You're not doing anything with the resultset?

[pcw]

Ok, I am really confused here, I have got:

 

<?php
$db_host = "localhost";
$db_name = "moveitho_sitebuilder";
$db_user = "moveitho_paul";
$db_pass = "****";
$table = "profiles";
mysql_connect($db_host,$db_user,$db_pass);
@mysql_select_db($db_name) or die("Unable to select database.");

$backupFile = 'backup/profiles.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $table";
$result = mysql_query($query) or die(mysql_error());

?>

 

But when I run this script, I get this error:

 

Access denied for user 'moveitho_paul'@'localhost' (using password: YES)

 

However, if I change this part of the script:

 

<?php
$backupFile = 'backup/profiles.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $table";
$result = mysql_query($query) or die(mysql_error());
?>

 

to something like this:

 

mysql_query("CREATE TABLE IF NOT EXISTS $name
(id INT NOT NULL,
username VARCHAR(255) NOT NULL,
favourite VARCHAR(255) NOT NULL)") or die(mySQL_error());

 

it connects to the database successfully and creates the table. I cant understand why it wont connect to the database when using this

 

$backupFile = 'backup/profiles.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $table";
$result = mysql_query($query) or die(mysql_error());

 

Any help is much appreciated

[pcw]

has anyone know how to fix this?

 

Thanks

[pcw]

This is what I have got now, as I couldnt get OUTFILE to work

 

<?php
include_once("data/required.php");
$hostname = "localhost";
$table  = 'profiles';

mysql_pconnect("$hostname", $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$result = mysql_query("SELECT username, gen_id FROM $table") or die(mysql_error());
print $result;

$filename = "backup/profiles.sql";
$fa = fopen( $filename, "w" ) or die("Error opening $filename");
fwrite( $fa, "$result" );
fclose( $fa );
?> 

 

However, now all it does is creates a file with Resource id #3 as the contents.

 

Ideally, i would like it to not only print the mysql table contents to the file, but I dont want to specify each sql field. Is there anyway I can get it to print all the mysql fields to the file?

 

Thanks

[Maq]

If your whole goal is to create a back up, why not use the mysqldump command?

 

$result is just a resource id for your query.  You have to actually tell it to grab the results from the db, just like a regular query that you wan to echo on the screen.

[pcw]

Hi Maq, thanks very much for your reply.

 

How would I go about implementing mysqldump into this script?

 

Thanks

[Maq]

mysqldump

[pcw]

Ok, so now I got:

 

<?php    
$host  = "localhost"; 
$user  = "moveitho_paul"; 
$pass  = "test"; 

$backupDir  = "/home/moveitho/public_html/sitebuilder/backup/"; 
$backupFileName = "sitebuilder.sql"; 

$conn = mysql_connect($host, $user, $pass) or die(mysql_error()); 

$back = $backupDir.$backupFileName; 
$strCommand = sprintf("mysqldump --force --all-databases -h%s -u%s -p%s > %s",$host,$user,$pass,$back) or die (mysql_error()); 

echo "Backupfile: $back"; 
?> 

 

which works, but I only want to make a backup of one table in the database. How would I do this?

 

Thanks

[trq]

From the manual...

 

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

[pcw]

Thanks, done it

 

<?php    
$host  = "localhost"; 
$user  = "****"; 
$pass  = "****"; 

$backupDir  = "/home/moveitho/public_html/sitebuilder/backup/"; 
$backupFileName = "sitebuilder.sql"; 

$conn = mysql_connect($host, $user, $pass) or die(mysql_error()); 

$back = $backupDir.$backupFileName; 
$strCommand = sprintf("mysqldump moveitho_sitebuilder profiles -h%s -u%s -p%s > %s",$host,$user,$pass,$back) or die (mysql_error()); 

echo shell_exec($strCommand); 
echo "Backup successful";
?> 

[trq]

Why are you connecting to mysql using mysql_connect?

 

This should suffice.

<?php    
$host  = "localhost"; 
$user  = "****"; 
$pass  = "****"; 

$backup  = "/home/moveitho/public_html/sitebuilder/backup/sitebuilder.sql"; 

$strCommand = sprintf("mysqldump moveitho_sitebuilder profiles -h%s -u%s -p%s > %s",$host, $user, $pass, $backup);
echo shell_exec($strCommand); 
echo "Backup successful";
?>