Peter Banda's shelter

I’m happy to announce that Apr 28th COEL framework became open-source as defined by the standard Apache Licence 2.0.

Check out the code at https://github.com/peterbanda/coel. If you want to contribute to the project contact me at mail@peterbanda.net.

Recently I was trying to set up a MySQL database for one of my projects. The major difficulty I’ve faced is that MySQL apparantly does not provide any support for Linux (user-defined) variables… or maybe I’m just lame since I was not able to find that. So after I’ve spent a couple of hours browsing and cursing, I decided to write a bash script that parses a given SQL file, replaces (evaluates) all ${..} ocurrences with the values of Linux variables, and finally runs the adjusted SQL.

It worked well on Ubuntu 11.10 with MySQL 5.1.63.

If you want to use it replace the assigments of all DB variables such as DB_HOST and DB_USER in the following bash script (create_DB.sh) with your own setting.

#!/bin/bash

# The name of the script
SCRIPT_NAME=create_DB.sql

# Variables
DB_HOST=localhost
DB_PORT=3306
DB_ADMIN=root
DB_USER=dbadmin
DB_USER_PASSWORD=xxxx
DB_NAME=db

# Collect variables and substitute
for var in `grep -o "\\$*{\([^{]*\)}" $SCRIPT_NAME`
do
val=`eval echo ${var}`
vars+=("-e s/$var/$val/g")
done

# Run the SQL script
sed "${vars[@]}" $SCRIPT_NAME | sudo -u $DB_ADMIN mysql -v -p -h $DB_HOST -P $DB_PORT

Here is the referenced MySQL script (create_DB.sql):

-- CREATE USER
CREATE USER '${DB_USER}'@'%' IDENTIFIED BY '${DB_USER_PASSWORD}';</code>

-- CREATE DB
CREATE DATABASE IF NOT EXISTS ${DB_NAME} CHARACTER SET utf8;

-- GRANT PRIVILEDGES
GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'%' WITH GRANT OPTION;

eNjOY!