jplew/syncdb

Bash script meant to take the tedium out of deploying and updating database-driven (eg Wordpress) websites. It rapidly synchronizes local and remote versions of a MySQL database, performs the necessary search and replace queries, then synchronizes all your uploads/binaries.

syncdb's Issues

Update Search-Replace-DB scripts

The paths to the Search-Replace-DB scripts have changed, along with one of the command line flags. I have replaced them as follows:

Script paths on github - line 171:

# Search-Replace-DB
searchScriptName=srdb.class.php
cliScriptName=srdb.cli.php
srdbPath=https://raw.github.com/interconnectit/Search-Replace-DB/master/
searchScriptPath=$srdbPath$searchScriptName
cliScriptPath=$srdbPath$cliScriptName

Command line flag for database name was -d, is now -n - line 782:

echo "./$cliScriptName -h "$l_db_host" -u $l_db_user -n $l_db_name -p $l_db_pass -c utf\-8 -s "$r_web_addr" -r "${l_web_addr:-$l_web_dir}" "

./$cliScriptName -h "$l_db_host" -u $l_db_user -n $l_db_name -p $l_db_pass -c utf\-8 -s "$r_web_addr" -r "${l_web_addr:-$l_web_dir}" 

and line 836:

echo "./$cliScriptName -h $r_db_host -u $r_db_user -n $r_db_name -p $r_db_pass -c utf\-8 -s $l_web_addr -r $r_web_addr"

./$cliScriptName -h "$r_db_host" -u "$r_db_user" -n "$r_db_name" -p "$r_db_pass" -c utf\-8 -s "$l_web_addr" -r "$r_web_addr"

I probably should fork your script and put this in a pull-request but I'm new to github and up against a project deadline at the moment so I hope you'll excuse the issue posting!

Can't connect to DreamHost via SSH using this.

First, this is super awesome - I can't believe there isn't more interest in it. I have it set up in my local WP install, configured the variables and now just going down the commands to test. Was able to create a local db backup in about .2 seconds. How cool.

I have a VPS with DreamHost. Running ssh [email protected] works as expected - my RSA keys are verified and I can connect without entering a password. Using those settings:

r_user=myusername
r_host=mydomain.com

I can't connect using SyncDB: Error: could not connect to host.

What am I doing wrong? What else should I try? Thanks!

What about comments and user

Hi, thank you for your apps.
How can we do to sync from local to distant server without modify wp_comments , users.
Because , in production server, users can create comments, or can subscribe, during local modification.
Thank you

wrong php binary detection on search_replace_local

I was getting a funny error when hitting search_replace_local:

./syncdb: line 1209: /Applications/MAMP/bin/php/php5.3.28/bin/php: No such file or directory

Weird because I run MAMP PRO and it's supposed to be php 5.6.7. There isn't a 5.3.28 in MAMP.
I thought MAMP was doing something funky but which php correctly returns /usr/bin/php.

Digging the code deeper it seems like the problem is here:

if [[ "${l_php-}" ]]
then
    my_php=$l_php 
fi

What is this l_php- ?

I had to comment those lines out to get the proper php binary and then it worked.

Just a heads up!

search/replace not working on remote after push

Getting the following error:

Error: could not find /usr/bin/php. Locate your server's version of the php command line interface (php-cli) and assign it to the variable $r_php.

Thus, all my remote links are still mydomain:8888/...

How can I fix? Is this something on my remote server (DreamHost)?

Does it work for WP Multisites?

Could it be for Drupal and WP too

Hi,
I read the code and I saw that It can fit for Drupal too... but I want it to work for both same time, so how do you prefer? to make totally new version for Drupal and make the changes or add new function in the script for drupal?
for ex.
Syncdb drupal pull..
Syncdb wp pull..

Thanks

Move syncdb script and config up a directory

My WordPress website is running on a shared server which is running nginx instead of Apache. This is cool, modern and fast but has the major downside of a) no .htaccess files to configure handy server settings and b) no access to nginx.conf to configure handy server settings (this is fair enough on a shared server).

The upshot of this is that the syncdb scripts and /.bak folder containing the DB backups end up sitting in my /public_html folder for all the world to see, download and use in their nefarious hacking attempts against my site :(.

I am in the process of writing some cleanup move/copy commands on the end of the syncdb script to sort out this problem. However, I've always thought that the syncdb scripts don't really belong in the web root folder anyway as they're not intended to be served as web site resources.

Can the script be redesigned to run from the folder above web root? DB backups should be saved into a folder in this location (above web root) rather than within web root. How does that sound?

Search string is empty

I've followed the instructions within the readme and set my local as eco.test and remote as my live site but it is saying that the string is empty. My local database has the values eco.test in so I know it's in there. Any ideas? Thanks

l_web_addr=eco.test
r_web_addr=ecoclub.store

/usr/bin/php -q srdb.cli.php -h localhost -u wpuser -n wordpress_db -p [password hidden] -c utf-8 -s eco.test -r ecoclub.store
search: Search string is empty

Syntax error on replace_remote_db

Hi,

First of all thanks for this excellent script! I'm really impressed. I'm running into trouble with a hosting that doesn't give me "DROP" privileges. So I've added here:
no_drop=true # set to true if your host sucks

...because my host sucks :D

Then I get this:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>/dev/null 2>&1' at line 1

Which is related to this:

sql_show="show databases; create database if not exists $r_db_name >/dev/null 2>&1;"

        echo "mysql -u$r_db_user -p$r_db_pass -h $r_db_host --show-warnings -Bse $sql_show"

        mysql -u$r_db_user -p$r_db_pass -h $r_db_host --show-warnings -Bse "$sql_show"

Any ideas on where this syntax error is? Ill keep looking.

Syntax error

When I try running the script (on my MacBook Pro, Mavericks) I get a syntax error:

./syncdb: line 267: syntax error near unexpected token `;'
./syncdb: line 267: `        return_local_standard();'

Request to move "r_site_dir" variable to syncdb_config

I am following Mark Jaquith's full WP-Skeleton based workflow as described in his 2011 Wordcamp SF address:

http://2011.sf.wordcamp.org/session/scaling-servers-and-deploys-oh-my/

This involves deploying my WP-Skeleton based website using Capistrano as recommended by him.

The problem is that Capistrano creates the following directory structure on my shared host server ( symbolic links are indicated by "->" ) and it isn't completely compatible with syncDB:

  ├──  public_html   ->   ~/deploy/current
  └──  deploy
           ├── current   ->   ~/deploy/releases/<latest>
           ├── releases
           ├── repo
           └── shared/content/uploads

So to use syncdb-config I set the parameters as follows, which works for the rsync of my uploads directory:

r_web_dir = deploy
l_upload_dir = shared/content/uploads/
r_upload_dir = shared/content/uploads/

However for MySQL pull/push, syncdb cannot find wp-config.php based on the above parameters, as wp-config.php lives in ~/deploy/current and syncdb expects it to be in ~/deploy.

So, I need to update the parameter r_site_dir in syncdb to get it to work. I change the line:

r_site_dir=$HOME/$r_web_dir

to be:

r_site_dir=$HOME/$r_web_dir/current

This means I can't get automatic updates to syncdb as I need to hack it to get it to work, or I modifiy it each time I use it.

QUESTION

Would it be possible to move r_site_dir to be a parameter in syncdb_config?

The logic would be that r_site_dir only needs to be set initially by the user if wp-config.php is in a different directory to r_web_dir on the server.

Otherwise r_site_dir would default to $HOME/$r_web_dir as it does at the moment.

Thanks,
Henry

ssh issues

Hi,

I have 2 issues when running the script with my current SSH setup.

First, the test_ssh fails for some reason:

Checking if SSH connection is working...
ssh -q <user>@<host> exit
<user>@<host>'s password:
TERM environment variable not set.
Test failed. Trying again...
ssh -q -o BatchMode=yes -o ConnectTimeout=15 <user>@<host> echo ok 2>&1
Error: could not connect to host. Confirm that the SSH connection, <user>@<host>, is working.

My host is not properly setup and I get prompted to enter the SSH password, could that be why?

So I tried to bypass it by commenting it out from the "pull" list of commands and it worked until do_remote_backup.

Checking the source code I can see it fails also because of ssh:
echo "ssh -qt $ssh_port$r_user@$r_host cd $r_web_dir; chmod +x $(basename $0); ./$(basename $0) backup_remote_db;"

To get past this I had to manually make a chmod +x in the script in the server and then everything worked like a charm.

Any ideas why? Possibly just an issue with my setup but it would be good to know why :)

Issue when moving Wordpress Multisite root to new folder on a Capistrano V3 deploy

Fantastic to find your script as I'm following Mark Jaquith's deploy strategy using Capistrano (V3) and Search Replace DB as described here and your script makes it so easy, especially compared to having to use an option like WP_Migrate_DB Pro:

http://markjaquith.wordpress.com/2012/05/26/wordpress-skeleton/

As a result of using Capistrano to deploy WordPress, my Use Case may be slightly trickier than the current one SyncDb is based on, mainly because as part of the Capistrano deployment, everything WordPress is moved to a subdirectory (named /current) of public_html on my server.

To complicate things further, my site is Multisite WordPress based on subdirectories.

In either case, I'm running into problems bringing my site up on my staging server after using SyncDB. It looks like this is due to the replace commands only substituting the local URL for the server URL, as I also need to update the WP_SITEURL in the database to include a new subdirectory (see below for full description).

So let me describe what I'm trying to do as simply as possible:

Scenario

Local Server Environment (Macbook Pro)

Based on wp-skeleton at: https://github.com/markjaquith/WordPress-Skeleton

Local Site: example.dev
2 Multisite sites based on language: example.dev/en and example.dev/de
Apache Root directory is: ../example.dev/
WordPress core is in subdirectory: ../example.dev/wp
WP_SITEURL was defined manually in the Administration > Settings > General panel to be example.dev/wp so this value lives in the database and not in wp-config.php.

Staging Server Environment (Hosted Service Provider)

Staging Server: staging.example.com
Apache Root directory is ../staging.example.com/public_html
WordPress core is in subdirectory: ../staging.example.com/public_html/current/wp

Question

So I think the problem may be that the substitutions I see being made in SyncDB are only example.dev to staging.example.dev, leaving my database setup inconsistent with my code structure? What do you think?

If so, I need to make extra updates in my staging server DB to take account of WordPress core being moved from /wp to /current/wp?

Is there an easy way I can modify SyncDb to take account of this?

r_web_dir shouldn't assume ~ home folder will be used

I am trying to deploy to a folder outside of my SSH users home directory:

/var/www/mysite.com/public_html

This script seems to only work if the target folder in side the user's home folder. Is that correct or am I missing something?

Why not simply allow the user to choose the full path to the deployment folder?

Documentation Correction line 87 for rsync_pull and rsync_push

Hi,

I want to write in all Caps how amazing this script is - Thank you so much for sharing.

I was hoping you could make a correction to the internal documentation of SyncDB. I would submit a pull request but it's adding one character on line 87 of the syncdb script.

...
82 # $l_upload_dir and $r_upload_dir are where your site's 'uploads' folder are
83 # located. This tells rsync which directories on your FTP should be
84 # synchronized. If you're using WP-Skeleton
85 # <http://markjaquith.wordpress.com/2012/05/26/wordpress-skeleton/>, all you
86 # have to synchronize is your shared folder (content/uploads is symlinked to
-- 87 # shared/content/uploads). Otherwise, it's probably wp-content/uploads.
...
++ 87 # shared/content/uploads). Otherwise, it's probably wp-content/uploads/.

Please note the final slash on line ++87. Without the final slash - the folders are synchronized to the wrong locations.

Thanks again JP - you are legend.

Alternate Sites - Staging and Production

You've added partial support for staging and production servers via the r_web_addr_alt setting in the config. Could I request extending this to r_web_dir as well? Perhaps I'm missing something but I'm not sure how I can currently upload files to one location for my staging server and another for my production server.

download_remote_db fails

Running download_remote_db throws an error, which spits out the usage info for scp and then says the download didn't work.

The problem is that the scp port is added to the command as a quoted string, but it should not be quoted. Line 895 (upload_script) does it the right way: ${scp_port} whereas line 735 is incorrect: "$scp_port". I guess this might work in some environments, but it doesn't in mine (bash, Ubuntu 12.04).

The confusing thing is that because you echo the command to execute first, it looks like it should be ok, but what actually gets executed is (assuming port 8822 for ssh):

scp -q "-P 123 "user@host:~/web_dir/.bak/bakname.bz2 .

The curly brackets method ${scp_port} works fine.

Use /bin/bash rather than /bin/sh

Hi,

Many thanks for the script - I'm hoping it's going to be a useful deployment tool for me :)

I've just tried running the script on my VVV vagrant VM and it failed. With some help I figured out that /bin/sh on an Ubuntu machine points to dash rather than bash. Changing the first line from #!/bin/sh to #!/bin/bash solved the problem. According to the article, this will work on non-Ubuntu servers as well so should be a safe cross-platform change.

executing chmod +x syncdb to remote server will make syncdb fail

After I executed the "chmod +x syncdb" on my remote server, then run ./syncdb help or other ./syncdb command the result below will show.

: invalid option
Usage: /bin/bash [GNU long option] [option] ...
/bin/bash [GNU long option] [option] script-file ...
GNU long options:
--debug
--debugger
--dump-po-strings
--dump-strings
--help
--init-file
--login
--noediting
--noprofile
--norc
--posix
--protected
--rcfile
--rpm-requires
--restricted
--verbose
--version
Shell options:
-irsD or -c command or -O shopt_option (invocation only)
-abefhkmnptuvxBCHP or -o option

Ftp only

Hi! My website is currently on a shared server that doesn't support ssh. Is there any way to use syncdb with just ftp?

Reset's wordpress installation after running your script

I had to make some modifications to your syncdb file to accomodate change in file names and paths. After executing your script, when I visit the url, it asks me to install the wordpress again!!!

Could you tell me why this is happening?

Below is the change I made.

searchScriptName=srdb.class.php
cliScriptName=srdb.cli.php

searchScriptPath=https://raw.github.com/interconnectit/Search-Replace-DB/master/srdb.class.php
cliScriptPath=https://raw.github.com/interconnectit/Search-Replace-DB/master/srdb.cli.php