Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
systems:yii2:database_backup [2018/02/23 12:04] smayr created |
systems:yii2:database_backup [2018/03/16 12:45] (current) smayr |
||
---|---|---|---|
Line 110: | Line 110: | ||
function actionBackupDatabaseLarge($tables = ' | function actionBackupDatabaseLarge($tables = ' | ||
{ | { | ||
+ | // | ||
+ | ini_set(' | ||
+ | | ||
Yii:: | Yii:: | ||
+ | | ||
+ | $strReturn = ""; | ||
| | ||
$db = Yii:: | $db = Yii:: | ||
$databaseName = $this-> | $databaseName = $this-> | ||
+ | | ||
// Set the default file name | // Set the default file name | ||
$fileName = $databaseName . ' | $fileName = $databaseName . ' | ||
Line 120: | Line 125: | ||
// Serve the file as a download | // Serve the file as a download | ||
header(" | header(" | ||
- | header('Content-Disposition: | + | header("Content-Disposition: |
+ | // Disable caching - HTTP 1.1 | ||
+ | header(" | ||
+ | // Disable caching - HTTP 1.0 | ||
+ | header(" | ||
+ | // Disable caching - Proxies | ||
+ | header(" | ||
| | ||
- | // Do a short header | + | // Do a short SQL header |
- | | + | |
- | | + | |
+ | |||
+ | // | ||
+ | // Tables & Views | ||
+ | // | ||
if ($tables == ' | if ($tables == ' | ||
$tables = array(); | $tables = array(); | ||
$result = $db-> | $result = $db-> | ||
foreach($result as $resultKey => $resultValue) { | foreach($result as $resultKey => $resultValue) { | ||
- | $tables[] = $resultValue['Tables_in_'.$databaseName]; | + | $tables[] = $resultValue["Tables_in_{$databaseName}"]; |
} | } | ||
- | //echo " | + | //$strReturn .= " |
} else { | } else { | ||
$tables = is_array($tables) ? $tables : explode(',', | $tables = is_array($tables) ? $tables : explode(',', | ||
} | } | ||
+ | | ||
// Run through all the tables | // Run through all the tables | ||
foreach ($tables as $table) { | foreach ($tables as $table) { | ||
- | $tableCountData = $db-> | + | |
- | $totalRecs = $tableCountData[0][' | + | |
- | //echo " | + | $totalRecs = $tableCountData[0][' |
+ | //$strReturn .= " | ||
+ | } | ||
| | ||
// SQL CREATE code | // SQL CREATE code | ||
- | | + | |
- | $createTableResult = $db-> | + | |
- | $createTableEntry = current($createTableResult); | + | |
- | | + | try { |
+ | | ||
+ | $createTableEntry = current($createTableResult); | ||
+ | if (!empty($createTableEntry[' | ||
+ | $strReturn .= " | ||
+ | } | ||
+ | } catch (\Exception $e) { | ||
+ | //throw new \Exception($e-> | ||
+ | $strReturn .= " | ||
+ | } | ||
+ | |||
+ | // Views | ||
+ | try { | ||
+ | $createViewResult = $db-> | ||
+ | $createViewEntry = current($createViewResult); | ||
+ | if (!empty($createViewEntry[' | ||
+ | $strReturn .= " | ||
+ | } | ||
+ | } catch (\Exception $e) { | ||
+ | //throw new \Exception($e-> | ||
+ | // | ||
+ | } | ||
| | ||
- | // Process table data in chunks to avoid running out of memory | + | |
- | for($startIdx = 0, $chunkSize = 10000; $startIdx < $totalRecs; $startIdx += $chunkSize) { | + | |
- | $tableData = $db-> | + | for($startIdx = 0, $chunkSize = 10000; $startIdx < $totalRecs; $startIdx += $chunkSize) { |
- | + | $tableData = $db-> | |
- | // Output the table data | + | |
- | foreach($tableData as $tableDataIndex | + | // Output the table data |
- | echo 'INSERT INTO `' . $table | + | foreach($tableData as $tableRowIndex |
- | | + | |
- | foreach($tableDataDetails | + | |
- | if(is_null($dataValue)) { | + | |
- | $escapedDataValue | + | if(is_null($fieldValue)) { |
- | } else { | + | $escapedFieldValue |
- | // Convert the encoding | + | } else { |
- | //$escapedDataValue | + | // Convert the encoding |
- | $escapedDataValue | + | //$escapedFieldValue |
- | + | $escapedFieldValue | |
- | // Escape any apostrophes using the datasource of the model. | + | |
- | $escapedDataValue | + | // Escape any apostrophes using the datasource of the model. |
- | //if (stripos($escapedDataValue, ' ') !== false) { | + | $escapedFieldValue |
- | // $escapedDataValue | + | //if (stripos($escapedFieldValue, ' ') !== false) { |
- | //} | + | // $escapedFieldValue |
- | //if (!is_numeric($escapedDataValue)) { | + | //} |
- | // $escapedDataValue | + | //if (!is_numeric($escapedFieldValue)) { |
- | //} | + | // $escapedFieldValue |
- | $escapedDataValue | + | //} |
+ | $escapedFieldValue | ||
+ | } | ||
+ | |||
+ | $tableRow[$fieldName] = $escapedFieldValue; | ||
} | } | ||
- | | + | $strReturn .= implode(',', |
- | $tableDataDetails[$dataKey] | + | |
+ | $strReturn .= " | ||
} | } | ||
- | echo implode(',', | ||
- | | ||
- | echo " | ||
} | } | ||
} | } | ||
| | ||
- | | + | |
} | } | ||
+ | | ||
+ | $strReturn .= " | ||
+ | | ||
+ | // | ||
+ | // Triggers | ||
+ | // | ||
+ | if ($triggers == ' | ||
+ | $triggers = array(); | ||
+ | $result = $db-> | ||
+ | foreach($result as $resultKey => $resultValue) { | ||
+ | $triggers[] = $resultValue[' | ||
+ | } | ||
+ | } else { | ||
+ | $triggers = is_array($triggers) ? $triggers : explode(',', | ||
+ | } | ||
+ | |||
+ | // Run through all the triggers | ||
+ | $strReturn .= " | ||
+ | foreach ($triggers as $trigger) { | ||
+ | $strReturn .= "DROP TRIGGER IF EXISTS `{$trigger}`;"; | ||
+ | // Triggers | ||
+ | $createTriggerResult = $db-> | ||
+ | $createTriggerEntry = current($createTriggerResult); | ||
+ | //if (!empty($createTriggerEntry[' | ||
+ | // $strReturn .= " | ||
+ | //} | ||
+ | if (!empty($createTriggerEntry[' | ||
+ | $strReturn .= " | ||
+ | } | ||
+ | //if (!empty($createTriggerEntry[' | ||
+ | // $strReturn .= " | ||
+ | //} | ||
+ | //if (!empty($createTriggerEntry[' | ||
+ | // $strReturn .= " | ||
+ | //} | ||
+ | //if (!empty($createTriggerEntry[' | ||
+ | // $strReturn .= " | ||
+ | //} | ||
+ | |||
+ | $strReturn .= " | ||
+ | } | ||
+ | | ||
+ | //return $strReturn; | ||
+ | Yii:: | ||
} | } | ||
} | } | ||
Line 202: | Line 285: | ||
[' | [' | ||
' | ' | ||
- | [' | + | [' |
- | [' | + | [' |
+ | [' | ||
+ | ' | ||
+ | ' | ||
+ | ]], | ||
]] : | ]] : | ||
'', | '', |