Thursday, June 19, 2014

Getting Django setup on Windows

A friend asked me how to setup an Django enviroment on Windows without the Cygwin fuzz. Here is the shortest steps I can think of:

1. Install Python3.4 as default path. (It should be "C:\Python34").
2. Open cmd.exe program on Windows and run the following commands:

    set PATH=%PATH%;C:\Python34;C:\Python34\Scripts
    pip install django
    python C:\Python34\Lib\site-packages\django\bin\django-admin.py startproject mysite
    cd mysite
    python manage.py syncdb

    (Follow prompts to setup an initial db. remember the user you used here.)

    python manage.py runserver

3. You are ready to django! Open your browser http://127.0.0.1:8000 to verify.
    Or login into http://127.0.0.1:8000/admin with the user you setup above.
  
What's next?
    Explore django with a guided tutorial here:
    https://docs.djangoproject.com/en/1.6/intro/tutorial01
   

Getting django started on Windows 7 with python 3 and MySQL 5.6

Django is a python web framework library and it works on both python 2 or 3. I will show you how to get python 3 setup in Cygwin environment.

If you're on a Windows OS, the best experience I have when working with Django on Windows is to use Cygwin, a Unix emulator shell that runs on Windows. The pip and django commands would automatically setup in Cygwin's PATH after installed. The default python package on Cygwin is only 2.7 though, so you have to search for "python3" package to get the latest python version. And you can have both versions installed without problem, but the executable are named "python" and "python3" respectively.

1. Install Cygwin python3 package. Verify "python3 -V" is working.
2. Install "pip" by downloading this "get-pip.py" file from http://pip.readthedocs.org/en/latest/installing.html and then run "python3 get-pip.py". Verify "pip3 --version" is working.
(NOTE: If you are running Windows 7, you might run into this issue: https://github.com/pypa/pip/issues/1448 where pip exit without a warning. In this case the workaround is install Cygwin "binutils" and "libuuid-devel" packages, and that fixed the problem for me.)
3. Install "django" by running "pip3 install django"
4. Finally install the MySQL driver with "pip3 install mysql-connector-python --allow-external mysql-connector-python" command.

Now to get a django project started, try these:
 
django-admin.py startproject myapp
 
The "django-admin.py" script should automatically in your PATH. and this create a new project with initial settings. To switch default database from SQLite3 to MySQL, change the "myapp/settings.py" with the following:

DATABASES = {
    'default': {
        'NAME': 'mydb',
        'ENGINE': 'mysql.connector.django',
        'USER': 'root',
        'PASSWORD': 'secret',
    }
}

Now I assume you have MySQL 5.6+ installed on your Windows already. Change the root password to match yours or using different DB user. You can now have django app setup the initial database schema tables for this specif myapp with this commad:
 
cd myapp
python3 manage.py syncdb
 

Follow the prompt and setup your admin user. Now you can start django web app:

 
python3 manage.py runserver 
  

Open browser to http://localhost:8000/admin. Now try to login and enjoy!

References:
https://www.python.org
http://cygwin.com
http://dev.mysql.com
https://docs.djangoproject.com

Thursday, June 12, 2014

How to initialize a new MySQL installation and create new database

 
For a freshly installed MySQL server, you would need to initialize the system tables and data directory like this:
 
cd mysql-<version> 
scripts/mysql_install_db --basedir=. --datadir=data
bin/mysqld_safe --defaults-file=my.cnf &
 
Before MySQL 5.6, this will setup a "root" with empty password that you can immediately login. For MySQL 5.7 however, it creates a random password for "root" user now. The password is generated under $HOME/.mysql_secret. You need to login and run "SET PASSWORD = PASSWORD('secret')" to change it.

After above, you my login using "root" and start creating your own database and users. For example:

    CREATE DATABASE mydb;
    GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' IDENTIFIED BY 'secret';
    GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' IDENTIFIED BY 'secret';

 

Monday, June 9, 2014

Using crontab to startup service

Did you know that crontab service has the "@reboot" schedule that would start a script during your system startup time? This is handy if you want something to run right after your system has stared. Try this:

# crontab -e
@reboot $HOME/crontab/runcmd.sh /apps/start-myapp.sh

The disadvantage of this vs the rc.d scripts are you do not have control on when the system shutdown("stop") state. So if your app doens't need to clean up during shutdown, but only care to start when during reboot, this would be an easy option.

Thursday, June 5, 2014

A simple cron wrapper script with logging

When working with crontab service, one thing I often need is to capture the ouput of the job. Having the job script aware of this output and logging is tedious, and often make the script harder to read. So I wrote a shell wrapper that will redirect all job script's STDOUT into a log file. This way I can inspect it when a job has run and the job script can just focus on the task itself.

# file: runcmd.sh
# Helper/wrapper script to run any command in the crontab env. This script will ensure
# user profile script is loaded and to log any command output into log files. It also
# ensure not to print anything to STDOUT to avoid crontab system mail alert.
#
# NOTE: be sure to pass in absolute path of the command to be run so it can be found.
#
# Usage:
#   ./runcmd.sh find $HOME/crontab/test.sh            # Simple use case
#   LOG_NAME=mytest ./runcmd.sh $HOME/crontab/test.sh # Change the log name to something specific
#

# Options
DIR=`dirname $0`
CMD="$@"
CMD_NAME=`basename $1`
LOG_NAME=${LOG_NAME:=$CMD_NAME}
LOG="$DIR/logs/$LOG_NAME.log`date +%s`"

# Ensure logs dir exists
if [[ ! -e $DIR/logs ]]; then
        mkdir -p $DIR/logs
fi

# Run cron command
source $HOME/.bash_profile
echo "`date` Started cron cmd=$CMD, logname=$LOG_NAME" >> $LOG 2>&1
$CMD >> $LOG 2>&1
echo "`date` Cron cmd is done." >> $LOG 2>&1


With this wrapper, you can run any shell script and their output will be recorded. For example this job script below will clean up the logs accumulated in our logs folder.

Note that the wrapper will also auto source the ".bash_profile". Often this this is needed if your job script expect all the env variables you already have setup in your login shell scripts.

# file: remove-crontab-logs.sh
DIR=`dirname $0`/logs
echo "Checking and removing logs in $DIR"
find $DIR -type f -mtime +31 -print -delete
echo "Done"


Now in the crontab file, you may run the job script like this:

# Clean up crontab logs
@montly $HOME/crontab/runcmd.sh $HOME/crontab/remove-crontab-logs.sh



Wednesday, June 4, 2014

A simple MySQL daily backup script

Using the MySQL export script I've showed from last post (assume you saved it in a file named "$HOME/database-export.sh"), you may now perform a daily backup with your crontab service like this.

#file: mysql-backups.sh
DIR=`dirname $0`
echo "Backup DB with export script."
$DIR/database-export.sh


echo "Moving exported files into backup dir."

DB_BAK_DIR=$HOME/mysql-backups/`date +%a`
echo "Removing old file (if exists) and saving new backup into $DB_BAK_DIR"
if [[ -e $DB_BAK_DIR ]]; then
  rm -fv $DB_BAK_DIR/*
else
  mkdir -p $DB_BAK_DIR
fi
cp -v $DIR/*.sql $DB_BAK_DIR


This script should create a daily folder under your home directory, for example like "$HOME/mysql-backups/Mon", "Tue", "Wed" etc. It should save up to 7 days in a week if you run this with a daily cron job.

# crontab -e
@daily $HOME/mysql-backups.sh > /dev/null 2>&1

This is not the most robust way of backing up your DB, but it's a simple solution if you just want something quick up and running without worry too much.

Saturday, May 31, 2014

How to export and import MySQL database

You can export the MySQL database with schema table definitions and data separated. Here is a simple bash shell script that will export an database.

# file: database-export.sh
DB_NAME=mydb
DB_SCHEMA_FILE=mydb.sql
DB_DATA_FILE=mydb-data.sql
DB_USERNAME=root
DB_PASSWORD=secret

# export schema only from mysql
 

echo "`date` Exporting database $DB_NAME schema to $DB_SCHEMA_FILE"
mysqldump --no-data --routines -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME > $DB_SCHEMA_FILE

# export data only from mysql
echo "`date` Exporting database $DB_NAME data to $DB_DATA_FILE"
mysqldump --single-transaction --quick --no-autocommit --no-create-info --extended-insert=false -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME > $DB_DATA_FILE


echo "`date` Done."


The schema export with "--routines" will export stored proc as well. When exporitng data you have to be little more careful. The "--single-transaction" will ensure your data is good. The "--no-autocommit" can really speed up your import later on certain OS.

Now to import these files back, you can create an empty database and then run the following.

# file: database-import.sh
# import schema and data into mysql
echo "`date` Importing Schema $DB_SCHEMA_FILE into $DB_NAME"
mysql -f -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME < $DB_SCHEMA_FILE
echo "`date` Importing DATA $DB_DATA_FILE into $DB_NAME"
mysql -f -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME < $DB_DATA_FILE

echo "`date` Done."


The import with "-f" option will allow you to continue even if you already have duplicated data in the tables; it will simply ignore and continue.

These two combo commands will allow you to quicly backup and restore a MySQL database.

Enjoy.

PS: I have come to learn that "mysqldump" with procedures will insert an extra line like "DEFINER 'user'@'hostname'", and it may create problem if you use different users to export/import or even run the application! Unfortunately there is no exclude from the "mysqldump" tool. So you have to manually strip that off if that's creating a problem for you.

mysqldump --no-data --routines -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME | perl -pi -e 's/DEFINER="\w+"@"\w+" //g' > $DB_SCHEMA_FILE

You can vote the issue here: http://bugs.mysql.com/bug.php?id=24680