README

A library for syncing Wordpress databases. Supports syncs to and from local and remote machines, and even remote to remote syncs. Useful as part of a deployment system.

This project is meant to be a composable component. It does one thing, sync Wordpress databases. If you want to do more, as part of a deployment strategy, then check out the other projects in the Wordpress\Deploy namespace.

Sync's are performed using an SSH connection, not via a remote mysql command connection. So, the connection remains secure, and you don't have to open up the database port to the world.

Dependencies

The following shell commands must be available on the source machine. You'll get a RuntimeException if they aren't.
- mysqldump
- gzip
The following shell commands must be available on the destination machine. You'll get a RuntimeException if they aren't.
- mysql
- gunzip
- php
interconnectit/Search-Replace-DB must be installed on the Destination machine, if you intend to do search and replace. The interconnectit/search-replace-db/srdb.cli.php command-line script in that library is used to do the actual search and replace in the database.
All other dependencies are defined in composer.json.

Install

curl -s http://getcomposer.org/installer | php
php composer.phar require cullylarson/wp-deploy-database-sync

Usage

You'll do everything using the Wordpress\Deploy\DatabaseSync::sync function.

Constructor Options

The Wordpress\Deploy\DatabaseSync constructor takes an array of options. The options you provide will depend on the type of sync you want to perform (i.e. local to local, local to remote, remote to local, or remote to remote). If you were to provide all of the options, it would look like this:

[
    'source' => [
        'tmp' => "",
        'local' => false,
        'ssh' => null,
        'keep_dump' => false,
        'db' => [
            'host' => "",
            'username' => "",
            'password' => "",
            'name' => "",
            'port' => "",
        ],
    ],
    'dest' => [
        'tmp' => "",
        'srdb' => "",
        'local' => false,
        'ssh' => null,
        'keep_dump' => false,
        'db' => [
            'host' => "",
            'username' => "",
            'password' => "",
            'name' => "",
            'port' => "",
        ]
    ],
    'local_tmp' => "",
    'search_replace' => []
]

The definition of these options is as follows:

local_tmp (string) (semi-required) Required for remote to remote syncs. When a remote to remote sync is performed, a dump file is copied from the source machine to the local machine, and then from the local machine to the destination machine. To do this, a folder for temporary files is used on the local machine.
search_replace (array) (optional, default:[]) If you want to do a search and replace on the data in the database, provide it here. The keys of this array are the text that will be searched for, and the values are what the search text will be replaced with. The search and replace is done in such a way as to preserve serialized data, since Wordpres is stupid and uses a crap-ton of serialized data.
source and dest (array) (required) These define options for the source and destination machines you will be syncing between. Each of these options is an associative array with the following values:
- tmp (string, required) Path to a folder where temporary files can be stored on this machine. If this is a local to local sync, the tmp can be the same for the source and the dest, they won't clash with each other.
- srdb (string) (semi-required for 'dest') Path to the srdb.cli.php file on the dest machine. This is only required if you want to do search and replace.
- local (boolean) (required) Defines whether this machine is local (e.g. the machine you want to sync to or from). This cannot be true if you provide a value for ssh. You must provide this value, even if it's set to false (i.e you must explicitly define the machine as local).
- ssh (resource|null) (optional, default:null) An SSH connection resource (c.f. ssh2_connect) if this machine is remote, or null if it's local. If you provide a connection resource, you must set local to false.
- keep_dump (boolean) (optional, default:false If true, the mysql dump file generated while performing the sync will be left on this machine. So, if this is the source machine, the dump from this machine will stay in the tmp folder. And if this is the dest machine, the dump file from source machine will be kept in the destination's tmp folder. NOTE: at no point is a dump generated for the destination machine, so this is not a backup of the destination.
- db (array) (required) Info. about the database on this machine.
  - host (string) (required) The host, as viewed by the source or dest machine itself. In other words, if the dest machine thinks the host is 'localhost', you can use 'localhost' here. This is because the dump and import are done on the machine's themselves, via SSH.
  - username (string) (required)
  - password (string) (required)
  - name (string) (required) The name of the database.
  - port (string) (optional, default: 3306)

Sync

The sync itself is performed by the Wordpress\Deploy\DatabaseSync::sync function. When this function is called, the following will happen:

The database is dumped to a file in the tmp folder on the source machine.
The dump file is copied to the tmp folder on the destination. If this is a remote to remote sync, then the following extra steps are taken:
1. The dump file is copied from the source machine to the local machine's local_tmp folder.
2. The dump file is copied from the local machine to the destination machine.
3. The dump file is removed from the local machine.
The dump file is removed from the source, unless the keep_dump option is set to true in the source machine options.
The dump file is imported into the destination machine's database. NOTE: This will completely overwrite the destination database.
The dump file is removed from the destination machine, unless the keep_dump option is set to true on the dest machine options.

If the search_replace option is provided, then the text from each key in this array will be replaced with the text from the corresponding value in the array. E.g.:

$search_replace = [
    "look for this" => "replace with this",
    "and look for this" => "so it can be replaced with this",
    "localhost:8080" => "productionurl.com",
];

Since the transfer is done using the mysqldump and mysql commands on the source and dest machines, no remote mysql command connection is made. Everything is done over SSH.

The Wordpress\Deploy\DatabaseSync::sync function can optionally accept a callback function. This callback will be called whenever the sync function wants to post a status update (e.g. "I'm running", "Here's the output of the rsync command", "Something went wrong", etc.). It allows you to have some control over whether and how messages are handled.

The callback must take one parameter, an instance of Wordpress\Deploy\DatabaseSync\Status. An example is below.

Examples

Create SSH Connections

The following code could be used to create SSH connections for some of the examples below:

<?php

$sourceSsh = ssh2_connect("localhost", 22, array('hostkey'=>'ssh-rsa'));
ssh2_auth_agent($sourceSsh, "my_username");

$destSsh = ssh2_connect("localhost", 22, array('hostkey'=>'ssh-rsa'));
ssh2_auth_agent($destSsh, "my_username");

NOTE: If you're using RSA for the examples below, and you get an auth error, you might need to run this command:

$ eval `ssh-agent -s` && ssh-add

Local to Local Sync

<?php

use Wordpress\Deploy\DatabaseSync;

$options = [
   'source' => [
       'tmp' => "/tmp",
       'local' => true,
       'db' => [
           'host' => "localhost",
           'username' => "source_db_username",
           'password' => "source_db_password",
           'name' => "source_db_name",
       ],
   ],
   'dest' => [
       'tmp' => "/tmp",
       'srdb' => "path/to/interconnectit/search-replace-db/srdb.cli.php",
       'local' => true,
       'db' => [
           'host' => "localhost",
           'username' => "dest_db_username",
           'password' => "dest_db_password",
           'name' => "dest_db_name",
       ]
   ],
];

$dbSync = new DatabaseSync($options);
$success = $dbSync->sync();

Local to Remote Sync

<?php

use Wordpress\Deploy\DatabaseSync;

$options = [
   'source' => [
       'tmp' => "/tmp",
       'local' => true,
       'db' => [
           'host' => "localhost",
           'username' => "source_db_username",
           'password' => "source_db_password",
           'name' => "source_db_name",
       ],
   ],
   'dest' => [
       'tmp' => "/tmp",
       'srdb' => "path/to/dest/interconnectit/search-replace-db/srdb.cli.php",
       // notice that the only thing that makes this remote is that
       // 'local' is false, and 'ssh' is a resource.
       'local' => false,
       'ssh' => $destSsh,
       'db' => [
           // this is the localhost, as viewed from the destination machine
           'host' => "localhost",
           'username' => "dest_db_username",
           'password' => "dest_db_password",
           'name' => "dest_db_name",
       ]
   ],
];

$dbSync = new DatabaseSync($options);
$success = $dbSync->sync();

Remote to Local

<?php

use Wordpress\Deploy\DatabaseSync;

$options = [
   'source' => [
       'tmp' => "/tmp",
       'local' => false,
       'ssh' => $sourceSsh,
       'db' => [
           // this is the localhost, as viewed from the source machine
           'host' => "localhost",
           'username' => "source_db_username",
           'password' => "source_db_password",
           'name' => "source_db_name",
       ]
   ],
   'dest' => [
       'tmp' => "/tmp",
       'srdb' => "path/to/interconnectit/search-replace-db/srdb.cli.php",
       // this is the only indicator that this is a local destination
       'local' => true,
       'db' => [
           'host' => "localhost",
           'username' => "dest_db_username",
           'password' => "dest_db_password",
           'name' => "dest_db_name",
       ],
   ],
];

$dbSync = new DatabaseSync($options);
$success = $dbSync->sync();

Remote to Remote

<?php

use Wordpress\Deploy\DatabaseSync;

$options = [
   'source' => [
       'tmp' => "/tmp",
       'local' => false,
       'ssh' => $sourceSsh,
       'db' => [
           // this is the localhost, as viewed from the source machine
           'host' => "localhost",
           'username' => "source_db_username",
           'password' => "source_db_password",
           'name' => "source_db_name",
       ]
   ],
   'dest' => [
        'tmp' => "/tmp",
        'srdb' => "path/to/dest/interconnectit/search-replace-db/srdb.cli.php",
        'local' => false,
        'ssh' => $destSsh,
        'db' => [
            // this is the localhost, as viewed from the destination machine
            'host' => "localhost",
            'username' => "dest_db_username",
            'password' => "dest_db_password",
            'name' => "dest_db_name",
        ]
   ],
   // required for remotet-to-remote syncs
   'local_tmp' => '/tmp',
];

$dbSync = new DatabaseSync($options);
$success = $dbSync->sync();

Status Callback

You can provide a callback function to sync and get status messsages (e.g "Dumping the database", "Transferring the file", etc.). Here's an example:

<?php

use Wordpress\Deploy\DatabaseSync;
use Wordpress\Deploy\DatabaseSync\Status;

$statusCallback = function(Status $status) {
    echo $status->Timestamp . " -- ";
    
    if( $status->isError() ) echo "ERROR: ";
    if( $status->isWarning() ) echo "WARNING: ";
    if( $status->isRawOutput() ) echo "================\n";
    
    echo $status->Message;
    
    if( $status->isRawOutput() ) echo "================\n";
}

$options = [...];
$dbSync = new DatabaseSync($options);
$dbSync->sync($statusCallback);

cullylarson / wp-deploy-database-sync

Maintainers

Details