How to export mysql database in .sql extension using php code or in codeigniter

Question

How Can I download .sql file using php code or codeigniter code!

possible duplicate of [Easy way to export a SQL table without access to the server or phpMyADMIN](http://stackoverflow.com/questions/81934/easy-way-to-export-a-sql-table-without-access-to-the-server-or-phpmyadmin) — ksbg, Apr 23 '15 at 07:53
no it's not duplicate... I don't want table access i want to download all database of mysql using php...just read question and then tell me is it duplicate... — Muhammad Fahad, Apr 23 '15 at 08:59
have you read the very first answer on the page? It tells you how to get a database dump using only php code. — ksbg, Apr 23 '15 at 11:13

score 4 · Answer 1 · edited Mar 25 '17 at 20:37

$this->dbutil->backup()

Permits you to backup your full database or individual tables. The backup data can be compressed in either Zip or Gzip format.

Note: This features is only available for MySQL databases. Note: Due to the limited execution time and memory available to PHP, backing up very large databases may not be possible. If your database is very large you might need to backup directly from your SQL server via the command line, or have your server admin do it for you if you do not have root privileges.

Usage Example

// Load the DB utility class
$this->load->dbutil();

// Backup your entire database and assign it to a variable


$backup =& $this->dbutil->backup(); 

// Load the file helper and write the file to your server

$this->load->helper('file');

write_file('/path/to/mybackup.sql', $backup); 

// Load the download helper and send the file to your desktop

$this->load->helper('download');

force_download('mybackup.sql', $backup);

follw the link

write_file('/path/to/mybackup.sql', $backup); what is path to mybackup.sql? — Muhammad Fahad, Apr 24 '15 at 08:06

score 0 · Answer 2 · answered Dec 24 '20 at 22:33

Here is complete PHP code to download the database backup

<?php

// Database configuration
$host = "localhost";
$username = "db-username";
$password = "db-password";
$database_name = "db-name";

// Get connection object and set the charset
$conn = mysqli_connect($host, $username, $password, $database_name);
$conn->set_charset("utf8");


// 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 = $database_name . '_backup_' . time() . '.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); 
}
?>

Reference: https://phppot.com/php/how-to-backup-mysql-database-using-php/

How to export mysql database in .sql extension using php code or in codeigniter

2 Answers2