Backup MySQL tables in Laravel without third-party libraries

I new to Laravel. I've been trying to create a controller that backups tables in the form of backup_date_.sql format without using any third-party library at all, but I'm getting frustrated. I've searched and I found some code examples. I've tried to use them within my BackupsController, but things are getting more and more difficult. Any help is really appreciated. This is my code, Thanks in advance.

<?php

public function query($data, $mode = PDO::FETCH_ASSOC)
{
$pdo = DB::connection()->getPdo();
$stmt = $pdo->query($data);
$results = $stmt->fetchAll($mode);
// $results = $stmt->fetch($mode);
return $results;
}

public function backup(Request $request)
{


if ($request->all()) {

$output = '';

foreach (request('table') as $table) {

$show_table_query = $this->query("SHOW CREATE TABLE " . stripslashes($table) . "");


foreach ($show_table_query as $show_table_row)
{
array_shift($show_table_row);

$output .= implode(", ", $show_table_row);

}

$single_result = DB::select('select * from ' . stripslashes($table));


foreach ($single_result as $key => $value)
{
$value = array_map(function($obj) {
return (array) $obj;
}, $single_result);


$keys = array_keys($value[$key]);
$val = array_values($value[$key]);
$get_keys = array_shift($keys);
$get_values = array_shift($val);

$table_column = implode(",", $keys);
// $table_value ="'" . implode("','", $val) . "'n";
$table_value ="'" . implode("','", $val) . "'";

$output .= DB::insert(
"INSERT INTO " . stripslashes($table) . "("
. $table_column . ") VALUES(" . $table_value . ")"
);

}
}
?>


2 Answers
2

This is a function I found and later modified to export my databases including all the data and stored procedures and functions if any exists in the database. The code was written for a codeigniter application but you can easily convert it to laravel.

Codeigniter version:

<?php
if(!function_exists("export_database")){
function export_database($bkpFileName = null){

$ci =& get_instance();
$targetTables = ;
$newLine = "rn";

$queryTables = $ci->db->query('SHOW TABLES');

foreach($queryTables->result() as $table){
$targetTables = $table->Tables_in_my_db;
}

foreach($targetTables as $table){
$tableData = $ci->db->query('SELECT * FROM '.$table);
$res = $ci->db->query('SHOW CREATE TABLE '.$table);

$cnt = 0;
$content = (!isset($content) ? '' : $content) . $res->row_array()["Create Table"].";" . $newLine . $newLine;
foreach($tableData->result_array() as $row){
$subContent = "";
$firstQueryPart = "";
if($cnt == 0 || $cnt % 100 == 0){
$firstQueryPart .= "INSERT INTO {$table} VALUES ";
if($tableData->num_rows() > 1)
$firstQueryPart .= $newLine;
}

$valuesQuery = "(";
foreach($row as $key => $value){
$valuesQuery .= $ci->db->escape($value) . ", ";
}

$subContent = $firstQueryPart . rtrim($valuesQuery, ", ") . ")";

if( (($cnt+1) % 100 == 0 && $cnt != 0) || $cnt+1 == $tableData->num_rows())
$subContent .= ";" . $newLine;
else
$subContent .= ",";

$content .= $subContent;
$cnt++;
}
$content .= $newLine;
}

$content = trim($content);

//check for stored procedures
$storedProcedures = $ci->db->query("SHOW PROCEDURE STATUS WHERE Db = '{$ci->db->database}'");
if($storedProcedures->num_rows() > 0){
foreach($storedProcedures->result() as $procedure){
$data = $ci->db->query("SHOW CREATE PROCEDURE {$procedure->Name}");
if($data->num_rows() > 0){
$dropProcedureSQL = "DROP PROCEDURE IF EXISTS {$procedure->Name};";
$sqlQuery = $data->row_array()["Create Procedure"];
$sqlQuery = preg_replace("/CREATE DEFINER=.+? PROCEDURE/", "CREATE PROCEDURE IF NOT EXISTS", $sqlQuery);
$sqlQuery = "rn" . $sqlQuery . "//";
$content .= $newLine . $newLine . $dropProcedureSQL . $sqlQuery ;
}
}
}

//check for functions
$functions = $ci->db->query("SHOW FUNCTION STATUS WHERE Db = '{$ci->db->database}';");
if($functions->num_rows() > 0){
foreach($functions->result() as $function){
$data = $ci->db->query("SHOW CREATE FUNCTION {$function->Name}");
if($data->num_rows() > 0){
$dropFunctionSQL = "DROP function IF EXISTS {$function->Name};";
$sqlQuery = $data->row_array()["Create Function"];
$sqlQuery = preg_replace("/CREATE DEFINER=.+? FUNCTION/", "CREATE FUNCTION IF NOT EXISTS", $sqlQuery);
$sqlQuery = "rn" . $sqlQuery . "//";
$content .= $newLine . $newLine . $dropFunctionSQL . $sqlQuery ;
}
}
}

$dbBackupFile = FCPATH . BKP_FILE_DIR;
if(is_null($bkpFileName))
$dbBackupFile .= "{$ci->db->database}.sql";
else
$dbBackupFile .= "{$bkpFileName}.sql";

$handle = fopen($dbBackupFile, "w+");
fwrite($handle, $content);
fclose($handle);

return $dbBackupFile;
}
}


Laravel version:

<?php
if(!function_exists("export_database")){
function export_database($bkpFileName = null){

$ci =& get_instance();
$targetTables = ;
$newLine = "rn";

$queryTables = DB::select(DB::raw('SHOW TABLES');

foreach($queryTables->result() as $table){
$targetTables = $table->Tables_in_my_database;
}

foreach($targetTables as $table){
$tableData = DB::select(DB::raw('SELECT * FROM '.$table);
$res = DB::select(DB::raw('SHOW CREATE TABLE '.$table);

$cnt = 0;
$content = (!isset($content) ? '' : $content) . $res->row_array()["Create Table"].";" . $newLine . $newLine;
foreach($tableData as $row){
$subContent = "";
$firstQueryPart = "";
if($cnt == 0 || $cnt % 100 == 0){
$firstQueryPart .= "INSERT INTO {$table} VALUES ";
if($tableData->count() > 1)
$firstQueryPart .= $newLine;
}

$valuesQuery = "(";
foreach($row as $key => $value){
$valuesQuery .= $ci->db->escape($value) . ", ";
}

$subContent = $firstQueryPart . rtrim($valuesQuery, ", ") . ")";

if( (($cnt+1) % 100 == 0 && $cnt != 0) || $cnt+1 == $tableData->count())
$subContent .= ";" . $newLine;
else
$subContent .= ",";

$content .= $subContent;
$cnt++;
}
$content .= $newLine;
}

$content = trim($content);

//check for stored procedures
$storedProcedures = DB::select(DB::raw("SHOW PROCEDURE STATUS WHERE Db = '{$ci->db->database}'");
if($storedProcedures->count() > 0){
foreach($storedProcedures->result() as $procedure){
$data = DB::select(DB::raw("SHOW CREATE PROCEDURE {$procedure->Name}");
if($data->count() > 0){
$dropProcedureSQL = "DROP PROCEDURE IF EXISTS {$procedure->Name};";
$sqlQuery = $data->row_array()["Create Procedure"];
$sqlQuery = preg_replace("/CREATE DEFINER=.+? PROCEDURE/", "CREATE PROCEDURE IF NOT EXISTS", $sqlQuery);
$sqlQuery = "rn" . $sqlQuery . "//";
$content .= $newLine . $newLine . $dropProcedureSQL . $sqlQuery ;
}
}
}

//check for functions
$functions = DB::select(DB::raw("SHOW FUNCTION STATUS WHERE Db = '{$ci->db->database}';");
if($functions->count() > 0){
foreach($functions->result() as $function){
$data = DB::select(DB::raw("SHOW CREATE FUNCTION {$function->Name}");
if($data->count() > 0){
$dropFunctionSQL = "DROP function IF EXISTS {$function->Name};";
$sqlQuery = $data->row_array()["Create Function"];
$sqlQuery = preg_replace("/CREATE DEFINER=.+? FUNCTION/", "CREATE FUNCTION IF NOT EXISTS", $sqlQuery);
$sqlQuery = "rn" . $sqlQuery . "//";
$content .= $newLine . $newLine . $dropFunctionSQL . $sqlQuery ;
}
}
}

/*$dbBackupFile = FCPATH . BKP_FILE_DIR;
if(is_null($bkpFileName))
$dbBackupFile .= "{$ci->db->database}.sql";
else
$dbBackupFile .= "{$bkpFileName}.sql";

$handle = fopen($dbBackupFile, "w+");
fwrite($handle, $content);
fclose($handle);*/

return $content;
}
}


Note:
I have tried my best to convert the above code from codeigniter to laravel. But since I don't have running instance of laravel to test it out I'm not sure it will work

Write a

command https://laravel.com/docs/5.6/artisan#writing-commands

with SSH mysqldump -uUSERNAME -p DATABASE > backup.sql and

mysqldump -uUSERNAME -p DATABASE > backup.sql

schedule https://laravel.com/docs/5.6/scheduling and

DONE :))

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.