Backup MySQL tables in Laravel without third-party libraries

Question

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 . ")"
         );

    }
}
?>

Answer 1

I created this specifically to clone WordPress subsite tables from one database to another (hence, the $prefix parameter). Leaving the default value of $prefix ('%') will get all tables in the source database.

This has been tested with Laravel 9.x.

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;

class CloneService
{
    public function clone(string $sourceDb, string $destDb, string $prefix): void
    {
        $tables = $this->getTables($sourceDb, $prefix);

        if ($tables->count() > 0) {
            $tables->each(function ($table) use ($sourceDb, $destDb) {
                $success = DB::statement("CREATE TABLE {$destDb}.{$table} LIKE {$sourceDb}.{$table};");

                if ($success) {
                    $this->insertData($sourceDb, $destDb, $table);
                }
            });
        }
    }

    public function getTables(string $dbName, string $prefix = '%'): Collection
    {
        $tables = collect();

        // Escape underscores
        $prefix = str_replace('_', '\_', $prefix);
        collect(DB::select("SHOW TABLES FROM {$dbName} LIKE '{$prefix}%';"))
            ->each(function ($result) use (&$tables) {
                // Convert the stdClass to an array, and get the first element
                $table = current((array)$result);
                $tables->push($table);
            });

        return $tables;
    }

    protected function insertData(string $sourceDb, string $destDb, string $table): void
    {
        $tableData = DB::select(DB::raw("SELECT * FROM {$sourceDb}.{$table};"));

        collect($tableData)->each(function ($row) use ($destDb, $table) {
            $rowData = get_object_vars($row);
            // Create a comma-separated string of the columns
            $columns = implode(',', array_keys($rowData));
            $values = array_values($rowData);
            // Create a comma-separated string of "?'s"
            $prep = implode(',', array_fill(0, count($values), '?'));

            $query = "INSERT INTO {$destDb}.{$table} ({$columns}) VALUES ({$prep})";

            DB::insert($query, $values);
        });
    }
}

Answer 2

I refactored @Igor Ilic's answer to be laravel compatible and improved it a little bit, I hope it is useful :)

It is well tested with laravel 9

function ExportDatabase(array $tablesToBackup = null, string $backupFilename = null): string
{
    $targetTables = [];
    $newLine = "\n";

    if ($tablesToBackup == null)
    {
        $queryTables = DB::select(DB::raw('SHOW TABLES'));
        foreach ($queryTables as $table)
        {
            $targetTables[] = $table->Tables_in_my_database;
        }
    }
    else
    {
        foreach ($tablesToBackup as $table)
        {
            $targetTables[] = $table;
        }
    }

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

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

            $valuesQuery = "(";
            foreach ($row as $key => $value)
            {
                $valuesQuery .= "'$value'" . ", ";
            }

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

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

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

        $content .= $newLine;
    }

    $content = trim($content);

    if (is_null($backupFilename))
    {
        return $content;
    }

    $dbBackupFile = storage_path('backups/database/');
    if (!File::exists($dbBackupFile))
    {
        File::makeDirectory($dbBackupFile, 0755, true);
    }

    $dbBackupFile .= "{$backupFilename}.sql";

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

    return $content;
}

Answer 3

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 = "\r\n";

        $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 = "\r\n" . $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 = "\r\n" . $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 = "\r\n";

        $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(count($tableData) > 1)
                        $firstQueryPart .= $newLine;
                }

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

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

                if( (($cnt+1) % 100 == 0 && $cnt != 0) || $cnt+1 == count($tableData))
                    $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 = "\r\n" . $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 = "\r\n" . $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

Answer 4

Write a

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

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

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

DONE :))