database backup like phpMyAdmin

[eluser]skunkbad[/eluser]
Maybe I haven't looked in the right place, or just haven't found what I am looking for. I'd like to have complete database backup made in standard sql format. Click a button, download the sql file. Yes, I could do this in phpMyAdmin, but I want the other users (the site owners) to be able to do their own backups.

I saw a solution that uses SELECT into $filename, and wondering if this is the best way to get started.

[eluser]Burak Guzel[/eluser]
You can run shell commands with the PHP shell_exec() function, which you can use to call mysqldump

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

[eluser]eoinmcg[/eluser]
buried deep in the user guide is this little gem:

http://ellislab.com/codeigniter/user-gui...tml#backup

[eluser]skunkbad[/eluser]
[quote author="eoinmcg" date="1277464683"]buried deep in the user guide is this little gem:

http://ellislab.com/codeigniter/user-gui...tml#backup[/quote]

Actually just found that, and it works, but not great. The reason why I say that is that it creates an individual insert for every row of a table, where phpMyAdmin groups rows together for a way smaller backup file. I guess its better nothing...

[eluser]skunkbad[/eluser]
[quote author="skunkbad" date="1277465334"][quote author="eoinmcg" date="1277464683"]buried deep in the user guide is this little gem:

http://ellislab.com/codeigniter/user-gui...tml#backup[/quote]

Actually just found that, and it works, but not great. The reason why I say that is that it creates an individual insert for every row of a table, where phpMyAdmin groups rows together for a way smaller backup file. I guess its better nothing...[/quote]

I ended up fixing the _backup function inside mysql_utility.php, and the inserts are the way I want them:

Code:

// --------------------------------------------------------------------
/**
* MySQL Export
*
* @access    private
* @param    array    Preferences
* @return    mixed
*/
function _backup($params = array())
{
    if (count($params) == 0)
    {
        return FALSE;
    }

    // Extract the prefs for simplicity
    extract($params);

    // Build the output
    $output = '';
    foreach ((array)$tables as $table)
    {
        // Is the table in the "ignore" list?
        if (in_array($table, (array)$ignore, TRUE))
        {
            continue;
        }

        // Get the table schema
        $query = $this->db->query("SHOW CREATE TABLE `".$this->db->database.'`.'.$table);
        
        // No result means the table name was invalid
        if ($query === FALSE)
        {
            continue;
        }
        
        // Write out the table schema
        $output .= '--'.$newline.'-- TABLE STRUCTURE FOR: '.$table.$newline.'--'.$newline.$newline;

        if ($add_drop == TRUE)
        {
            $output .= 'DROP TABLE IF EXISTS '.$table.';'.$newline.$newline;
        }
        
        $i = 0;
        $result = $query->result_array();
        foreach ($result[0] as $val)
        {
            if ($i++ % 2)
            {                    
                $output .= $val.';'.$newline.$newline;
            }
        }
        
        // If inserts are not needed we're done...
        if ($add_insert == FALSE)
        {
            continue;
        }

        // Grab all the data from the current table
        $query = $this->db->query("SELECT * FROM $table");
        
        if ($query->num_rows() == 0)
        {
            continue;
        }
    
        // Fetch the field names and determine if the field is an
        // integer type.  We use this info to decide whether to
        // surround the data with quotes or not
        
        $i = 0;
        $field_str = '';
        $is_int = array();
        while ($field = mysql_fetch_field($query->result_id))
        {
            // Most versions of MySQL store timestamp as a string
            $is_int[$i] = (in_array(
                                    strtolower(mysql_field_type($query->result_id, $i)),
                                    array('tinyint', 'smallint', 'mediumint', 'int', 'bigint'), //, 'timestamp'),
                                    TRUE)
                                    ) ? TRUE : FALSE;
                                    
            // Create a string of field names
            $field_str .= '`'.$field->name.'`, ';
            $i++;
        }
        
        // Trim off the end comma
        $field_str = preg_replace( "/, $/" , "" , $field_str);
        
        
        // Build the insert string
        $x = 0;
        $output .= 'INSERT INTO '.$table.' ('.$field_str.') VALUES'.$newline;
        foreach ($query->result_array() as $row)
        {
            $val_str = '';
        
            $i = 0;
            foreach ($row as $v)
            {
                // Is the value NULL?
                if ($v === NULL)
                {
                    $val_str .= 'NULL';
                }
                else
                {
                    // Escape the data if it's not an integer
                    if ($is_int[$i] == FALSE)
                    {
                        $val_str .= $this->db->escape($v);
                    }
                    else
                    {
                        $val_str .= $v;
                    }                    
                }                    
                
                // Append a comma
                $val_str .= ', ';
                $i++;
            }
            
            // Remove the comma at the end of the string
            $val_str = preg_replace( "/, $/" , "" , $val_str);
                            
            // Build the INSERT string
            if($x != $query->num_rows() - 1)
            {
                $output .= '('.$val_str.'),'.$newline;
            }
            else
            {
                $output .= '('.$val_str.')';
            }
            $x++;
        }
        
        $output .= ';'.$newline.$newline;
    }

    return $output;
}

[eluser]elambiguo[/eluser]
Do not know if it will serve you ....

I use an external application free to manage the database, efficient and simple to use ....

http://www.mysqldumper.net/

[eluser]cahva[/eluser]
Why not use mysqldump command straight from server using exec command? No timeout and memory limit limitations..

EDIT: damn. Somehow missed Burak Guzel advice(as did everybody else seems)

[eluser]skunkbad[/eluser]
[quote author="cahva" date="1277484417"]Why not use mysqldump command straight from server using exec command? No timeout and memory limit limitations..

EDIT: damn. Somehow missed Burak Guzel advice(as did everybody else seems) [/quote]

Not everyone, like shared hosting customers, will have access to exec. I came up with an easy solution, and it is built into CI. I extended the database utility class.

I understand that the CI backup may not work for some larger databases. I've been working on a solution to try to make the backup better.