Created
May 30, 2022 07:23
-
-
Save mdestafadilah/f25181c6d43f1fd04d03535944aa8ba9 to your computer and use it in GitHub Desktop.
BACKUP DB CI
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
system\database\drivers\mysqli\mysqli_utility.php | |
``` | |
/** | |
* MySQLi Export | |
* | |
* @access private | |
* @param array Preferences | |
* @return mixed | |
* @source https://stackoverflow.com/a/44667873 | |
*/ | |
function _backup($params = array()) | |
{ | |
// Currently unsupported | |
//---return $this->db->display_error('db_unsuported_feature'); | |
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 = mysqli_fetch_field($query->result_id)) | |
{ | |
// Most versions of MySQL store timestamp as a string | |
$is_int[$i] = (in_array( | |
//strtolower(mysqli_field_type($query->result_id, $i)), | |
strtolower($field->type), | |
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 | |
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 | |
$output .= 'INSERT INTO '.$table.' ('.$field_str.') VALUES ('.$val_str.');'.$newline; | |
} | |
$output .= $newline.$newline; | |
} | |
return $output; | |
} | |
``` | |
``` | |
$this->load->helper(array('form', 'url', 'file', 'download')); | |
$this->db = $this->load->database('website', TRUE); // the TRUE paramater tells CI that you'd like to return the database object. | |
$prefs = array( | |
'tables' => array('user'), // Array of tables to backup. | |
'ignore' => array(), // List of tables to omit from the backup | |
'format' => 'zip', // gzip, zip, txt | |
'filename' => 'backup_' . date("Y-m-d-H-i-s") . '.sql', // File name - NEEDED ONLY WITH ZIP FILES | |
'add_drop' => true, // Whether to add DROP TABLE statements to backup file | |
'add_insert' => true, // Whether to add INSERT data to backup file | |
'newline' => "\n", // Newline character used in backup file | |
); | |
$this->load->dbutil($this->db, TRUE); | |
$backup = $this->dbutil->backup($prefs); | |
$db_name = 'backup-on-' . date("Y-m-d-H-i-s") . '.zip'; | |
$backup_path = APPPATH.'/assets/backup/'; // this is the destination directory name | |
if (!file_exists($backup_path)) { | |
mkdir($backup_path, 0755, true); | |
} | |
$save = $backup_path . $db_name; | |
$this->load->helper('file'); | |
write_file($save, $backup); | |
``` |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment