Jump to content

[SOLVED] PHP and MySQL backup


pcw

Recommended Posts

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

Link to comment
Share on other sites

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());

?> 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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";
?> 

Link to comment
Share on other sites

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";
?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.