vielhuber/syncdb

Syncs a database (mysql, pgsql) between two servers (optional via ssh tunnel).

🔥 syncdb 🔥

syncdb syncs databases between two servers., _(*1)

Features

• Most common use case: Sync your production database to your local environment
• You also can sync between any servers, even from remote to remote (without local)
• Works with direct database connections or via ssh tunnels
• Currently supports mysql, postgresql support will be added soon
• Has also a fast mode where the sql file is zipped (you also can choose the compression level)
• Does include a search/replace mechanism called magicreplace
• (Remote) commands like mysqldump, mysql, zip, e.g. can be overwritten manually to fit any environment
• Works on Linux, Mac and Windows (with WSL)
• Supports parallel execution of multiple syncs
• Uses optimization techniques for a faster restore
• Also supports ssh connections to servers without the support for public keys
• Shows live restore progress

Requirements

Mac

Install Homebrew and then coreutils:, _(*2)

brew install coreutils

Windows

Choose one of the two following options:, _(*3)

• Install WSL2
• Install Cygwin:
  • Install all default packages + unzip
  • Add C:\cygwin64\bin to your environment PATH (high priority, but below php)

Installation

Mac / Windows (WSL2) / Linux

mkdir ~/.syncdb
cd ~/.syncdb
composer require vielhuber/syncdb
chmod +x vendor/vielhuber/syncdb/src/syncdb

Now add ~/.syncdb/vendor/vielhuber/syncdb/src/ to your path environment., _(*4)

Windows (CMD)

mkdir "%USERPROFILE%/.syncdb"
cd "%USERPROFILE%/.syncdb"
composer require vielhuber/syncdb

Now add C:\Users\xxx\.syncdb\vendor\vielhuber\syncdb\src\ to your path environment., _(*5)

Update

Mac / Windows (WSL2) / Linux

cd ~/.syncdb
composer update
chmod +x vendor/vielhuber/syncdb/src/syncdb

Windows (CMD)

cd "%USERPROFILE%/.syncdb"
composer update

Usage

syncdb profile-name

Configuration

Simply put your desired configuration files in ~/.syncdb/profiles/profile-name.json:, _(*6)

mkdir ~/.syncdb/profiles
cd ~/.syncdb/profiles
nano example.json

{
    "engine": "mysql",
    "source": {
        "host": "200.10.10.10",
        "port": "3307",
        "database": "EXAMPLE",
        "username": "EXAMPLE",
        "password": "EXAMPLE",
        "cmd": "mysqldump",
        "ssh": false
    },
    "target": {
        "host": "localhost",
        "port": "3306",
        "database": "EXAMPLE",
        "username": "EXAMPLE",
        "password": "EXAMPLE",
        "cmd": "mysql",
        "ssh": false
    },
    "replace": {
        "https://www.example.com": "http://www.example.local",
        "www.example.com": "www.example.local"
    }
}

You can find more examples in the profiles folder in this git repo., _(*7)

Excluding table data

Sometimes it is useful to exclude certain table data (e.g. logs, cache, ...), while preserving the structure. Database engines don't provide a good way to do this. Therefore you can use the ignore_table_data-directive in your json-config:, _(*8)

{
    "ignore_table_data": ["table1", "table2", "table3"]
}

Privileges

Since MySQL 5.7 and MySQL 8.0.21 accessing the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege. Most providers don't have this option available. This results in the error message:, _(*9)

Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces, _(*10)

syncdb automatically adds --no-tablespaces to your mysqldump-commands. You can turn off this behaviour by adding "tablespaces": true to your configuration., _(*11)