Backup a mysql database and download as a file

Question

How to backup the mysql database and download it as a .sql file by using PHP Codes

Answer 1

A very simple solution would be something like (first example): http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx

Naturally this will only make a Data dump of the table.

What you could do is use this code:

http://snipplr.com/view/173/mysql-dump/

What this code does is actually gets a description of the table (i.e its structure), creates all the tables and pushes data. pretty much like any other tool does.

Then its just a matter of saving it from string to a file (file_put_contents() for instance or something similar, depending on your preference and need)

Answer 2

mysqldump -u username -p password database > file

Alternatively, phpMyAdmin can do this too with the Export tool.

Answer 3

Use phpmyadmin

Edit:

You can use shell_exec to execute this command

mysqldump -u username -p password database > file

This will generate a dump file,and then redirect user to this generated file.

Answer 4

Do you have phpmyadmin? If so you can export it from there by clicking "Export" at the top (on the selected table/db).

Answer 5

I know its a bit late but hope someone else will find this.

//php file - html code:
    require_once 'connect.php'; //holds database variables with connect details
    require_once 'Admin_DatabaseFiles_Backup.php'; //Include the admin logout script

<form action="" method="post" class="form form">
    <!--<input type="hidden" name="backup" value="1" />-->
    <div class="float_left w200">
        <p>
            <label class="title">Backup database</label>
            <span class="left">
              <input type="checkbox" name="db" value="1" checked="checked"/>
            </span>
        </p>
        <p>
            <label class="title">Backup files</label>
            <span class="left">
                <input type="checkbox" name="files" value="1" checked="checked" />
            </span>
        </p>
    </div>
    <p class="float_left">
        <input type="submit" name="submit" value="Backup" class="button" />
    </p>
</form>

//php file Admin_DatabaseFiles_Backup.php:
<?php


if ($_POST['submit']=="Backup"){

    if ($_POST['db'] == "1"){

        $directory = "DatabaseFileBackups/";
        $dateAndTime = "".date('d-m-Y-H-i-s');
        $fileName = "".$dbname.$dateAndTime.".sql";
        $backupFile = "mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost $dbname > ".$directory.$fileName;

        exec($backupFile,$output);

        if($output == ''){ 
            echo = '<br />Failed To Backup Database!';
        }else{
            echo = '<br />Database Backup Was Successful!';
        }
    }

    if ($_POST['files'] == "1"){

            echo 'Seleceted files';

    }
}


?>

Answer 6

If you have phpMyAdmin you can do it at the Export menu.

If you look for a command-line tool take a look at mysqldump.