Backup Multitenant DBS

[rylxes]

is there a way to backup multi tenant databases, keeping in mind that there is only one database connection

[atymic]

It depends how your environment is configured.
Are there multiple databases sharing one connection, or are all tables in one database?

Can you post your config?

[shashankj99]

atymic... I too faced the similar problem... I have multiple databases sharing one connection...

[cvieiraeduardo]

Hey guys.
So, I had the same problem in the past and to solve this I made this code:

I have a table with the list of all my clients in a "main" bank with the client name and the database name of it.

$company = (new Company())->all();

So I make a foreach with each client and initiate the connection with his bank and I make the call to the artisan to run the backup: run for this client.

foreach($company as $comp){

           \Config::set('database.connections.main.database',$comp->data_base); 

              //$comp-data_base is name of my client database.

           \DB::reconnect('main'); 

             // name of my connection on config/database.php**

            $this->call('backup:run');

}

I hope this helps.

[drbyte]

For Hyn\MultiTenant 5.4, to do backups of the entire app and all registered tenant databases I created the following custom command which calls a custom BackupJobFactory which overrides the default factory which only does one database, and instead calls a custom TenantDbSelector which loops through and processes all the tenant dbs.

Feel free to use it for inspiration for your own situation.

namespace App\Console\Commands;

use App\Services\BackupJobFactory;
use Exception;
use Spatie\Backup\Commands\BackupCommand as SpatieBackupCommand;
use Spatie\Backup\Events\BackupHasFailed;

class BackupCommand extends SpatieBackupCommand
{

    protected $signature = 'backup:tenants {--disable-notifications}';

    public function handle()
    {
        consoleOutput()->comment('Starting backup...');

        $disableNotifications = $this->option('disable-notifications');

        try {
            $backupJob = BackupJobFactory::createFromArray(config('backup'));

            if ($disableNotifications) {
                $backupJob->disableNotifications();
            }

            $backupJob->run();

            consoleOutput()->comment('Backup completed!');

        } catch (Exception $exception) {
            consoleOutput()->error("Backup failed because: {$exception->getMessage()}.");

            if (! $disableNotifications) {
                event(new BackupHasFailed($exception));
            }

            return 1;
        }
    }
}

<?php

namespace App\Services;

use Spatie\Backup\BackupDestination\BackupDestinationFactory;
use Spatie\Backup\Tasks\Backup\BackupJob;
use Spatie\Backup\Tasks\Backup\BackupJobFactory as SpatieBackupJobFactory;

class BackupJobFactory extends SpatieBackupJobFactory
{
    public static function createFromArray(array $config): BackupJob
    {
        return (new BackupJob())
            ->setFileSelection(static::createFileSelection($config['backup']['source']['files']))
            ->setDbDumpers(
                static::createDbDumpers($config['backup']['source']['databases'])
                ->merge(TenantBackupDbSelector::getTenantDatabaseConnections())
            )
            ->setBackupDestinations(BackupDestinationFactory::createFromArray($config['backup']));
    }
}

<?php

namespace App\Services;

use Hyn\Tenancy\Models\Website;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Spatie\Backup\Tasks\Backup\DbDumperFactory;
use Spatie\DbDumper\Databases\MySql;
use Spatie\DbDumper\DbDumper;

class TenantBackupDbSelector extends DbDumperFactory
{
    public static function getTenantDatabaseConnections(): Collection
    {
        $websites = Website::all();

        return $websites->map(function ($tenant) {
            return self::createConnection($tenant->uuid);
        });
    }

    public static function createConnection(string $tenantDbName): DbDumper
    {
        $dbConfig = config('database.connections.' . config('tenancy.db.system-connection-name', 'system'));

        $dbDumper = static::forDriver($dbConfig['driver'])
            ->setHost(Arr::first(Arr::wrap($dbConfig['host'] ?? '')))
            ->setDbName($tenantDbName)
            ->setUserName($dbConfig['username'] ?? '')
            ->setPassword($dbConfig['password'] ?? '');
        if ($dbDumper instanceof MySql) {
            $dbDumper->setDefaultCharacterSet($dbConfig['charset'] ?? '');
        }
        if (isset($dbConfig['port'])) {
            $dbDumper = $dbDumper->setPort($dbConfig['port']);
        }
        if (isset($dbConfig['dump'])) {
            $dbDumper = static::processExtraDumpParameters($dbConfig['dump'], $dbDumper);
        }

        return $dbDumper;
    }
}

I also enabled encryption using the approach suggested by @skollro here: https://simonkollross.de/posts/creating-encrypted-backups-of-laravel-apps
(Granted, even on L5.8.28 I had to register the event in my EventServiceProvider, but maybe that's because I had other events in there I had to register already. I'll investigate that at some point.)

[spatie-bot]

Dear contributor,

because this issue seems to be inactive for quite some time now, I've automatically closed it. If you feel this issue deserves some attention from my human colleagues feel free to reopen it.

[lukasreusch]

@drbyte , thanks for your code. It helped me a lot.
I modified TenantBackupDbSelector slightly to use it with tenancyforlaravel.com.

<?php

namespace App\Services;

use App\Models\Tenant;
use Hyn\Tenancy\Models\Website;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Spatie\Backup\Tasks\Backup\DbDumperFactory;
use Spatie\DbDumper\Databases\MySql;
use Spatie\DbDumper\DbDumper;

class TenantBackupDbSelector extends DbDumperFactory
{
    public static function getTenantDatabaseConnections(): Collection
    {
        $tenants = Tenant::all();
        $dbConfig = config('database.connections.mysql');
        $dbPrefix = config('tenancy.database.prefix');

        return $tenants->map(function ($tenant) use ($dbConfig, $dbPrefix) {

            $dbDumper = static::forDriver($dbConfig['driver'] ?? 'mysql')
                ->setHost(Arr::first(Arr::wrap($dbConfig['host'] ?? '')))
                ->setDbName("$dbPrefix{$tenant->id}")
                ->setUserName($dbConfig['username'] ?? '')
                ->setPassword($dbConfig['password'] ?? '');
        
            if ($dbDumper instanceof MySql) {
                $dbDumper->setDefaultCharacterSet($dbConfig['charset'] ?? '');
            }
            if (isset($dbConfig['port'])) {
                $dbDumper = $dbDumper->setPort($dbConfig['port']);
            }
            if (isset($dbConfig['dump'])) {
                $dbDumper = static::processExtraDumpParameters($dbConfig['dump'], $dbDumper);
            }

            return $dbDumper;
        });
    }
}

[bsormagec]

@drbyte , thanks for your code. It helped me a lot. I modified TenantBackupDbSelector slightly to use it with tenancyforlaravel.com.

<?php

namespace App\Services;

use App\Models\Tenant;
use Hyn\Tenancy\Models\Website;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Spatie\Backup\Tasks\Backup\DbDumperFactory;
use Spatie\DbDumper\Databases\MySql;
use Spatie\DbDumper\DbDumper;

class TenantBackupDbSelector extends DbDumperFactory
{
    public static function getTenantDatabaseConnections(): Collection
    {
        $tenants = Tenant::all();
        $dbConfig = config('database.connections.mysql');
        $dbPrefix = config('tenancy.database.prefix');

        return $tenants->map(function ($tenant) use ($dbConfig, $dbPrefix) {

            $dbDumper = static::forDriver($dbConfig['driver'] ?? 'mysql')
                ->setHost(Arr::first(Arr::wrap($dbConfig['host'] ?? '')))
                ->setDbName("$dbPrefix{$tenant->id}")
                ->setUserName($dbConfig['username'] ?? '')
                ->setPassword($dbConfig['password'] ?? '');
        
            if ($dbDumper instanceof MySql) {
                $dbDumper->setDefaultCharacterSet($dbConfig['charset'] ?? '');
            }
            if (isset($dbConfig['port'])) {
                $dbDumper = $dbDumper->setPort($dbConfig['port']);
            }
            if (isset($dbConfig['dump'])) {
                $dbDumper = static::processExtraDumpParameters($dbConfig['dump'], $dbDumper);
            }

            return $dbDumper;
        });
    }
}

That's lifesaver bro thanks!

[nidhalkratos]

@drbyte , thanks for your code. It helped me a lot. I modified TenantBackupDbSelector slightly to use it with tenancyforlaravel.com.

<?php

namespace App\Services;

use App\Models\Tenant;
use Hyn\Tenancy\Models\Website;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Spatie\Backup\Tasks\Backup\DbDumperFactory;
use Spatie\DbDumper\Databases\MySql;
use Spatie\DbDumper\DbDumper;

class TenantBackupDbSelector extends DbDumperFactory
{
    public static function getTenantDatabaseConnections(): Collection
    {
        $tenants = Tenant::all();
        $dbConfig = config('database.connections.mysql');
        $dbPrefix = config('tenancy.database.prefix');

        return $tenants->map(function ($tenant) use ($dbConfig, $dbPrefix) {

            $dbDumper = static::forDriver($dbConfig['driver'] ?? 'mysql')
                ->setHost(Arr::first(Arr::wrap($dbConfig['host'] ?? '')))
                ->setDbName("$dbPrefix{$tenant->id}")
                ->setUserName($dbConfig['username'] ?? '')
                ->setPassword($dbConfig['password'] ?? '');
        
            if ($dbDumper instanceof MySql) {
                $dbDumper->setDefaultCharacterSet($dbConfig['charset'] ?? '');
            }
            if (isset($dbConfig['port'])) {
                $dbDumper = $dbDumper->setPort($dbConfig['port']);
            }
            if (isset($dbConfig['dump'])) {
                $dbDumper = static::processExtraDumpParameters($dbConfig['dump'], $dbDumper);
            }

            return $dbDumper;
        });
    }
}

That's lifesaver bro thanks!

@bsormagec
I noticed that laravelfortenancy also creates MySQL users who have permissions on their tenant databases (With their usernames and passwords).
Did you manage to backup those MySQL users too or only the databases?

[bsormagec]

@drbyte , thanks for your code. It helped me a lot. I modified TenantBackupDbSelector slightly to use it with tenancyforlaravel.com.

namespace App\Services;

use App\Models\Tenant;

use Hyn\Tenancy\Models\Website;

use Illuminate\Support\Arr;

use Illuminate\Support\Collection;

use Spatie\Backup\Tasks\Backup\DbDumperFactory;

use Spatie\DbDumper\Databases\MySql;

use Spatie\DbDumper\DbDumper;

class TenantBackupDbSelector extends DbDumperFactory

{

public static function getTenantDatabaseConnections(): Collection

{

    $tenants = Tenant::all();

    $dbConfig = config('database.connections.mysql');

    $dbPrefix = config('tenancy.database.prefix');

    return $tenants->map(function ($tenant) use ($dbConfig, $dbPrefix) {

        $dbDumper = static::forDriver($dbConfig['driver'] ?? 'mysql')

            ->setHost(Arr::first(Arr::wrap($dbConfig['host'] ?? '')))

            ->setDbName("$dbPrefix{$tenant->id}")

            ->setUserName($dbConfig['username'] ?? '')

            ->setPassword($dbConfig['password'] ?? '');

        if ($dbDumper instanceof MySql) {

            $dbDumper->setDefaultCharacterSet($dbConfig['charset'] ?? '');

        }

        if (isset($dbConfig['port'])) {

            $dbDumper = $dbDumper->setPort($dbConfig['port']);

        }

        if (isset($dbConfig['dump'])) {

            $dbDumper = static::processExtraDumpParameters($dbConfig['dump'], $dbDumper);

        }

        return $dbDumper;

    });

}

}

That's lifesaver bro thanks!

@bsormagec

I noticed that laravelfortenancy also creates MySQL users who have permissions on their tenant databases (With their usernames and passwords).

Did you manage to backup those MySQL users too or only the databases?

I am just doing batch db backup job for whole tenants not users.