Sync your development environment with a remote one

While the developing process you need sometimes to sync you local development database with a remote one (i.e. production).

Below there is a script allowing to recreate the development database and fill in with data from the remote server.

MySQL

#!/bin/sh

DBUSER_REMOTE="root"
DBPASSWORD_REMOTE="root"
DBUSER_LOCAL="root"
DBPASSWORD_LOCAL="root"
DBNAME_REMOTE=""
DBNAME_LOCAL=""
TMPFILE="$(mktemp db.XXXXXXX)"
HOST="www.example.com"

ssh $HOST "mysqldump -u$DBUSER_REMOTE -p$DBPASSWORD_REMOTE $DBNAME_REMOTE | gzip" | gunzip > $TMPFILE

mysqladmin -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL -f drop $DBNAME_LOCAL
mysqladmin -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL create $DBNAME_LOCAL
mysql -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL $DBNAME_LOCAL < $TMPFILE
rm $TMPFILE

PostgreSQL

#!/bin/sh

DBUSER_REMOTE="postgres"
DBUSER_LOCAL="postgres"
DBNAME_REMOTE=""
DBNAME_LOCAL=""
TMPFILE="$(mktemp db.XXXXXXX)"
HOST="www.example.com"

ssh $HOST "pg_dump -U $DBUSER_REMOTE -Fc $DBNAME_REMOTE" > $TMPFILE

dropdb -U $DBUSER_LOCAL $DBNAME_LOCAL
createdb -U $DBUSER_LOCAL $DBNAME_LOCAL
pg_restore -U $DBUSER_LOCAL -Fc -d $DBNAME_LOCAL $TMPFILE
rm $TMPFILE

Sync media files

$HOST="www.example.com"
$PATH="/absolute/path/to/media"
$LOCAL_DIR="."
rsync -rltDvH $HOST:$PATH $LOCAL_DIR

PyCharm can't run a project with MySQL database

I wanted to run my project in PyCharm IDE and got this error.

django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: dlopen(/Users/boris/env/lib/python2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
  Referenced from: /Users/boris/env/lib/python2.7/site-packages/_mysql.so
  Reason: image not found

After googling a while I figured out there is a mess with DYLD_LIBRARY_PATH environment variable. So, first I needed to locate the path where libmysqlclient.18.dylib located.

$ locate libmysqlclient.18.dylib
/usr/local/mysql-5.5.22-osx10.6-x86_64/lib/libmysqlclient.18.dylib

Done. In Run/Debug Configurations in Environment variables I added (plus to PYTHONUNBUFFERED=1) DYLD_LIBRARY_PATH=/usr/local/mysql-5.5.22-osx10.6-x86_64/lib/:$DYLD_LIBRARY_PATH.

It started working.

Install MySQL on Mac OS X to work with Python

I need to get mysql support for python on my Mac OS X Lion.

Download the latest MySQL .DMG distributive from somewhere (i.e. official website) and install it.

sudo pip install python-mysql

It requires superuser privileges to get installed.

What may happen:

while installing mysql-python

Downloading/unpacking mysql-python
  Downloading MySQL-python-1.2.3.tar.gz (70Kb): 70Kb downloaded
  Running setup.py egg_info for package mysql-python
    sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 14, in <module>
      File "/Users/boris/projects/a1/th4x/build/mysql-python/setup.py", line 15, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 24, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found
    Complete output from command python setup.py egg_info:
    sh: mysql_config: command not found

Traceback (most recent call last):

  File "<string>", line 14, in <module>

  File "/Users/boris/projects/a1/th4x/build/mysql-python/setup.py", line 15, in <module>

    metadata, options = get_config()

  File "setup_posix.py", line 43, in get_config

    libs = mysql_config("libs_r")

  File "setup_posix.py", line 24, in mysql_config

    raise EnvironmentError("%s not found" % (mysql_config.path,))

EnvironmentError: mysql_config not found

----------------------------------------
Command python setup.py egg_info failed with error code 1
Storing complete log in /Users/boris/.pip/pip.log

solution:

Add /usr/local/mysql/bin/ to PATH

export PATH=/usr/local/mysql/bin/:$PATH

(from my ~/.bashrc)

trying to run dev server

Unhandled exception in thread started by <bound method Command.inner_run of <django.contrib.staticfiles.management.commands.runserver.Command object at 0x10c59bc90>>
Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/django/core/management/commands/runserver.py", line 88, in inner_run
    self.validate(display_num_errors=True)
  File "/Library/Python/2.7/site-packages/django/core/management/base.py", line 249, in validate
    num_errors = get_validation_errors(s, app)
  File "/Library/Python/2.7/site-packages/django/core/management/validation.py", line 35, in get_validation_errors
    for (app_name, error) in get_app_errors().items():
  File "/Library/Python/2.7/site-packages/django/db/models/loading.py", line 146, in get_app_errors
    self._populate()
  File "/Library/Python/2.7/site-packages/django/db/models/loading.py", line 64, in _populate
    self.load_app(app_name)
  File "/Library/Python/2.7/site-packages/django/db/models/loading.py", line 78, in load_app
    models = import_module('.models', app_name)
  File "/Library/Python/2.7/site-packages/django/utils/importlib.py", line 35, in import_module
    __import__(name)
  File "/Users/boris/projects/a1/th4x/tradecontrol/models.py", line 4, in <module>
    from tradecontrol.db import auto_detect
  File "/Users/boris/projects/a1/th4x/tradecontrol/db/__init__.py", line 2, in <module>
    import MySQLdb as mysql
  File "/Library/Python/2.7/site-packages/MySQLdb/__init__.py", line 19, in <module>
    import _mysql
ImportError: dlopen(/Library/Python/2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
  Referenced from: /Library/Python/2.7/site-packages/_mysql.so
  Reason: image not found

solution:

Add to your ~/.bashrc add

export DYLD_LIBRARY_PATH=/usr/local/mysql/lib

and then reread .bashrc

. ~/.bashrc

Allow remote connections to MySQL server

To enable remote access for MySQL server:

$ sudo vim /etc/mysql/my.cnf
### edit my.cnf
### set bind address and comment out or remove skip-networking (if exists)

[mysqld]
...
bind-address=SERVER-EXTERNAL-IP
# skip-networking

$ /etc/init.d/mysql restart

Now it works.

How to limit joined records in MySQL

I have to tables with one-to-many relationship. I needed to select all from the master table and join only one record which contains the oldest date.
The solution is:

SELECT parent.*,
       c1.*
  FROM parent
       JOIN child c1
          ON c1.parent_id = parent.id
       LEFT JOIN child c2
          ON c2.parent_id = c1.parent_id AND c2.sort < c1.sort -- field to determine the order (maybe whatever you want)
GROUP BY parent.id,
         parent.name,
         c1.id,
         c1.name
HAVING COUNT(c2.id) < 1 -- limitation of joined records
ORDER BY parent.name, c1.name;

I found it there.

Create a MySQL database for new user

To create a database and a user, to grant some privileges:

CREATE DATABASE test_db DEFAULT CHARACTER SET UTF8;
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON test_db.* TO 'user'@'%';