= Yii 2 Database Backup =
Add the ''actionBackupDatabase()'' to the ''app\controllers\SiteController.php'' file:
class SiteController extends Controller
{
...
/**
* Get host name or database name from database connection string in
* Yii::$app->db->dsn:
* [dsn] => 'mysql:host=localhost;dbname=acme_mydatabase'
* Usage:
* $db = Yii::$app->getDb();
* $dbName = $this->getDsnAttribute('dbname', $db->dsn);
*/
private function getDsnAttribute($name, $dsn)
{
if (preg_match('/' . $name . '=([^;]*)/', $dsn, $match)) {
return $match[1];
} else {
return null;
}
}
/**
* Dumps the MySQL database that this controller's model is attached to.
* Serve the sql file as a download so that the user can save the backup to their local computer.
*
* @param string $tables Comma separated list of tables to download, or '*' to download them all.
*/
function actionBackupDatabase($tables = '*')
{
$return = '';
$db = Yii::$app->getDb();
$databaseName = $this->getDsnAttribute('dbname', $db->dsn);
// Do a short header
$return .= '-- Database: `' . $databaseName . '`' . "\n";
$return .= '-- Generation time: ' . date('D jS M Y H:i:s') . "\n\n\n";
if ($tables == '*') {
$tables = array();
$result = $db->createCommand('SHOW TABLES')->queryAll();
foreach($result as $resultKey => $resultValue) {
$tables[] = $resultValue['Tables_in_'.$databaseName];
}
} else {
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
// Run through all the tables
foreach ($tables as $table) {
$tableData = $db->createCommand('SELECT * FROM `' . $table . '`')->queryAll();
$return .= 'DROP TABLE IF EXISTS `' . $table . '`;';
$createTableResult = $db->createCommand('SHOW CREATE TABLE `' . $table . '`')->queryAll();
$createTableEntry = current($createTableResult);
$return .= "\n\n" . $createTableEntry['Create Table'] . ";\n\n";
// Output the table data
foreach($tableData as $tableDataIndex => $tableDataDetails) {
$return .= 'INSERT INTO `' . $table . '` VALUES(';
foreach($tableDataDetails as $dataKey => $dataValue) {
if(is_null($dataValue)) {
$escapedDataValue = 'NULL';
} else {
// Convert the encoding
//$escapedDataValue = mb_convert_encoding( $dataValue, "UTF-8", "ISO-8859-1" );
$escapedDataValue = $dataValue; // no char conversion (keep it as UTF-8)
// Escape any apostrophes using the datasource of the model.
$escapedDataValue = str_replace("'", "\'", $escapedDataValue); // escape apostrophes
//if (stripos($escapedDataValue, ' ') !== false) {
// $escapedDataValue = "'" . $escapedDataValue . "'"; // quote string if spaces found
//}
//if (!is_numeric($escapedDataValue)) {
// $escapedDataValue = "'" . $escapedDataValue . "'"; // quote string if non-numeric
//}
$escapedDataValue = "'" . $escapedDataValue . "'"; // quote string for all fields w/o NULL
}
$tableDataDetails[$dataKey] = $escapedDataValue;
}
$return .= implode(',', $tableDataDetails);
$return .= ");\n";
}
$return .= "\n\n\n";
}
// Set the default file name
$fileName = $databaseName . '-backup-' . date('Y-m-d_H-i-s') . '.sql';
// Serve the file as a download
header("Content-Type: text/x-sql");
header('Content-Disposition: attachment; filename="'. $fileName . '"'); // File will be called $fileName
echo $return;
}
/**
* Dumps the MySQL database that this controller's model is attached to.
* This action will serve the sql file as a download so that the user can save the backup to their local computer.
*
* @param string $tables Comma separated list of tables you want to download, or '*' if you want to download them all.
*/
function actionBackupDatabaseLarge($tables = '*')
{
//ini_set('memory_limit','64M'); // limit memory to 64MB
ini_set('memory_limit','-1'); // no memory limit
Yii::$app->response->format = Response::FORMAT_RAW; // Raw for Text output
$strReturn = "";
$db = Yii::$app->getDb();
$databaseName = $this->getDsnAttribute('dbname', $db->dsn);
// Set the default file name
$fileName = $databaseName . '-backup-' . date('Y-m-d_H-i-s') . '.sql';
// Serve the file as a download
header("Content-Type: text/x-sql");
header("Content-Disposition: attachment; filename='{$fileName}'"); // File will be called $fileName
// Disable caching - HTTP 1.1
header("Cache-Control: no-cache, no-store, must-revalidate");
// Disable caching - HTTP 1.0
header("Pragma: no-cache");
// Disable caching - Proxies
header("Expires: 0");
// Do a short SQL header
$strReturn .= "-- Database: `{$databaseName}`\n";
$strReturn .= "-- Generation time: " . date('D jS M Y H:i:s') . "\n\n\n";
//-----------------
// Tables & Views
//-----------------
if ($tables == '*') {
$tables = array();
$result = $db->createCommand('SHOW TABLES')->queryAll();
foreach($result as $resultKey => $resultValue) {
$tables[] = $resultValue["Tables_in_{$databaseName}"];
}
//$strReturn .= "Tables: " . print_r($tables, true); //debug
} else {
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
// Run through all the tables
foreach ($tables as $table) {
if ($is_data_included) {
$tableCountData = $db->createCommand("SELECT COUNT(*) AS total FROM `{$table}`")->queryAll();
$totalRecs = $tableCountData[0]['total'];
//$strReturn .= "Total: " . print_r($tableCountData, true); //debug
}
// SQL CREATE code
$strReturn .= "DROP TABLE IF EXISTS `{$table}`;";
// Tables
try {
$createTableResult = $db->createCommand("SHOW CREATE TABLE `{$table}`")->queryAll();
$createTableEntry = current($createTableResult);
if (!empty($createTableEntry['Create Table'])) {
$strReturn .= "\n\n" . $createTableEntry['Create Table'] . ";\n\n";
}
} catch (\Exception $e) {
//throw new \Exception($e->getMessage() . ":\n" . json_encode($dumped), 0, $e);
$strReturn .= "\n\n-- No table definition for '{$table}'\n\n";
}
// Views
try {
$createViewResult = $db->createCommand("SHOW CREATE VIEW `{$table}`")->queryAll();
$createViewEntry = current($createViewResult);
if (!empty($createViewEntry['Create View'])) {
$strReturn .= "\n\n" . $createViewEntry['Create View'] . ";\n\n";
}
} catch (\Exception $e) {
//throw new \Exception($e->getMessage() . ":\n" . json_encode($dumped), 0, $e);
//$strReturn .= "\n\n-- No view definition for '" . $table . "'\n\n";
}
if ($is_data_included) {
// Process table data in chunks to avoid running out of memory
for($startIdx = 0, $chunkSize = 10000; $startIdx < $totalRecs; $startIdx += $chunkSize) {
$tableData = $db->createCommand("SELECT * FROM `{$table}` LIMIT {$startIdx},{$chunkSize}")->queryAll();
// Output the table data
foreach($tableData as $tableRowIndex => $tableRow) {
$strReturn .= "INSERT INTO `{$table}` VALUES(";
foreach($tableRow as $fieldName => $fieldValue) {
if(is_null($fieldValue)) {
$escapedFieldValue = 'NULL';
} else {
// Convert the encoding
//$escapedFieldValue = mb_convert_encoding( $fieldValue, "UTF-8", "ISO-8859-1" );
$escapedFieldValue = $fieldValue; // no char conversion (keep it as UTF-8)
// Escape any apostrophes using the datasource of the model.
$escapedFieldValue = str_replace("'", "\'", $escapedFieldValue); // escape apostrophes
//if (stripos($escapedFieldValue, ' ') !== false) {
// $escapedFieldValue = "'{$escapedFieldValue}'"; // quote string if spaces found
//}
//if (!is_numeric($escapedFieldValue)) {
// $escapedFieldValue = "'{$escapedFieldValue}'"; // quote string if non-numeric
//}
$escapedFieldValue = "'{$escapedFieldValue}'"; // quote string for all fields without NULL
}
$tableRow[$fieldName] = $escapedFieldValue;
}
$strReturn .= implode(',', $tableRow);
$strReturn .= ");\n";
}
}
}
$strReturn .= "\n\n\n";
}
$strReturn .= "\n\n\n";
//-----------------
// Triggers
//-----------------
if ($triggers == '*') {
$triggers = array();
$result = $db->createCommand('SHOW TRIGGERS')->queryAll();
foreach($result as $resultKey => $resultValue) {
$triggers[] = $resultValue['Trigger'];
}
} else {
$triggers = is_array($triggers) ? $triggers : explode(',', $triggers);
}
// Run through all the triggers
$strReturn .= "\n\n-- Triggers \n\n";
foreach ($triggers as $trigger) {
$strReturn .= "DROP TRIGGER IF EXISTS `{$trigger}`;";
// Triggers
$createTriggerResult = $db->createCommand("SHOW CREATE TRIGGER `{$trigger}`")->queryAll();
$createTriggerEntry = current($createTriggerResult);
//if (!empty($createTriggerEntry['sql_mode'])) {
// $strReturn .= "\n\n" . $createTriggerEntry['sql_mode'] . ";\n\n";
//}
if (!empty($createTriggerEntry['SQL Original Statement'])) {
$strReturn .= "\n\n" . $createTriggerEntry['SQL Original Statement'] . ";\n\n";
}
//if (!empty($createTriggerEntry['character_set_client'])) {
// $strReturn .= "\n\n" . $createTriggerEntry['character_set_client'] . ";\n\n";
//}
//if (!empty($createTriggerEntry['collation_connection'])) {
// $strReturn .= "\n\n" . $createTriggerEntry['collation_connection'] . ";\n\n";
//}
//if (!empty($createTriggerEntry['Database Collation'])) {
// $strReturn .= "\n\n" . $createTriggerEntry['Database Collation'] . ";\n\n";
//}
$strReturn .= "\n\n\n";
}
//return $strReturn;
Yii::$app->response->data = $strReturn;
}
}
Add a call to action BackupDatabase in ''app\views\layouts\main.php'' file:
['class' => 'navbar-nav navbar-right'],
'items' => [
['label' => 'Home', 'url' => ['/site/index']],
// Admin menu only
!Yii::$app->user->isGuest && Yii::$app->user->identity->username === 'admin' ?
['label' => 'Admin', 'url' => ['/site/admin'],
'items' => [
['label' => 'Backup Database', 'url' => ['/site/backup-database']],
['label' => 'Backup Database Large', 'url' => ['/site/backup-database-large']],
['label' => 'Backup Database (Structure)', 'url' => [
'site/backup-database-large',
'is_data_included' => 0
]],
]] :
'',
],
...
]);
NavBar::end();
?>