How to backup specific table using PHP?

Question

I have a script that backup the whole database. What if I need to backup a specific table of a specific database? For example, inside the database "Demo" I have two(2) tables "User" and "Sales". I have a drop-down that have the list of tables inside the database so that I can choose what table to backup. However I don't know how to backup a specific table. How can I do it?

PHP script for backup whole database:

// Get All Table Names From the Database
$tables = array();
$sql = "SHOW TABLES";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}

$sqlScript = "";
foreach ($tables as $table) {

    // Prepare SQLscript for creating table structure
    $query = "SHOW CREATE TABLE $table";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_row($result);

    $sqlScript .= "\n\n" . $row[1] . ";\n\n";

    $query = "SELECT * FROM $table";
    $result = mysqli_query($conn, $query);

    $columnCount = mysqli_num_fields($result);

    // Prepare SQLscript for dumping data for each table
    for ($i = 0; $i < $columnCount; $i ++) {
        while ($row = mysqli_fetch_row($result)) {
            $sqlScript .= "INSERT INTO $table VALUES(";
            for ($j = 0; $j < $columnCount; $j ++) {
                $row[$j] = $row[$j];

                if (isset($row[$j])) {
                    $sqlScript .= '"' . $row[$j] . '"';
                } else {
                    $sqlScript .= '""';
                }
                if ($j < ($columnCount - 1)) {
                    $sqlScript .= ',';
                }
            }
            $sqlScript .= ");\n";
        }
    }

    $sqlScript .= "\n"; 
}

if(!empty($sqlScript))
{
    // Save the SQL script to a backup file
    $backup_file_name = $dbname . '_backup_' . date("F Y") . '.sql';
    $fileHandler = fopen($backup_file_name, 'w+');
    $number_of_lines = fwrite($fileHandler, $sqlScript);
    fclose($fileHandler); 

    // Download the SQL backup file to the browser
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=' . basename($backup_file_name));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($backup_file_name));
    ob_clean();
    flush();
    readfile($backup_file_name);
    exec('rm ' . $backup_file_name); 
}

@LelioFaieta i am not familiar with mysqldump can you show me an example — loot verge
– loot verge, Commented Oct 19, 2018 at 16:17
open the browser, go to www.google.com, search mysqldump and you will find tons of results. — user2399035
– user2399035, Commented Oct 19, 2018 at 16:19
From what I see it should be easy enough to modify that script to not run SHOW TABLES and use a $_POST or $_GET variable to get the table(s). Have you tried doing that? — apokryfos
– apokryfos, Commented Oct 19, 2018 at 16:22

Ashly Taylor · Accepted Answer · 2018-10-19 16:31:39Z

4

You can try mysqldump

Usage: mysqldump [OPTIONS] database [tables]

i.e.

mysqldump -u username -p db_name table1_name table2_name table3_name > dump.sql

You can use the exec() function to execute an external command.

Note: between shell_exec() and exec(), I would choose the second one, which doesn't return the output to the PHP script -- no need for the PHP script to get the whole SQL dump as a string : you only need it written to a file, and this can be done by the command itself.

That external command will :

be a call to mysqldump, with the right parameters, and redirect the output to a file. For example :

mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql

Which means your PHP code would look like this :

exec('mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql');

Of course, up to you to use the right connection information, replacing the ... with those.

Source

edited Oct 19, 2018 at 16:31

answered Oct 19, 2018 at 16:22

Ashly Taylor

2471 silver badge7 bronze badges

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

5 Comments

loot verge Over a year ago

how can I execute this in a php script?

Ashly Taylor Over a year ago

You can use the exec() function to execute an external command. That external command will : be a call to mysqldump, with the right parameters, and redirect the output to a file. For example : mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql Which means your PHP code would look like this : exec('mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql'); Of course, up to you to use the right connection information, replacing the ... with those.

loot verge Over a year ago

so I do this exec(mysqldump -u username -p demo user sales > mydump.sql); to back-up a specific table and this exec(mysqldump -u username -p demo > mydump.sql); to back-up the whole database?

Ashly Taylor Over a year ago

I update the main answer with exec() example. Please have a look

loot verge Over a year ago

I used this "* exec("mysqldump --user=sa --password=micromax --host=localhost ".$dbname." > ".$filename."");*" the file is generated but there is no data

Marco · Accepted Answer · 2018-10-19 16:24:24Z

1

Is there a specific reason you're writing it to a file custom? Is it for back-up or display purposes.

If it's back up, look at Mysqldump.

mysqldump -u username -p demo user sales > mydump.sql use \ to multi-line.

https://idiallo.com/blog/mysql-dump-table-where has a nice explanation included.

You need to run it in a exec function (so exec('mysqldump ....'); )

answered Oct 19, 2018 at 16:24

Marco

3892 silver badges10 bronze badges

5 Comments

loot verge Over a year ago

oh so if I want to backup the whole database I just need to do this exec(mysqldump -u username -p demo);?

Marco Over a year ago

and > myfile.sql, also don't forget the quotation marks. Yep. There are other ways to back it up, e.g. if you're using WAMP you can use the export function of phpmyadmin.

loot verge Over a year ago

Oh ok, last question where do the file will be saved? and Can I set a specific path?

Marco Over a year ago

Yes, you just change the name to the correct path, so the section after > /path/filename.sql e.g. if save to backup folder in root, use > backup/mydump.sql

loot verge Over a year ago

I used this "* exec("mysqldump --user=sa --password=micromax --host=localhost ".$dbname." > ".$filename."");*" the file is generated but there is no data

Collectives™ on Stack Overflow

How to backup specific table using PHP?

2 Answers 2

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related