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, Oct 19 '18 at 16:17
open the browser, go to www.google.com, search mysqldump and you will find tons of results. — Lelio Faieta, Oct 19 '18 at 16:19
or open the [mysql manual](https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html) — Lelio Faieta, Oct 19 '18 at 16:20
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, Oct 19 '18 at 16:22
Why using php for that purpose? Can't you use MySQL Workbenches or any other tool to administrate your DB? — Cid, Oct 19 '18 at 16:25
@Cid I have a drop down menu where I would choose what table to backup — loot verge, Oct 19 '18 at 16:26
@Cid yes I know but I really need to backup a specific table in my local site for convenience purposes — loot verge, Oct 19 '18 at 16:29

Ashly Taylor · Accepted Answer · 2018-10-19T16:31:39.223

2

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 '18 at 16:31

answered Oct 19 '18 at 16:22

Ashly Taylor

217
1
7

how can I execute this in a php script? – loot verge Oct 19 '18 at 16:23
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. – Ashly Taylor Oct 19 '18 at 16:29
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? – loot verge Oct 19 '18 at 16:30
I update the main answer with exec() example. Please have a look – Ashly Taylor Oct 19 '18 at 16:32
I used this "* exec("mysqldump --user=sa --password=micromax --host=localhost ".$dbname." > ".$filename."");*" the file is generated but there is no data – loot verge Oct 20 '18 at 05:18

score 1 · Answer 2 · answered Oct 19 '18 at 16:24

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 '18 at 16:24

Marco

335
2
9

oh so if I want to backup the whole database I just need to do this **exec(mysqldump -u username -p demo);**? – loot verge Oct 19 '18 at 16:27
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. – Marco Oct 19 '18 at 16:31
Oh ok, last question where do the file will be saved? and Can I set a specific path? – loot verge Oct 19 '18 at 16:33
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 – Marco Oct 19 '18 at 16:43
I used this "* exec("mysqldump --user=sa --password=micromax --host=localhost ".$dbname." > ".$filename."");*" the file is generated but there is no data – loot verge Oct 20 '18 at 05:02

How to backup specific table using PHP?

2 Answers2