Database Dumper

README.md

Database Dumper

1. Copying files dumper.php and config.yaml.

2. Seting up config.yaml

3. Setting up cron. For example:

   0 4 * * * cd /var/www/backups && /usr/local/bin/php dumper.php config.yaml

4. Or manual: php dumper.php config.yaml

5. Profit!

Result:

/var/www/backups/Y-m-d_H-i
 - foo
   - table1.sql.gz - structure with data
   - table2.sql.gz - structure with data
   - audits.sql.gz - structure only
   - logs.sql.gz   - structure only
   - ...
 - bar
   - table1.sql.gz - structure with data
   - table2.sql.gz - structure with data
   - ...

config.yaml

name: "production"
host: "127.0.0.1"
port: 3306
user: "root"
password: "root"
gzip: true
#path: /var/www/backups
databases:
    -   database: foo
        structure:
            - audits
            - logs

    -   database: bar

dumper.php

#!/usr/bin/php
<?php

class Database
{
    protected $name;

    protected $structure = [];

    public function __construct(array $params)
    {
        $this->name      = $params['database'];
        $this->structure = $params['structure'] ?? [];
    }

    public static function make(array $params): Database
    {
        return new self($params);
    }

    public function getDatabaseName(): string
    {
        return $this->name;
    }

    public function getStructureTables(): array
    {
        return $this->structure;
    }
}

class Config
{
    protected $config = [];

    public function __construct(?string $filename)
    {
        if (empty($filename) || ! file_exists($filename)) {
            throw new InvalidArgumentException('Configuration file does not exist!');
        }

        $this->load($filename);
    }

    public function name(): string
    {
        $name = $this->value('name') ?: $this->host();

        return preg_replace(['/\./', '/[^\_|\w]/'], ['_', ''], $name);
    }

    public function host(): string
    {
        return $this->value('host');
    }

    public function port(): int
    {
        return $this->value('port');
    }

    public function user(): string
    {
        return $this->value('user');
    }

    public function password(): string
    {
        return $this->value('password');
    }

    /**
     * @return \Database[]|array|null
     */
    public function databases(): array
    {
        return array_map(function (array $params) {
            return Database::make($params);
        }, $this->value('databases', []));
    }

    public function directory(): string
    {
        if ($path = $this->value('path')) {
            return rtrim($path, '/\\');
        }

        return rtrim(realpath('.'), '/\\');
    }

    public function hasArchive(): bool
    {
        $value = $this->value('gzip', false);

        return filter_var($value, FILTER_VALIDATE_BOOLEAN);
    }

    protected function load(string $filename): void
    {
        $this->config = yaml_parse_file($filename);
    }

    protected function value(string $key, $default = null)
    {
        return $this->config[$key] ?? $default;
    }
}

class MySQL
{
    protected $config;

    protected $database;

    public function __construct(Config $config, string $database)
    {
        $this->config   = $config;
        $this->database = $database;
    }

    public static function make(Config $config, string $database): MySQL
    {
        return new self($config, $database);
    }

    public function tables(): array
    {
        return array_map(function (array $row) {
            return $row[0];
        }, $this->show());
    }

    protected function show(): array
    {
        return $this->connect()->query('SHOW TABLES')->fetch_all();
    }

    protected function connect(): mysqli
    {
        $connect = new mysqli(
            $this->config->host(),
            $this->config->user(),
            $this->config->password(),
            $this->database,
            $this->config->port()
        );

        $connect->set_charset('utf8mb4');

        if ($connect->connect_error) {
            throw new RuntimeException('Connection failed: ' . $connect->connect_error);
        }

        return $connect;
    }
}

class Dumper
{
    /** @var \Config */
    protected $config;

    protected $base_path;

    protected $dynamic_path;

    protected $archive_extension = 'sql.gz';

    protected $date;

    public function __construct(?string $config)
    {
        $this->config = new Config($config);

        $this->setPath();
    }

    public static function make(?string $config): Dumper
    {
        return new self($config);
    }

    public function run(): void
    {
        $this->info('Dump process started...');
        $this->eachDatabases();

        $this->info('Create a symlink...');
        $this->symlink();
    }

    protected function eachDatabases()
    {
        foreach ($this->config->databases() as $database) {
            $this->info($database->getDatabaseName(), 'dump process started...');

            $this->handle($database);
        }
    }

    protected function handle(Database $database): void
    {
        $name = $database->getDatabaseName();

        $mysql = MySQL::make($this->config, $name);

        $schema = $database->getStructureTables();

        foreach ($mysql->tables() as $table) {
            $has_schema = in_array($table, $schema);

            $options = $has_schema ? ['--no-data'] : [];

            $this->execute($name, $table, array_merge($options, ['--tables', $table]));
        }
    }

    protected function execute(string $database, string $table, array $options = []): void
    {
        $options = array_merge($this->parameters(), [$database], $options);

        $this->call(implode(' ', $options) . $this->store($database, $table));
    }

    protected function store(string $database, string $table): string
    {
        $path = $this->getPath($database) . '/' . $table;

        return $this->config->hasArchive() ? $this->toArchive($path) : $this->toSql($path);
    }

    protected function toArchive(string $path): string
    {
        return sprintf(' | gzip > %s.%s', $path, $this->archive_extension);
    }

    protected function toSql(string $path): string
    {
        return sprintf(' > %s.sql', $path);
    }

    protected function symlink(): void
    {
        $path = $this->date;

        $this->call("ln -sfn $path last");
    }

    protected function parameters(): array
    {
        return [
            'mysqldump',
            '--host="' . $this->config->host() . '"',
            '--port=' . $this->config->port(),
            '--user="' . $this->config->user() . '"',
            '--password="' . $this->config->password() . '"',
            '--disable-keys',
            '--single-transaction',
            '--add-drop-table=false',
            '--create-options',
            '--skip-extended-insert',
            '--complete-insert',
            '--quick',
        ];
    }

    protected function setPath(): void
    {
        $this->base_path = $this->config->directory();

        $this->date = date('Y-m-d_H-i');

        $this->dynamic_path = implode(DIRECTORY_SEPARATOR, [
            $this->date,
            $this->config->name(),
        ]);
    }

    protected function getPath(string $database = null): string
    {
        $path = implode(DIRECTORY_SEPARATOR, array_filter([
            $this->base_path,
            $this->dynamic_path,
            $database,
        ]));

        return $this->ensurePath($path);
    }

    protected function ensurePath(string $path): string
    {
        if (! file_exists($path)) {
            mkdir($path, 0777, true);
        }

        return $path;
    }

    protected function info(string ...$messages): void
    {
        echo implode(' ', $messages) . PHP_EOL;
    }

    protected function call(string $command): void
    {
        passthru($command);
    }
}

$config = $argv[1] ?? null;

Dumper::make($config)->run();