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
– Drudge Rajen, Commented Mar 26, 2016 at 15:11
you're mixing mysql_ with mysqli_ here, can't do that. That's why it's not working. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 26, 2016 at 15:18

Funk Forty Niner · Accepted Answer · 2016-03-26 15:45:39Z

1

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.

answered Mar 26, 2016 at 15:45

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SGiga Over a year ago

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?

Funk Forty Niner Over a year ago

@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.

Collectives™ on Stack Overflow

Backing up entire mysql database using php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related