pcw Posted March 18, 2009 Share Posted March 18, 2009 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 Quote Link to comment Share on other sites More sharing options...
Cory94bailly Posted March 18, 2009 Share Posted March 18, 2009 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()); ?> Quote Link to comment Share on other sites More sharing options...
trq Posted March 18, 2009 Share Posted March 18, 2009 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. Quote Link to comment Share on other sites More sharing options...
waynew Posted March 18, 2009 Share Posted March 18, 2009 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? Quote Link to comment Share on other sites More sharing options...
pcw Posted March 19, 2009 Author Share Posted March 19, 2009 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 Quote Link to comment Share on other sites More sharing options...
pcw Posted March 19, 2009 Author Share Posted March 19, 2009 has anyone know how to fix this? Thanks Quote Link to comment Share on other sites More sharing options...
pcw Posted March 19, 2009 Author Share Posted March 19, 2009 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 Quote Link to comment Share on other sites More sharing options...
Maq Posted March 19, 2009 Share Posted March 19, 2009 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. Quote Link to comment Share on other sites More sharing options...
pcw Posted March 19, 2009 Author Share Posted March 19, 2009 Hi Maq, thanks very much for your reply. How would I go about implementing mysqldump into this script? Thanks Quote Link to comment Share on other sites More sharing options...
Maq Posted March 19, 2009 Share Posted March 19, 2009 mysqldump Quote Link to comment Share on other sites More sharing options...
pcw Posted March 19, 2009 Author Share Posted March 19, 2009 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 Quote Link to comment Share on other sites More sharing options...
trq Posted March 19, 2009 Share Posted March 19, 2009 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. Quote Link to comment Share on other sites More sharing options...
pcw Posted March 19, 2009 Author Share Posted March 19, 2009 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"; ?> Quote Link to comment Share on other sites More sharing options...
trq Posted March 19, 2009 Share Posted March 19, 2009 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"; ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.