PHP function for exporting a MySQL database to an SQL file.

Without C-panel PHP function for exporting a MySQL database to an SQL file.

17

Arun Kr.
13-Jun-24

This function takes several parameters:

  1. $host: The MySQL host.
  2. $user: The MySQL username.
  3. $pass: The MySQL password.
  4. $name: The name of the database to be exported.
  5. $tables: An optional array of specific tables to export. If not specified, all tables will be exported.
  6. $backup_name: An optional name for the backup file. If not specified, a default name will be generated based on the current date and time.
    public function exportDatabase($host, $user, $pass, $name, $tables = false, $backup_name = false)
    { 
        set_time_limit(3000); 
        $mysqli = new mysqli($host, $user, $pass, $name); 
        $mysqli->select_db($name); 
        $mysqli->query("SET NAMES 'utf8'");
        
        // Get list of tables
        $queryTables = $mysqli->query('SHOW TABLES'); 
        while($row = $queryTables->fetch_row()) { 
            $target_tables[] = $row[0]; 
        }
        
        // Filter tables if specific tables are provided
        if($tables !== false) { 
            $target_tables = array_intersect($target_tables, $tables); 
        } 
        
        // Initial SQL setup
        $content = "-- Database: `$name`\n\n";
        
        foreach($target_tables as $table) {
            if (empty($table)) { 
                continue; 
            }
            
            // Table structure
            $res = $mysqli->query('SHOW CREATE TABLE ' . $table);	
            $tableMLine = $res->fetch_row(); 
            $content .= "-- Table structure for table `$table`\n";
            $content .= $tableMLine[1] . ";\n\n";
            
            // Table data
            $result = $mysqli->query('SELECT * FROM `' . $table . '`');  
            $fields_amount = $result->field_count;  
            
            $content .= "-- Dumping data for table `$table`\n";
            
            while($row = $result->fetch_assoc()) {
                $content .= "INSERT INTO $table VALUES(";
                for($j=0; $j<$fields_amount; $j++) {
                    $row[$j] = $mysqli->real_escape_string($row[$j]);
                    $content .= '"' . $row[$j] . '"';
                    if ($j<($fields_amount-1)) {
                        $content .= ',';
                    }
                }
                $content .= ");\n";
            } 
            
            $content .= "\n\n";
        }
        
        // Set headers for download
        $backup_name = $backup_name ? $backup_name : $name . '__' . date('H-i-s') . '_' . date('d-m-Y') . '.sql';
        header('Content-Type: application/octet-stream');  
        header('Content-Disposition: attachment; filename="' . $backup_name . '"'); 
        
        // Output content
        echo $content; 
        exit;
    }
    

Here's a breakdown of what the function does:

  1. It sets the time limit for script execution to 3000 seconds.
  2. It establishes a MySQL connection using the provided credentials and sets the database.
  3. It retrieves the list of tables in the database.
  4. If specific tables are specified, it filters the list of tables to only include those.
  5. It starts building the SQL content with some initial setup commands.
  6. For each table, it retrieves the table structure and data.
  7. It formats the table creation SQL to include "IF NOT EXISTS" to avoid errors if the table already exists.
  8. It fetches data from each table and constructs INSERT statements for each row.
  9. It adds the necessary SQL commands to set character sets and collations.
  10. It sets up HTTP headers for the browser to download the SQL file.
  11. It outputs the SQL content, forcing a download of the SQL file.
@Since 2024 Arun'Log Powered by Arun Git