Backing up entire mysql database using php

Question

I have this code that I found but I have a problem trying to get it to work... It says that there is no file and directory, what does that mean and how can I fix it? Also how do I adapt the code to backup the whole database and not just the table:

<?php
    $servername = ""; //this is the local server name 
    $username   = ""; // this is mysql username for my database
    $password   = ""; // this is the database password
    $database   = ""; //this is the database name

    $conn = new mysqli($servername, $username, $password, $database); //this will create a connection to the database

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $table_name = "activities";
   $backup_file  = "/tmp/activities.sql";
   $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";

   mysql_select_db('');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not take data backup: ' . mysql_error());
   }

   echo "Backedup  data successfully\n";

   mysql_close($conn);
?>

Updated code:

    $conn = new mysqli($servername, $username, $password, $database); //this will create a connection to the database

   if(! $conn ) {
      die('Could not connect: ' . mysqli_error());
   }

   $table_name = "activities";
   $backup_file  = "/tmp/activities.sql";
   $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";

   mysqli_select_db('');
   $retval = mysqli_query( $sql, $conn );

   if(! $retval ) {
      die('Could not take data backup: ' . mysqli_error());
   }

   echo "Backedup  data successfully\n";

   mysqli_close($conn);
?>

It means that you don't have `activities.sql` file in `tmp` folder. — Drudge Rajen, Mar 26 '16 at 15:11
you're mixing `mysql_` with `mysqli_` here, can't do that. That's why it's not working. — Funk Forty Niner, Mar 26 '16 at 15:18
Consult the duplicate question link your question was closed with. Answers are in there. If you still can't fix it, I'll help you later, but only if you help yourself ;-) No better way to learn. — Funk Forty Niner, Mar 26 '16 at 15:23
@Fred-ii- Ive changed all the mysqls to mysqli but now it wont show me any of the errors... — SGiga, Mar 26 '16 at 15:37
please update your question with the code you are now using BUT... placed underneath your original code. Do NOT overwrite what you were using and I'll have a look at it. — Funk Forty Niner, Mar 26 '16 at 15:38
I reopened the question and posted an answer in order to better illustrate, as it would have been too long here in comments. Read it over carefully. @SGiga — Funk Forty Niner, Mar 26 '16 at 15:46

score 1 · Accepted Answer · answered Mar 26 '16 at 15:45

You don't need mysqli_select_db(''); you already declared your database in the 4th parameter.

Then ( $sql, $conn ); the connection comes first ( $conn, $sql );.

Then mysqli_error() requires a connection paramter mysqli_error($conn).

Here's a rewrite and do fill in the 4 parameters respectively for the 4 variables for the connection.

<?php 

$servername = ""; //this is the local server name 
$username   = ""; // this is mysql username for my database
$password   = ""; // this is the database password
$database   = ""; //this is the database name

$conn = new mysqli($servername, $username, $password, $database); //this will create a connection to the database

   if(! $conn ) {
      die('Could not connect: ' . mysqli_error($conn));
   }

   $table_name = "activities";
   $backup_file  = "/tmp/activities.sql";
   $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";

   $retval = mysqli_query( $conn, $sql );

   if(! $retval ) {
      die('Could not take data backup: ' . mysqli_error($conn));
   }

else {
   echo "Backed up data successfully\n";
}

   mysqli_close($conn);
?>

If you have any further problems, see what mysqli_error($conn) throws you and use error reporting should the path to the file not be correct, so make sure PHP can read your file and that the folder/file have the right permissions set.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

Thank you so much for this!! I do still have a problem though... It says access is denied for user. Is that because of the code or the host Im using? — SGiga, Mar 26 '16 at 15:49
@SGiga You're welcome. Make sure that permissions in db is given to the user (being you) to perform that action. Also make sure the folder and file have the right permissions to read/write as stated in my answer. — Funk Forty Niner, Mar 26 '16 at 15:51

Backing up entire mysql database using php

1 Answers1