Scripting MySQL database setup for Rails with Bash

I am working a bash script to set up a complicated application. As part of that setup I need to run a series of SQL commands to create the initial users and databases this app will require.

It turns out you can get mysql to execute arbitrary statements from the command line using the -e switch but that would be both ugly and tedious for a lot of statements. Sounds like a job for heredocs! Lets try it out:

mike@railsdev:~$ read -d ” test <<‘EOT’
create database test;
select 1;
EOT

mike@railsdev:~$ mysql -u root -ppassword -e “$test”
+—+
| 1 |
+—+
| 1 |
+—+

Perfect! I love it when things work the way you expect. On to useful stuff:

read -d ” MYSQL_SETUP <<'EOF'
create database myapp_test;
create database myapp_development;
create database myapp_production;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'myapp_password';
grant all privileges on myapp_production.* to appuser@localhost;
grant all privileges on myapp_test.* to appuser@localhost;
grant all privileges on myapp_development.* to appuser@localhost;
EOF

mysql -u root -ppassword -e "$MYSQL_SETUP"

And then use the same trick to write a nice new database.yml for the app to use those credentials:

read -d ” DB_CREDENTIALS <<'EOF'
production:
adapter: mysql
database: myapp_production
pool: 5
username: appuser
password: myapp_password

development:
adapter: mysql
database: myapp_development
pool: 5
username: appuser
password: myapp_password

test:
adapter: mysql
database: myapp_test
pool: 5
username: appuser
password: myapp_password
EOF

read -d '' DB_CREDENTIALS < /var/www/myapp/config/database.yml

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s